Page 352 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 309
FIGURE 16.14
This worksheet contains formulas to calculate annuities.

TABLE 16.4 Creating Formulas for Financial Applications 16
The Annuity Calculator Worksheet

Cell Formula Description
B4 None (input cell) Initial investment (can be 0)
B5 None (input cell) The amount deposited on a regular basis
B6 None (input cell) The number of deposits made in 12 months
B7 None (input cell) TRUE if you make deposits at the beginning of period;
FALSE otherwise
B10 None (input cell) The length of the investment, in years (can be fractional)
B13 None (input cell) The annual interest rate
B16 =B4 Displays the initial investment amount
B17 =B5*B6*B10 Calculates the total of all regular deposits
B18 =B16+B17 Adds the initial investment to the sum of the deposits
B19 =B13*(1/B6) Calculates the periodic interest rate
B20 =FV(B19,B6*B10,-B5,-B4,IF(B7,1,0)) Calculates the future value of the investment
B21 =B20-B18 Calculates the interest earned from the investment

309