The Mathematics of Money

236 Chapter 5 Spreadsheets

We set up an amortization table, and look for her account balance to hit the target value.

Rows Omitted

1 Rate: 8.00%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $4,315.77 $2,000.00 $54,268.77

Starting Balance

$47,953.00

4 2 $54,268.77 $4,884.19 $2,060.00 $61,212.96

5 3$5$61 212 90 509 17 $2 121 80 $68 043 93

E

Rows Omitted

25 23 $477,382.83 $42,964.45 $3,832.21 $524,179.49

24 22 $434,552.51 $39,109.73 $3,720.59 $477,382.83

25 23 $477,382.83 $42,964.45 $3,832.21 $524,179.49

24 22 $434,552.51 $39,109.73 $3,720.59 $477,382.83

1 Rate: 9.00%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $4,315.77 $2,000.00 $54,268.77

Starting Balance

$47,953.00

4 2 $54,268.77 $4,884.19 $2,060.00 $61,212.96

5 3$5$61 212 90 509 17 $2 121 60 $68 043 93

E

So we see that the target account value is reached in year 23.

Solving for Interest Rates

We have solved for time with present values in Section 4.3, and with future values in this

section. So far, however, we haven’t looked at the question of fi nding a needed interest rate.

As with time, algebraically manipulating the annuity formulas to fi nd a required interest

rate is not a practical goal, but spreadsheets can provide us the ability to handle this.

Example 5.4.3 Bryce has $28,500 in his retirement account, and he plans to

contribute $2,500 each year to this account. He wants to have $1,000,000 in this

account 35 years from now. What interest rate does he need to earn to reach this goal?

We start by setting up a spreadsheet to illustrate Bryce’s account. Since we don’t have an

interest rate, we will set the spreadsheet up with an educated guess. You could use anything

reasonable for this; for now, let’s set things up assuming 8%. We get:

Rows Omitted

37 35 $786,735.37 $62,938.83 $2,500.00 $852,174.20

ACB DE

1 Rate: 8.00%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $2,280.00 $2,500.00 $33,280.00

Starting Balance

$28,500.00

4 2 $33,280.00 $2,662.40 $2,500.00 $38,442.40

5 3$3$30 440 40 075 30 $2 500 00 $44 017 40

E

From this, we see that at an 8% rate, Bryce’s ending value falls short of the target. Fortu-

nately, though, we’ve set up our spreadsheet so that changing the interest rate only requires

changing one cell.

If we bump the rate up to 9% we get:

Rows Omitted

37 35 $1,026,215.61 $92,359.40 $2,500.00 $1,121,075.01

36 34 $939,188.63 $84,626.98 $2,500.00 $1,026.216.61

1 Rate: 9.00%

2

3

A B C D

Year Interest Earned Payment Ending Balance

1 $2,565.00 $2,500.00 $33,565.00

Starting Balance

$28,500.00

4 2 $33,565.00 $3,020.85 $2,500.00 $39,085.85

5 3$3$39 085 85 517 73 $2 500 00 $45 103 58

E