The Mathematics of Money

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

Rows Omitted

12 10 $285,565.66 $21,417.42 $21,349.68 $328,332.76

1 Rate: 7.50%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $2,890.20 $15,000.00 $56,426.20

Starting Balance

$38,536.00

4 2 $56,426.20 $4,231.97 $15,600.00 $76,258.17

5 3 $4 240 00 $339 20 $2 163 20 $76 742 40

E

Having found the accumulated future value, we now move to an amortization table for

the payments. We could use a separate spreadsheet, typing in the $328,332.76 as the

starting balance. However, there is an advantage to putting the amortization table in the

same spreadsheet as our future value table: if we want to work out some other “what-ifs”

and change the assumptions for the accumulation period, we would then need to calcu-

late the new future value and then type the value into the spreadsheet. If we combine

both tables in the same spreadsheet, we can use a cell reference to transfer the future

value to the amortization table, allowing it to change automatically when we change our

assumptions.

To do this, we’ll start with a basic amortization table, highlight it in its entirety, and copy.

Then, in the spreadsheet we used for the accumulation, we’ll go to cell G1 and paste. In J1

we will enter the formula “
E12”. Then we just need to change the headers to suit annual

payments and to describe the “use of a fund” scenario instead of the more common “paying

off a loan” one, and also adjust the interest calculation for annual rather than monthly pay-

ments. The result should look like this:

Rows Omitted

29 27 $25,000.00 $888.11 $24,111.89 -$9,309.98

1 Rate: 6.00% Initial Balance: $328,332.76

2

3

G H I J

Month Payment From Principal Ending Balance

1 $25,000.00 $5,300.03 $323,032.73

From Interest

$19,699.97

4 2 $25,000.00 $19,381.96 $5,618.04 $317,414.69

5 3$5$25 000 00 $19 381 96 618 04 $317 414 69

K

In the 27th year, the scholarship fund runs out of money under these assumptions. So we can

conclude that under these assumptions the fund will last 26 years. In the 27th year, paying

out the full $25,000 would drop the fund below $0, so there will only be enough money left

in that year to pay $25,000  $9,309.98 
 $15,690.02.

Of course, the answer we arrive at is only as good as the assumptions it is based on. If the

assumptions about fundraising, investment returns, or annual scholarship payouts differ

from the ones we used, the results will differ as well. Fortunately, though, we can make

those changes with not much effort because of the way we set up our spreadsheet.

The exercises that follow will present you with the opportunity to work out other, similar

problems on your own by using spreadsheets like the ones we have used in the examples.

Hopefully, this chapter has demonstrated the power of spreadsheets as a computational tool,

and has equipped you with the basic ability to work with them. We have actually, though,

barely scratched the surface. You may want to take advantage of further opportunities,

whether in academic coursework, workplace or continuing education training sessions, or

independent study, to strengthen your skills with this powerful business tool.

5.4 Solving Annuity Problems with Spreadsheets 241