Prev: Protecting workbook from Plagiarism
Next: filtered values
From: dlb21 on 23 Apr 2010 03:35
I have been using the following formula to calculate growth but the answer
looks incorrect where there is an inital fee for each regular payment or an
initial fee as indicated below:
Fee £250 Initial Inv £100000 Net Investment £99750 then £500 per month
growth 1%, applied monthly, and income of 3%. 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.


From: Joe User on 23 Apr 2010 10:41
"dlb21" wrote:
> I have been using the following formula to calculate
> growth but the answer looks incorrect where there is
> an inital fee for each regular payment or an initial fee

It is unwise to post a follow-up to a previous discussion out of context. I
was writing a response to you in the original thread. But since you modified
some of the details with your new posting, I will respond here.


> Fee £250 Initial Inv £100000 Net Investment £99750
> then £500 per month growth 1%, applied monthly, and
> income of 3%. The results are £100,747.50, £102,259.98,
> £103,787.57, £105,330.45, £106,888.76, £108,462.64

You terms are confusing ("growth 1% ... income of 3%"?), and it is unclear
what model you used to arrive at those numbers. So let's reverse-engineer
the process using the very formula that you are trying to derive.


> RATE(6,-500,-100000+500,108462.64,1)
> it gives an answer of 0.92%

Since the fees reduce the initial investment, and assuming an investment
value of 108,462.64 after 6 months, the correct formula is:

=RATE(6,-500,-99750+500,108462.64,1)

which is indeed about 1.000000%, the average (if not actual) monthly growth
rate. Your mistake was to use 100000 for the initial investment instead of
99750 (100000 - 250).

So with that value in B1, it appears that your model is:

A1: =100000-250
A2: =A1*(1+$B$1)+500
copy A2 into A3:A6
A7: =A6*(1+$B$1)

But the intermediate amounts in A2:A6 do now match the numbers you posted.

If the model above is incorrect, please show how you calculated the
intermediate amounts. Or are they perhaps actual values of an investment
growing at variable monthy rates? If so, they are irrelevant to the
computation.


> if I use a monthly rate of interest (0.083333%)

Why would you now use 1%/12 as the monthly rate? Alternatively, if 1% was
an annual nominal interest rate, how did the investment grow to 108462.64
after 6 months?

The point is: the investment __did__ grow at a rate of about 1% per month.


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

I don't follow you at all.

First, as I noted previously, you should use 99750 instead of 100000.

Second, if the (average) monthy growth were 1%/12, the investment value
after 6 months would be about 102756.05, not 108462.64.

Finally, even with your formula above, RATE(-6,-500,-100000+500,108462.64,1)
results in about 0.958381%, not even close to the 0.92% that you mention.

And when that is annualized your way --
(1+RATE(-6,-500,-100000+500,108462.64,1))^12-1 -- the result is about
12.126574%, not 0.0074%.


> Any thoughts as to how to resolve this.

Get your numbers straight. Post the formulas, if any, for calculating the
intermediate investment values. Use the actual initial investment amount,
apparently 99750 instead of 100000.

And keep all discussion in one thread -- this one, now. ;-)


----- original message -----

"dlb21" wrote:
> I have been using the following formula to calculate growth but the answer
> looks incorrect where there is an inital fee for each regular payment or an
> initial fee as indicated below:
> Fee £250 Initial Inv £100000 Net Investment £99750 then £500 per month
> growth 1%, applied monthly, and income of 3%. 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.
 | 
Pages: 1
Prev: Protecting workbook from Plagiarism
Next: filtered values