Excel CAGR Formula: Compound Annual Growth Rate Calculation, Methods, and Real-World Examples

The Excel CAGR formula calculates Compound Annual Growth Rate — the smoothed annual rate of growth between two points in time, accounting for compounding. CAGR is one of the most-used metrics in business and finance because it tells you the equivalent steady annual growth rate even when actual growth was uneven.

Why CAGR matters. Raw growth rates are misleading. If a business grew 50% one year and shrunk 25% the next, average growth is 12.5% — but the actual end-to-end result is only 12.5% growth over two years (12.5% / 2 = 6.25% per year if averaged annually... but CAGR is 6.07%). CAGR gives you the truly comparable annual rate.

The CAGR formula. CAGR = (End Value / Start Value)^(1/Years) - 1. In Excel, this becomes: =(End/Start)^(1/Years)-1. Multiplied by 100 to get a percentage.

Common uses. Investment returns over multiple years (stocks, bonds, real estate). Revenue growth for businesses (year-over-year comparisons). Population growth, customer acquisition rates, market expansion. Compounding savings projections. Any time-series where you want a smoothed annual growth rate.

Why use Excel for CAGR. Speed (one formula). Accuracy (exact compound math). Flexibility (recalculates as data changes). Reporting (easy to copy across categories). Visual (combine with charts to show actual vs CAGR-projected growth).

This guide covers four ways to calculate CAGR in Excel — direct formula, RATE function, POWER function, and named formulas — with practical examples for investing, business reporting, and financial modeling.

Method 1: Direct formula. The simplest approach.

Setup. In your spreadsheet, you have: beginning value in cell B2, ending value in B3, number of years in B4 (or calculate years from start and end dates).

Formula. In B5: =(B3/B2)^(1/B4)-1. Format cell B5 as percentage with 2-3 decimal places.

Example 1: Investment CAGR. You invested $10,000 in 2018. By 2024 it's worth $15,386. How much CAGR? Beginning: 10000 in B2. Ending: 15386 in B3. Years: 6 in B4 (2024-2018). Formula: =(15386/10000)^(1/6)-1 = 0.0739 or 7.39%. Your investment grew at 7.39% per year compounded.

Example 2: Revenue CAGR for a business. Revenue 2020: $5M. Revenue 2024: $9M. 4 years. =(9000000/5000000)^(1/4)-1 = 0.1576 or 15.76% CAGR.

Common variations. =(B3/B2)^(1/(B4-B2_year))-1 if years are stored as dates. =((B3/B2)^(1/B4))-1 — equivalent with explicit parentheses. =POWER(B3/B2,1/B4)-1 — using POWER function for clarity.

Year counting. From 2018 to 2024 is 6 years (2024-2018=6). Some calculations use 'years inclusive' (e.g., 2018-2024 = 7 if counting both endpoints), but for CAGR we use the year span, not inclusive count. From January 1, 2018 to January 1, 2024 = exactly 6 years.

Date-based year calculation. If start_date in C2 and end_date in C3: years = (C3-C2)/365.25. For more precision: =(C3-C2)/DAYS(DATE(YEAR(C3)+1,MONTH(C3),DAY(C3))-DATE(YEAR(C3),MONTH(C3),DAY(C3)),0) — but standard practice uses 365.25 day average.

Why /1 in the exponent. The formula raises (end/start) to (1/years), which is the inverse of years. This is mathematically equivalent to taking the n-th root, where n = years. If you've taken algebra, this is the same as POWER(end/start, 1/years).

Method 2: RATE function. An alternative that returns the same result.

Formula. =RATE(years, 0, -start, end). The 0 is for periodic payment (none in CAGR). The negative sign on start treats it as an outflow.

Example. =RATE(6, 0, -10000, 15386) = 0.0739 or 7.39%. Same as direct CAGR formula.

Advantages. Built-in financial function — may feel more natural to financial analysts. Documents the calculation as a financial computation. RATE handles edge cases (annuities, periodic deposits) that direct formula doesn't.

Disadvantages. Slightly slower (iterative calculation under the hood). Requires correct sign convention (start is negative). Less obvious to non-finance users.

Method 3: POWER function. The most readable variation.

Formula. =POWER(end/start, 1/years)-1. Mathematically identical to direct method.

