The Mathematics of Money

222 Chapter 5 Spreadsheets

Building a Future Value Spreadsheet Template

Both in working through the examples of this (and the following) sections and in doing the

homework, we will be creating a lot of future value spreadsheets. While it is of course fi ne

to create a new spreadsheet from scratch for each problem we work, we can save a lot of

pointless and redundant work by saving a future value spreadsheet with the basic features

required, and then modifying that basic spreadsheet as needed for each problem.

If you take the spreadsheet we created in Example 5.2.1 and copy the last row down to

another 30 or 40 more rows, it will serve as a good basic template for future value spreadsheets.

It is highly recommended that you do this before continuing with this section.

Spreadsheets for Nonannual Annuities

Suppose we want a spreadsheet to illustrate an annuity whose payments are not annual.

The approach is essentially the same, but we have to be careful to make sure that we don’t

mistakenly apply the entire annual interest rate every period. In other words, don’t forget

the T in PRT.

Example 5.2.2 Use spreadsheets to fi nd the future value of an annuity of $83.33

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

We start from the Example 5.2.1 spreadsheet as a template, making the following changes:

Change the header of column A to read Month instead of Year

Change the payment amount in D3 to 83.33 instead of 1000.

Change the formula in C2 to 
ROUND(B2*.08*1/12,2)

We also need to change the interest calculation in all the other cells of column C. But we

have another problem as well; since this is monthly, to get to 8 years we need n 
 8(12) 


96 rows, more than we have in the template spreadsheet to start off.

We can attack both of these issues at the same time though, since if we copy more rows, we

can copy them with the “new” interest formula.

We don’t want to copy the fi rst row, however, since it is special in that its Starting Balance

entry is not a formula the way it is in other rows. So we will do the following:

Copy cell C2 to C3.

Highlight row 3, copy, and then paste the contents of row 3 as far down as (at least) row 97.

This will overwrite the existing rows so that they contain the new interest formula.

The resulting spreadsheet should look something like this one:

Rows Omitted

1 Month Starting Balance Interest Earned Payment Ending Balance

2

3

A B C D

1 $0.00 $83.33 $83.33

2 $0.56 $83.33 $167.22

$0.00

$83.33

4 3$$167.22 1.11 $83.33 $251.66

E

97 96 $10,998.61 $73.32 $83.33 $11,155.26

96 3 $10,998.61 $73.32 $83.33 $11,155.26

So we conclude that the future value would be $11,155.26.

We have not shown all of the rows of this spreadsheet because the space required to show

them would be so large, and because we really are interested only in the answer from the

last row anyway. One major disadvantage of using spreadsheets for nonannual annuities

is the number of rows that they usually require. These extra rows don’t require any extra

effort on our part—the computer is doing all of the heavy lifting—but can make a spread-

sheet awkward to work with.

Notice that Example 5.2.2 is really the same as Example 5.2.1, except that the payments

have been changed from annual to monthly. Not surprisingly, the future value with the

•

•

•

•

•