CAGR formula: calculate Compound Annual Growth Rate over multiple years

Why arithmetic average growth misstates multi-year performance

If revenue rises +100% from $100 to $200 in Year 1 and drops -50% back to $100 in Year 2, the simple average of +100% and -50% claims +25% annual growth—even though net growth is 0%!

Compound Annual Growth Rate (CAGR) solves Start * (1 + r)^Years = End for r, giving (End / Start) ^ (1 / Years) - 1. Note that Years equals the number of compounding intervals (e.g., 2023 to 2026 is 3 intervals, not 4 rows).

Use a positive starting value, a positive number of years and a nonnegative ending value. CAGR is a smoothed historical rate, not a prediction; it ignores intermediate volatility and is not a cash-flow-adjusted investment return. IFERROR alone does not validate these assumptions.

CAGR formula and forward compounding check

Growing from $500,000 to $864,000 across 3 years gives ratio 1.728; its cube root 1.728 ^ (1/3) is 1.20, so CAGR = 0.20 (20%). Compounding $500,000 * 1.20^3 confirms $864,000.

=ROUND((B2 / A2) ^ (1 / C2) - 1, 4)

Zero starting value boundary (#DIV/0!)

CAGR is undefined when StartRev = 0 (#DIV/0!) or negative; wrap with IFERROR or IF(A2<=0, "N/A", ...) on pre-revenue cohorts.

=ROUND(A2 * ((1 + 0.2) ^ C2), 2)

Further reading

• Microsoft Support — RRI function (equivalent compound growth rate)