The Mathematics of Money

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

D4 “
C4-B4”, and in E4 “
E3-D4”. The second month’s row can then be copied for the

months up to the 24th. The resulting spreadsheet will be:

Rows Omitted

26 24 $227.48 $1.62 $225.86 $0.13

25 23 $227.48 $3.22 $224.26 $225.99

1 Rate: 8.59% Initial Balance: $5,000.00

2

3

A B C D

Month Payment To Principal Ending Balance

1 $227.48 $191.69 $4,808.31

To Interest

$35.79

4 2 $227.48 $34.42 $193.06 $4,615.25

5 3 $227 48 $33 04 $194 44 $4 420 81

E

An amortization table is supposed to show how the loan is paid off in its entirety. For this

reason, we probably would have expected that the ending balance in the 24th month would

have been 0. Thirteen cents isn’t much, but it isn’t zero. Why isn’t it zero?

When we calculated the monthly payment, it didn’t come out to be exactly $227.48.

Actually, the payment comes out to be $227.4842828 each month for us to pay off the

entire loan over the 24 months. Of course it would be ridiculous to actually try to pay

$227.4842828, but rounding means that with each payment we are actually $0.0042828

short. This difference is less than a penny—not worth worrying about—but over the course

of 24 payments this tiny monthly shortfall adds up enough to actually appear. Most of

the time, we just ignore these small amounts, just as we ignore the fraction of a penny in

the monthly payment. In some cases, though, the fi nal payment actually will be adjusted

slightly, to make the fi nal balance come out to be zero. (This is sometimes legally required.)

In this case, if we raised the last payment to $227.61, the extra 13 cents of the payments

would take the balance to actually $0.00.

Sometimes, the end of an amortization table will show a negative balance instead of

a positive one. This happens when the payment is rounded slightly up; for example, if

the payment from a formula comes out to be $393.48867102 we would round that up to

$393.49, even though this means each payment is just a tiny bit more than it theoretically

should be. In that case, instead of raising the last payment to make everything balance out

to zero, we would lower it.

As recommended with future values, it is a good idea to save a basic amortization table as a

template to work from, so that you do not have to always create them from scratch. The spread-

sheet from Example 5.3.1 would be a good one to use as a template, though you might want to

add more rows before saving it (since most amortization tables will run more than 24 rows.)

Using Amortization Tables to Find Payoff Time

So far, we’ve used amortization tables to illustrate how a loan gets paid off, payment by

payment. When we set one up, we have already calculated the payment based on the loan’s

original balance, interest rate, and term. The table sheds light onto how the loan is paid off,

but it doesn’t really tell us anything that we didn’t already know.

But amortization tables can also be used as a tool to answer questions that we weren’t

able to before. In Chapter 4, we saw that our annuity formulas could be used to fi nd the

payment or present value, but manipulating the formula to solve for an interest rate or term

was not feasible. Setting up an amortization table with a spreadsheet, though, provides a

way to solve those sorts of problems. The following example will illustrate this.

Example 5.3.2 Suppose Javad took out a mortgage for $172,500. The term is

30 years, and the interest rate is 7.5%. The monthly payment is scheduled to be

$1,206.15 , but he fi gures he can pay $1,800 each month. How long will it take him

to pay off the loan if he does this?

5.3 Amortization Tables with Spreadsheets 229