Sign in or start a free trial to avail of this feature.
3. Percentage Growth Rates and CAGR
Find out how to calculate annual revenue growth rates and the trickier Compound Annual Growth Rate (CAGR) for a multi-year period.
Percentage Growth Rates (00:10)
The percentage growth rate is one of the most common calculations you’ll perform in Excel. In this lesson, we calculate this formula from one year to the next. For example, to calculate the percentage growth rate of revenue from 2007 to 2008, we take one revenue for 2008, subtract revenue for 2007, and divide the result by revenue for 2007.
Compound Annual Growth Rate (01:14)
The Compound Annual Growth Rate, or CAGR, calculates a constant annual growth rate between any two years. Over a period of several years, it evens out variations that can occur from one year to the next. Here we calculate the CAGR of revenue from 2007 to 2012.
To calculate CAGR, we start by taking the final revenue, in 2012, and dividing by the initial revenue, from 2007. We put all this to the power of 1 divided by the number of years. This is 5 in our example (i.e. 2012 minus 2007). Finally, we subtract 1 from this result.
The result gives us the growth rate that would have led to the same revenue growth we observed, if that growth rate had been achieved every year.
In the previous lesson, we learned how to calculate the net present value of an investment. In this lesson, we'll learn how to calculate percentage growth rates and the compound annual growth rate, or CAGR. One of the most common calculations you perform in Excel is the percentage growth rate. In this example, we have five years of revenue. And I'd like to calculate the annual growth rate for 2008 to 2012. The formula for this is quite easy. It's the new revenue minus the old revenue, all divided by the old revenue. So let's write this in Excel. So I'll write equals and open a bracket, and put in the new revenue minus the old revenue.
We then divide this by the old revenue, and then press Tab to complete. And this tells me that the growth from 2007 to 2008 was 3%.
I then copy this for the remaining cells, and Alt + E + S + F to paste formulas.
And I now have the growth rate for each year that I want.
Now let's move on to a trickier calculation. The compound annual growth rate, or CAGR. CAGR describes the rate of which revenue grew from 2007 to 2012, if it had been a steady annual rate each year. The formula for CAGR is quite complex. It starts with taking the ending value 380,000, divided by the starting value and putting this all to the power of one divided by the number of years. We then take one from this number.
Let's now calculate this in Excel. So I'll start off by writing equals and opening a bracket, and we'll take the ending value and divide it by the beginning value.
We then put this to the power of one divided by the number of years. Which is 2012 minus 2007.
We then close the brackets and take one from this number. We press Return and get the answer of 4.84%.
So this means that if the company had grown 4.84% each year from 2007 to 2012, we'd reach the same revenue of 380,000.
Let's check this answer by taking the initial number, and multiplying it by one plus the CAGR.
We'll put this to the power of five for five years.
And then press Enter. And we can see that this gives us the correct ending value. So that you get some additional practice with percentage growth rates, I've included a quick exercise on the next sheet for a similar company, which requires you to calculate annual growth rates, and the CAGR for the same time period.