The Mathematics of Money

Copyright © 2008, The McGraw-Hill Companies, Inc.

5.2 Finding Future Values with Spreadsheets

Spreadsheets can be effectively used to calculate future values for annuities, as the follow-

ing example will show:

Example 5.2.1 Use spreadsheets to fi nd the future value of an annuity of $1,000

per year for 8 years at an interest rate of 8%.

We will calculate this future value by setting up a spreadsheet that shows how the value of

the annuity grows with each payment, similar to the tables used in Section 4.2 when we fi rst

encountered annuities and their future values. In Section 4.2 we developed the future value

of an annuity in two different ways. We could use either the bucket or the chronological

approach here equally well, but since the chronological approach probably seemed more

natural, we will set up our spreadsheet that way. (In other words, we will set up our spread-

sheet showing how the account grows year by year.)

We start as follows:

Use the fi rst row for titles of columns for Year, Starting Balance, Interest Earned,

Payment, and Ending Balance

Set up the fi rst row of values by entering 1 in A2, 0 in B2, and 1000 in D2. In C2 enter

the formula “
ROUND(B2*.08*1,2)”. In E2 enter the formula “
B2C2D2”.

In the second row, enter the following formulas: In A3, enter “
A21”, in B3 enter

“
E2” (since the starting balance of year 2 is the same as the ending balance of

year 1), and in D3 enter “
D2”. Copy C2 into C3 and E2 into E3.

The result should look like this:

1

2

3

A C D

Payment

$1,000.00

$1,000.00

B E

Year

1 $0.00 $0.00 $1,000.00

2 $1,000.00 $80.00 $2,080.00

Starting Balance Interest Earned Ending Balance

The entries for the rows for years 3 to 8 will be set up in precisely the same way as for

year 2.

So we can simply copy this row into the six below to complete our task.

1

2

3

A C D

Payment

$1,000.00

$1,000.00

B E

Year

1 $0.00 $0.00 $1,000.00

2 $1,000.00 $80.00 $2,080.00

Starting Balance Interest Earned

4 3 $2,080.00 $166.40 $1,000.00 $3,246.40

5 4 $3,246.40 $259.71 $1,000.00 $4,506.11

6 5 $4,506.11 $360.49 $1,000.00 $5,866.60

7 6 $5,866.60 $469.33 $1,000.00 $7,335.93

8 7 $7,335.93 $586.87 $1,000.00 $8,922.80

9 8 $8,922.80 $713.82 $1,000.00 $10,636.62

Ending Balance

So the future value is $10,636.62.

Calculating the future value from the annuity formula as a check, we would get $10,636.63

as the future value. As with the compound interest spreadsheets in Section 5.1, the reason

for this slight difference once again lies in the rounding. In the spreadsheet we rounded the

interest calculation each year; this rounding doesn’t happen when we use the annuity formula.

These discrepancies are far too small to make any meaningful difference in the results.

•

•

•

5.2 Finding Future Values with Spreadsheets 221