Example. =POWER(15386/10000, 1/6)-1 = 0.0739.

Advantages. Explicit POWER function makes the math clear. Easier to debug — you can see exactly what's being raised to what.

Disadvantages. Slightly longer to type. Most analysts use direct caret (^) operator.

Method 4: Named formula. For repeated use, name your CAGR.

Setup. Formulas tab → Define Name. Name: CAGR. Refers to: =(end/start)^(1/years)-1. (Replace end, start, years with actual cell references.)

Usage. Then use =CAGR anywhere. More maintainable in large models — one place to update the formula if business logic changes.

For very large models. Consider LET function: =LET(start, B2, end, B3, yr, B4, (end/start)^(1/yr)-1). Names internal variables for readability.

Real-world examples. CAGR in action across industries.

Example 1: Stock investment over 10 years. You bought $50,000 of an index fund in 2014. In 2024, it's worth $128,000. CAGR = (128000/50000)^(1/10)-1 = 0.0973 or 9.73%. Your investment grew at 9.73% CAGR over 10 years — including all the ups and downs.

Compared to 'average return' of (128000-50000)/50000/10 = 15.6% — the simple average overstates true growth because it doesn't compound. CAGR is the accurate measure.

Example 2: Company revenue growth, 5-year comparison. Acme Corp revenue: 2019: $50M, 2024: $89M. CAGR = (89/50)^(1/5)-1 = 0.1224 or 12.24%. Useful for: investor pitches ('we've grown 12% CAGR'), benchmarking against competitors, projecting 2029 revenue at the same rate (89×1.1224^5 ≈ $158M).

Example 3: Customer base growth. Year 1: 1,000 customers. Year 4: 12,000 customers. CAGR = (12000/1000)^(1/3)-1 = 1.289 or 128.9%. Astronomically high — typical for early-stage startups. Reflects S-curve growth pattern.

Example 4: Real estate appreciation. House bought for $180,000 in 2008. Sold for $385,000 in 2024. CAGR = (385000/180000)^(1/16)-1 = 0.0488 or 4.88%. Modest annualized appreciation typical of long-term housing.

Example 5: Negative CAGR (decline). A business's revenue dropped from $5M (2018) to $3.2M (2024). CAGR = (3.2/5)^(1/6)-1 = -0.0742 or -7.42% per year. Useful for: identifying decline trends, comparing decline severity across business units, modeling worst-case scenarios.

Example 6: Comparing investments. Investment A: $10K → $14K in 5 years. CAGR = (14/10)^(1/5)-1 = 6.96%. Investment B: $10K → $12K in 3 years. CAGR = (12/10)^(1/3)-1 = 6.27%. Investment A had higher CAGR. Useful when investments have different time horizons — CAGR normalizes for fair comparison.

Common CAGR mistakes and how to avoid them.

Mistake 1: Counting years incorrectly. The most common error. From 2018 to 2024 is 6 years (2024-2018=6), not 7. From January 1, 2020 to December 31, 2024 is 5 years (4 full years of growth between the two endpoints), even though it spans parts of 5 calendar years.

Mistake 2: Off-by-one errors in date calculations. If your data is 'year-end values for 2018-2024' (7 data points: 2018, 2019, 2020, 2021, 2022, 2023, 2024), there are 6 years of growth, not 7. Count gaps between data points, not data points themselves.

Mistake 3: Using CAGR for periods with negative values. If your starting or ending value is negative or zero, CAGR breaks down. (You can't take a fractional power of a negative number meaningfully.) For start = 0, CAGR is mathematically undefined (or infinite). Use different metrics: absolute change, NPV, IRR, or specify 'changed from -X to Y over Z years.'

Mistake 4: Misleading CAGR over short periods. CAGR works best for ≥3 years. Over 1-2 years, raw growth rates are often more informative. CAGR over 2 years can be heavily influenced by start/end timing.

Mistake 5: Confusing CAGR with average annual growth. CAGR ≠ (sum of yearly growth rates) / n. Average can be 15% while CAGR is only 6% if growth was volatile. Always use the formula, not arithmetic averaging.

Mistake 6: Inappropriate extrapolation. CAGR tells you what happened, not what will happen. Don't project 5 years of past 30% CAGR forward — growth tends to slow as scale increases. Use CAGR for description, not blind projection.

Mistake 7: Comparing apples to oranges. CAGR of revenue vs CAGR of profit vs CAGR of customer count are all valid but not directly comparable. Specify what's growing. 'Revenue CAGR of 12%' is meaningful; 'Growth CAGR of 12%' is ambiguous.

Mistake 8: Ignoring inflation. Nominal CAGR (raw numbers) includes inflation. Real CAGR (inflation-adjusted) shows actual purchasing power growth. For long periods, real CAGR is more meaningful for individual returns. =(real_end/real_start)^(1/years)-1 after adjusting both endpoints to a single year's purchasing power.

Building a CAGR analysis template. A reusable spreadsheet for ongoing analysis.

Setup the structure. Row 1: Headers (Year, Value). Row 2: Year 1 label. Rows 3-N: subsequent years. Column C: 'Year-over-Year %' growth. Column D: 'CAGR to Date' calculation.

YoY growth formula. C3: =(B3-B2)/B2. Format as percentage. Copy down. Shows individual year growth rates.

CAGR to date formula. D3: =(B3/B$2)^(1/(A3-A$2))-1. Format as percentage. Copy down. Shows CAGR from base year to each subsequent year.

Final CAGR. Below the data: =(last_value/first_value)^(1/total_years)-1. Single number summarizing entire period.

Add visualization. Insert chart of data. Add exponential trendline. Display equation and R-squared. Visual confirmation that growth is roughly exponential.

Add projections. Below CAGR cell: 'Projected next 5 years.' New column with future years. Formula: =start_value * (1+cagr)^(year_number). Shows projected value at constant CAGR.

Caveats display. Add notes: 'Past performance doesn't guarantee future results.' 'Projection assumes constant growth rate.' Important for stakeholders reading the analysis.

Make it dynamic. Use OFFSET to handle variable data lengths. Use ranges named (e.g., 'data_start' and 'data_end') for reusability. Build dropdown selectors for different metrics.

Example multi-metric template. Sheet with sales, profit, customers, and units across years. Each gets its own CAGR. Compare which growing fastest. Identify diverging trends.

For investment portfolios. Calculate CAGR for each holding. Compare to benchmark CAGR (S&P 500, sector index). Identify outperformers and underperformers.

Advanced CAGR techniques. Beyond basic calculation.

Geometric mean approach. CAGR is mathematically the geometric mean of (1 + yearly growth rates) minus 1. Excel: =GEOMEAN(C2:C7)-1 where C2:C7 are (1 + each year's growth rate). Equivalent to standard CAGR formula. Useful when you have individual yearly growth rates rather than start/end values.

Weighted CAGR. When periods have unequal weights. If period 1 is 1 year and period 2 is 4 years, calculate weighted CAGR. Formula: ((end1/start1)^year1 * (end2/start2)^year2)^(1/(year1+year2))-1. Helpful when consolidating multiple investment periods.

CAGR with reinvested dividends. For stock CAGR including dividends: Total Return CAGR = ((End Price + Cumulative Dividends Reinvested) / Start Price)^(1/years) - 1. Often dramatically higher than price-only CAGR.

Risk-adjusted CAGR. CAGR doesn't account for risk. Sharpe ratio = (CAGR - risk_free_rate) / standard_deviation_of_returns. Better metric for comparing investments with different risk profiles.

Trend-adjusted CAGR. If growth is decelerating (typical for maturing businesses), basic CAGR overstates expected future growth. Adjust by fitting decay function or using recent-period CAGR rather than full-history CAGR.

Forward-looking CAGR vs trailing CAGR. Trailing CAGR uses historical data. Forward CAGR is what you expect (used for valuation). Stating which is which avoids confusion.

CAGR for IRR comparison. For more complex cash flow patterns (uneven contributions and withdrawals), use IRR function instead. IRR = the discount rate that makes NPV of cash flows = 0. For simple two-endpoint problems, CAGR and IRR give same answer; for complex flows, IRR is more accurate.

For very volatile data. Consider log-returns rather than CAGR. Log-return = LN(end/start)/years. Mathematically equivalent for small percentages, more intuitive for very high returns. Often used in finance for option pricing and risk modeling.

CAGR for personal finance. Practical applications for individuals.

Retirement projections. If your 401k grew from $50K to $180K over 15 years, CAGR = (180/50)^(1/15)-1 = 8.93%. Projecting forward 20 more years at 8.93%: $180K * 1.0893^20 ≈ $990K. Realistic baseline assumption (not guaranteed).

House price appreciation analysis. Home bought 8 years ago for $310K, now valued at $480K. CAGR = (480/310)^(1/8)-1 = 5.62%. Compare to local market CAGR — are you outperforming, average, or underperforming?

Education ROI. Student loans of $40K. Salary jumped from $40K (pre-degree) to $90K (post-degree). Over how many years did the degree pay back? If salary increase = $50K/year × 30 working years = $1.5M, that's CAGR equivalent of (1500000/40000)^(1/30)-1 = 12.92%. Very high return on education investment.

Savings rate projection. You're saving $500/month with employer match. After 10 years, you have $87K invested. CAGR = (87000/0)^(1/10)-1 = undefined (started at 0). Use total contributions ($72K) vs balance ($87K) for return analysis: CAGR = (87000/72000)^(1/10)-1 = 1.88% — that's just on the contributed amount; in reality, your total return is higher because contributions weren't all at start.

Dollar-cost averaging caveat. CAGR assumes a single starting point. For ongoing contributions, you need IRR. CAGR can mislead in DCA scenarios.

Long-term investing decision. Comparing two funds: Fund A CAGR 8.5% (10 years). Fund B CAGR 10.1% (10 years). Fund B looks better, but check expense ratios, risk profiles, and tax treatment before deciding.

Inflation-adjusted retirement. If you want $50K/year in today's dollars in 30 years, with 3% inflation, you need $50K × 1.03^30 = $121K nominal per year. Your portfolio CAGR must outpace inflation to maintain purchasing power.

CAGR in business reporting and presentations.

Quarterly earnings reports. CFOs love CAGR. It smooths volatile quarter-to-quarter results. 'Q1 2024 revenue down 5% YoY but 5-year CAGR is 12%' tells investors the long-term picture matters.

Investor pitches. Startups use CAGR to highlight growth — 'we've grown ARR at 200% CAGR' is impressive. Be careful: high CAGR over short periods (2 years) is less meaningful than over longer periods.

Strategic planning. Use historical CAGR to baseline future targets. 'If we maintain 8% revenue CAGR over the next 5 years, we'll reach $X.' Helpful for board-level discussions.

Benchmarking against competitors. Compare your CAGR to peers'. If competitors are growing at 15% CAGR and you're at 8%, that's a strategic concern.

Customer cohort analysis. Calculate CAGR by cohort (customers who joined in different years). Identify which acquisition periods retained best.

Department-level metrics. Calculate CAGR for revenue, cost, headcount, customer count by business unit. Identify which units are growing fastest, which are stagnant.

Communicating CAGR effectively. Don't just say '12% CAGR.' Say 'Revenue grew from $5M to $9M over 2020-2024 — a 12% CAGR.' Anchor the rate to actual numbers and time period. Pair with visualization showing actual vs CAGR-projected trajectory.

Common reporting mistakes. Comparing CAGRs across different time periods (a 3-year CAGR isn't comparable to a 10-year CAGR). Reporting CAGR without context (industry, growth stage, macro environment). Cherry-picking time periods to inflate CAGR.

Final thoughts. The Excel CAGR formula is one of the most useful skills for finance, business, and personal investing. With a single formula — =(End/Start)^(1/Years)-1 — you can normalize growth comparisons, project future values, and communicate trends clearly.

The key is choosing CAGR for the right situations. Use it when you have a clear start, clear end, and 3+ years between them. Use it to compare investments or business units fairly. Use it to communicate consistent multi-period growth to investors or stakeholders.

Don't use it for volatile short periods, scenarios with multiple contributions or withdrawals, or anywhere the start value is zero or negative. For those cases, raw growth rates, IRR, or alternative metrics are better.

Master the formula, understand the math behind it (geometric mean of 1+growth rates), and use it consistently. CAGR is one of the simplest yet most powerful tools in your Excel financial toolkit. Combined with proper visualization and clear reporting, it turns raw historical data into meaningful narrative — exactly what business stakeholders need to make decisions.