dlb21 said:
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 said:
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?