CAGR

[satishrana]

Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?

[fairwinds]

Hi,

Try this UDF

Function CAGR(First, Last, Periods)
    CAGR = ((Last / First) ^ (1 / Periods)) - 1
End Function

Put the code in a module and use

=CAGR(A1,A2,A3)

in the sheet as a normal formula

[Andrew Poulsom]

You can use the RATE function, eg:

=RATE(A3,,-A1,A2)

where A1 contains the starting amount, A2 the ending amount and A3 the number of years.

[sureshkumaricwa]

Hello sir,

Im not able to get the CAGR rate by using rate function.

[kunal]

Hey thanks guys,,, I was facing the same issue to calculate CAGR....
The rate formula works .

[madmurr]

Hi - is there a formula that properly calculates CAGR when you end up with a negative number (e.g.) over a 5-year period you go from making $100 in Year 1 to losing $50 in year 5 - what is the -CAGR in this calculation)?

Thanks in advance for any help that can be offered.

[Richard Schollar]

Try looking at the IRR function in Excel. Dependent on what information you are using (and what circumstances it is modelling) will determine the applicability of IRR as a measure. If I had an investment eg in a bank earning +x% interest at the start but earning -x% interest at the end, I wouldn't leave said investment in the bank so the -x% wouldn't apply...

[dtelford]

Originally Posted by fairwinds

Hi,

Try this UDF

Function CAGR(First, Last, Periods)
    CAGR = ((Last / First) ^ (1 / Periods)) - 1
End Function

Put the code in a module and use

=CAGR(A1,A2,A3)

in the sheet as a normal formula

Just to say thanks. This has worked perfectly first time of trying.

[bobtownusa]

I believe the correct formula for excel is as follows:

=((Last#/First#)^(1/((count(data range))-1))-1)

The rate calculated from this formula can be tested by multiplying the first number ("First#" in the formula listed above) by the rate and continuing to multiply each subsequent number by the rate until you reach the nth point. That final number should equal the last number ("Last#" in the formula listed above).

For example, if an actual trend is as follows (if each data point is in the same excel row):
cell A1: 229,363
cell B1: 225,309
cell C1: 191,707
cell D1: 146,023

The CAGR as calculated by my formula above will be -14.0%

The formula for the values above would look like this:
=((D1/A1)^(1/((COUNT(A1:D1))-1))-1)

It can be proved by multiplying 229,363 (and subsequent results) by -1.14%. The trend will look as follows:
n1: 229,363
n2: 197,313 (calculated)
n3: 169,741 (calculated)
n4: 146,023 (calculated) - THE SAME VALUE AS cell D1!

Hope this helps...

[bobtownusa]

Originally Posted by bobtownusa

I believe the correct formula for excel is as follows:

=((Last#/First#)^(1/((count(data range))-1))-1)

The rate calculated from this formula can be tested by multiplying the first number ("First#" in the formula listed above) by the rate and continuing to multiply each subsequent number by the rate until you reach the nth point. That final number should equal the last number ("Last#" in the formula listed above).

For example, if an actual trend is as follows (if each data point is in the same excel row):
cell A1: 229,363
cell B1: 225,309
cell C1: 191,707
cell D1: 146,023

The CAGR as calculated by my formula above will be -14.0%

The formula for the values above would look like this:
=((D1/A1)^(1/((COUNT(A1:D1))-1))-1)

It can be proved by multiplying 229,363 (and subsequent results) by -1.14%. The trend will look as follows:
n1: 229,363
n2: 197,313 (calculated)
n3: 169,741 (calculated)
n4: 146,023 (calculated) - THE SAME VALUE AS cell D1!

Hope this helps...

Quick Correction: the first value isn't multiplied by -1.14%...you just apply the decline rate. Would technically be 229,363 + (229,363 * -.14).