Tricky FV function

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


--
MPuser

On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
<[email protected]> 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

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

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




On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
><[email protected]> 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

--ron

On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
><[email protected]> 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: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

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
.
.
.
30th year 12 monthly instals.: 500*(1.03)^29-500 per month


That should get you started.

David

Duane wrote:

>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

On Wed, 30 Nov 2005 22:02:33 -0600, davidm
<[email protected]> wrote:

>
>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

> ... 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" <[email protected]> wrote in message
news:[email protected]...
> On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld
> <[email protected]>
> wrote:
>
>>On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
>><[email protected]> 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: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

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: (1+r)^(a*b) <>(1+r)^a*b


>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

Ron Rosenfeld Wrote:
> On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld
> [email protected]
> wrote:
>
> On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
> [email protected] 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: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

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?


--
MPuser

Dana,

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?

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


Dana DeLouis Wrote:
> ... 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" [email protected] wrote in message
> news:[email protected]...
> On Wed, 30 Nov 2005 19:15:30 -0500, Ron Rosenfeld
> [email protected]
> wrote:
>
> On Wed, 30 Nov 2005 15:56:15 +0000, MPuser
> [email protected] 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: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


--
MPuser

On Tue, 6 Dec 2005 17:25:38 +0000, MPuser
<[email protected]> wrote:

>
>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