Excel - tuition savings question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Smith - 21 Feb 2007 03:41 GMT

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

Reply to this Message

joeu2004 - 21 Feb 2007 05:20 GMT

> 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
[quoted text clipped - 5 lines]
> 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)
D2:D11: porfolio balance
D2: =B2
D3: =D2*(1+8%) + B3 - C3
Copy D3 to D4:D11

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.

Reply to this Message

Kevin Smith - 21 Feb 2007 05:47 GMT

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

> > 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
[quoted text clipped - 46 lines]
> your assumptions are dubious. But you can refine this model as you
> see fit.

Reply to this Message