CAGR

satishrana

New Member
Joined
Feb 26, 2004
Messages
23
Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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.
 
Upvote 0
Hey thanks guys,,, I was facing the same issue to calculate CAGR....
The rate formula works .
:)
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top