9. 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.
Annual growth rate, g = (Revyear 2 - Revyear 1) / Revyear 1
Compound annual growth rate
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
One of the most common calculations you'll 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 = and open a bracket, and put in the new revenue, minus the old revenue.
We'll 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 can 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 at 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 by taking the ending value, 380,000, divided by the starting value, and putting this all to the power of 1, divided by the number of years.
We then take 1 from this number.
Let's now calculate this in Excel.
So I'll start off by writing = and opening a bracket, and we'll take the ending value and divide it by the beginning value.
We'll then put this to the power of 1, divided by the number of years, which is 2012 minus 2007.
We'll then close the brackets and take 1 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 1 plus the CAGR.
And we'll put this to the power of 5 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.