The Mathematics of Money

238 Chapter 5 Spreadsheets

We’ll just have to try something and see how it works, basing our future guesses on how it

works out. Jumping up to 20%, we get:

Rows Omitted

182 180 $250.00 $250.00 $0.00 $15,000.00

ACDEB

1 Rate: 20.00% Initial Balance: $15,000.00

2

3

A B C D

Month Payment To Principal Ending Balance

1 $250.00 $0.00 $15,000.00

To Interest

$250.00

4 2 $250.00 $250.00 $0.00 $15,000.00

5 3$000$250 00 $250 00 $15 000 00

E

At 20%, a $250.00 monthly payment is just enough to cover the interest, making no progress

against the balance. So the rate must be lower, somewhere between 12.5% and 20.00%.

We continue the process of making educated guesses and refi ning them. It may take many

guesses, but eventually we discover that an interest rate of 18.77% leaves an ending balance

of $80.17, while 18.78% leaves a balance of $27.13. So we can conclude that the rate is

somewhere between 18.77% and 18.78%.

Using Goal Seek

Many versions of Microsoft Excel include a feature called Goal Seek, which can help in

solving these sorts of problems. In simple terms, what Goal Seek does is the same “guess-

and-check” approach that we used in the previous two examples; the advantage is that the

computer does all of the guessing and checking. (Depending on the version of Excel or

other spreadsheet program you are using, the instructions given below may not work; if

they don’t, consult your software’s manuals or ask your instructor about if, and how, you

can do the same thing with your program.)

Let’s return to Example 5.4.4, but now suppose that we want to work with a $275.00 monthly

payment. Obviously this will change the interest rate answer as well. If you take the spreadsheet

from that example with the 18.78% interest rate we found, but change the payment to $275.00,

your spreadsheet should look like the one shown here.

Rows Omitted

182 180 $275.00 –$373.57 $648.57 –$24,518.57

ACDEB

1 Rate: 18.78% Initial Balance: $15,000.00

2

3

A B C D

Month Payment To Principal Ending Balance

1$$275.00 40.25 $14,959.75

To Interest

$234.75

4 2$40$275.00 $234.12 .88 $14,918.87

5 3$41$275 00 $233 48 52 $14 877 95

E

Rows Omitted

182 180 $275.00 -$373.57 $648.57 -$24,518.57

ACDEB

1 Rate: 18.78% Initial Balance: $15,000.00

2

3

A B C D

Month Payment To Principal Ending Balance

1$$275.00 40.25 $14,959.75

To Interest

$234.75

4 2$40$275.00 $234.12 .88 $14,918.87

5 3$41$275 00 $233 48 52 $14 877 35

E

The large negative balance refl ects the fact that this payment at this interest rate would

more than pay off the loan. What we want is a zero balance in cell E182, and we hope to

fi nd it by changing the interest rate in cell B1.

From the Tools menu on the toolbar, select Goal Seek. This will cause the pop-up box shown

in Figure 5.2 (shown on the next page) to appear on your screen. The fi rst option in that box will

ask you to “Set Cell”. The cell we want to set is B182, so type that in. The next option asks “To

Value”. We want B182 to contain 0, so that is what we type into this box. The last option asks

“By changing Cell”. The cell we want to change is B1, so we type that in. If you then click on

“OK”, the program should quickly change cell B1 to the desired solution: 21.04%.