How to calculate the CAGR on Excel?

Please go to my personal website if you want to know more about me or get in touch.

Do you find forecasting to be difficult? Don’t know where to start?

Building a baseline can be easy. At least a baseline you can use to fine tune.

Create your free account

When it comes to forecast and previsional, one very basic approach which can do the trick is to use the CAGR, Compound Annual Growth Rate. CAGR is an important concept when it comes to make a sales forecast. CAGR allows to understand pretty quickly what is the general growth trend of any metric. 

The CAGR formula on Excel

CAGR fomula on Excel is pretty straightforward to compute. Imagine that you have the following table and want to understand what has been the average growth rate observed between all these years:

Period Year Value
0 2015 100
1 2016 150
2 2017 210
3 2018 360
4 2019 380
5 2020 399

You have 6 values and 5 periods in the example above with as you can see different year over year growth rate

To get the CAGR between 2015 and 2020, enter the following formula in Excel:

= (399/100)^(1/5) - 1 = 31.8% on average.

The theoretical formula to use on Excel is : = (end value / start value)^(1 / number of period) - 1

Why using the CAGR?

Using the Compound annual growth rate is good in the following use cases:

You need to understand the long trend growth rate. When you have multiple period and year to cover with different year over year or month over month growth rates, you might want just to assess the long term growth rate. What has been the average growth rate observed between the first period and the very last one. Using CAGR can help understanding the underlying trend.

You don't have much other information to build a forecast. Sometimes, you need to build quickly a forecast, predict what the sales activity will be. A quick back of the envelope calculation. You might not have a thousands of data points at your disposal to start forecasting. Using the CAGR is then a good way to perform a quick forecast.

The variation between the years are not huge, meaning the plotted growth curve is pretty linear - hence using the CAGR is fairly accurate. If one year, the growth observed is +30%, then you have +4%, then +16% and -8%, using a CAGR might not be the best approach. The volatility observed between two growth periods is too high for the CAGR to be a good predictive tool. Having such variations in the growth rate means that either the dataset you're trying to forecast is too small to get a pattern emerged or that there are a few of very business activity drivers behind your performance, which are hard by essence to predict.

You don't have much time to invest to deepen the forecast analysis  and the CAGR will just be fine for your to use something quickly. 

You don't need to use a statistical approach to buid a forecastSometimes, you don't need to use a bazooka to hunt bees. A simple reliable method will be ok.

This post was rated: 5 / 5 by 1 readers
You rated: Not rated yet... but please be kind!

Do you find forecasting to be difficult? Don’t know where to start?

We enable sales forecasting, using a machine learning algorithm. Have a look at our product page to understand how we can help you building your sales forecast

Create your free account