Reduction in Growth

[dlb21]

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?

 

[Niek Otten]

=RATE(120,500,100000,-210000)

 

[Joe User]

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]

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

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

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?

 

[Joe User]

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

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

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

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?