CAGR Formula in Excel: Growth Rate with RRI and Power Method

The CAGR formula in Excel answers "at what steady yearly rate did this grow?" — smoothing lumpy revenue, portfolio, or user trajectories into one comparable number. It is the inverse of compounding forward: FV projects value from a rate, CAGR extracts the rate from two values. Investor updates, category reviews, and salary benchmarking all run on it, which is why getting its edge cases right matters more than getting its syntax right.

On the Data Analyst Roadmap, CAGR sits beside PMT in the finance-modelling block: the two functions that turn analysts into people who can price things. This quantitative foundation is also central to data science in finance.

CAGR Formula Excel: Syntax and How It Works

No single "CAGR()" exists — two equivalent expressions do the job. The teaching form:

=(End_Value/Start_Value)^(1/Years)-1

And the built-in, per Microsoft Support: RRI:

=RRI(nper, pv, fv)

=RRI(4, 5000000, 9000000) returns the same 15.8% with the period count explicit — preferable inside models where years come from date arithmetic rather than literals. The core intuition: CAGR is the geometric mean of growth, not the arithmetic one. It answers "what constant rate compounds start into end?" which is the only growth figure that survives comparison across assets, periods, and volatilities.

Step-by-Step Example: Category Growth Review

D2C category revenue: ₹50,00,000 in FY22, ₹90,00,000 in FY26 — four years, with yearly growth of +40%, −10%, +25%, +8% underneath:

Step 1 — compute headline CAGR in B4:

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

with B1 = 5000000 (FY22), B3 = 9000000 (FY26). Result: 15.8%. Format as percentage with two decimals — a CAGR quoted without decimals implies precision the inputs rarely support.

Step 2 — show why the naive average misleads. Average of (+40, −10, +25, +8)/4 = 15.75%? Here they nearly match by luck — construct the honest comparison with a volatile twin (say +80%, −40%, +60%, −20%, same endpoints impossible, so instead demonstrate on the same series):

=AVERAGE(40%, -10%, 25%, 8%)

The arithmetic mean ignores that −10% shrinks the base the next +25% grows from. On volatile series the average systematically exceeds CAGR — sometimes by multiples — which is why bonus targets written on "average growth" are quietly easier than targets written on CAGR. State which one a KPI uses.

Step 3 — derive years from dates so the model survives refreshes:

=(B3/B1)^(1/((A3-A1)/365))-1

With start/end dates in A1/A3, the exponent annualises fractional periods automatically — 4 years 7 months becomes 4.58, no manual counting. For month-level precision use YEARFRAC(A1, A3, 1) in place of the day division.

Step 4 — cross-check with RRI in the audit column:

=RRI(4, B1, B3)

Identical 15.8% confirms both implementations. Keep this cross-check pattern from interface auditing: two independent expressions of the same quantity, compared in a check cell, catch transcription errors before reviews do.

How do you annualise growth over months instead of years?

CAGR over 30 months is not a 30-month rate — annualise it so it compares with yearly figures. Drive the exponent from dates with YEARFRAC and keep the power form:

=(B3/B1)^(1/YEARFRAC(A1, A3, 1))-1

With start/end dates in A1/A3, YEARFRAC returns 2.5 for thirty months and the exponent annualises automatically. The RRI equivalent takes fractional periods just as happily: =RRI(2.5, B1, B3). Two cautions: YEARFRAC's basis argument changes day-counting at the margins (basis 1 actual/actual is the honest default for growth math), and sub-year CAGR amplifies noise — a hot quarter annualised to 60% misleads more than it informs. For periods under a year, show the absolute multiple beside the annualised rate so readers see both the fact and the projection.

Common Mistakes and Fixes

The second classic: counting periods wrong. FY22→FY26 is 4 intervals, not 5 data points — using 5 annualises over an extra phantom year and understates growth. The rule: periods = number of compounding steps, i.e. end-year minus start-year. Date-driven exponents (Step 3) eliminate this class of error entirely.

CAGR Formula Excel vs Other Return Metrics

Use CAGR for endpoint-to-endpoint stories with no interim flows: revenue trajectories, user growth, category benchmarks. Switch to XIRR the moment money enters or leaves mid-period — SIPs, fundraises, dividend payouts. And relegate the arithmetic average to diagnostics; it describes the years, not the journey.

When to Use the CAGR Formula Excel in Analyst Work

Investor and leadership updates. "Revenue compounded at 15.8% over four years" is the opening line of every growth narrative — one number carrying the whole trajectory, with the yearly path charted beside it for honesty. Standardise the date-driven form so next quarter's update is a data refresh, not a rebuild.

Benchmarking across assets and periods. Fund A: 5-year CAGR. Fund B: 3-year CAGR. Only annualised rates compare across mismatched horizons — absolute multiples cannot. The same logic benchmarks regions, categories, and cohorts: CAGR is the common currency of growth claims.

Target-setting with teeth. A "grow 2x in 5 years" mandate is a 14.9% CAGR (=2^(1/5)-1) — instantly decomposable into yearly run-rates the business can be held to. Writing targets as CAGRs exposes whether the required yearly pace has ever actually been achieved, which is precisely the conversation targets should force.

Related Excel Tutorials

• Excel Formulas: The Complete Guide
• XLOOKUP Formula in Excel
• SUM Formula in Excel
• Excel Pivot Tables Guide
• Explore All Guides in the Excel Tutorials Hub