Which of the following formulas correctly uses the PV function in Excel to calculate the present value of an investment with an annual interest rate of 6% (entered in cell B2), 10 periods (entered in cell B3), and a payment of \$1,000 per period (entered in cell B4), assuming payments are made at the end of each period?

• A. =PV(B2, B3, -B4, 0, 1)
• B. =PV(B2, B3, -B4)
• C. =PV(B2, B3, B4, 0, 1)
• D. =PV(B2, B3, B4)

Answer: (B)

Verified step by step guidance

1. Step 1: Understand the PV function in Excel. The PV function calculates the present value of an investment or loan based on a constant interest rate, number of periods, and periodic payments. The syntax is: =PV(rate, nper, pmt, [fv], [type]).
2. Step 2: Break down the parameters: 'rate' is the interest rate per period (in this case, B2, which is 6%), 'nper' is the total number of periods (B3, which is 10), 'pmt' is the payment made each period (B4, which is \$1,000). The optional 'fv' is the future value (assumed to be 0 if omitted), and 'type' specifies when payments are made (0 for end of period, 1 for beginning).
3. Step 3: Analyze the problem. Since payments are made at the end of each period, the 'type' parameter should be 0. If the 'type' parameter is omitted, Excel assumes payments are made at the end of the period by default.
4. Step 4: Consider the sign convention. In Excel, cash outflows (payments) are represented as negative values, so the 'pmt' parameter (B4) should be entered as -B4 to reflect this.
5. Step 5: Identify the correct formula. Since the future value (fv) and type are optional and payments are made at the end of the period, the correct formula is =PV(B2, B3, -B4), which omits the optional parameters.