... your total to $3,447,796.15...

Hi. I arrived at the same value. As another option, here is a non-array
version:
Some named ranges:

A =100000
ir =10%/12
i =1+ir
g =1+3%

=A*i^360+(500*((g/i^12)^30-1)*i^360*(i^12-1))/(ir*(g-i^12))`

$3,447,796.15

HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Ron Rosenfeld" wrote in message
...
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