CAGR and Percentage Change in Excel: Counting Periods, Not Data Points
Seven years of revenue is six periods of growth, and using seven turns a 15.7% CAGR into 13.4%. Here is the formula, the three Excel functions that all compute it, and the percentage-point confusion that costs more than the arithmetic.
Count the Gaps, Not the Numbers
Compound annual growth rate answers one question: what steady annual rate would take the starting value to the ending value over the period. It is a single formula and almost every error in it is the same error, counting data points instead of the intervals between them.
Revenue for 2019 through 2025 is seven figures and six years of growth. Using seven understates the rate by more than two percentage points.
Try the compound interest calculatorRun the arithmetic forwards to check that your CAGR reproduces the ending value.The formula, three ways
All three return the same number. RRI is the most readable and is available in current versions. Use it unless you need the workbook to open in something very old.
Swipe sideways to compare columns.
| Year | Revenue |
|---|---|
| 2019 | $1,240,000 |
| 2025 | $2,980,000 |
Non-annual periods
RRI returns a rate per period, whatever the period is. Feeding it 36 months returns a monthly rate, which then needs annualising.
Simple percentage change, and its two traps
Percentage points are not percent
This is the error that reaches board packs. A margin improving from 8% to 10% has improved by 2 percentage points and by 25%. Both statements are correct and they are very different claims.
Swipe sideways to compare columns.
| From | To | Percentage points | Percent change |
|---|---|---|---|
| 8% | 10% | +2.0 pp | +25.0% |
| 40% | 42% | +2.0 pp | +5.0% |
| 2% | 4% | +2.0 pp | +100.0% |
| 62% | 58% | −4.0 pp | −6.5% |
Rule of thumb: use percentage points when comparing two percentages, and percent change when comparing two quantities. Label the column so the reader knows which they are looking at, because the two cannot be distinguished from the number alone.
Reversing a percentage change
A 20% increase followed by a 20% decrease does not return to the starting point. It ends 4% below it, and the asymmetry grows with the size of the change.
Swipe sideways to compare columns.
| Change | From $100 up, then down | Net |
|---|---|---|
| 10% | $110.00 then $99.00 | −1.0% |
| 20% | $120.00 then $96.00 | −4.0% |
| 30% | $130.00 then $91.00 | −9.0% |
| 50% | $150.00 then $75.00 | −25.0% |
Never average growth rates
Growth rates compound, so the arithmetic mean overstates them. A year of +50% followed by a year of −50% averages to zero and actually loses a quarter of the money.
What CAGR does not tell you
- Volatility. Two series with identical endpoints have identical CAGRs, whatever happened in the middle. Report the standard deviation of annual growth alongside it.
- Whether the endpoints are representative. A CAGR measured from a trough to a peak is a marketing number. Moving either endpoint by one year can change it by several points.
- Anything about the future. It is a description of what happened, and extrapolating it is a forecasting decision that needs its own justification.
- The effect of inflation. A 15.7% nominal CAGR at 3% inflation is about 12.4% real, and the difference compounds over a long series.
- Whether the growth was organic. Acquisitions, price increases, and new products all show up in one number, and they mean different things.
- Anything about a series that crosses zero. CAGR is undefined when the starting value is zero or the sign changes, and any formula that returns a number there is wrong.
How many periods should I use for CAGR?
The number of intervals, which is one fewer than the number of data points. Revenue for 2019 through 2025 is seven figures and six periods. Using seven understates the rate; on the example above it turns 15.74% into 13.35%.
What is the difference between RRI and RATE?
None, for this purpose. RRI takes periods, present value, and future value, and is purpose-built for a growth rate. RATE is the general annuity function and needs a zero payment argument. Use RRI for readability.
Can CAGR be negative?
Yes, and it works normally as long as both endpoints are positive. Falling from $2,000,000 to $1,400,000 over four years is a −8.5% CAGR. What does not work is a zero or negative starting value, where the formula is undefined.
Why does a 20% gain followed by a 20% loss not break even?
Because the loss applies to a larger base. $100 becomes $120, and 20% of $120 is $24 rather than $20, leaving $96. The gap widens with the size of the swings, which is why a volatile series compounds to less than its average return suggests.
Should I say "up 2 percentage points" or "up 25%"?
Percentage points when both figures are already percentages, such as a margin moving from 8% to 10%. Percent change when comparing quantities. Say which one you mean, since a reader cannot tell from the number and the two differ by more than a factor of ten in that example.
How do I compute CAGR when the dates are not whole years apart?
Use YEARFRAC for the period count: =RRI(YEARFRAC(StartDate, EndDate), Start, End). That handles a period like 3 years and 7 months without rounding, and it is more honest than forcing the data to whole years.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team