Below is a link to a simple Compound Annual Growth Rate (CAGR) calculator built in MS Excel with a proof to give you comfort that the number is correct.
If you want to learn how to create your own CAGR calculations watch the YouTube video below otherwise continue with the blog post.
Compound Annual Growth Rate (CAGR) is a popular measure of the smoothed growth between 2 periods.
For example if your sales have been the following in the last 5 years:
CAGR will answer the question of what constant growth rate would get us from 10 000 to 20 000 in the period. The answer would be 18.92% and below the proof showing how, using 18.92% growth per year, 10 000 will grow to 20 000 in the period.
It is an important measure as the volatility on numbers can sometimes hide what is actually happening.
Below you can see that the actual numbers (columns) look different from what slow and steady (CAGR) would look like.
If you wanted to know what the CAGR would be if it was just until year 3, you will see that the number jumps to 34.16%. So in this case is seems that most of the growth happened quickly and then it tapered off. Depending where you start and end the CAGR will tell you a different story about your business.
Instructions for using the Online CAGR Calculator in MS Excel
Below instructions for using the calculator
- Enter the number in the appropriate periods (1). Note that in reality you only need to enter the first and last number, but you will inevitably be asked to consider other periods and also it is nice to graph the actual movement versus the smoothed movement.
- Choose the period you want to go to (2)
- The CAGR will be shown (3)
You can change the end period with the dropdown, but note that the end period chosen must have a number in it to work. Also Period 1 CANNOT be negative.
The CAGR Calculator also has a convenient proof showing how it gets to the correct answer and a chart showing the actual numbers versus the smoothed numbers.
MS Excel does not have a CAGR formula in it, but that does not mean that you need to revert to your maths text books. The online calculator makes use of the RATE function, but you could also work out CAGR using:
- IRR function
- XIRR function
- Goalseek Tool
- RATE function.
To learn more about creating a CAGR Calculator in MS Excel, look at our online Time Value of Money course.