About this lesson
Year-on-year growth and CAGR in Google Sheets
In Google Sheets, CAGR is (end ÷ start) to the power of 1 ÷ years, less 1: =(B7/B2)^(1/5)-1, or =RRI(5,B2,B7). Count the years of growth, not the number of years in the table: FY20 to FY25 is 5 years. The average of each year's growth rate is higher than CAGR when growth is uneven, so quote CAGR.
Using Excel? Do this lesson in Excel.
=B3/B2-1What you'll practise
Review six years of revenue for Banksia Pharmacy Group: growth each year, then the compound annual growth rate by hand and with RRI, and why the average of the yearly rates overstates it.
4 steps · 40 points · the cells outlined in gold below
- year-on-year growth
- CAGR
- RRI
- compound growth
The data
| A | B | C | |
|---|---|---|---|
| 1 | Financial year | Revenue ($m) | Growth |
| 2 | FY20 | 142.6 | |
| 3 | FY21 | 151.9 | |
| 4 | FY22 | 173.8 | |
| 5 | FY23 | 184.8 | |
| 6 | FY24 | 189.3 | |
| 7 | FY25 | 214.7 | |
| 8 | |||
| 9 | CAGR, by hand | ||
| 10 | CAGR, with RRI | ||
| 11 | Average yearly growth |