Collage savings (2000)

Topic

New Reply

What formula would you use to find out how much per month you would have to put aside to have for example $40,000.00 saved up for collage if child was just born this month say 18 or 19 years before collage
Thanks for any help

Reply | Quote

Replies

The =PMT(rate,nper,pv,fv,type) function can be used to determine the savings required to accomplish a future value. You’ll have to make your own estimate of the interest/earnings rate you can achieve, this example uses 5%:

	A	B
1	Earnings Rate:	5%
2	Years:	18
3	Target:	40000
4	Save Monthly:	=PMT(B1/12,B2*12,0,-B3)

Reply | Quote

This is more or less a matter of guesswork, because you have to estimate

• the effect of inflation on college fees over the period
• compared with the (increasing) amount you might be able to save because of your pay rises over the same years[/list]And that’s assuming nothing ‘drastic’ happens over the intervening years, as well!

  So the target you have to achieve is not $40K but whatever the college fees are likely to be in 18-19 years time. If we assume 3% inflation for college fees (for the sake of argument) then the $40K fees now will be around $68K in 19 years time, at 5% it will be about $96K, at 7% around $135K. You get the general idea …

  John

Reply | Quote

Hi John,

The effects of inflation can largely be ignored, if you base your calculations on today’s values and simply increment the savings amount each year in line with CPI changes. Except for the effects of taxation, you should be able to put the investment into an account where the interest on the savings at least keeps pace with CPI. For forecasting putposes, this effectively amounts to assuming the fees will remain static and you’ll be getting no savings on the investment. The only variable that remains to be taken account of is the relative rate of college fee inflation comapred to the CPI.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

(Edited by JohnBF on 23-Jan-07 01:15. )

In the US, the Bureau of Labor and Statistics has a Consumer Price Index series for US College Education, SEEB01; using the current index system and seasonal adjustment, the series is CUSR0000SEEB01. Here is Dec 97 through Dec 06.

	A	B	C
1	1997	299.8
2	1998	311.6
3	1999	323.8
4	2000	338.3
5	2001	358.9
6	2002	384.2
7	2003	422
8	2004	458.3
9	2005	488.6
10	2006	522.5	=(B10/B1-1)/9

8.25% over the last ten years, but the more recent years have been accelerating; the last 5 years is 9.1%.

Edit: corrected for denominator error.

Reply | Quote

Hi. I have attached a file that shows a data table that uses the FV function (Future Value). The table shows twenty years worth of variable monthly payments at variable rates. That is, you can use the table to see how much you would accumulate if you put away $100 per month and received 3% interest on that savings. Over 20 years you would accumulate 32,830. If you change the number of years in cell B2 you will see how the change would affect your accumulation. You can also change the percentage amounts (E7:R7) and/or the amount you put away each month (D8:D23) and the table will automatically recalculate.

The downside of this table is that it assumes a constant monthly payment over the entire period, but it will give you some idea of how much you’ll need to put away in order to accumulate a specific amount of money..

Reply | Quote