The Mathematics of Money

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

gross pay amounts could have any extra decimal places in it. Of course, it doesn’t hurt

anything to tell the program to round it, and you may want to get in the habit of including

rounding of any formulas whose answers should be dollar amounts.

While failing to actually round did not make any difference in this payroll example, in a

large spreadsheet that involves many repeated calculations, rounding can become an issue.

For that reason, from here on in we will adopt the practice of rounding all formulas that

calculate money values.

Illustrating Compound Interest with Spreadsheets

In Chapter 3, we fi rst looked at compound interest by using a table and watching the account

value grow with each compounding. Then we developed the compound interest formula,

and so we had a much more effi cient way of getting from present value to future value, and

we abandoned the compounding-by-compounding table approach.

When we just use the formula, though, we lose the ability to see how that growth

occurs. While the huge fi nal account values that compound interest can create over time

should no longer be surprising to us, it was probably hard to believe at fi rst, which is

why having a table that shows the growth year by year was helpful to convince us that

the fi gures really were correct. This is of course useful if you need to convince someone

else that a fi gure that seems unrealistic is actually correct. Also, if we want to be able

to see where the account value is at different points along the way, a table makes it easy

to see, while getting those answers with the formula would require using the formula over

and over again.

Of course, creating these sorts of table by hand is tedious, but it is not too hard to do with

a spreadsheet. In the following example, we will use a spreadsheet to create such a table.

Example 5.1.3 To m deposited $5,000 at 7% compounded annually for 4 years.

Use a spreadsheet to fi nd his future value and illustrate the compound growth by

showing the interest earned with each compounding.

Our goal here is to create a table similar to the ones we saw at the start of Chapter 3. So

as a starting point, we will want to label columns for time, starting balance, interest earned,

and ending balance:

1

2

3

4

A B C D

Time Starting Balance Interest Earned Ending Balance

Yo u may need to adjust the column widths to fi t these labels.

We should then have rows for each year, so we will want to label our rows for time 1, 2, 3,

and so on. We could do this by typing that into each row. But we can avoid all that typing by

use of formulas. We will enter 1 in cell A2, and then, since the cell below it should be 1 more,

we can enter into that cell the formula “
A21”. Then, we copy this formula, highlight cells

A4 through 5, and paste into those cells. The result should be:

1

2

3

4

A B C D

Time

1

2

3

Starting Balance Interest Earned

5 4

Ending Balance

5.1 Using Spreadsheets: An Introduction 215