Compound Annual Growth Rate (CAGR) is the constant annual growth rate that, applied year-over-year, would take a starting value to an ending value over a specific number of years. It's one of the most commonly used metrics in finance, business analysis, and investment reporting because it smooths out volatile year-by-year growth into a single comparable rate. CAGR is the answer to the question: "if this had grown at a steady rate every year, what would that rate have been?"
This guide walks through how to calculate CAGR in Excel, the three main methods (the manual formula, the RRI function, and the POWER function approach), how CAGR differs from average annual growth, when CAGR is the right metric vs. when it misleads, the common pitfalls (negative starting values, inflation adjustment issues, comparing different time periods), and worked examples for both revenue growth scenarios and investment return scenarios. Understanding CAGR well is foundational for anyone working with financial data in Excel.
The CAGR formula is straightforward: CAGR = (Ending Value / Starting Value) ^ (1 / Number of Years) - 1. The result is a decimal that you typically format as a percentage. For example, if revenue grew from $100,000 to $200,000 over 5 years, CAGR = (200,000/100,000)^(1/5) - 1 = 2^0.2 - 1 = 1.1487 - 1 = 0.1487 or about 14.87% per year. The result tells you that compound annual growth at 14.87% applied steadily for 5 years would take $100,000 to $200,000.
The most important insight about CAGR is that it's a smoothed average that hides year-by-year volatility. A company that grew from $100K to $200K over 5 years might have done it with five years of consistent ~15% growth, or with 200% in one year and flat in others, or with 50% growth in years 1-2 and decline in years 3-5. The CAGR is the same in all three scenarios. This smoothing is useful for comparing investment returns or business growth across different time periods, but it's misleading when the underlying volatility matters for the decision being made.
For Excel users, CAGR calculations are quick once you know the right formula or function. The manual formula gives you full control. The RRI function (Rate of Return on Investment) is purpose-built for this calculation and produces the same result with cleaner syntax. The POWER function approach uses Excel's general exponentiation function rather than the caret operator. All three methods produce identical numerical results; the choice between them is mostly about formula readability and personal preference rather than calculation differences.
CAGR formula: CAGR = (Ending Value / Starting Value) ^ (1 / Number of Years) - 1. Excel manual formula: =(B/A)^(1/n)-1 where A is start, B is end, n is years. RRI function: =RRI(n, A, B) — Excel's purpose-built CAGR function. POWER function: =POWER(B/A, 1/n)-1. Format result: as percentage with appropriate decimal places. Common uses: revenue growth, investment returns, customer growth, any value-over-time scenario where steady growth comparison is useful.
The mathematical reasoning behind CAGR comes from compound growth. If a value compounds at rate r per year for n years, the ending value is starting × (1+r)^n. To find r given the starting and ending values, rearrange algebraically: r = (Ending/Starting)^(1/n) - 1. The 1/n exponent (or nth root) takes the total growth ratio and converts it back to the per-year rate that, when compounded over n years, produces that total ratio. The math is straightforward once you've seen it; the intuition takes a few examples to develop fully.
One key conceptual point: CAGR uses the number of years between the start and end values, not the number of data points. If you have annual values for years 2020, 2021, 2022, 2023, 2024, that's 5 data points but only 4 years between the first and last. CAGR = (2024 value / 2020 value)^(1/4) - 1, not (2024 value / 2020 value)^(1/5) - 1. The off-by-one error is one of the most common CAGR calculation mistakes, and it produces a meaningfully different rate than the correct formula.
Another key conceptual point: CAGR isn't simple average growth. If revenue grew 50% one year and -10% the next, the simple average growth is 20% per year, but the CAGR over those two years is sqrt(1.50 × 0.90) - 1 = sqrt(1.35) - 1 ≈ 16.19%. The simple average overstates the actual compound growth because it doesn't account for the fact that the second year's percentage applies to a different base than the first year's. CAGR correctly handles this compounding effect through the geometric calculation.
The CAGR result is always a single number representing what the per-year rate would have been if growth had been steady. The actual year-over-year growth rates may vary substantially. Looking at CAGR alone tells you the smoothed picture; looking at year-over-year rates alongside CAGR tells you both the average performance and the volatility. For most financial reporting, both are useful — CAGR for the headline comparison and year-over-year details for understanding the path that produced the smoothed result.
The most explicit method. Formula: =(End/Start)^(1/n)-1. Example: =(B2/A2)^(1/5)-1 calculates CAGR where B2 is ending value, A2 is starting value, and the period is 5 years. The caret (^) is Excel's exponentiation operator. The formula directly implements the CAGR mathematical definition. Most users prefer this method when they want the math visible in the formula bar for verification or teaching purposes.
Excel's purpose-built CAGR function. Syntax: =RRI(nper, pv, fv) where nper is the number of periods (years), pv is the present value (starting), and fv is the future value (ending). Example: =RRI(5, 100000, 200000) returns the same CAGR as the manual formula. The cleaner syntax makes RRI preferred when the formula is part of a larger model where readability matters across many users reviewing the spreadsheet.
Uses Excel's POWER function instead of the caret operator. Formula: =POWER(End/Start, 1/n)-1. Example: =POWER(B2/A2, 1/5)-1. Functionally identical to the manual formula but some users find POWER more readable than caret notation. Particularly useful in formulas that already use POWER for other calculations and where syntactic consistency matters more than the slight extra typing the function call requires compared to the caret operator approach.
If your data has the starting value in A1, ending value in A6, and the years are 2019-2024 (5 years between), formula could be =(A6/A1)^(1/5)-1. The hardcoded 5 is acceptable for simple cases but breaks if you change the range. For dynamic flexibility, use COUNT or other formulas to count periods automatically: =(A6/A1)^(1/(COUNT(A1:A6)-1))-1, recognizing that 6 data points span 5 years between first and last.
When values are at irregular time intervals (not exactly annual), the standard CAGR formula doesn't apply cleanly. Excel's XIRR function calculates the internal rate of return for cash flows at specified dates, which produces an annualized rate equivalent to CAGR for non-annual data. Different function but related concept. Useful for investment analysis where transactions happen at irregular intervals rather than at exact yearly anniversaries.
An alternative formulation: =LN(End/Start)/n. The natural log version produces a slightly different number that some financial analysts prefer for certain comparisons because of additivity properties. Most general business reporting uses the standard CAGR formula above; the log version is mostly used in academic finance and certain quantitative analyses. The two metrics tell similar stories at typical growth rates but diverge at extreme rates beyond 30-40 percent annual growth.
The most explicit way to calculate CAGR in Excel is to write the formula directly. Suppose you have a starting value of $100,000 in cell A2, an ending value of $200,000 in cell B2, and you want CAGR over 5 years. The formula is =(B2/A2)^(1/5)-1. Format the result cell as a percentage with appropriate decimal places (typically 2-4 decimals for CAGR display). The formula returns 0.14869835... which displays as 14.87% with 2 decimal places.
For a more flexible version that doesn't hardcode the year count, store the number of years in a separate cell and reference it. If C2 contains 5, the formula becomes =(B2/A2)^(1/C2)-1. Now you can change the year count by editing C2 without rewriting the formula. This is the right pattern for any formula you'll reuse across multiple scenarios because it makes the model more transparent and easier to audit when reviewers want to verify the inputs producing a given output.
For data structured as a series of annual values rather than just start and end points, you can use a formula that automatically picks the correct values. If years 2020 through 2024 are in row 1 (B1:F1) and corresponding values are in row 2 (B2:F2), the CAGR formula becomes =(F2/B2)^(1/(F1-B1))-1. The year columns provide the period count automatically. This pattern works well for dashboards where users add new annual data over time and the CAGR formula adjusts without manual updates to its references.
One subtlety: ensure the period count reflects years between the first and last data points, not the count of data points themselves. Five values for years 2020, 2021, 2022, 2023, 2024 represent 4 years between first and last (2024 - 2020 = 4). If you incorrectly use 5 in the denominator, the resulting CAGR will be slightly lower than the correct rate. The off-by-one error is a common source of CAGR calculation issues in spreadsheets that aren't carefully designed and reviewed during construction.
Company X had revenue of $5 million in 2019 and $12 million in 2024. CAGR over the 5-year period: =(12/5)^(1/5)-1 = 1.1936 - 1 = 0.1936 or 19.36% per year. Interpretation: revenue grew at the equivalent of 19.36% per year compounded over 5 years. Note: actual year-over-year growth may have been more volatile (perhaps 30% in some years, 5% in others); CAGR represents the smoothed equivalent that produced the same total growth.
You invested $10,000 in 2014 and the value grew to $25,000 by 2024 (10 years). CAGR: =(25000/10000)^(1/10)-1 = 1.0959 - 1 = 0.0959 or 9.59% per year. Interpretation: the investment compounded at 9.59% per year on average. This is comparable to other investment returns expressed as CAGR (S&P 500 historical CAGR is roughly 10% annualized; bond returns historically lower; specific funds vary widely).
Your SaaS company had 1,000 customers at the end of 2020 and 8,000 customers at the end of 2024. CAGR over 4 years: =(8000/1000)^(1/4)-1 = 1.6818 - 1 = 0.6818 or 68.18% per year. Interpretation: customer base grew at 68% compounded annually. High-growth SaaS businesses commonly report customer or ARR growth using CAGR, with public companies citing 3-5 year CAGR as a key narrative metric in earnings reports and investor presentations.
Your website had 50,000 monthly visitors in January 2022 and 150,000 monthly visitors in January 2025. CAGR over 3 years: =(150000/50000)^(1/3)-1 = 1.4422 - 1 = 0.4422 or 44.22% per year. CAGR works for any metric where compound growth is meaningful — population, employees, products sold, page views, etc. The metric must have been measured at consistent points in time across the period for the calculation to be meaningful and comparable.
Sales of a declining product fell from $5 million in 2020 to $3 million in 2024. CAGR over 4 years: =(3/5)^(1/4)-1 = 0.8801 - 1 = -0.1199 or -11.99% per year. Negative CAGR means the value declined at that compounded rate. Useful for tracking shrinking categories or businesses. Different from positive CAGR only in interpretation; the same formula works regardless of whether the value grew or declined over the measurement period.
Excel includes a purpose-built function for CAGR called RRI (Rate of Return on Investment). Syntax: =RRI(nper, pv, fv) where nper is the number of periods (years for annual CAGR), pv is the present value (starting), and fv is the future value (ending). For the same example as above (start $100,000, end $200,000, 5 years): =RRI(5, 100000, 200000) returns 0.14869835... — the same CAGR as the manual formula calculation.
RRI is functionally identical to the manual (end/start)^(1/n)-1 formula. The difference is purely syntactic — RRI's named arguments make the formula more self-documenting than caret-based exponentiation. For workbooks that will be reviewed by other users, RRI's clarity is a meaningful benefit. For quick personal calculations, the manual formula is fine because the math is intuitive once you've used it a few times.
RRI was added to Excel relatively recently (Excel 2013 onward). For workbooks that will be opened in older Excel versions, the manual formula or POWER function is more compatible. RRI works in Microsoft 365, Excel 2021, Excel 2019, Excel 2016, and Excel 2013 — covering essentially every Excel version still in active use today. The compatibility concern only matters for very old Excel installations that almost no one uses anymore.
One useful pattern with RRI is calculating CAGR for cells that reference other parts of a workbook. =RRI(YEARS_CELL, START_CELL, END_CELL) with named ranges or named references makes the CAGR calculation read like the conceptual definition of CAGR rather than as raw cell math. For financial models that will be presented to executives or external readers, the readability is worth the extra setup time of creating the named references that make the formula self-documenting in the displayed sheet.
A common confusion is the difference between CAGR and simple average annual growth rate. Simple average annual growth takes the year-over-year growth rates and averages them arithmetically. CAGR uses the geometric mean of the growth ratios, accounting for compounding. For volatile growth, the two metrics produce different numbers, and CAGR is generally the right choice because compound growth is how money, populations, and businesses actually grow over time.
Worked example: a company's revenue grew 50% in year 1 and -10% in year 2. Simple average growth: (50% + -10%) / 2 = 20% per year. CAGR: starting $100, after year 1 = $150, after year 2 = $135. CAGR = (135/100)^(1/2) - 1 = 1.1619 - 1 = 16.19%. The CAGR (16.19%) is lower than the simple average (20%) because the volatility produces less actual ending value than steady 20% growth would produce.
The general mathematical principle: geometric mean ≤ arithmetic mean, with equality only when all the values being averaged are identical. The more variable the year-by-year growth, the larger the gap between simple average and CAGR. For investment returns specifically, the gap matters meaningfully because volatile investments have lower CAGR than their simple-average suggests, which affects retirement planning and other long-horizon financial decisions.
For most reporting contexts, CAGR is the right metric. Simple average growth misleads when growth is volatile, which is most real-world growth patterns. CAGR is the metric that tells you what steady rate would produce the actual ending value from the actual starting value. Use simple average only when the question genuinely is "what was the average year-over-year change?" rather than "what compound rate is equivalent to the actual growth?" The distinction matters for financial accuracy and is worth understanding clearly when reporting growth metrics to others.
For financial analysts presenting CAGR results, always include the time period (start year to end year) and ideally the starting and ending values alongside the rate. "Revenue grew at 14% CAGR" is much less informative than "Revenue grew from $5M in 2020 to $12M in 2024, a 19.4% CAGR over 4 years." The complete framing prevents the CAGR from being interpreted with wrong assumptions about what time period or starting point produced the rate. Transparent presentation also lets readers verify the calculation themselves if they want to.
CAGR works well for steady value-over-time growth, but it has clear limits. Negative starting values break the math — CAGR can't be meaningfully calculated when starting from a negative number because the compound-growth concept doesn't apply. For situations like a company moving from loss to profit, use absolute changes or alternate metrics like profit margin trajectories instead of trying to force CAGR onto data that doesn't fit its assumptions.
Very short time periods produce volatile CAGR that doesn't represent meaningful trend. CAGR over 1-2 years is essentially just year-over-year growth and doesn't add information beyond simpler metrics. CAGR is most useful over 3-10 year periods where the smoothing effect captures meaningful trend signals while the period is short enough to be relevant. Decade-plus CAGR can mask important transitions within the period that affect interpretation, so very long-period CAGR sometimes deserves segmentation into shorter sub-periods for fuller context.
Cyclical data can produce misleading CAGR when start and end years happen to fall at different points in the cycle. A retail company measured at peak holiday-season revenue vs. trough mid-summer revenue produces noise rather than signal. Use rolling or annualized data, or measure between matching points in cycles, to avoid cyclical artifacts contaminating the CAGR calculation. Always sanity-check whether the start and end measurement points are comparable beyond just being separated by the appropriate number of years.
Inflation-adjusted vs. nominal CAGR can tell different stories. A 5% nominal CAGR during a period of 4% inflation produces only 1% real CAGR after adjusting for purchasing power changes. For long-period comparisons or international comparisons across countries with different inflation environments, real (inflation-adjusted) CAGR is often more meaningful than nominal CAGR. Specify which version you're calculating and which version your audience needs for the decision the analysis supports during financial reporting.
When you want to compare growth across different time periods, businesses, or investments using a single comparable rate. Standard in financial reporting, investment analysis, and business growth communication. The smoothing effect makes CAGR comparable across periods with different volatility profiles, even when actual year-over-year growth varied substantially within each period being compared.
When the question is specifically about the most recent year's performance compared to the prior year. Year-over-year growth (YoY) is more responsive to recent trends than CAGR, which smooths across multiple years. Quarterly earnings reports typically lead with YoY comparisons; CAGR appears for longer-term context. Both metrics together provide fuller picture than either alone for most reporting purposes.
When starting from negative values, when measuring categorical changes, or when the audience needs dollar-amount answers rather than percentage rates. "Revenue increased by $50 million over 5 years" is more concrete than "19% CAGR" for some audiences. Absolute changes pair well with CAGR — show both the dollar amount and the rate for comprehensive reporting that addresses different reader information needs.
When measuring returns on investments with multiple cash flows in and out across irregular dates, internal rate of return (IRR for periodic cash flows; XIRR for irregular dates) is the right metric rather than simple CAGR. CAGR works for single-investment-to-end-value scenarios; IRR handles the more complex case of varying contributions and withdrawals over time. The choice depends on whether the underlying scenario is a simple two-point comparison or a complex cash-flow stream.
One of CAGR's main values is enabling comparisons. A business with 15% revenue CAGR over 5 years can be directly compared to a peer with 12% CAGR over the same period, with the higher number indicating faster compound growth. Investment returns expressed as CAGR can be compared across different asset classes (stocks, bonds, real estate) and time periods. The single comparable rate format makes CAGR useful for benchmarking and competitive analysis where readers need to evaluate multiple options side by side.
For valid comparisons, the periods being compared need to be the same length and ideally cover the same calendar years. Comparing a company's 5-year CAGR (2020-2024) to a peer's 5-year CAGR (2018-2022) introduces noise because the underlying economic conditions differed between the two periods. Using identical periods for all entities being compared eliminates this concern and produces apples-to-apples comparisons that readers can trust for their decision-making purposes.
For comparisons across very different time periods, decompose into shorter sub-periods. "Company A had 25% CAGR 2014-2019 then 8% CAGR 2019-2024" is more informative than a blended single CAGR for the entire decade. The sub-period decomposition surfaces important transitions that the smoothed metric would hide, which often matters for interpretation. Use both approaches together — overall CAGR for the headline summary and sub-period CAGRs for understanding the path that produced the headline result.
For investment portfolio reporting, CAGR is typically computed annually as part of standard performance measurement. Public mutual funds, ETFs, and investment platforms report 1-year, 3-year, 5-year, and 10-year CAGRs (often labeled "average annual return" though it's the geometric CAGR rather than arithmetic average). The standardization makes cross-fund comparison easy. Personal portfolio tools like Personal Capital, Empower, and various brokerage platforms calculate CAGR automatically for users tracking their long-term investment performance over time.