The Mathematics of Money

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

At the time of this writing, the current version of Excel’s Goal Seek feature is not com-

pletely reliable. It will often work, but may crash unpredictably, and so it cannot be entirely

relied on. At best, it is worth trying on any given problem, but if it fails to work, you will

have to revert to the trial-and-error approach.

Changing Interest Rates

It is possible, by adapting the techniques of Sections 4.6 and 4.7, to deal with changing

interest rates in future value calculations, but doing it that way gets tedious very quickly.

Trying to work through those sorts of calculations for present values using the annuity

formulas is even worse. With a little adjustment, our spreadsheets can handle these types

of problems effi ciently and effectively.

We will illustrate this with a present value example; it should be clear from this present

value example how to make similar adjustments to a future value spreadsheet.

Example 5.4.5 Viveca borrowed $75,000 to start up a small business. The loan

carries a variable interest rate. For the fi rst year, the rate is 4.99%. In the second year,

the rate increases to 6.99%. Thereafter, the rate will be based on a national index

of interest rates, but it is guaranteed never to go above 9.99%. She will be required

to make payments of at least $2,000 each quarter. Assuming that she makes the

minimum quarterly payment, and that the interest rate will always be the highest it can

be, how long will it take her to pay off the loan?

We start with a basic amortization spreadsheet, and make a few changes.

We insert two rows at the top of the sheet to allow room for more than one interest

rate.

In the top rows we insert each of the interest rates that we will be using.

We change the header of the time column from Month to Quarter.

The header now looks like this:

1 1st Rate: 4.99% Initial Balance: $75,000.00

2

3

A B C D

2nd Rate: 6.99%

3rd Rate: 9.99%

4 Quarter Payment To Interest To Principal Ending Balance

E

We now adjust the interest formulas. In the fi rst four rows, the formula should be set to use

the fi rst rate and calculate the interest quarterly. So in cell C5 we change the formula to

“
Round(D1*$B$1/4,2)”; in cell C6 we change it to “
Round(E5*$B$1/4,2)” and copy this

formula into cells C7 and C8 to complete the fi rst year.

In cell C9 we cover the fi f th quarter, which is in the second year and so needs to refl ect the

second-year rate. So we set cell C9 to “
Round(E8*$B$2/4,2)”, and copy that into cells C10

through C12. Likewise, we set C13 to be “
Round(E12*$B$3/4,2)” and copy that formula

into all the remaining cells below it.

Once we have done this, we simply read the spreadsheet the same way as we have in prior

examples.

•

•

•

5.4 Solving Annuity Problems with Spreadsheets 239

FIGURE 5.2