The Mathematics of Money

224 Chapter 5 Spreadsheets

The future value found in Example 5.2.3 could also have been calculated by using the

techniques developed in Sections 4.6 and 4.7. But doing that would require us to break the

stream of payments into several separate buckets, calculate the future values separately,

and then add them all together. The effort required to set up and use a spreadsheet to do this

compares pretty favorably to that approach.

The advantage of a spreadsheet becomes more obvious when dealing with a stream of

payments that change frequently, as shown in the following example:

Example 5.2.4 Erika is setting up a retirement account. She plans to make a deposit

into this account every year, but, rather than invest the same amount each year, she

intends to deposit $2,000 this year and then increase her annual deposits by 4% each

year. If her account earns 7.25%, how much will she have in 30 years?

Even though there is a clear pattern to her deposits, Erika’s account is not even close to being

an annuity. Every year’s payment is different! Even though it is theoretically possible to fi nd

her future value without using spreadsheets, it would be a nightmare. Using a spreadsheet,

though, this is hardly any more trouble than if the payments all stayed the same.

We set up the spreadsheet just like the previous examples. For the fi rst payment, we enter

2,000. Then, for the next year’s payment we use the formula “
ROUND(1.04*D2,2)”. Then,

we can simply copy rows until we reach year 30. To save space, only the fi rst 2 years and the

last year are shown below:

Rows Omitted

1 Year Starting Balance Interest Earned Payment Ending Balance

2

3

A B C D

1 $0.00 $2,000.00 $2,000.00

2 $145.00 $2,080.00 $4,225.00

$0.00

$2,000.00

4 3 $4,225.00 $306.31 $2,163.20 $6,694.51

E

31 30 $276,539.29 $20,049.10 $6,237.34 $302,825.73

96 3 $252,253.47 $18,288.38 $5,997.44 $276,539.29

Erika’s future value is then $302,825.73.

From these examples, it should be clear that using spreadsheets for these sorts of

calculations can offer advantages over using formulas, especially when the payments

are not all exactly equal. The way we have set them up makes it comparatively easy to

make changes to the stream of payments. But, what if we also want to be able to change

the interest rate easily? The following example will illustrate a way to make interest rate

changes conveniently.

Example 5.2.5 Rework Example 5.2.4 to fi nd Erika’s future value if her account

earned (a) 8% and (b) 10%.

We could make this change by editing the formula in C2, changing it to “
ROUND

(B2*.08*1,2)” and then copying that formula all the way down. Then, to do 10%, we could

just do another change and copy. Instead, though, we will take a different approach and set

the spreadsheet up so that we can easily change the rate to whatever we want more easily.

To accomplish this, insert a new row above the column titles. In the new cell A1, enter the

text “Rate:”, and then in the new B1 enter the number 0.08. (You may want to format this

to look like a percent: Format Cells Number Percentage.) We will now change our

interest formulas so that instead of having a particular interest rate in them, they instead will

use whatever has been entered in cell B1.

At fi rst thought, it seems that the formula to put in the new C3 would be “
ROUND

(B3*B1*1,2)”. That would work fi ne in C3, but when we copy the formula down to the

next row, the spreadsheet would also move the address “B1” down a row, and look for

the interest rate in B2. This will obviously not work. We need a way to tell the spreadsheet

cf