How to calculate CAGR (compound annual growth rate) in Excel

CAGR is the single smoothed annual rate that connects where a metric started to where it ended — the cleanest way to summarise multi-year growth in one number. This guide gives you the exact Excel formula, two alternative methods using POWER and RATE, a worked example you can copy, and the handful of pitfalls (off-by-one years, negative bases) that quietly corrupt the figure.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.What CAGR actually is

CAGR — compound annual growth rate — answers one question: if a metric had grown at a perfectly steady rate every year, what rate would have taken it from its starting value to its ending value? It smooths out the bumpy reality of individual years into a single, comparable annual figure.

That makes it the go-to number for revenue, users, AUM, or any metric tracked over several years. A board member doesn’t want to hear “up 40%, then down 5%, then up 30%” — they want “a 19% CAGR over three years”. It is also why CAGR is honest in a way a simple average is not: averaging yearly growth rates ignores compounding and lets one wild year dominate. CAGR uses only the endpoints and the time elapsed, so it always reconciles back to the actual numbers.

The trade-off: because CAGR only looks at the two endpoints, it is blind to everything in between. A metric that crashed and recovered shows the same CAGR as one that grew smoothly. Pair it with year-over-year growth when you need to see the path, not just the destination.

2.Step 1 — Lay out the data

Open Excel. You need three things: the beginning value, the ending value, and the number of years between them. Put them in their own cells so the formula stays readable.

If you have a start date and end date rather than a year count, derive the years with =YEARFRAC(start_date, end_date) — it handles partial years correctly, which matters more than people expect.

3.Step 2 — The CAGR formula

The textbook formula, and the one to memorise:

In Excel, using the layout above:

=(B2/B1)^(1/B3)-1

Format the result cell as a percentage (Ctrl+Shift+%). With our numbers, this returns 0.1907 — a 19.07% CAGR. The caret (^) is Excel’s exponent operator, so (1/B3) raises the growth ratio to the power of one-fifth, which is the same as taking the fifth root.

4.Step 3 — The POWER method

Some people find the caret hard to read in a long formula. POWER makes the exponent explicit and produces an identical result:

=POWER(B2/B1, 1/B3) - 1

Read it as “raise the ratio B2/B1 to the power 1/B3, then subtract 1”. There is no numerical difference between this and the caret form — pick whichever your team finds clearer. POWER tends to win in shared models because reviewers can parse it at a glance.

5.Step 4 — The RATE method

Excel has no function literally called CAGR, but the financial function RATE does exactly the same job — it solves for the interest rate that turns a present value into a future value over a number of periods:

=RATE(B3, 0, -B1, B2)

The arguments are: number of periods (B3), payment per period (0 — there are none), present value (-B1, entered negative because RATE treats it as a cash outflow), and future value (B2). Get the sign wrong on the beginning value and RATE returns an error, which is a useful built-in sanity check. This method is handy when you are already working inside a finance model that thinks in cash flows.

6.Step 5 — Count the years correctly

This is where most CAGR mistakes live. The exponent is the number of periods between the endpoints, not the number of data points.

If you have five annual figures — 2021, 2022, 2023, 2024, 2025 — the elapsed time from the first to the last is 4 years, not 5. Counting the labels instead of the gaps inflates the denominator, which deflates the CAGR. When in doubt, use dates and YEARFRAC so Excel counts the elapsed time for you and you never have to argue about it.

Worked example

A small SaaS product grows its annual recurring revenue as follows (illustrative figures):

The yearly growth is all over the place — a 50% jump, then a near-flat year, then a small decline. CAGR cuts through it. There are 5 years between start-2021 and start-2026, so:

=(240000/100000)^(1/5)-1   →   0.1907   →   19.07%

Notice this is not the average of the YoY column (which averages to about 20.4%). The simple average overstates the truth because it weights the volatile 50% year equally with the flat ones and ignores compounding. The 19.07% CAGR is the figure that actually reconciles 100,000 to 240,000 over five years — so it is the one to put in the deck.

Common mistakes

1. Off-by-one on the years. Counting data points instead of the gaps between them. Five annual figures span four years. Use YEARFRAC on real dates to remove the guesswork.

2. A negative or zero beginning value. CAGR divides by the beginning value and takes a root of the ratio. If the base is zero or negative, the maths breaks or returns nonsense. CAGR is only meaningful when both endpoints are positive.

3. Quoting the average of yearly rates instead. The arithmetic mean of annual growth rates is always equal to or higher than the true CAGR. It is a different, and usually flattering, number — don’t pass it off as CAGR.

4. Comparing a partial year with full years. If your ending value is a year-to-date figure, annualise it first or your CAGR will understate the rate. Never mix a 9-month figure into a series of 12-month ones.

The 30-second shortcut

CAGR is the headline; the trend underneath it is the story. Our free forecasting calculator takes any pasted column of yearly or monthly values and shows you the growth rate alongside an actual forecast, in your browser, no signup. Same maths, none of the cell-reference fiddling.

Frequently asked questions