In this section we present more complicated versions of Mario’s problem from section 1.7. We start by trying to determine whether a young girl’s parents are putting enough money aside to save for her college education. Here’s the problem:
• On her tenth birthday Linda Jones’s parents decide to deposit $4,000 in a savings account for their daughter. They intend to put an additional $4,000 in the account each year on her 11th, 12th, ..., 17th birthdays.
• All account balances will earn 8% per year.
• On Linda’s 18th, 19th, 20th, and 21st birthdays, her parents will withdraw $20,000 to pay for Linda’s college education.
Is the $4,000 per year sufficient to cover the anticipated college expenses? We can easily solve this problem in a spreadsheet:
1
Annual cost of college 20,000
Birthday
In bank on birthday, before deposit/withdrawal
Deposit or withdrawal
at begin. of year Total
End of year with interest
10 0.00 4,000.00 4,000.00 4,320.00
11 4,320.00 4,000.00 8,320.00 8,985.60
12 8,985.60 4,000.00 12,985.60 14,024.45
13 14,024.45 4,000.00 18,024.45 19,466.40
14 19,466.40 4,000.00 23,466.40 25,343.72
15 25,343.72 4,000.00 29,343.72 31,691.21
16 31,691.21 4,000.00 35,691.21 38,546.51
17 38,546.51 4,000.00 42,546.51 45,950.23
18 45,950.23 -20,000.00 25,950.23 28,026.25
19 28,026.25 -20,000.00 8,026.25 8,668.35
20 8,668.35 -20,000.00 -11,331.65 -12,238.18
21 -12,238.18 -20,000.00 -32,238.18 -34,817.24
NPV of all payments -13,826.4037 <-- =NPV(B2,C8:C18)+C7
SAVING FOR COLLEGE
By looking at the end-year balances in column E, the $4,000 is not enough—Linda and her parents will run out of money somewhere between her 19th and 20th birthdays.6 By the end of her college career, they will be $34,817 “in the hole.” Another way to see this is to look at the net present value calculation in cell C20: As we saw in the previous section, a combination savings/withdrawal plan is fully funded when the NPV of all the payments/withdrawals is zero.
In cell C20 we see that the NPV is negative—Linda’s plan is underfunded.
How much should Linda’s parents put aside each year? There are several ways to answer this question, which we explore below.
6 At the end of Linda’s 19 year (row 16), there is $8,668.35 remaining in the account. At the end of the following year, there is a negative amount in the account.
Method 1: Trial and error
Assuming that you have written the spreadsheet correctly, you can “play” with cell B3 until cell E18 or cell C20 equals zero. Doing this shows that Linda’s parents should have planned to deposit $6,227.78 annually:
1
Annual cost of college 20,000
Birthday
In bank on birthday, before deposit/withdrawal
Deposit or withdrawal
at begin. of year Total
End of year with interest
10 0.00 6,227.78 6,227.78 6,726.00
11 6,726.00 6,227.78 12,953.77 13,990.08
12 13,990.08 6,227.78 20,217.85 21,835.28
13 21,835.28 6,227.78 28,063.06 30,308.10
14 30,308.10 6,227.78 36,535.88 39,458.75
15 39,458.75 6,227.78 45,686.52 49,341.45
16 49,341.45 6,227.78 55,569.22 60,014.76
17 60,014.76 6,227.78 66,242.54 71,541.94
18 71,541.94 -20,000.00 51,541.94 55,665.29
19 55,665.29 -20,000.00 35,665.29 38,518.52
20 38,518.52 -20,000.00 18,518.52 20,000.00
21 20,000.00 -20,000.00 0.00 0.00
NPV of all payments 0.0000 <-- =NPV(B2,C8:C18)+C7
SAVING FOR COLLEGE
Notice that the net present value of all the payments (cell C20) is zero when the solution is reached. The future payouts are fully funded when the NPV of all the cash flows is zero.
Method 2: Using Excel’s Goal Seek
Goal Seek is an Excel function that looks for a specific number in one cell by adjusting
the value of a different cell (for a discussion of how to use Goal Seek, see Chapter 000). To solve our problem of how much to save, we can use Goal Seek to set E18 equal to zero. After hitting Tools|Goal Seek, we fill in the dialog box:
When we hit “OK,” Goal Seek looks for the solution. The result is the same as before:
$6,227.78.
Method 3: Using the Excel NPV formula
The method in this subsection involves the most preparation. Its advantage is that it leads to a very compact solution to the problem—a solution that doesn’t require a long Excel table for its implementation. On the other hand, the formulas required are somewhat intricate (if you really hate formulas, skip this method!).
Linda’s parents are going to make 8 deposits of $X each, starting today. The present value of these deposits is
( ) ( )
2( )
7( ) ( )
2( )
71 1 1
1.08 1.08 1.08 1 1.08 1.08 1.08
X X X
X X⎛ ⎞
⎜ ⎟
+ + + + = ⎜ + + + + ⎟
⎝ ⎠
… … .
The account created will then have 4 withdrawals of $20,000, starting in year 8. The present value of these withdrawals is:
( ) (
8) (
9)
10( )
11 7( ) ( ) (
2) (
3)
420, 000 20, 000 20, 000 20, 000 20, 000 1 1 1 1
1.08 * 1.08
1.08 1.08 1.08 1.08 1.08 1.08 1.08
⎛ ⎞
⎜ ⎟
+ + + = + + +
⎜ ⎟
⎝ ⎠
Setting these two equations equal allows us to solve for X:
( ) ( ) ( ) ( )
In Excel both the numerator and the denominator are computed by filling in the dialog box for the PV function:
The numerator:
( ) ( ) (2 ) (3 )4
1 1 1 1
3.1212684 1.08 1.08 1.08 1.08
+ + + =
to complete the numerator, we have to multiply by 20, 000 / 1.08 .
( )
7Note that Type is 1 (payments at beginning of period).
Note that for both of these dialog boxes we’ve put in a negative payment Pmt. For the reason, refer to our discussion on page000.
Rows 2-9 of the following spreadsheet show how we use these two PV functions to solve for the annual deposit required:
1
Linda's age when plan started 10
Linda's age at last deposit 17
Number of deposits 8 <-- =B3-B2+1
Number of withdrawals 4
Annual cost of college 20,000
Interest rate 8%
Annual deposit 6,227.78 <-- =(B6/(1+B7)^(B4-1))*PV(B7,4,-1)/PV(B7,B4,-1,,1)
Linda's age today Annual amount deposited
0 1,768.81 <-- =($B$6/(1+$B$7)^($B$3-A12))*PV($B$7,4,-1)/PV($B$7,$B$3-A12+1,-1,,1) 1 1,962.73 <-- =($B$6/(1+$B$7)^($B$3-A13))*PV($B$7,4,-1)/PV($B$7,$B$3-A13+1,-1,,1) 2 2,184.47 <-- =($B$6/(1+$B$7)^($B$3-A14))*PV($B$7,4,-1)/PV($B$7,$B$3-A14+1,-1,,1)
3 2,439.68
SAVING FOR COLLEGE--USING EXCEL FORMULAS ONLY
Annual Deposit Required to Fund 4 years of $20,000 when Linda is 17
0
Linda's age at start of plan
The formula in cell B9 is the solution:
( )
( )7 ( )2 ( )4 ( )2 ( )7
1 1 1 1 1 1
20,000 ... 1 ...
1.08 1.08 1.08 1.08 1.08 1.08
1.08
= (B6/(1+B7)^ B4-1 )* PV(B7,B5,-1) / PV(B7,B4,-1,,1)
↑ ↑
↑
+ + + + + + +
The problem as initially set out assumed that Linda was 10 years old today. The table in rows 12 – 27 shows the problem solution for other starting ages.7
7 The table in rows 12-27 would be simpler to compute if we used Data Table. This advanced feature of Excel is explained in Chapter 30. The file Chapter01.xls on the disk accompanying Principles of Finance with Excel shows how to use Data Table to do the calculations in rows 12-27.
Pension plans
The savings problem of Linda’s parents is exactly the same as that faced by an individual who wishes to save for his retirement. Suppose that Joe is 20 today and wishes to start saving so that when he’s 65 he can have 20 years of $100,000 annual withdrawals. Adapting the previous spreadsheet, we get:
1
Joe's age today 20
Joe's age at last deposit 64
Number of deposits 45 <-- =B3-B2+1
Number of withdrawals 20
Annual withdrawal from age 65 100,000
Interest rate 8%
Annual deposit 2,540.23 <-- =(B6/(1+B7)^(B4-1))*PV(B7,B5,-1)/PV(B7,B4,-1,,1)
Joe's age today Annual amount deposited
20 2,540.23 <-- =($B$6/(1+$B$7)^($B$3-A12))*PV($B$7,$B$5,-1)/PV($B$7,$B$3-A12+1,-1,,1) 22 2,978.96 <-- =($B$6/(1+$B$7)^($B$3-A13))*PV($B$7,$B$5,-1)/PV($B$7,$B$3-A13+1,-1,,1) 24 3,496.73 <-- =($B$6/(1+$B$7)^($B$3-A14))*PV($B$7,$B$5,-1)/PV($B$7,$B$3-A14+1,-1,,1)
26 4,109.02
Annual Deposit Required to Fund 20 years of $100,000 when Joe is 65
Joe's age at start of plan
In the table in rows 12 – 27 you see the power of compound interest: If Joe starts saving at age 20 for his retirement, an annual deposit of $2,540.23 will grow to provide him with his retirement needs of $100,000 per year for 20 years at age 65. On the other hand, if he starts saving at age 35, it will require $8,666.90 per year.