# CAGR

This is a discussion on CAGR within the Excel Questions forums, part of the Question Forums category; Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?...

1. ## CAGR

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

2. ## Re: CAGR

Hi,

Try this UDF

Code:
```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

3. ## Re: CAGR

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.

4. ## Re: CAGR

Hello sir,

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

5. ## Re: CAGR

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

6. ## Re: CAGR

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.

7. ## Re: CAGR

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

8. ## Re: CAGR

Originally Posted by fairwinds
Hi,

Try this UDF

Code:
```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.

9. ## Re: CAGR

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

10. ## Re: CAGR

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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•