Excel - tuition savings question

[Kevin Smith]

Folks,
Need some help here. This is the problem - need to solve in Excel.
John will enter college 6 years from now, and spend 4 years in
college. The current annual cost of college education is $25,000 and
is expected to rise 5% each year. Thus, the first college payment is
due 6 years from now and the last payment is due 9 years from now.
Dad would like to make 10 annual investments beginning now and ending
9 years from now such that the total value of his investments and
returns will exactly cover the cost of college. He would like to
determine his first investment, which he plans to increase in
subsequent years by 6% each year. He expects to earn 8% annual return
on his investments.
a) Need to develop a table that shows investments, returns, tuition
expenes and account balances for each year
b) What is the first year's investment (the answer is $8797.55).

I would appreciate any guidance on this.

Thanks,
Kevin

 

[joeu2004]

John will enter college 6 years from now, and spend 4 years in
college. The current annual cost of college education is $25,000 and
is expected to rise 5% each year. Thus, the first college payment is
due 6 years from now and the last payment is due 9 years from now.

Using your numbers, the annual costs can be estimated as follows:

C8: =fv(5%,6,0,-25000)
C9: =fv(5%,7,0,-25000) or =C8*(1+5%)
C10: =fv(5%,8,0,-25000) or =C9*(1+5%)
C11: =fv(5%,9,0,-25000) or =C10*(1+5%)

Dad would like to make 10 annual investments beginning now and ending
9 years from now such that the total value of his investments and
returns will exactly cover the cost of college. He would like to
determine his first investment, which he plans to increase in
subsequent years by 6% each year. He expects to earn 8% annual return
on his investments.
a) Need to develop a table that shows investments, returns, tuition
expenes and account balances for each year
b) What is the first year's investment (the answer is $8797.55).

Intuiting your model, one approach might be:

A2:A11: year number (0-9)
B2: initial investment (8797.55)
B3: =B2*(1+6%)
Copy B3 to B4:B11
C2:C11: withdrawals (C8:C11 as above)
D211: porfolio balance
D2: =B2
D3: =D2*(1+8%) + B3 - C3
Copy D3 to D411

However, I believe that has an off-by-one-year error. If we assume
that investments and withdrawals are both at the beginning of the year
(you wrote the first payment is "due in 6 years"), it does not make
sense to invest in the 9th year.

With that in mind, I would clear B11 (9th-year investment); thus, you
are making only 9 investments. Then, if we interpolate B2 (initial
investment) by increasing and decreasing appropriately until D11
(portfolio balance) is the smallest positive value (near zero), we get
$9686.80.

(Alternatively, you could use Excel Solver to derive B2.)

Notes: This does not take income tax into account. Also, some of
your assumptions are dubious. But you can refine this model as you
see fit.

 

[Kevin Smith]

Terrific - you are awesome. Much appreciated !!!!!