calculating compound growth

[Johan]

Hi,

I have office 2003. I need an excel formula to calculate Average percentage
increase.
Example start wage 5000 current wage 10000
Start date 01/02/2004 (1st Feb) current date 03/03/2009 (3rd March)
length of service 5 years
What was the average annual % increase received to go from 5000 to 10000 in
5 years. This needs to take compound growth into account
(the xirr formula does not work)

Please help

Johan Campbell
(e-mail address removed)

 

[dbsocal]

The equation I use is:
=((y/x)^(1/n))-1
where
y= value in final year
x=value in 1st year
n=# of years

 

[joeu2004]

start wage 5000 current wage 10000
Start date 01/02/2004 (1st Feb) current date 03/03/2009 (3rd March)
length of service 5 years
What was the average annual % increase
=rate(5,0,-5000,10000)

(the xirr formula does not work)

XIRR would be overkill for this problem. But it certainly does work, if you
use it correctly. The key is for one value to be negative and the other to
be positive, just I did in the RATE formula about. If A1 is -5000, A2 is
10000, B1 is 1/2/2004, and B2 is 3/3/2009, then:

=xirr(A1:A2,B1:B2)

The small difference between RATE and XIRR results is because XIRR is "more
accurate" -- probably too accurate for your purposes.

 

[Alojz]

Hi, both correct for integer years. Bit tricky with odd months. When using
periodic compounding, equation is: FV=PV*(1+i)^n*(1+i/f)
FV - future value, PV - present value, i - interest rate (annual in this
case), n - number of full years, f - fraction of year, in case of 1 odd month
this is 12, e.g. 1/12 of year. Extracting i needs itteration, that's why I
would use either goal seek or solver.
Using continuous compounding, equation is: FV=PV*e^(i*t)
e - Euler's constant, base of the natural log (approx. 2.718281), i - annual
interest rate, t - number of years (in this case 5 1/12, e.g. 61/12), when
logarithming, we can easy get i as: i=(ln(FV)-ln(PV))/t
N.B. i for periodic compounding is bigger than i when continuous compounding
(due to continous compounding). Btw, I guess IRR works pretty well. For 5
year, when i for continuous compounding is 10%, IRR function in Excel shows
9.9885%, less than 1.15 b.p. (baisis point - 1/100 p.c.) difference, in my
opinion really not too much. Just do not forget u have to input the initial
flow as negative when using IRR function.

HTH
Alojz

 

[Alojz]

u r completely rite, I was trying to make bit expatiation so was later with
my post