Short answer: CAGR (compound annual growth rate) is the single steady yearly rate that would take a value from its starting point to its ending point. In Excel, the quickest formula is =(End/Start)^(1/Years)-1, or the built-in =RRI(Years, Start, End). Format the cell as a percentage and you are done.
Growth rarely moves in a straight line. Sales jump one year, dip the next, then recover. CAGR smooths that bumpy path into one number you can compare across products, companies or time periods. Below is how it works, three ways to calculate it in Excel, a worked example, and the mistakes that trip most people up.
What CAGR actually measures
The formula is:
CAGR = (Ending value ÷ Starting value)1 ÷ number of years − 1
It answers one question: “If this had grown by the same percentage every single year, what would that percentage be?” It ignores the path in between and looks only at where you started, where you ended, and how long it took. That is its strength (one clean, comparable number) and its weakness (it hides volatility).
Step by step: three ways to calculate CAGR in Excel
Assume your starting value is in B2, your ending value is in B7, and the number of years between them is 5.
1. The direct formula
=(B7/B2)^(1/5)-1
Divide end by start, raise it to the power of one over the number of years, subtract 1. This works in every version of Excel, Google Sheets and LibreOffice. If your years are in column A, you can make the period automatic with =(B7/B2)^(1/(A7-A2))-1.
2. The RRI function
=RRI(5, B2, B7)
RRI stands for “relevant rate of interest”. Its arguments are the number of periods, the present value and the future value, and it returns the equivalent growth rate per period. It gives exactly the same result as the direct formula and is easier to read. It has been available since Excel 2013.
3. The RATE function
=RATE(5, 0, -B2, B7)
RATE is built for loans and annuities, so it follows a cash-flow sign convention: the starting value must be negative (money going out) and the ending value positive. The 0 means there are no regular payments in between. Forget the minus sign and Excel returns a #NUM! error.
Whichever you choose, press Ctrl+Shift+5 (or use Home → Number → %) to show the result as a percentage.
Worked example: five years of revenue
Here is a small business’s revenue over six year-ends (five years of growth). The third column shows each year’s actual change, and the last column shows the smooth path CAGR implies.
| Year | Revenue | Actual yearly change | Smooth path at 10.53% CAGR |
|---|---|---|---|
| 2020 | 120,000 | – | 120,000 |
| 2021 | 138,000 | +15.0% | 132,641 |
| 2022 | 131,000 | −5.1% | 146,614 |
| 2023 | 162,000 | +23.7% | 162,058 |
| 2024 | 175,000 | +8.0% | 179,130 |
| 2025 | 198,000 | +13.1% | 198,000 |
The calculation: 198,000 ÷ 120,000 = 1.65. Raise 1.65 to the power of 1/5 = 1.1053. Subtract 1 and you get 10.53%. In Excel, =(B7/B2)^(1/5)-1, =RRI(5,B2,B7) and =RATE(5,0,-B2,B7) all return 10.53%.
Notice that the actual path swung from −5.1% to +23.7%, yet the smooth path lands on exactly the same ending value. That is what CAGR is: the one constant rate that connects the two endpoints.
Now compare it with the simple average of the five yearly changes: (15.0 − 5.1 + 23.7 + 8.0 + 13.1) ÷ 5 = 10.95%. If you grew 120,000 at 10.95% for five years you would reach about 201,769, which overshoots the real 198,000. The simple average overstates growth whenever returns bounce around, which is why CAGR is the better summary.
Common mistakes
- Counting data points instead of periods. Six year-end values (2020 to 2025) cover five years of growth, not six. Using 6 in the example above gives 8.70% instead of 10.53%. The safest fix is to subtract the years:
A7-A2. - Averaging yearly growth rates. As shown above, the arithmetic mean overstates compound growth when results vary. Use CAGR for “how fast did it grow overall”.
- Dropping the minus sign in RATE. RATE needs opposite signs for the start and end values, or it returns
#NUM!. - Using a zero or negative starting value. CAGR is undefined if you start at zero, and misleading if either endpoint is negative (for example, profits that swing from loss to gain). Report the absolute change instead.
- Mixing up periods. For 18 months of data, use 1.5 years, not 18. If you divide by months, you get a monthly rate, not an annual one.
- Treating CAGR as a forecast. It describes what happened between two points. It says nothing about how bumpy the ride was, or what comes next. Changing the start or end year can change the answer a lot.
FAQ
Is there a CAGR function in Excel?
Not by that name. RRI is the closest thing: =RRI(periods, start, end) returns the compound growth rate directly. The manual formula =(End/Start)^(1/Years)-1 gives the same answer.
How do I calculate CAGR for months or quarters?
Express the period in years. For 18 months, use 1.5: =(End/Start)^(1/1.5)-1. If you want a rate per quarter instead, use the number of quarters, then annualise with =(1+quarterly_rate)^4-1.
What is the difference between CAGR and average annual growth?
Average annual growth is the arithmetic mean of each year’s percentage change. CAGR is the geometric rate that links the start and end values. When growth is uneven, the average is always higher than CAGR, and only CAGR reproduces the real ending value.
Does CAGR work in Google Sheets?
Yes. The direct formula and RATE work as they do in Excel, and Google Sheets also supports RRI.
Sources
This guide is for learning purposes only and is not investment advice.
Want more spreadsheet tricks and ideas explained simply? Watch the FindThinkLearn videos or subscribe on YouTube @findthinklearn.
Related: What Happens If You Miss the 10 Best Days in the Stock Market? · How Does Inflation Shrink Your Money? What $100 Buys Over Time
Pingback: What Happens If You Miss the 10 Best Days in the Stock Market? - findthinklearn