Results 1 to 6 of 6
Thread: Collage savings (2000)

20070122, 13:22 #1
 Join Date
 Dec 2002
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
Collage savings (2000)
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

20070122, 16:59 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Collage savings (2000)
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%:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td>Earnings Rate:</td><td align=right>5%</td><td align=center>2</td><td>Years:</td><td align=right>18</td><td align=center>3</td><td>Target:</td><td align=right>40000</td><td align=center>4</td><td>Save Monthly:</td><td align=right>=PMT(B1/12,B2*12,0,B3)</td></table>John ... I float in liquid gardens
UTC 7ąDS

20070122, 18:10 #3
 Join Date
 Jan 2001
 Location
 Quedgeley, Gloucester, England
 Posts
 5,333
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Collage savings (2000)
This is more or less a matter of guesswork, because you have to estimate <UL><LI>the effect of inflation on college fees over the period <LI>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 1819 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<font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>
Ita, esto, quidcumque...

20070123, 05:04 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,054
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: Collage savings (2000)
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
[MS MVP  Word]

20070123, 08:15 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Collage savings (2000)
<P ID="edit" class=small>(Edited by JohnBF on 23Jan07 01:15. )</P>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.
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=right>1997</td><td align=right>299.8</td><td align=right></td><td align=center>2</td><td align=right>1998</td><td align=right>311.6</td><td align=right></td><td align=center>3</td><td align=right>1999</td><td align=right>323.8</td><td align=right></td><td align=center>4</td><td align=right>2000</td><td align=right>338.3</td><td align=right></td><td align=center>5</td><td align=right>2001</td><td align=right>358.9</td><td align=right></td><td align=center>6</td><td align=right>2002</td><td align=right>384.2</td><td align=right></td><td align=center>7</td><td align=right>2003</td><td align=right>422</td><td align=right></td><td align=center>8</td><td align=right>2004</td><td align=right>458.3</td><td align=right> </td><td align=center>9</td><td align=right>2005</td><td align=right>488.6</td><td align=right> </td><td align=center>10</td><td align=right>2006</td><td align=right>522.5</td><td align=right>=(B10/B11)/9</td></table>
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.John ... I float in liquid gardens
UTC 7ąDS

20070123, 22:05 #6
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Collage savings (2000)
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 (D823) 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..