Excel 2000 [% calculation]

[Jonathan King]

Does anyone know of a Excel formula to calculate Compound
Annual Growth %. I have revenue numbers over a 4 year
period that I would like to calculate the Compounded
Annual Growth %.

 

[Norman Harker]

Hi Jonathan!

Not much to go on in terms of what you have but here's the basics.

By formula:

=(EndValue/StartValue)^(1/4)-1

By Function:

=RATE(4,0,-StartValue,EndValue,0,0)

In the case of the Function a sign convention is adopted whereby
payments out are negative and payments in are positive.

To reflect the income during the period in the rate of return:

If a constant level of income is received at regular intervals (eg
monthly):

(eg)

=(1+RATE(48,200,-10000,12000,0,0))^12-1

If the income is at regular intervals but at differing amounts:

(eg)

=(1+IRR(A1:48,0))^12-1

If the income is received at irregular intervals

=XIRR(RangeOfFlows,RangeOfDates,0)