How to calculate the CAGR on Excel?

Build a forecast without programming

You want to build a sales forecast? Datainsightout can help you with that for a very affordable price. You would just need to upload a CSV with your numbers to make it work.

30 days for only usd 155

See how it works

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.

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!

Build a forecast without programming

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

30 days for only usd 155

See how it works