How to Calculate Compound Annual Growth Rate (“CAGR”) in Google Sheets
In this article, you will learn what CAGR is and how to compute CAGR in Google Sheets.
What is CAGR?
The CAGR stands for the compound annual growth rate, which is the annualized average revenue growth rate between particular two years (e.g., 2015 and 2021). While general revenue growth shows how much revenue in a year has grown compared to the last year's, the CAGR shows the average revenue growth in a particular period. This concept assumes that growth happens at a compounded rate.
How to compute a CAGR in Google Sheets
If you assume the beginning and last year of the period are T and Te, respectively, and the revenue in year T is R, and Re in year Te, respectively, the CAGR is computed by the following formula: (Re/R)^(1/(Te-T)-1. Check this formula with specific numbers in the data set in the picture below. Imagine we calculate the CAGR from 2016 to 2021 - T:2016, Te:2021, R:1,000 and Re:2,000. With these assumptions, the CAGR is 14.9% (=(2,000/1,000)^(1/(2021-2016))-1). This means that if the revenue of 1,000 in 2016 grows at 14.9% over the next five years, the revenue will reach 2,000.
You can use the CAGR to see the revenue growth in a certain period in historical data or a projected period in your business plan and compare it with your competitors.