Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduction in Growth
I have been able to calculate the reduction in growth where there is a lump
sum investment and have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments but cannot find a firula that works where there is an initial investment and regular monthly payments Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduction in Growth
"dlb21" wrote:
Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? The devil is in the details. Based on your previous usage [1], I would say: =(1+rate(120,-500,-100000+500,210000,1))^12-1 which is the same as writing: =(1+rate(120,500,100000-500,-210000,1))^12-1 I assume that if the initial investment of 100,000 is on 1 Jan 2010, the first monthly investment is on 1 Feb 2010, the last investment is on 1 Dec 2019, and the future valuation date ("end of 10 years") is on 1 Jan 2020, with no monthly investment on that date. Is that the correct model? If not, please be more specific about the dates for: the initial investment; the first payment; the last payment; and the future valuation date. To "test" the formula for my model, consider just the first 6 months that grows at a compounded monthly rate of 1%. So: there are 5 investments of 500 after an initial investment of 100,000; the beginning balances after deposits are about 100000, 101500, 103015, 104545.15, 106090.60 and 107651.51; and the balance after 6 months is about 108,728.02. Note that RATE(6,-500,-100000+500,108728.02,1) is indeed about 1%, as we expect. (It is closer to 1% if we reference a cell with the actual computed future value instead of its displayed approximation.) In contrast, the formula RATE(6,-500,-100000,fv,1) actually implies an initial investment of 100,500, and it returns about 1% if the balance after 6 months (fv) is about 109,258.78. ----- Endnotes [1] You wrote that you "have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments". I assume you have a typo and you meant to write "-600,,86144,1", meaning: monthly investments of 600 and a future value of 86144. The point is: you seem to use negative numbers for investments. ----- original message ----- "dlb21" wrote: I have been able to calculate the reduction in growth where there is a lump sum investment and have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments but cannot find a firula that works where there is an initial investment and regular monthly payments Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduction in Growth
Thanks - that works for most things I want to do but not if I have an initial
fee too Using the same formula and the approach you suggested: Fee £250 Initial Inv £100000 Net Investment £99750 then £500 per month growth 1%. The results are £100,747.50, £102,259.98, £103,787.57, £105,330.45, £106,888.76, £108,462.64 using the formula: RATE(6,-500,-100000+500,108462.64,1) it gives an answer of 0.92% which looks about right but if I use a monthly rate of interest (0.083333%) then annualise the result (1+RATE(6,-500,-100000+500,108462.64,1))^12-1 then I get 0.0074% which means the initial fee is having too large an effect on the end result. Any thoughts as to how to resolve this. Thanks "Joe User" wrote: "dlb21" wrote: Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? The devil is in the details. Based on your previous usage [1], I would say: =(1+rate(120,-500,-100000+500,210000,1))^12-1 which is the same as writing: =(1+rate(120,500,100000-500,-210000,1))^12-1 I assume that if the initial investment of 100,000 is on 1 Jan 2010, the first monthly investment is on 1 Feb 2010, the last investment is on 1 Dec 2019, and the future valuation date ("end of 10 years") is on 1 Jan 2020, with no monthly investment on that date. Is that the correct model? If not, please be more specific about the dates for: the initial investment; the first payment; the last payment; and the future valuation date. To "test" the formula for my model, consider just the first 6 months that grows at a compounded monthly rate of 1%. So: there are 5 investments of 500 after an initial investment of 100,000; the beginning balances after deposits are about 100000, 101500, 103015, 104545.15, 106090.60 and 107651.51; and the balance after 6 months is about 108,728.02. Note that RATE(6,-500,-100000+500,108728.02,1) is indeed about 1%, as we expect. (It is closer to 1% if we reference a cell with the actual computed future value instead of its displayed approximation.) In contrast, the formula RATE(6,-500,-100000,fv,1) actually implies an initial investment of 100,500, and it returns about 1% if the balance after 6 months (fv) is about 109,258.78. ----- Endnotes [1] You wrote that you "have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments". I assume you have a typo and you meant to write "-600,,86144,1", meaning: monthly investments of 600 and a future value of 86144. The point is: you seem to use negative numbers for investments. ----- original message ----- "dlb21" wrote: I have been able to calculate the reduction in growth where there is a lump sum investment and have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments but cannot find a firula that works where there is an initial investment and regular monthly payments Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reduction in Growth
Since you reposted your inquiry by starting a new thread, please see my
response there. The newer posting has some modifications. For future note, it is prudent to keep all follow-up discussion in the original thread (this one) so that everyone has the complete context. ----- original message ----- "dlb21" wrote in message ... Thanks - that works for most things I want to do but not if I have an initial fee too Using the same formula and the approach you suggested: Fee £250 Initial Inv £100000 Net Investment £99750 then £500 per month growth 1%. The results are £100,747.50, £102,259.98, £103,787.57, £105,330.45, £106,888.76, £108,462.64 using the formula: RATE(6,-500,-100000+500,108462.64,1) it gives an answer of 0.92% which looks about right but if I use a monthly rate of interest (0.083333%) then annualise the result (1+RATE(6,-500,-100000+500,108462.64,1))^12-1 then I get 0.0074% which means the initial fee is having too large an effect on the end result. Any thoughts as to how to resolve this. Thanks "Joe User" wrote: "dlb21" wrote: Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? The devil is in the details. Based on your previous usage [1], I would say: =(1+rate(120,-500,-100000+500,210000,1))^12-1 which is the same as writing: =(1+rate(120,500,100000-500,-210000,1))^12-1 I assume that if the initial investment of 100,000 is on 1 Jan 2010, the first monthly investment is on 1 Feb 2010, the last investment is on 1 Dec 2019, and the future valuation date ("end of 10 years") is on 1 Jan 2020, with no monthly investment on that date. Is that the correct model? If not, please be more specific about the dates for: the initial investment; the first payment; the last payment; and the future valuation date. To "test" the formula for my model, consider just the first 6 months that grows at a compounded monthly rate of 1%. So: there are 5 investments of 500 after an initial investment of 100,000; the beginning balances after deposits are about 100000, 101500, 103015, 104545.15, 106090.60 and 107651.51; and the balance after 6 months is about 108,728.02. Note that RATE(6,-500,-100000+500,108728.02,1) is indeed about 1%, as we expect. (It is closer to 1% if we reference a cell with the actual computed future value instead of its displayed approximation.) In contrast, the formula RATE(6,-500,-100000,fv,1) actually implies an initial investment of 100,500, and it returns about 1% if the balance after 6 months (fv) is about 109,258.78. ----- Endnotes [1] You wrote that you "have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments". I assume you have a typo and you meant to write "-600,,86144,1", meaning: monthly investments of 600 and a future value of 86144. The point is: you seem to use negative numbers for investments. ----- original message ----- "dlb21" wrote: I have been able to calculate the reduction in growth where there is a lump sum investment and have used (1+RATE(120,-600,86144,,1,3.5%))^12-1 where the investment is made by regular monthly payments but cannot find a firula that works where there is an initial investment and regular monthly payments Say initial investment £100,000 regular investments £500 per month Capital at the end of 10 years £210,000 What was the growth rate? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reduction Fractions to LCD | Excel Discussion (Misc queries) | |||
Average data/reduction | New Users to Excel | |||
Worksheet size reduction | Excel Discussion (Misc queries) | |||
need help with a formula - % reduction | New Users to Excel | |||
How can I set up a debt reduction plan? | Excel Discussion (Misc queries) |