The Mathematics of Money

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

monthly payments is not all that far off of the future value with annual payments; getting

the money in a bit sooner and having interest compound more often leads to a larger future

value, but not that much larger. Since nonannual spreadsheets are so awkwardly large,

and because the results are so close, it is common practice to use annual payments with

future value spreadsheets, especially when the purpose is a prediction or a projection.

So, for example, even if it were an annuity of $83.33 per month we wanted to calculate

and illustrate with a spreadsheet, we might choose to instead treat it as though the total

$83.33(12) 
 $999.96 (essentially $1,000) was paid annually.

There are, of course, situations where it would be inappropriate to do this. In the exer-

cises, you should set your spreadsheet up according to the payment schedule specifi ed in

the problem. You will, however, fi nd that many of the exercises and remaining examples

use annual payments.

Finding Future Values When the “Annuity” Isn’t

Why would someone want to go to the effort of creating a spreadsheet when we could have

just as well have found the future value with an annuity formula? One advantage of using

a spreadsheet is that it allows you to see how the payments and interest build the account

value. This can be especially helpful when the future value that will accumulate seems “too

good to be true”.

The annuity formula gives essentially the same end result, but it does nothing to illustrate

the account’s growth. We chose to use the annuity formulas for practical reasons—they

were a lot less work than the alternatives. Even though the row-by-row chronological

approach seemed logical and illustrated things nicely, we had to abandon it because of

the effort required to do something like this line by line with pencil, paper, and calculator.

Spreadsheets, though, allow us to create a line-by-line buildup of the future value with a

far more reasonable amount of effort.

A second advantage of using a spreadsheet will be illustrated in the following example.

Example 5.2.3 Rework Example 5.2.1, this time assuming that the account started

out with a $5,000 balance, and that the last three payments were increased to $2,000

instead of $1,000.

This new stream of payments is not an annuity, since the payments are not all equal. How-

ever, we can readily fi nd the future value simply by changing the appropriate cells in the

payment column. The required changes are these:

Change the starting balance for the fi rst year. Change cell B2 from 0 to 5,000.

Change the payments for the last 3 years. Since the spreadsheet is set to assume that

each year’s payment is the same as the previous year’s, if we change cell D7 from

1,000 to 2,000, this will change the payments for the last 3 years.

The resulting spreadsheet should look like this:

1

2

3

A C D

Payment

$1,000.00

$1,000.00

B E

Year

1 $5,000.00 $400.00 $6,400.00

2 $6,400.00 $512.00 $7,912.00

Starting Balance Interest Earned

4 3 $7,912.00 $632.96 $1,000.00 $9,544.96

5 4 $9,544.96 $763.60 $1,000.00 $11,308.56

6 5 $11,308.56 $904.68 $1,000.00 $13,213.24

7 6 $13,213.24 $1,057.06 $2,000.00 $16,270.30

8 7 $16,270.30 $1,301.62 $2,000.00 $19,571.92

9 8 $19,571.92 $1,565.75 $2,000.00 $23,137.67

Ending Balance

(If your spreadsheet includes rows beyond these, they are harmless and can be ignored.)

So the future value comes to $23,137.67.

•

•

5.2 Finding Future Values with Spreadsheets 223