# Compound Growth Rate Formula in Excel: 3 Methods (CAGR Guide) Published: 2026-03-15 Author: Warren Team URL: https://www.heywarren.com/blog/compound-growth-rate-formula-excel --- You opened a spreadsheet, dropped in a starting value and an ending value, and reached for the `CAGR()` function — only to discover Excel does not have one. That gap trips up analysts, founders, and personal investors every day, leading to wrong growth numbers in board decks, investment memos, and retirement projections. The [compound growth rate formula in Excel](/blog/cagr-formula) is not built in, but it is easy once you know which combination of functions to use. This guide walks through three reliable approaches (RRI, POWER, and the exponent operator), shows how to handle irregular periods with XIRR, demonstrates the Google Sheets equivalent, and flags the mistakes that most often produce wrong answers. ## What the Compound Growth Rate Formula in Excel Actually Computes The compound annual growth rate (CAGR) is the constant rate at which a value would have to grow each year, compounding annually, to move from a beginning value to an ending value over a given number of years. It smooths out the noisy year-over-year growth you see in real data and produces a single annualized growth figure that is easy to compare across investments, business lines, or time horizons. Mathematically, the CAGR formula is: `CAGR = (Ending Value / Beginning Value)^(1 / n) − 1` Where `n` is the number of compounding periods (usually years). The expression is the geometric mean of the per-period growth factors, minus one. That distinction matters: a simple arithmetic average of annual returns will overstate growth whenever returns are volatile, because losses compound asymmetrically against gains. CAGR fixes this by using the geometric mean of the growth factor. A quick intuition pump: imagine a portfolio that returns +50% in year one and −50% in year two. The [arithmetic mean](/blog/arithmetic-mean-versus-geometric-mean) is 0% — it sounds like you broke even. But $100 grows to $150 and then falls to $75, a real annualized loss of about −13.4%. CAGR, computed via the geometric mean, captures that reality where simple averages cannot. Anytime you see a multi-year growth claim that does not specify whether it is arithmetic or geometric, assume the higher number is being marketed at you. Excel does not ship with a function literally called `CAGR()`, which is why the compound growth rate formula in Excel must be assembled from primitives. Fortunately, you have three clean options, each with trade-offs in readability and flexibility, plus a fourth function (`XIRR`) for the irregular-period case that the simple CAGR formula cannot handle. ## Method 1: The RRI Function (Excel's Built-In Shortcut) If you are on Excel 2013 or later (including Microsoft 365), the cleanest way to compute CAGR is the `RRI` function. RRI stands for "[rate of return](/blog/calculating-rates-of-return) on investment" and it is purpose-built for exactly this calculation. ![Three equivalent approaches to calculating CAGR in Excel, from most readable to most compact.](data:image/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20660%20125%22%20width%3D%22660%22%20height%3D%22125%22%20role%3D%22img%22%3E%3Ctitle%3EFlow%20diagram%3C%2Ftitle%3E%3Crect%20width%3D%22100%25%22%20height%3D%22100%25%22%20fill%3D%22%23f8fafc%22%2F%3E%3Crect%20x%3D%2230%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22115%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3ERRI%20Function%3C%2Ftext%3E%3Ctext%20x%3D%22115%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3E%3DRRI%28nper%2C%20pv%2C%20fv%29%3C%2Ftext%3E%3Cline%20x1%3D%22205%22%20y1%3D%2262.5%22%20x2%3D%22237%22%20y2%3D%2262.5%22%20stroke%3D%22%2364748b%22%20stroke-width%3D%222%22%2F%3E%3Cpolygon%20points%3D%22244%2C62.5%20235%2C57.5%20235%2C67.5%22%20fill%3D%22%2364748b%22%2F%3E%3Crect%20x%3D%22245%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22330%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EPOWER%20Function%3C%2Ftext%3E%3Ctext%20x%3D%22330%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3E%3DPOWER%28fv%2Fpv%2C%201%2Fn%29%E2%88%921%3C%2Ftext%3E%3Cline%20x1%3D%22420%22%20y1%3D%2262.5%22%20x2%3D%22452%22%20y2%3D%2262.5%22%20stroke%3D%22%2364748b%22%20stroke-width%3D%222%22%2F%3E%3Cpolygon%20points%3D%22459%2C62.5%20450%2C57.5%20450%2C67.5%22%20fill%3D%22%2364748b%22%2F%3E%3Crect%20x%3D%22460%22%20y%3D%2225%22%20width%3D%22170%22%20height%3D%2275%22%20rx%3D%2210%22%20fill%3D%22white%22%20stroke%3D%22%232563eb%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22545%22%20y%3D%2258.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3ECaret%20Operator%3C%2Ftext%3E%3Ctext%20x%3D%22545%22%20y%3D%2278.5%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2211%22%20fill%3D%22%2364748b%22%3E%3D%28fv%2Fpv%29%5E%281%2Fn%29%E2%88%921%3C%2Ftext%3E%3C%2Fsvg%3E) *Three equivalent approaches to calculating CAGR in Excel, from most readable to most compact.* ### Syntax `=RRI(nper, pv, fv)` - `nper` — number of periods (years for annual CAGR) - `pv` — present value (the beginning value) - `fv` — future value (the ending value) ### Worked Example Suppose revenue grew from $1,000,000 in 2020 to $1,610,510 in 2025. That is five years of growth (2020 to 2025). Lay it out: | Cell | Value | |------|-------| | A1 | 1000000 (beginning value) | | A2 | 1610510 (ending value) | | A3 | 5 (number of years) | Then in cell A4 enter: `=RRI(A3, A1, A2)` Format the cell as a percentage and you will get `10.00%`. That is your CAGR. Done. ### Why RRI Is the Right Default `RRI` reads like English to anyone reviewing the workbook, which matters when a CFO or auditor needs to retrace your math. It also sidesteps a common error: typing the exponent in the wrong place when you write the formula by hand. If your file might be opened by colleagues on Excel 2010 or earlier, fall back to one of the next two methods. One more advantage worth flagging — `RRI` returns a clean decimal that you can chain into other formulas without worrying about operator precedence. If you want to project a future value from a CAGR, `=B1 * (1 + RRI(B3, B1, B2))^B6` is unambiguous, where `B6` is the number of forward years. With raw `^` math you can get into hairy nested parenthesis problems quickly. ## Method 2: The POWER Function `POWER` raises a number to a power. It maps directly to the textbook CAGR formula and works in every version of Excel ever released. ### Syntax `=POWER(fv / pv, 1 / nper) − 1` ### Worked Example Using the same revenue numbers, in cell A4 enter: `=POWER(A2/A1, 1/A3) - 1` Format as percentage and you again get `10.00%`. Identical answer, different tool. ### When to Reach for POWER `POWER` is helpful when your formula is part of a larger expression and you want the math to be visually explicit — analysts in IB, equity research, and FP&A often prefer this style because it mirrors the formula they would write on a whiteboard. It is also easier to extend: if you want to compute compound growth rate in Excel for a non-annual period (monthly, quarterly), `POWER` makes the period denominator easier to spot and audit. ## Method 3: The Caret Operator (^) The caret `^` is Excel's exponent operator and gives you the most compact compound growth rate formula in Excel. It is functionally identical to `POWER` but takes fewer keystrokes. ### Syntax `=(fv / pv)^(1 / nper) − 1` ### Worked Example In cell A4: `=(A2/A1)^(1/A3) - 1` Same `10.00%` result. The parentheses around `(1/A3)` are not optional — without them, Excel will divide first, exponentiate by 1, and then divide by `A3`, which is not what you want. This is the single most common mistake people make when typing the formula by hand. ## Step-by-Step: Building a CAGR Worksheet From Scratch Here is a clean recipe you can replicate on any laptop in under two minutes. 1. Open a new workbook. In `A1` type `Beginning Value`, in `A2` type `Ending Value`, in `A3` type `Years`, in `A4` type `CAGR`. 2. In `B1` enter your starting figure (for example, the share price five years ago). 3. In `B2` enter your ending figure (today's share price). 4. In `B3` enter the number of years between the two observations. Be careful: if you are looking at year-end 2020 and year-end 2025, that is **five** periods, not six. The number of periods equals the difference in years, not the count of years displayed. 5. In `B4` enter your preferred formula: `=RRI(B3, B1, B2)`, `=POWER(B2/B1, 1/B3) - 1`, or `=(B2/B1)^(1/B3) - 1`. 6. Right-click `B4`, choose Format Cells, and apply Percentage with two decimals. You now have a reusable CAGR calculator. To audit it, enter $100 in `B1`, $200 in `B2`, and `1` in `B3`. The answer should be exactly `100.00%`. If it is not, you have a parenthesis or reference error. ## Handling Irregular Periods With XIRR CAGR assumes equal time intervals. The moment your data points are spaced unevenly — quarterly revenue with a missing quarter, an investment with sporadic deposits and withdrawals, a fund with unevenly dated cash flows — `RRI`, `POWER`, and the caret operator will all give you the wrong annualized growth. ![XIRR is required whenever cash flows are unevenly spaced; CAGR suffices for simple two-point calculations.](data:image/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20600%20211%22%20width%3D%22600%22%20height%3D%22211%22%20role%3D%22img%22%3E%3Ctitle%3EHierarchy%3C%2Ftitle%3E%3Crect%20width%3D%22100%25%22%20height%3D%22100%25%22%20fill%3D%22%23f8fafc%22%2F%3E%3Crect%20x%3D%22220%22%20y%3D%2220%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22%232563eb%22%2F%3E%3Ctext%20x%3D%22300%22%20y%3D%2254%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22700%22%20fill%3D%22white%22%3EAnnualized%20Growth%3C%2Ftext%3E%3Cpath%20d%3D%22M%20300%2078%20L%20300%20105.5%20L%20210%20105.5%20L%20210%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%22130%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22210%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EUse%20RRI%2FCAGR%3C%2Ftext%3E%3Ctext%20x%3D%22210%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3EEqual%20periods%2C%20single%20sta%E2%80%A6%3C%2Ftext%3E%3Cpath%20d%3D%22M%20300%2078%20L%20300%20105.5%20L%20390%20105.5%20L%20390%20133%22%20stroke%3D%22%23cbd5e1%22%20stroke-width%3D%222%22%20fill%3D%22none%22%2F%3E%3Crect%20x%3D%22310%22%20y%3D%22133%22%20width%3D%22160%22%20height%3D%2258%22%20rx%3D%228%22%20fill%3D%22white%22%20stroke%3D%22%230891b2%22%20stroke-width%3D%222%22%2F%3E%3Ctext%20x%3D%22390%22%20y%3D%22158%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2213%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EUse%20XIRR%3C%2Ftext%3E%3Ctext%20x%3D%22390%22%20y%3D%22176%22%20text-anchor%3D%22middle%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2210%22%20fill%3D%22%2364748b%22%3EUnequal%20gaps%20or%20multiple%20%E2%80%A6%3C%2Ftext%3E%3C%2Fsvg%3E) *XIRR is required whenever cash flows are unevenly spaced; CAGR suffices for simple two-point calculations.* The fix is `XIRR`, which computes the [internal rate of return](/blog/how-is-irr-calculated) for a series of cash flows that occur on specific dates. ### XIRR Syntax `=XIRR(values, dates, [guess])` - `values` — a range of cash flows (negative for outflows, positive for inflows) - `dates` — the corresponding date for each cash flow - `guess` — optional starting estimate (Excel defaults to 10%) ### Example: An Account With Irregular Deposits | Date | Cash Flow | |------|-----------| | 2021-01-15 | −10,000 | | 2022-06-30 | −5,000 | | 2023-11-01 | −2,500 | | 2026-04-19 | 22,800 (current value) | In cell `C5` enter: `=XIRR(B1:B4, A1:A4)` Format as percentage. The result is the annualized growth rate that correctly accounts for the date of each contribution. This is what brokerage statements call your "money-weighted return" and it is the only honest way to annualize growth when your cash flows are not evenly spaced. ### When XIRR Beats CAGR Use `XIRR` whenever you have: - Unequal time gaps between observations - Multiple deposits or withdrawals within the period - A need to weight returns by how long each dollar was invested For a single beginning value and a single ending value spaced exactly N years apart, `RRI` and `XIRR` will agree. For anything more complex, `XIRR` is the only correct answer. ## Compound Growth Rate in Google Sheets Google Sheets implements all the same functions, so the compound growth rate in Excel translates directly. `RRI`, `POWER`, the caret operator, and `XIRR` all behave identically. You can copy a workbook containing CAGR formulas from Excel to Sheets and it will compute the same values. One small bonus: Google Sheets has supported `RRI` since 2017, and the function autocompletes the same way. If you want to compute Google Sheets CAGR, use any of the three methods above without modification. The one place to be careful is the `XIRR` function — Sheets requires that your dates column be formatted as actual dates, not text. If `XIRR` returns a `#NUM!` error, the most common cause is that a date got pasted in as a string. Select the date column and apply Format → Number → Date. ## Nominal vs. Real CAGR A growth rate computed from raw dollar values is a nominal CAGR — it includes the effect of inflation. To convert to a real (inflation-adjusted) CAGR, use the Fisher equation: ![A 7% nominal CAGR with 3% inflation yields only a 3.88% real (inflation-adjusted) return.](data:image/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20800%20210%22%20width%3D%22800%22%20height%3D%22210%22%20role%3D%22img%22%3E%3Ctitle%3EComparison%3C%2Ftitle%3E%3Crect%20width%3D%22100%25%22%20height%3D%22100%25%22%20fill%3D%22%23f8fafc%22%2F%3E%3Ctext%20x%3D%22230%22%20y%3D%2257.5%22%20text-anchor%3D%22end%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3ENominal%20CAGR%3C%2Ftext%3E%3Crect%20x%3D%22240%22%20y%3D%2225%22%20width%3D%22450%22%20height%3D%2255%22%20rx%3D%226%22%20fill%3D%22%232563eb%22%2F%3E%3Ctext%20x%3D%22702%22%20y%3D%2257.5%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22700%22%20fill%3D%22%232563eb%22%3E%257%3C%2Ftext%3E%3Ctext%20x%3D%22230%22%20y%3D%22152.5%22%20text-anchor%3D%22end%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22600%22%20fill%3D%22%230f172a%22%3EReal%20CAGR%3C%2Ftext%3E%3Crect%20x%3D%22240%22%20y%3D%22120%22%20width%3D%22249.42857142857142%22%20height%3D%2255%22%20rx%3D%226%22%20fill%3D%22%237c3aed%22%2F%3E%3Ctext%20x%3D%22501.42857142857144%22%20y%3D%22152.5%22%20font-family%3D%22system-ui%2C-apple-system%2Csans-serif%22%20font-size%3D%2214%22%20font-weight%3D%22700%22%20fill%3D%22%237c3aed%22%3E%253.88%3C%2Ftext%3E%3C%2Fsvg%3E) *A 7% nominal CAGR with 3% inflation yields only a 3.88% real (inflation-adjusted) return.* `Real CAGR = ((1 + Nominal CAGR) / (1 + Inflation Rate)) − 1` In Excel, with nominal CAGR in `B4` and the average annual inflation rate in `B5`: `=(1 + B4) / (1 + B5) - 1` For investments held over five or more years, the gap between nominal and real CAGR is often the difference between feeling rich and actually being rich. A 7% nominal return during a period of 3% inflation is only a 3.88% real return. Always know which one you are quoting. A common shortcut you will see in older textbooks is `Real CAGR ≈ Nominal CAGR − Inflation Rate`. That approximation is acceptable when both numbers are small (under about 5%), but it diverges noticeably at higher rates. For anything you are publishing, signing your name to, or making decisions on, use the full Fisher equation. ## Common Mistakes That Produce the Wrong CAGR Even with the right formula, four pitfalls show up again and again. Watch for them. ### 1. Off-by-One on the Period Count If you have data for 2020, 2021, 2022, 2023, 2024, and 2025, that is **five** years of growth (six observations, five intervals). Counting six is the most common error in CAGR worksheets and it understates growth by roughly 16% in relative terms. ### 2. Mixing Percentages With Raw Values If your "ending value" cell contains `1.61` (a growth multiple) instead of `1,610,510` (a dollar value), you will get a nonsense answer. CAGR formulas expect raw values, not pre-computed ratios. If you already have the ratio, skip the division: use `=POWER(B2, 1/B3) - 1` where `B2` already holds the multiple. ### 3. Forgetting Parentheses Around the Exponent `=(B2/B1)^1/B3 - 1` is **not** the same as `=(B2/B1)^(1/B3) - 1`. Without the inner parentheses, Excel raises the ratio to the power of 1, then divides by `B3`. The answer will look plausible but be completely wrong. This is why `RRI` is often safer for hand-typed formulas. ### 4. Using a Mid-Period Value as the Endpoint CAGR is defined on snapshots taken at the start and end of full periods. Using a value from June against a value from December skews the annualization. If you must work with mid-year data, switch to `XIRR` or interpolate to year-end first. ### 5. Annualizing Sub-Annual Periods Incorrectly If you are computing growth from January to June (six months) and want an annualized figure, your `n` is `0.5`, not `1`. In `RRI` you cannot pass a fractional period directly in older versions, so use `POWER` or the caret operator: `=(B2/B1)^(1/0.5) - 1`. This squares the half-year growth to annualize it. ## Alternative Approaches Worth Knowing For completeness, three other functions occasionally come up in CAGR conversations. - **`GEOMEAN`** — computes the geometric mean of a range of growth factors. If you have a column of annual growth factors (e.g., `1.08, 0.95, 1.12, 1.04`), `=GEOMEAN(range) - 1` returns the CAGR. Useful when you have annual returns rather than start/end values. - **`RATE`** — solves for the periodic interest rate of an annuity. You can coerce it into a CAGR with `=RATE(nper, 0, -pv, fv)`, but `RRI` is cleaner for the no-payment case. - **`LOGEST`** — fits an exponential trendline to a range and returns the growth multiplier. For a long time series this can be more representative than a two-point CAGR, since it uses every data point rather than just the endpoints. For most workflows, stick with `RRI` for clean two-point CAGR and `XIRR` for irregular cash flows. The other functions are situational. ## Authoritative Sources For deeper background and primary-source data on this topic, the following authoritative sources are useful starting points: - [IRS](https://www.irs.gov/) - [SEC](https://www.sec.gov/) - [Federal Reserve](https://www.federalreserve.gov/) ## Conclusion Three clean takeaways for using the compound growth rate formula in Excel: 1. Use `=RRI(nper, pv, fv)` as your default — it is built-in, readable, and hard to mistype. 2. Fall back to `=POWER(fv/pv, 1/nper) - 1` or `=(fv/pv)^(1/nper) - 1` when you need universal compatibility or want the math visible. 3. Switch to `=XIRR(values, dates)` the moment your cash flows are spaced unevenly — `RRI` will give you the wrong number. 4. Count your periods, not your data points: five year-ends span four intervals, six year-ends span five. 5. Convert nominal to real CAGR with the Fisher equation when comparing returns across inflationary regimes. Ready to put this knowledge to work? Try Warren, your AI financial advisor — get personalized, conflict-free guidance at heywarren.com --- ## Related Reading **More from Warren**: - [CAGR Formula: How to Calculate Compound Annual Growth Rate](/blog/cagr-formula) - [EPS Formula: How to Calculate Earnings Per Share and Why It Matters](/blog/eps-formula) - [Fixed Rate Swap: How Interest Rate Swaps Work and Who Uses Them](/blog/fixed-rate-swap) **Authoritative sources**: - [SEC EDGAR — Company Filings](https://www.sec.gov/edgar/searchedgar/companysearch) - [Federal Reserve Economic Data (FRED)](https://fred.stlouisfed.org/) - [Bureau of Labor Statistics — Data Tools](https://www.bls.gov/data/)