Question

John just turned 12 (at t = 0), and he will be entering college 6 years from now (at t = 6). College tuition and expenses at State U. are currently $16,500 a year, but they are expected to increase at a rate of 3.5% a year. John is expected to graduate in 4 years. Tuition and other costs will be due at the beginning of each school year (at t = 6, 7, 8, and 9). So far, John’s college savings account contains $10,000 (at t = 0). John’s parents plan to add an additional $12,000 in each of the next 4 years (at t = 1, 2, 3, and 4). Then they plan to make 2 equal annual contributions in each of the following two years, t = 5,, and 6. They expect their investment account to earn 5.5%. How large must the annual payments at t = 5, and 6 be to cover John’s anticipated college costs?

Answer 1

John will be entering college 6 years from now (at t = 6).

Let's find the total college tuition expenses that will be incurred from t=6 to t=9 :

College tuition and expenses at State U. are currently $16,500 a year (at t=0), and are expected to increase at a rate of 3.5% a year

Hence, college tuition at t=1 will be [16500 + (16500*0.035)] = 16500 * (1 + 0.035) ;

college tuition at t=2 will be 16500*(1 + 0.035) * (1 + 0.035) = 16500 * (1 + 0.035)²

.....and so on.

(Ending Value = Beginning value x (1 + Rate of increase per period)^{number of periods} )

Hence, college tuition at the beginning of John starting college, at t=6 will be $16,500 x (1+0.035)⁶ = $20,282.71

at t=7 = $20,282.71 x 1.035 = 20,992.60

at t=8 = 20,992.60 x 1.035 = 21,727.34

at t=9 = 21,727.34 x 1.035 = 22,487.80

Total college tuition costs = $20,282.71 + 20,992.60 + 21,727.34 + 22,487.80 = $85,490.45

John’s college savings account contains $10,000 (at t = 0).

John’s parents plan to add an additional $12,000 in each of the next 4 years (at t = 1, 2, 3, and 4).

They expect their investment account to earn 5.5%.

First let us calculate the accumulated balance that John’s college savings account will have after t=4. We will do this using the FV (future value) function in Excel. The inputs to this formula are :

Rate (the interest rate on the investment account) = 5.5%, Nper (the number of periods)= 4, Pmt (the deposit made in the account each year) = 12000, Pv (the present value, or current balance) = 10,000, and Type (denotes that deposits are made at the start of each period) = 1

Notice the formula result at the bottom (ignore the negative sign). Hence, the accumulated balance that John’s college savings account will have at the end of t=4 will be $67,361.34

This balance will become the Present Value that we will need to find the equal annual contributions to be made at t = 5, and 6.

We will also need the Future Value (account balance) at the end of t=6. The value of the total college costs that we calculated above ($85,490.45) will be the required number here. But, these total college costs are at the start of t=9, whereas we need to find the value of these costs at the end of t=6.

In order to do that, we use the present value formula : PV = Fv / (1+r)ⁿ

= $85,490.45 / (1+0.055)²

= $85,490.45 / 1.113025

= $76,809.10 (account balance to be attained at the end of t=6)

(Note : n=2 because we're discounting back by 2 periods)

Now we calculate the equal annual contributions to be made at t = 5, and 6 using the PMT function.

Inputs : Rate = 5.5%, Nper = 2, Pv = 67,361.34 (balance at the end of t=4) (enter as a negative figure), Fv = 76,809.10 (balance at the end of t=6), Type = 1

Annual Payments required at t = 5, and 6 to cover John’s anticipated college costs = $846.04