The Mathematics of Money

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

Example 5.1.4 To m deposited $5,000 at 7% compounded annually for 40 years. Use

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

the interest earned with each compounding.

This is the same problem as Example 5.1.3, just for 36 more years. However, carrying the

table out 36 years further is not much work. Each year works essentially the same as the one

before. So highlight cells A5 to D5 and copy. Then, highlight from A5 to D41 and paste. This

will add all the additional rows needed. To save space, rather than show the entire table, we

will show only the last row here.

41

A B C D

40 $69,974.20 $4,898.19 $74,872.39

Two things deserve comment before we leave this example. First, in Example 5.1.4, with-

out much explanation the solution said to take the table as far as row 41. The reason for

going to row 41 was that, since the first row is a header, the number of the row is always

one ahead of the number of the year. Row 3 contained year 2, row 4 contained year 3, and

so on. For that reason, we could have seen that year 40 would be in row 41. However, if we

didn’t notice that it would not have been too big of a deal. If you are unsure about how far

to copy, you can simply copy “a ways” down. If you copy too many rows, it is no problem;

you can either delete or ignore the extras. If you do not copy enough, it is a simple matter

to just copy some more. It never is a bad thing to first figure out the exact number of rows

you need, but it is not something that is worth worrying too much about either.

Secondly, if you go ahead and fi nd the future value of $5,000 at 7% compounded

annually for 40 years, using our compound interest formula, you certainly would have

every reason to expect the result to be $74,872.39. Yet, it isn’t! Calculating the future value

with the compound interest formula gives $74,872.29. The reason for this discrepancy

lies in the rounding. In our spreadsheet calculation, we rounded the interest every year.

But there was no such year-to-year rounding in the formula, and so it is possible that the

formula-calculated value will differ from the spreadsheet-calculated one.

If you change the formulas in the spreadsheet, taking out the rounding instructions, your

fi nal answer will agree with the formula’s. Why then did we do the rounding? In reality, when

a bank pays you interest, it pays you interest in dollars and cents—rounded amounts. A strong

case can be made that $74,872.39 is the better answer to this question, since what you see in

your spreadsheet is a simulation of what actually would happen in an actual account.

In any case, though, as we have seen repeatedly in the past, small discrepancies due to

rounding are not enough to worry about. The difference between these two answers raises a

question that we should be able to answer, and we should also recognize that it is not a big deal.

However, it is a good thing to be aware of this consideration, since, as we’ll see in the rest of the

chapter, discrepancies due to rounding often show up in places such as amortization tables, and

when they do it is important to be able to recognize and explain the source of the discrepancy.

More Formatting and Shortcuts

In the examples we have done so far, we have worried about formatting only so far as it

affects the ability to reasonably read the spreadsheet. Making it pretty has not really been

a goal. There is plenty more that we can do with formatting. We can adjust fonts, highlight

cells, use colors, and so on to make the table’s appearance more professional and visu-

ally appealing. However, as we stated at the outset, our goal in this chapter is only to use

spreadsheets as a tool for calculations. We will limit our formatting efforts only to the

minimum needed to make the table reasonably easy to understand. You may, though, want

to explore and play around with some other formatting options on your own.

Excel and other spreadsheet programs also offer a number of shortcuts, quicker ways

to do many of the things we did above without having to navigate through menus. There

are, for example, tricks for copying and pasting in Excel that can accomplish what we’ve

done here with fewer mouse clicks. Your instructor may have shown you some of these, or

5.1 Using Spreadsheets: An Introduction 217