2.3 System of Four Annuity Variables

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97

Problem. There is a tight connection between all of the inputs and output to annuity valuation. Indeed,

they form a system of four annuity variables: (1) Payment, (2) Discount Rate / Period, (3) Number of

Periods, and (4) Present Value. Given any three of these variables, find the fourth variable.

Solution Strategy. Given any three of these variable, we will use as many equivalent ways of solving for

the fourth variable as possible. In solving for the Payment, use the formula and PMT function. In solving

for the Discount Rate / Period, use the RATE function. In solving for the Number of Periods, use the

NPER function. In solving for the Present Value, use a Time Line, formula, and the PV function.

FIGURE 2.3 Spreadsheet for Annuity - System of Four Annuity Variables.

How To Build Your Own Spreadsheet Model.

1. Start with the Present Value Spreadsheet, Then Insert and Delete Rows. Open the

spreadsheet that you created for Annuity - Present Value and immediately save the spreadsheet

under a new name using the File | Save As command. Select the range A7:A17 and click on

Insert | Rows. Select the cell A25, click on Edit | Delete, select the Entire Row radio button

on the Delete dialog box, and click on OK. Select the range A26:A27, click on Edit | Delete,

select the Entire Row radio button on the Delete dialog box, and click on OK.

2. Inputs. Enter the inputs in the range B4:B7.

3. Payment. The formula for the Payment = (Present Value) / ((1 - ((1 + Discount Rate/Period) ^ (-

Number of Periods))) / (Discount Rate/Period)). Enter =B7/((1-((1+B5)^(-B6)))/B5) in cell B10.

The Excel PMT function can be used to calculate an annuity payment using the following format

=PMT(Discount Rate / Period, Number of Periods, -Present Value, 0). Enter =PMT(B5,B6,-

B7,0) in cell B11.

4. Discount Rate / Period. The Excel RATE function can be used to calculate the discount rate /

period for an annuity using the following format =RATE(Number of Periods, Payment, -Present

Value, 0). Enter =RATE(B6,B4,-B7,0) in cell B14.

5. Number of Periods. The Excel NPER function can be used to calculate an annuity payment

using the following format =NPER(Discount Rate / Period, Payment, -Present Value, 0). Enter

=NPER(B5,B4,-B7,0) in cell B17.

We see that the system of four annuity variables is internally consistent. The four outputs in rows 10

through 26 (Payment = $80.00, Discount Rate / Period = 6.0%, Number of Periods = 5, and Present Value

= $336.99) are identical to the four inputs in rows 4 through 7. Thus, any of the four annuity variables can

be calculated from the other three in a fully consistent manner.