The Mathematics of Money

that the cell address “B1” should not change when we copy the cells down. This is done

by using a dollar sign in the cell address. By using “$B$1” instead of “B1”, the program

understands that no matter where we may copy the cell, the address of the interest

rate always stays in column B and row I. So instead the formula should be “
ROUND

(B3*$B$1*1,2)”. We can now copy this cell down the rest of the row.

Doing this creates a spreadsheet like this:

Rows Omitted

32 30 $309,932.07 $24,794.57 $6,237.34 $340,963.98

31 29 $281,420.95 $22,513.68 $5,997.44 $309,932.07

1 Rate: 8.00%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $0.00 $2,000.00 $2,000.00

Starting Balance

$0.00

4 2 $2,000.00 $160.00 $2,080.00 $4,240.00

5 3 $4 240 00 $339 20 $2 163 20 $6 742 40

E

So at 8%, Erika’s future value would be $340,963.98.

To fi nd the future value for 10% (or any other rate for that matter,) we can just change the

cell with the rate in it. Doing so for 10%, we get that Erika’s future value at that rate would

be $473,534.93.

Using the $ to indicate that the cell being referenced in a formula should not change as the

formula is copied to other locations is called an absolute cell reference. As the previous

example has illustrated, there are times where absolute references are necessary in a spread-

sheet. When you enter a formula into a spreadsheet that refers to other cells, it is a good

idea to ask yourself whether or not those cell references should change if the formula is

copied, so that you make the appropriate choice as to whether an absolute reference should

be used.

You may want to modify your future value spreadsheet template now so that the interest

rate is given as a cell reference, as we did in this example. While not essential, having the

interest rate so easily changeable can be an advantage.

The following exercises offer you the opportunity to get comfortable with using spread-

sheets to fi nd future values. (If you have previously covered Sections 4.6 and 4.7 you may

recognize a number of these exercises from those sections. You may want to compare the

solution techniques from those sections to the spreadsheet approach we are using here;

each approach has its advantages and disadvantages.)

Exercises 5.2 225

A. Finding Future Values Using Spreadsheets

1. Khalil deposits $5,000 annually into an investment account that earns 8.3% compounded annually. Assuming
   he keeps this up for 37 years, calculate his account’s future value (a) using the annuity formulas and (b) using a
   spreadsheet.


2. Cristina deposits $1,750 annually into an investment account earning 9.35%. How much will she have in this account
   in (a) 10 years, (b) 20 years, (c) 30 years, (d) 40 years, and (e) 50 years?

EXERCISES 5.2

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