The Mathematics of Money

240 Chapter 5 Spreadsheets

Rows Omitted

10 6$$2 000 00 1221 45 $778 55 $69 118 44

9 5 $2,000.00 $1,234.82 $765.18 $69,896.99

14 10 $2 000 00 $1 1678 46 $321 52 $386 885 08

13 9 $2,000.00 $1,686.32 $313.68 $67,206.60

Rows Omitted

Rows Omitted

88 84 $2,000.00 $4.81 $1,995.19 -$1,802.54

87 83 $2,000.00 $53.43 $1,946.57 $192.65

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

5 1 $2,000.00 $935.63 $1,064.37 $73,935.63

6 2$1$2 000 00 $935 63 064 37 $73 935 63

E

So we see that under these assumptions, Viveca will have the loan paid off in a bit less than

22 years. Of course, this assumes the minimum payment and the maximum interest rate. If

the rate stays below the maximum, or if Viveca pays more than the minimum, the loan will

not take nearly as long to pay off; we know how to work out the payoff time under any other

assumptions that we like. What we have here is a worst-case scenario. We conclude that it

will take at most 84 quarters (or 22 years) to pay off the loan.

Very Complicated Calculations

Of course, the more complicated the situation, the more complicated the spreadsheets need

to be to deal with it, but the methods we have been using here will allow us to manage

enormously complicated situations with a reasonable effort. We will work through one

such example here to illustrate how this can be done; the exercises provide the opportunity

to work out other situations.

Example 5.4.6 A high school alumni association has established a scholarship

fund for graduates of the school. The association plans to raise funds through an

annual campaign in each of the next 10 years, and then use the accumulated fund

to pay out $25,000 in scholarships each year. The fund presently has $38,536 in

it, and the leaders expect that they can raise $15,000 this year and increase the

amount raised by 4% each year. They believe that the money in the fund will earn

7.5% during the fundraising period, and 6% during the period when it is being used

for scholarships.

Under these assumptions, how long will the fund be able to pay out scholarships?

There are two parts to this problem. We need to find the future value of the accumu-

lation period, which will then become the present value of the scholarship payment

period.

First, the accumulation period. Working from one of our future value spreadsheets, we

work this out in a way similar to that shown in Example 5.2.4: