# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-cagr-and-percentage-change
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the compound interest calculator](https://dothecalculation.com/calculators/compound-interest-calculator) — Run the arithmetic forwards to check that your CAGR reproduces the ending value.

## The formula, three ways

**CAGR**

```
CAGR = (Ending ÷ Beginning)^(1 ÷ n) − 1, where n is the number of periods
```
- In Excel: =(End/Start)^(1/n)-1
- Or: =RRI(n, Start, End), which is purpose-built and clearer
- Or: =RATE(n, 0, -Start, End), the general annuity function with no payments

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.

**Revenue, 2019 to 2025**
| Year | Revenue |
| --- | --- |
| 2019 | $1,240,000 |
| 2025 | $2,980,000 |

**The right answer and the wrong one**

```
=RRI(6, 1240000, 2980000) = 15.74%    ·    =RRI(7, 1240000, 2980000) = 13.35%
```
- 2025 minus 2019 is 6, which is the number of years of growth.
- Seven is the number of annual figures, including both endpoints.
- Check by compounding forwards: 1,240,000 × 1.1574⁶ = 2,980,000.

> **The check that takes five seconds** — Multiply the starting value by one plus the CAGR, raised to n. If it does not land on the ending value, n is wrong. Put that check in a cell beside the CAGR and never think about the off-by-one again.

## 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.

**Monthly data, annual answer**

```
Monthly rate: =RRI(36, Start, End)    ·    Annualised: =(1+MonthlyRate)^12−1
```
- Do not multiply the monthly rate by 12. That gives a nominal rate, not a compound one.
- A 1.2% monthly rate is 15.39% annualised, not 14.4%.
- For irregular dates, use YEARFRAC to compute n: =RRI(YEARFRAC(StartDate, EndDate), Start, End).

## Simple percentage change, and its two traps

**Percentage change**

```
=(New − Old) / ABS(Old)
```
- The ABS matters when the base can be negative. A loss of $50,000 improving to a loss of $20,000 is a 60% improvement, and without ABS the sign flips.
- When Old is zero the change is undefined, not infinite. Return a blank or "n/a" rather than #DIV/0!.
- Formatting the cell as a percentage handles the multiplication by 100; do not do both.

## 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.

**The same movement, described two ways**
| 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.

**Up then down by the same percentage**
| 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% |

**Undoing an increase**

```
Original = New ÷ (1 + increase)
```
- A price of $138 after a 15% increase was $120, not $117.30.
- Subtracting 15% from $138 gives $117.30, which is wrong by $2.70.
- The same shape as removing tax from a tax-inclusive price.

## 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.

**The geometric mean of a growth series**

```
=GEOMEAN(1+rates) − 1    ·    or simply =RRI(n, First, Last)
```
- GEOMEAN needs growth factors, so 1 plus each rate, not the rates themselves.
- On +50% and −50%: GEOMEAN(1.5, 0.5) − 1 = −13.4% a year.
- RRI on the first and last values gives the same answer without a helper column, and is the better habit.

> **CAGR is a summary, not a description** — A CAGR of 15.7% is consistent with steady growth and with two flat years followed by a doubling. It only ever uses the first and last values, so it is blind to everything in between. Show the series alongside it, or the reader will assume the smooth version.

## 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-cagr-and-percentage-change). Quote freely with attribution and a link to this page._