CAGR

S

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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
fairwinds said:
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
Click to expand...
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
bobtownusa said:
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...
Click to expand...
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
You must log in or register to reply here.

Similar threads

E
  • Question Question
CAGR on Excel - can the first year start negative
Replies
5
Views
4K
Cubist
Cubist
D
  • Question Question
Calculating the cumulative annual fee on a growing account (growth has both fixed AND compounding elements)
Replies
3
Views
267
kvsrinivasamurthy
K
J
  • Question Question
Issue returning a value based on values between two percentage points
2
Replies
10
Views
796
awoohaw
awoohaw
V
  • Question Question
interest calculations
Replies
2
Views
298
vsuram
V
A
  • Question Question
=rate incorrect or not?
Replies
3
Views
83
Dave Patton
D

Forum statistics

Threads
1,223,630
Messages
6,173,457
Members
452,516
Latest member
archcalx

Share this page

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