How to calculate CAGR in Google Sheets and Excel
Short answer
To calculate Compound Annual Growth Rate in Google Sheets or Excel, use the geometric formula =(End/Start)^(1/Years)-1 or the built-in =RRI(Years, Start, End) function. CAGR determines the constant annual rate that compounds initial capital to its final balance over time, smoothing out periodic volatility into a single annualized metric without requiring interim pricing data.
Formula and recurrence relations
CAGR = (V_end / V_start)^(1 / n) − 1 Google Sheets / Excel: =RRI(n, V_start, V_end) Manual Formula: =(B2 / B1)^(1 / B3) − 1
The Geometric Mean versus Arithmetic Averaging
When analyzing investment returns across multiple years, relying on the simple arithmetic average produces deceptive numbers. If a portfolio gains 100% in Year 1 and falls 50% in Year 2, the arithmetic average is (+100% - 50%) / 2 = +25% per year. However, starting with $10,000, your portfolio rose to $20,000, then fell back to $10,000. Your actual net compound return is 0.00%.
Compound Annual Growth Rate solves this fundamental distortion by calculating the geometric mean. It measures the precise constant rate at which initial capital must compound year after year to reach the final valuation.
Step-by-Step Implementation in Google Sheets and Excel
Spreadsheet software provides two primary ways to compute CAGR. The most transparent approach utilizes direct cell referencing: place your initial investment in cell B1, your ending valuation in cell B2, and your elapsed holding duration in years in cell B3. In cell B4, enter =(B2/B1)^(1/B3)-1 and format the result as a percentage.
Alternatively, modern spreadsheet engines provide the native RRI function: =RRI(B3, B1, B2). The RRI function accepts arguments in the order of periods, present value, and future value. Both formulas return identical geometric rates to full floating-point precision.
When CAGR Breaks Down: The Multi-Cash-Flow Boundary
A critical systems boundary of CAGR is that it assumes zero cash movements between the initial start date and final end date. It assumes a single lump-sum deposit at inception and a single terminal withdrawal at exit.
If you make recurring monthly deposits, take interim dividends as cash, or rebalance across accounts, CAGR ceases to be mathematically valid. In such situations, the correct financial engine is XIRR (Extended Internal Rate of Return), which accounts for calendar-stamped cash flow schedules.
Logarithmic Compounding and Duration Scaling
Behind the fractional exponent (1 / n) lies logarithmic mathematics. Taking the natural logarithm of both sides transforms multiplicative compounding into additive linear progression: ln(1 + CAGR) = (ln(V_end) - ln(V_start)) / n. This relationship reveals why small percentage differences in CAGR produce enormous terminal wealth divergence over multiple decades.
When working with fractional periods such as 3 years and 7 months, convert duration into exact decimal years: n = 3 + (7 / 12) = 3.5833 years. Using exact calendar day counts via (Date_end - Date_start) / 365.25 provides even higher precision across leap year cycles.
The natural logarithm of the terminal multiple divided by elapsed years yields the continuously compounded rate, which is slightly lower than the effective annual geometric CAGR.
$10,000 Growing to $18,000 Over 5 Years
Suppose an investor allocates $10,000 into an index portfolio on January 1, 2020, and holds it untouched until January 1, 2025, when the portfolio is liquidated at $18,000. We compute the exact geometric CAGR using both mathematical steps and spreadsheet formulas.
| Parameter / Cell | Description | Value | Formula Representation |
|---|---|---|---|
| Start Capital (B1) | Initial portfolio balance | $10,000 | Baseline cash input |
| End Valuation (B2) | Terminal portfolio valuation | $18,000 | Liquidation sum |
| Duration Years (B3) | Elapsed holding time | 5.0 years | 2020-01-01 to 2025-01-01 |
| Absolute Gain | Dollar increase | $8,000 | =B2 - B1 (+80.00%) |
| Growth Multiple | Ratio of terminal to initial | 1.8000 | =B2 / B1 |
| Geometric CAGR (B4) | Annualized compound rate | 12.47% | =(B2/B1)^(1/B3)-1 or =RRI(B3, B1, B2) |
Numbered Step-by-Step Procedure:
Divide ending valuation by beginning capital: $18,000 / $10,000 = 1.8000 (80.00% total gain).
Invert the 5-year duration: 1 / 5 = 0.2000.
Raise growth factor to the exponent: 1.8000^0.20 = 1.12468.
Subtract 1: 1.12468 - 1 = 0.12468 (12.47% per annum).
Common mistakes
- ✕Using the arithmetic average (e.g. +80% / 5 = +16% per year), which overstates annual compounding performance by 3.53 percentage points.
- ✕Applying CAGR to portfolios with periodic monthly contributions or withdrawals, where dollar-weighted timing requires XIRR.
- ✕Entering holding periods in months rather than fractional years (e.g. entering 18 months as 18 instead of 1.5, causing a catastrophic calculation error).
- ✕Comparing CAGRs across differing time horizons without controlling for economic cycles and market volatility regimes.
CAGR Calculator
Calculate compound annual growth rate, absolute return, and year-by-year progression with interactive sliders.
Related resources
CAGR Calculator
Interactive compound annual growth rate calculator with live progression charts.
Compound Interest in Systems Glossary
Formal mathematical definition and exponential progression properties.
Book 01: Compounding Wealth Multiplier
Our foundational short book explaining exponential accumulation and recurrence relations.
Volatility Drag: Geometric vs Arithmetic Returns
Why CAGR equals the geometric mean and always trails arithmetic averages under volatility.