On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld
wrote:

On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
wrote:


At least this is tricky for me...

I want to use a FV function to calculate the future value of an
investment over time. Just as an example, how could I input these
figures into a FV function?

I start with a $100,000 investment that is going to grow over 30 years
at 10% annually. However, I am also going to save $500 per month into
this same investment. On top of that, I would like the monthly
contribution to increase by 3% every year. So my monthly savings in
year two would be $515...in year three it would be $530.45, etc.

How can I set excel up to figure out the future value at the end of the
30 year period?

Thanks!
MPuser

If I understand you correctly, I would use three formulas and SUM them,
although you could certainly combine them all in one cell.

I have also assumed, in the math, that 1/12 of the 5% compounds monthly; since
you are making monthly contributions.

APR = annual return. (10%)
Term = years of investment (30)
BaseContrib = initial monthly investment ($500)
AnnContribIncr = the annual % increase in your monthly contribution (3%)


1. FV of the $100,000 after 30 years:

=FV(APR/12,Term*12,0,-100000)

2. FV of $500 monthly payments over 30 years

=FV(APR/12,Term*12,-BaseContrib)

3. FV of the annual 3% increment to the initial $500 contribution. The first
increment will be invested over 29 years; the second over 28 years; and so
forth.

=SUMPRODUCT(FV(APR/12,(Term-ROW(INDIRECT("1:29")))*12,
-BaseContrib*(1+AnnContribIncr)^ROW(INDIRECT("1:29" ))+500))

By the way, using your numbers and the above formulas, I get a total future
value of $5,581,146.95

Of course, that is unadjusted for inflation and/or taxes, but it still seems
like a hefty sum. At 3.5% annual inflation, it would have a present value of
about $2M -- enough to retire on.



--ron

OK I think the third formula should be:

=SUMPRODUCT(FV(APR/12,12*(30-ROW(INDIRECT("1:29"))),
-BaseContrib*(1+AnnContribIncr)^(ROW(INDIRECT("1:29 ")))
+BaseContrib*(1+AnnContribIncr)^(ROW(INDIRECT("1:2 9"))-1)))

Unfortunately, that drops your total to $3,447,796.15 which, considering
inflation at 3.5%, would be the equivalent of $1,228,375.33 :-((

But, if you could get 12% return, then you'd wind up with about $2M after
inflation :-).


--ron