The Mathematics of Money

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

(a) How long will it take to pay off the loan if they only pay the scheduled $845.76?

(b) How long will it take to pay off the loan if they make the extra payments?

To answer (a), we can set up an amortization table for Ted and Kirsti, using their current

balance, interest rate, and payments. We do not need to worry about what their original

balance might have been, since we are interested only in the future of their loan, not its past.

Rows Omitted

174 172 $845.76 $2.27 $843.49 -$422.85

173 171 $845.76 $6.81 $838.95 $420.64

ACDEB

1 Rate: 6.49% Initial Balance: $94,372.57

2

3

A B C D

Month Payment To Principal Ending Balance

1 $845.76 $335.36 $94,037.21

To Interest

$510.40

4 2 $845.76 $508.58 $337.18 $93,700.03

5 3 $845 76 $506 76 $339 00 $93 361 03

E

By the same reasoning used in the previous example, we conclude that their loan will be paid

off after 171 more payments of $845.76 and a 172nd payment of $422.91.

To answer (b) we just need to adjust the payments in the table we created for part (a). We

change the fi rst payment to $8,200 ($7,000 plus $1,200). Since the spreadsheet is set up

to copy the payments to all future ones, this makes all of the payments $8,200. But directly

entering $1,200 for the second payment does not affect the fi rst, while making all subse-

quent payments $1,200. To return Ted and Kirsti to their $845.76 payment, we change the

13th payment, which also changes all of the subsequent ones. The result looks like this:

Rows Omitted

1 Rate: 6.49% Initial Balance: $94,372.57

2

3

A B C D

Month Payment To Principal Ending Balance

1 $8,200.00 $7,689.60 $86,682.97

To Interest

$510.40

4 2 $1,200.00 $468.81 $731.19 $85,951.78

5 3 $1 200 00 $506 76 $339 00 $93 361 03

E

18 16 $845.76 $417.24 $428.52 $76,718.51

19 172 $845 76 $411 92 8433 84 -$76 287 67

17 15 $845.76 $419.54 $426.22 $77,147.03

16 14 $845.76 $421.88 $423.93 $77,573.25

Rows Omitted

144 142 $845.76 $0.23 $845.53 -$802.21

143 141 $845.76 $4.78 $840.98 $43.32

ACDEB

From this, we see that the 142nd payment would be the last, and that payment would only

need to be $43.55.

Negative Amortization

Even though we can now mathematically handle any payment schedule for a given loan,

there is usually a practical limit to what we can do with actual payments. While you are

usually allowed to pay more than the scheduled payment, you are not normally allowed

5.3 Amortization Tables with Spreadsheets 231