Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]

Posted on April 29th, 2014 in Financial Modeling - 22 comments

Here is a story we all are familiar with,

Jack learns about compound interest in school. He quickly forgets about it while learning other complicated things like trignometry, partial differentiation, correct spelling of trigonometry etc. Jack graduates and ends up with a job he loves / hates. Years pass and one day out of boredom jack looks thru mail and stumbles on a credit card bill. As Jack has nothing else to do, he opens it. He looks at something and wonders why his credit card number has only 7 digits!

Then he realizes that it is the amount he owes to credit card company!!!

Jack scratches his head vigorously and wonders how the tiny bits of money he spends on gas, toothpaste and occasional pizza can add up to $19,234.55 ?

Then he realizes the true power and meaning of compound interest!

The moral of the story: Never open a credit card bill.

Of course the real moral is, understand the power of compound interest and use it to your advantage.

But we are not here to talk about unhealthy money habits.

We are here to talk about awesome benefits of Excel.

So moving on, lets talk about how we can use Excel to calculate Compounded Annual Growth Rate (CAGR for short).

What is CAGR? What does it signify?

Let us say you are the CEO of ACME Inc. You have been selling various widgets since 2009. In your latest annual report you want to tell your shareholder at what rate you have been growing ACME Inc. sales. The figure are,

  • 2009 – $150 Mn
  • 2010 – $125 Mn
  • 2011 – $160 Mn
  • 2012 – $174 Mn
  • 2013 – $195 Mn
  • 2014 – $210 Mn

Now, if you see the growth rates, they are all over the place. Right from -16.67% to 28%. But you want to report a single annual growth rate.

This is where CAGR (Compounded Annual Growth Rate) comes handy.

Imagine that the sales of ACME Inc. grew at constant rate every year from $150 Mn in 2009 to reach the $210 Mn in 2014. This is what CAGR represents.

As you can see, CAGR removes all the volatility in the numbers to tell at what uniform rate the numbers grew (or declined) every year.

Arithmetic behind CAGR

While we can never explain the arithmetic behind how credit card companies calculate interest, penalties, fees, interest on penalties and miscellaneous charges, we can easily understand the logic of CAGR.

You see CAGR uses the all important compound interest math at the heart.

Lets throw a few variables in to this article now:

Lets say the starting value is P (in our case P= $150 Mn in 2009)

End value is A (A = $210 Mn in 2014)

and N represents the number of years it took P to become A. In our case N is 5 years.

R denotes the rate of growth (CAGR).

The basic equation is

Compound Interest Equation for calculating CAGR using Excel

A = P *(1+R/100)^N

And we need to find R.

So lets do some simple Arithmetic. Lets expand,

Expanding Compound Interest equation to find R

not that kind of expansion, you silly!

We need to do this:

Finding Compounded Annual Growth Rate (CAGR) using Excel

The final equation for R is =(A/P)^(1/N) – 1

Calculating CAGR in Excel

Now that we have finished a crash course in arithmetic behind compound interest, we can calculate CAGR in Excel. There are 3 ways to do this.

  1. Using raw arithmetic as shown in above equation.
  2. Using RATE formula
  3. Using IRR formula

Using arithmetic equation for calculating CAGR

Calculating CAGR (Compounded Annual Growth Rate) using Excel arithmeticThis is simple. Assuming,

  • P is the initial value
  • A is the last value
  • N is the number of years

in a blank cell, write

=(A/P)^(1/N) – 1

And you get the Compounded Annual Growth Rate as output. (Just need to format the cell as %).

Using RATE() formula

Calculating CAGR using RATE() formula in ExcelRATE() is a financial formula (function) in Excel that can tell us what would be the interest rate for an annuity. Sounds complicated? See this example:

Lets say, as the CEO of ACME Inc. you took a loan of $30Mn to expand your company. You agreed to pay $5 Mn per year for next 10 years to pay off the loan. So what is the effective rate of interest?

=RATE(10, 5, -30) tells us that the rate of interest is 10.558%

We can use RATE() formula with below parameters to calculate CAGR,

=RATE(N,,-P,A)

Explanation:

In our situation, we want to know at what uniform rate the sales grew from $150 Mn in 2009 to $210 Mn in 2014.

This is same as taking a loan of $150 Mn in 2009 and paying off $0 per year for 5 years and paying one lump-sum payment of $210 Mn in 2014.

So we use =RATE(N,,-P,A) to indicate that we are paying $0 per year (hence omission of 2nd parameter) and paying one lump-sum amount at the end.

Using IRR() formula

While the above 2 formulas do not require any changes in the original data, this one requires that we re-shape the data.

See this picture:

Calculating CAGR using IRR() function in Excel

So original data in B3:B8 is re-shaped in D3:D8,

Now, use =IRR(D3:D8) to get the rate of growth (CAGR).

Related: Using IRR() function over a dynamic range with OFFSET.

Other scenarios where CAGR is helpful

  • You purchased some shares at $100.00 per share on 1st of February 2007. You sold them off at $270.13 per share on 3rd of April 2014. What is your annual rate of return?
  • Your website traffic was 15,000 page views per month in Jan 2012. In March 2014 it is 75,000 per month. What is the monthly growth rate?
  • You had 10 paying clients when you started business 5 years ago. Today you have 300. What is the annual growth rate?

So what method do you use to calculate CAGR?

I have always relied on the brute arithmetic calculation of (A/P)^(1/N) -1. I guess derive strange pleasure writing equations in Excel cells.

What about you? Do you compute CAGR? Where do you use it? And what method do you use to calculate it? Please share your tips & thoughts in comments.

Feeling love towards money & modeling it in Excel?

