How to Calculate CAGR in Excel

When you’re doing financial analysis, it’s helpful to add some context to growth numbers. That’s where using a Compounded Annual Growth Rate (CAGR) is very useful. Rather than saying a company has grown 50% over 10 years, you could say instead that they’ve grown by an average of 4.1% every year. It also gives you a percentage to use going forward if you need to forecast what you expect next year’s growth to be since you’ll have a starting point. The CAGR effectively tells you what the average growth rate has been during that period of time. It doesn’t mean that it’s grown every year by that rate, but that on average, it has risen by that amount. Below, I’ll go over how to calculate CAGR in Excel.

Using Amazon as an example

To calculate CAGR what you need to know are just two things: the total growth and the duration of time. Let’s use Amazon’s sales as an example. In 2018, total revenues for the year reached $233 billion. Back in 2010, the company had $34 billion in sales, meaning that Amazon’s revenues have grown by 585% during that time. Impressive, no doubt, but it doesn’t tell us how well it’s typically done on a yearly basis. This is where we calculate CAGR to help determine what the average growth was over that time.

From 2010 to 2018, that’s eight years that it took for sales to grow by 585%. To calculate CAGR, we use the following equation:

(Current Year Amount / Base Year Amount) ^ ( 1 / # of Years)

In the Amazon calculation, it would look as follows:

(233/34) ^ (1/8) – 1 = 27%

What this tells us is that Amazon for the past eight years has averaged an annual growth rate of around 27.2%. There’s an easy way we can prove this out. Starting with our base year of 2010, take the sales amount of $34 billion and multiply it by (1 + growth rate) each and every year. Here is how the level of growth looks like year over year:

As you can see, if we assume a 27.2% growth rate each and every year, we arrive to the same end value. Although Amazon did not grow at this consistent of a pace, using CAGR helps to average the results over a period of time.

Whether you’re looking at sales, dividends, or any other kind of growth, using CAGR can be a very useful tool in putting into context just how the strong the rate of growth was.

Using CAGR to help forecast

Having CAGR is also useful if we want to forecast out future sales. Let’s assume that Amazon will have a more modest rate of growth for the next 10 years. That rather than a CAGR of 27.2%, it’ll be closer to 20% instead. Now, we can create a forecast around that and assume that sales will grow by 20% each year (on average) for the next 10. Our forecast would look as follows:

To prove this out, what we can do is the following: 233,000 x (1.20^10) = 1,442,675

Based on these projections, we would expect amazon to hit the one trillion dollar mark in sales by the end of 2026. Maintaining a CAGR of 20%, however, will be difficult, even for a company like Amazon. Although all it takes is some big acquisitions and it will be well on its way!

If you don’t want to calculate CAGR on your own, you can use our free online calculator!

If you liked this post on How to Calculate CAGR in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.