The Mathematics of Money

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

An interest rate of 9% reaches the goal, but overshoots it by quite a bit. Bryce would probably

be fi ne with having more money, but we don’t want to tell him he needs 9% when in fact he

doesn’t need quite that much. We know the needed rate lies somewhere between 8% and

9%. So by trial and error, we try different rates to see what we get, adjusting upward when

the result is too low, and downward when it is too high.

At 8.5%, we get $977,074.09 in year 35. At 8.75%, the result is $1,046,513.97. The 8.5%

result was a bit closer, so we’ll try something between the two, maybe a bit closer to 8.5%.

Tr ying 8.6% we get $1,004,258.77, which is awfully close to the target. Moving down just

slightly to 8.59% we get $1,001,505.37. Dropping down just a bit more to 8.58% we get

$998,759.80

Unless we take our interest rate out to more than the customary two decimal places, we

won’t hit $1,000,000 exactly. Both 8.58% and 8.59% give results that are as close to the

target as one could reasonably expect; 8.58% gives a result that is closer, but it falls a little

bit short, which might make 8.59% the better call. We can argue back and forth for either

answer, but there is not much difference between them, and in any case the answer is

approximate anyway.

For a fi nal answer: Bryce needs to earn somewhere between 8.58% and 8.59%.

The whole trial-and-error approach to Example 5.4.3 may seem a little rough, but it is actu-

ally a perfectly appropriate and highly effective solution method. Our spreadsheet is set up

in a way that makes changing the rate easy, and so even though this approach is not exactly

elegant, it is still an effi cient way of arriving at the answer we needed.

What about solving for interest rates with a present value? This can be accomplished in

a very similar way.

Example 5.4.4 A contractor is offering a payment plan for home improvement

projects. The contractor is advertising that with its plan, you can fi nance $15,000

worth of improvements for $250 a month for 10 years. What is the interest rate?

We start with an amortization table, plugging in $15,000 for the initial balance, $250 for the

payment, and a guess for the interest rate. Using these values with an 12.5% guess, we get:

Rows Omitted

182 180 $250.00 -$349.18 $599.18 -$34,120.86

181 179 $250.00 $343.01 $593.01 $33,521.68

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

2

3

A B C D

Month Payment To Principal Ending Balance

1$$250.00 93.75 $14,906.25

To Interest

$156.25

4 2$94$250.00 $155.27 .73 $14,811.52

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

E

We are interested in the balance at the end of the 180th month, because we know that when

we have found the correct rate this balance should be zero (or at least as close as we can

get to zero with a two decimal place interest rate). At 12.5%, the $250.00 monthly payment

would more than pay off the loan; the actual rate must therefore be quite a bit higher.

Yo u probably have already noticed one thing that is going to be annoying about trying different

interest rates and checking for the result: since your computer monitor can’t display 182 rows

on a single screen, you have to scroll down to see what happens in the 180th month. You can

get around this diffi culty by hiding the rows in between that you don’t need to see. Click on any

cell in the 5th row, hold the mouse click down and scroll down to the 181st row. Then, with this

section of the spreadsheet highlighted, choose Format R ow Hide. Those rows will now

be hidden; they are still there, but don’t display on the screen, so you don’t have to scroll past

them to get to the row you actually want to see.

We now need to try a higher rate, and on the basis of the result at 12.5% it seems like we

need to try something much higher. There is no way of knowing precisely how much higher.

5.4 Solving Annuity Problems with Spreadsheets 237