CAGR (Compound Annual Growth Rate)

Excel Formulas › Financial

All versionsRRIPower

CAGR is the smooth yearly growth rate that takes a starting value to an ending value over N years — the standard way to compare investments or revenue growth. It’s one short formula, and RRI does it natively.

Quick formula: for a start value in B1, end value in B2, over N years in B3:
=(B2 / B1)^(1 / B3) - 1
Divide end by start, take the Nth root (raise to 1/N), and subtract 1. Format the cell as a percentage.

Functions used (tap for the full reference guide):

RRIPOWER

The example

Revenue grew from $100k to $161k over 5 years.

AB
1Start (yr 0)$100,000
2End (yr 5)$161,000
3Years5
4CAGR10.0%

The formula

The compound annual growth rate:

=(B2 / B1)^(1 / B3) - 1 // (161000/100000)^(1/5) - 1 ≈ 10.0%

How it works

CAGR un-compounds the total growth into a per-year rate:

  1. B2 / B1 is the total growth factor over the whole period — 1.61×.
  2. Raising it to 1 / B3 takes the 5th root, finding the single yearly factor that compounds to 1.61 over 5 years — about 1.10.
  3. Subtracting 1 converts the factor (1.10) to a rate (0.10 = 10%).
  4. Format as a percentage. The same answer comes from =RRI(B3, B1, B2).

Count years, not data points. Five years of growth spans yr 0 to yr 5. If your data is in a row of 6 annual figures, N is 5 (the number of gaps), not 6.

Try it: interactive demo

Live demo

Enter start, end, and years.

CAGR:

Variations

Native RRI function

Purpose-built for this — note the argument order:

=RRI(B3, B1, B2)

From a row of annual values

First and last cells, count the gaps:

=(End / Start)^(1 / (COUNT(values) - 1)) - 1

Project a future value from CAGR

Grow the end value forward another 3 years:

=B2 * (1 + CAGR)^3

Pitfalls & errors

Off-by-one on years. Use the number of periods between the start and end (gaps), not the count of data points. 6 yearly figures = 5 years of growth.

CAGR hides volatility. It’s a smoothed average; the actual path may have been bumpy. It answers “equivalent steady rate,” not “what happened each year.”

Negative or zero start. CAGR is undefined if the starting value is ≤ 0. The math (and meaning) breaks down.

Practice workbook

📊
Download the free CAGR (Compound Annual Growth Rate) practice workbook
The growth example with the live CAGR formula and the RRI equivalent, plus a projection variant and 4 challenges with answers. No sign-up required.

Frequently asked questions

What is the CAGR formula in Excel?
=(end / start)^(1 / years) - 1, formatted as a percentage. For example =(B2/B1)^(1/B3)-1. The RRI function does the same: =RRI(years, start, end).
How many years do I use in CAGR?
Use the number of periods between the start and end values (the gaps), not the count of data points. Six annual figures represent five years of growth.
Why is my CAGR an error or nonsense?
CAGR requires a positive starting value and at least one year. A zero/negative start or zero years makes the formula undefined.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Compound interest · Future value of savings · Percent change

Function references: RRI · POWER