|
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 |
Tricky FV function
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 |
Tricky FV function
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 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 --ron |
Tricky FV function
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 |
Tricky FV function
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 |
Tricky FV function
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 ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=489606 |
Tricky FV function
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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=489606 |
Tricky FV function
On Wed, 30 Nov 2005 22:02:33 -0600, davidm
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 |
Tricky FV function
... 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 |
Tricky FV function
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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=489606 |
Tricky FV function
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" 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 -- MPuser |
Tricky FV function
On Tue, 6 Dec 2005 17:25:38 +0000, MPuser
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 |
| All times are GMT +1. The time now is 02:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com