Tricky FV function

[MPuser]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

Sorry, but the third formula is incorrect. I'll need to work on it a bit more.

--------------------------------

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

--ron

 

[Ron Rosenfeld]

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:29"))-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

 

[davidm]

I agree with Ron's handling of the first 2 parts of the question. You
can double-check your results with the following approach:

The FV of $500 p.a. at 10% p.a. interest (compounding monthly) for 30
years is calculated as follows:

FV= ((Amt of $500 over 30years*12 months @ 10%/12)-1)/10%/12

=500*((((1+(0.1/12))^30*12)-1))/(0.1/12)
=$86,3541.09

The 3rd part is easier with a spreadsheet layout:

The 3% increments will produce a cash flow of:
1st year 12 monthly instals.: 500 *(1.03)^0-500 = 0
2nd year 12 monthly instals.: 500*(1.03) ^1-500 per month
3rd year 12 monthly instals.: 500*(1.03)^2-500 per month

 

[duane]

should not the monthly interest rate be 0.0957/12 rather than 0.1/12 to
allow for the compounding to equate to 10% annual? Or is it really a
10% annual rate, compounded monthly to yield 10.47% annually?

 

[davidm]

should not the monthly interest rate be 0.0957/12 rather than 0.1/12

to allow >for the compounding to equate to 10% annual? Or is it really
a 10% annual rate, >compounded monthly to yield 10.47% annually?


Duane,

If you are to be offered annual percentage rate (APR) of 10%
compounding monthly on your savings, you are in reality going to enjoy
an interest in excess of 10% because of the 12-time monthly rolling-over
effect. Hence, your *effective* annual rate is (1+(.10/12))^12-1
=0.104713 or 10.4713% (as you correctly stated). It is not conventional
financial pratice to view your effective rate as that slightly reduced
rate which will compound to the specified annal rate.

Davidm

 

[Ron Rosenfeld]

I agree with Ron's handling of the first 2 parts of the question. You
can double-check your results with the following approach:

The FV of $500 p.a. at 10% p.a. interest (compounding monthly) for 30
years is calculated as follows:

FV= ((Amt of $500 over 30years*12 months @ 10%/12)-1)/10%/12

=500*((((1+(0.1/12))^30*12)-1))/(0.1/12)
=$86,3541.09

The 3rd part is easier with a spreadsheet layout:

The 3% increments will produce a cash flow of:
1st year 12 monthly instals.: 500 *(1.03)^0-500 = 0
2nd year 12 monthly instals.: 500*(1.03) ^1-500 per month
3rd year 12 monthly instals.: 500*(1.03)^2-500 per month

Not sure where you're getting your values.

For the 2nd part:

$500 monthly at 10% for 30 yrs:

=FV(10%/12,30*12,-500)
=$1,130,243.96

The third equation effectively invests each years increment for n years, where
n decreases depending on the year involved. So there is $15 invested monthly
for 29 years, and so forth. Part 3 comes to about $333,000

This can be checked by setting up a column for each year, and using a formula
that invests the amount for one year, but adds the value of the previous years
computation as the PV for the current year.

so

A1: 500
B1: =A1*1.03

A2: =FV(10%/12,12,-A1)
B2: =FV(10%/12,12,-B1,-A2)

Then copy/drag A2&B2 across for a total of 30 years and the value in AD2 is, as
it should be, the sum of my formulas of Part 2 and Part 3


--ron

 

[Dana DeLouis]

... 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. :>)

 

[davidm]

Correction:

To set the record straight,

FV= ((Amt of $500 over 30years*12 months @ 10%/12)-1)/10%/12

=500*((((1+(0.1/12))^*(*30*12*)*)-1))/(0.1/12)
=$1,130,243.96

Sorry for missing the brackets around the exponent 30*12 . Note:

I agree with Ron's handling of the first 2 parts of the question. You

can double->check your results with the following approach:

The FV of $500 p.a. at 10% p.a. interest (compounding monthly) for 30

years is calculated as follows:

 

[MPuser]

On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfel
(e-mail address removed)
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 o
the
30 year period?

Thanks!
MPuser

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

I have also assumed, in the math, that 1/12 of the 5% compound
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 contributio
(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; an
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 tota
future
value of $5,581,146.95

Of course, that is unadjusted for inflation and/or taxes, but it stil
seems
like a hefty sum. At 3.5% annual inflation, it would have a presen
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:29"))-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 $2
after
inflation .


--ron

Ron,

Great stuff here, thanks for the help. Just a quick thought...

If the hypothetical investment is a mutual fund portfolio...should th
expected return of 10% be compounded monthly or annually? Wouldn'
this drastically affect the outcome of the future value

 

[MPuser]

Dana,

Great stuff here, thanks for the help. Just a quick thought...

If the hypothetical investment is a mutual fund portfolio...should th
expected return of 10% be compounded monthly or annually? Wouldn'
this drastically affect the outcome of the future value?

If it should be compounded differently, how do you alter your equation

 

[Ron Rosenfeld]

Ron,

Great stuff here, thanks for the help. Just a quick thought...

If the hypothetical investment is a mutual fund portfolio...should the
expected return of 10% be compounded monthly or annually? Wouldn't
this drastically affect the outcome of the future value?

The error in the estimate of your expected return will be greater than the
potential error in selecting a monthly vs yearly compounding period.
--ron