Excel guide
How to calculate CAGR in Excel
Short answer: CAGR = (End value / Start value)^(1 / number of years) - 1. In Excel, with revenue of 100 in 2021 in B2 and 150 in 2025 in F2: =(F2/B2)^(1/(2025-2021))-1 returns 10.7%.
Two formulas that give the same result
- Classic: =(End/Start)^(1/Years)-1, for example =(F2/B2)^(1/4)-1.
- Built-in: =RRI(Years, Start, End), for example =RRI(4, B2, F2). Available since Excel 2013.
- Format the result as a percentage with one decimal (0.0%).
Common mistakes
- Counting periods, not years: 2021 to 2025 is 4 years, not 5.
- Hardcoding the number of years inside the formula. Reference the year cells instead, so the CAGR updates when the timeline moves, and AutoColor won't flag it as a plug.
- Negative or zero start values: CAGR is not meaningful and Excel returns #NUM!. Show n.m. instead.
One click with Plzfix
Click CAGR on the Plzfix tab, then pick the end value, start value, end year and start year. Plzfix writes the formula with cell references, so nothing is hardcoded.
Questions
What is the difference between CAGR and average growth?
Average growth is the arithmetic mean of yearly growth rates. CAGR is the constant rate that takes the start value to the end value, so it ignores volatility in between. Banks quote CAGR.
How do I show n.m. when CAGR cannot be computed?
Wrap the formula: =IFERROR((F2/B2)^(1/4)-1,"n.m."). Plzfix has an IFERROR(n.a.) button that wraps any selected formulas in one click.
How much does Plzfix cost?
There is a free tier with a daily click allowance and every tool unlocked. Pro is €10 per month, €50 for 6 months or €90 per year, per user, with unlimited use.