The Mathematics of Money

230 Chapter 5 Spreadsheets

We set up an amortization table for Javad’s loan just as in the previous example, using

$1,800 as the payment. Unfortunately, we don’t know how many rows we will need. We do,

though, know that it will be less than the 360 we would need for the full 30 years, so we’ll

use 360, since we know that will defi nitely be enough.

The result looks like this:

Rows Omitted

362 360 $1,800.00 -$4,958.93 $6,758.93 -$800,187.04

361 359 $1,800.00 -$4,916.94 $6,716.94 -$793.428.11

1 Rate: 7.50% Initial Balance: $172,500.00

2

3

A B C D

Month Payment To Principal Ending Balance

1 $1,800.00 $721.87 $171,778.13

To Interest

$1,078.13

4 2 $1,800.00 $1,073.61 $726.39 $171,051.74

5 3 $1 800 00 $1 069 07 $730 93 $170 320 81

E

The values in row 362 (for month 360) are absurd, though, showing negative interest and

an enormous negative balance. Since Javad’s higher-than-required payment would have

fi nished off the loan long before the 360th month, carrying the table out that far gives us

nonsensical values. We can, though, use this table to answer our original question. The

mortgage is paid off when the balance reaches $0, so we can scroll through the table to fi nd

the point where this happens. Rows 148 and 149 look like this:

148

149

$1,781.63

$1,792.76

146 $1,800.00 $18.37 $1,158.30

147 $1,800.00 $7.24 -$634.46

We see that if Javad pays the full $1,800.00 for the 147th payment, that would take his

mortgage balance below zero. From this we can conclude that his loan will be paid off entirely

by the time of his 147th monthly payment. We can also conclude, though, that he does not

need to make a full $1,800 payment in the last month to bring his balance to zero.

How much should his last payment be? We can determine this in either of two ways. Since

making the full payment would bring the balance to $634.46 below zero, his 147th payment

will need to be only $1,800.00  $634.46 
 $1,165.54. Another way of seeing this is to

note that his last payment needs to cover a balance of $1,158.30 from the prior month,

plus $7.24 of interest, for a total of $1,158.30  $7.24 
 $1,165.54. Either way the con-

clusion is the same: his last payment should be $1,165.54. You can verify this by changing

the 147th payment to $1,165.54 to get:

148

149

$1,781.63

$1,158.30

146 $1,800.00 $18.37 $1,158.30

147 $1,165.54 $7.24 $0.00

To answer the original question: It will take Javad 147 months to pay off the loan at this rate,

with his last payment being $1,165.54.

While this last example assumed a consistent payment schedule made from the beginning

of the loan, there is no reason why we couldn’t do the same sort of thing starting from some

point other than the loan’s beginning, or using an irregular payment schedule. The follow-

ing example will illustrate this.

Example 5.3.3 Ted and Kirsti owe $94,372.57 on their mortgage right now. Their

monthly payment is $845.76 and their interest rate is 6.49%. They have inherited some

money, and they are thinking about making a one-time $7,000 payment to reduce

their mortgage debt. They also expect to be able to pay $1,200 a month for the next

year, and then go back to paying $845.76 after that.

cf