While it may take us forever to earn few millions or pay off that student loan, we can model all of that in Excel in a few hours! Here are a bunch of articles to help you get started with just that.

 

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]”

  1. Excel says:

    I use the power formula to calculate the CAGR.
    power(A/P,1/5)-1

  2. Very well written Chandoo. Great explanation.

  3. Bob says:

    Back in the dark ages I was taught that CAGR (or AAGR – Average Annual Growth Rate) was the exponential growth rate of the line that best fit all the data. i.e., that you had to consider the data from all of the data points. The 6.96% CAGR you calculate ignores the performace in intervening years, notably the large sales drop in 2010. Therefore I would use LOGEST(B3:B8)*-1 and say that a more representative estimate of the average compound growth of ACME’s sales over the period from 2010 to 2014 was 9.26% – more attractive than 6.96%. If ACME’s sales were: 150,220,230,240,180,210 your calculations would still yield 6.96% while LOGEST would calculate the average growth to be 3.26%.

  4. Brian says:

    Using IRR to calculate the rate of growth of sales is not strictly speaking a correct application of the IRR concept IMHO. The IRR function is intended to measure the return on an amount invested. In your example the final sales figure would represent the amount realised at the end of the investment term rather than the level of sales ( or any other variable) at a particular time. But interesting application nevertheless.

    Also an interesting post about LOGEST function Bob. How does it handle negative values ? As you know the IRR doesn’t handle negatives very well, sometimes producing two or more solutions to the same problem.

  5. sswilcox says:

    I always use the full mathematical formula and have never considered the notion of using an Excel formula as an alternative. Of course, I then always verify the CAGR by plugging it back in and making sure I end up at the correct number. So much for shortcuts.

  6. Bob says:

    LOGEST doesn’t handle negatives well at all. If ACME had negative sales that would probably be a signal to avoid them. However, if looking at profits and some periods were negative we would have the formula return “NMF” – No Meaningful Figure – instead of the #NUM! error and then use cumulative profits/cumulative sales to compare competitor profitability (or couple Sales Growth with absolute improvement in % Return on Sales over the period being analyzed.) Three key measures: Grow sales faster than your world (i.e. gain market share); grow your income faster than your sales (i.e. improve ROS/become more efficient); and grow cash faster than your income (effectively manage your assets).

  7. jraju says:

    Hi,
    Cagr could be used only for those parameters, where you are having a kind of closing stock. Opening stock etc. But in calculating the growth rate of Gross State Domestic Product and such a kind of parameters, then this formula could not be used. Only mere arithmetic average could be used. Because, the Value added to the particular years are only taken in to account for GSDP. Any comments, chandoo, Am I correct

    • Chandoo says:

      Hi Jraju.. I think CAGR works even in such cases too.

      When someone says Country X’s GDP grew by 8% on average in the last 5 years, I would imagine the GDP grew from 100 to 136 in 5 years, not 132.

      When you explain 100 to 136, 8% would be the CAGR.

  8. jraju says:

    Hi,
    Chandoo, could i expect your views on my point above about using arithmetic average for gsdp

  9. jraju says:

    Hi, chandoo,

    Please review the use of cagr in gsdp . The gross value added from each sector for the year only has been taken in to the calculation of GSDP. There is no opening stock or closing stock to brought forward in such case. So, only arithmetic mean, that is the growth rate of each year divided by the no of years is the possible way, as far as i know. Expecting your clarification

  10. Luis says:

    I use =RRI(nper,pv,fv)
    It is very simple

  11. Rupa says:

    Nice explanation.

  12. tony says:

    Very beautiful, but can further explain how to calculate a compound monthly growth rate (CMGR).
    Thank you

  13. jraju says:

    Hi, Chandoo
    Expecting your clarification on using cagr in gsdp

  14. Joy says:

    Hi Chandoo,
    How to calculate if I hv yearly growth rates and not absolute figures.

    • Bob says:

      If you have no absolute values you can set the first year to 1 and construct an an annual index [prior period index * (1+ Annual Growth Rate)] for each year. The Compound Growth Rate of this calculated index, calculated using the Logest function as I suggested in above comments, will be the same as if you had all the actual values. I assume the other calculation methods would work as well. As noted in above discussion, if an actual value (not the % change) is negative, calculating a meaningful CGR is problematical.

  15. Umesh says:

    Sometimes you might feel that CAGR is not an effective way of calculating the growth. For ex. in calculating the CAGR %, what is important is the Value during the base year & Value during the final year of calculation, all other values are non-significant. So irrespective of what growth rates are achieved (even 0 or negative) during the intermediary years, CAGR is calculated based on the final year results & first year results only.

  16. Bob says:

    I beg to differ with Umesh. I beleive that all the points in a trend are relevant. Would you rather have a business with periodic revenues of 10, 15, 20, 25, 30 or a stock with periodic revenues 10, 5, 20, 10, 5, 30? Some may like the roller coaster but others get sick. Ignoring intermediate points is quick and easy but ignores both cummulative revenues and very important volatility.

  17. Elizabeth Crum says:

    Hi there – I am using the RATE funtion to calculate CAGR, using your example above. The “starting” point (2009) is annual sales for a product. The number is not negative, how do I keep the number positive in the spreadsheet, yet use the RATE function? I tried putting in a negative sign in the formula and it didn’t work…. I had to change the first sales figure to a negative number. Thank you!

    • Bob says:

      The negative sign has to be in front of the pv argument. So if your starting point value is in cell A2 then the formula should be RATE(number of periods , , -A2 ,ending value. If your values are in A2:A5, e.g., 10,15,20,25. Then the formula would be:
      =RATE(3,,-A2,A5) which would return 35.72%.
      I continue to think that using RATE is not the best way to calcualte CAGR – see my comments above.

Leave a Reply