The compound annual growth rate (CAGR) shows the rate of return of an investment over a certain period of time, expressed in annual percentage terms. Below is an overview of how to calculate it both by hand and by using Microsoft Excel.
What is CAGR?
But first, let’s define our terms. The easiest way to think of CAGR is to recognize that over a number of years, the value of something may change – hopefully for the better – but often at an uneven rate. The CAGR provides the one rate that defines the return for the entire measurement period. For example, if we were presented with year-end prices for a stock like:
- 2015: $100
- 2016: $120
- 2017: $125
From year-end 2015 to year-end 2016, the price appreciated by 20% (from $100 to 120). From year-end 2016 to year-end 2017, the price appreciated by 4.17% (from $120 to 125). On a year-over-year basis, these growth rates are different, but we can use the formula below to find a single growth rate for the whole time period.
CAGR requires three inputs: an investment’s beginning value, its ending value and the time period (expressed in years). Online tools, including Investopedia’s CAGR calculator, will give the CAGR when entering these three values. The formula is:
Plugging in the above values we get [(125 / 100)^(1/2) – 1] for a CAGR of 11.8%. Despite the fact that the stock’s price increased at different rates each year, its overall growth rate can be defined as 11.8%.
Tips & Tricks for Calculating CAGR
One mistake that’s easy to make in figuring CAGR is to incorrectly count the time period For instance, in the above example, there are three calendar years. But since the data is presented as year-end prices, we really we only have two completed years. That’s why the equation reads 1/2, not 1/3.
Now, let’s say we had a stock whose annual price data was presented in percentage, instead of dollar, terms:
- 2015: 10%
- 2016: 15%
- 2017: -4%
In this case, the data is being shown from the beginning of the year, as in, the entire yearly return in 2015 (10%), the entirely yearly return in 2016 (15%), and the entire yearly return in 2017 (-4%). So when calculating CAGR, we would actually be working with a time period of three years.
We would need to convert these percentages into actual beginning and ending values. This is a good opportunity to use a spreadsheet, since it’s easy to add a helper column to convert the percentages into values.
Calculating CAGR in Excel
The math formula is the same as above: you need ending values, beginning values and a length measured in years. Although Excel has a built-in formula, it is far from ideal, so we will explain that last.
Financial modeling best practices require calculations to be transparent and auditable. The trouble with piling all of the calculations into a formula is that you can’t easily see what numbers go where, or what numbers are user inputs or hard-coded.
The way to set this up in Excel is to have all the data in one table, then break out the calculations line by line. For example, let’s derive the compound annual growth rate of a company’s sales over 10 years:
The CAGR of…