Hi,
Try this UDF
Put the code in a module and useCode:Function CAGR(First, Last, Periods) CAGR = ((Last / First) ^ (1 / Periods)) - 1 End Function
=CAGR(A1,A2,A3)
in the sheet as a normal formula
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)?...
Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?
Hi,
Try this UDF
Put the code in a module and useCode:Function CAGR(First, Last, Periods) CAGR = ((Last / First) ^ (1 / Periods)) - 1 End Function
=CAGR(A1,A2,A3)
in the sheet as a normal formula
"Fair Winds and Following Seas"
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.
Hello sir,
Im not able to get the CAGR rate by using rate function.
Hey thanks guys,,, I was facing the same issue to calculate CAGR....
The rate formula works .
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.
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...
Richard Schollar
Using xl2013
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...
Like this thread? Share it with others