How to Calculate CAGR in Excel: A Simple Formula Guide
By Markets & Investing DeskUpdated Nov 10, 2026
Key takeaways
- RATE needs the beginning value entered as a negative number, or it returns a #NUM! error.
- Four year-end values span three years of growth, so the exponent is 1/3, the most common CAGR miscount.
- For returns listed as percentages, =PRODUCT(1+C3:C5)^(1/3)-1 compounds them instead of averaging.
- When deposits or withdrawals occur during the period, XIRR gives an annualized rate that accounts for timing.
The fastest way to calculate CAGR in Excel is to put the beginning value in one cell, the ending value in another and the number of years in a third, then enter =(End/Start)^(1/Years)-1 and format the result as a percentage. Excel also has a built-in function that returns the same answer: =RRI(Years, Start, End). For an investment that grew from $100 to $125 over two years, either version returns 11.80%.
The CAGR Formula Behind the Spreadsheet
Compound annual growth rate is the constant yearly rate that would carry a starting value to an ending value over a given number of years, assuming growth compounds each year:
CAGR = (Ending value / Beginning value)^(1 / n) − 1
Here n is the number of years. Dividing ending by beginning gives total growth as a multiple (1.25 means up 25% overall). Raising it to the power 1/n takes the nth root, which turns a multi-year multiple into a single annual factor. Subtracting 1 converts that factor back into a rate.
The result is a smoothed figure. A stock that rose 20% one year and 4.17% the next (from $100 to $120 to $125) has a two-year CAGR of 11.8%, even though it never grew 11.8% in any single year.
Four Excel Formulas That Return the Same CAGR
Assume the beginning value is in B2, the ending value in B3 and the number of years in B4.
| Method | Formula | Notes |
|---|---|---|
| Exponent operator | =(B3/B2)^(1/B4)-1 | Mirrors the math directly |
| POWER function | =POWER(B3/B2,1/B4)-1 | Same calculation, easier to read for some users |
| RRI function | =RRI(B4,B2,B3) | Arguments are periods, present value, future value |
| RATE function | =RATE(B4,,-B2,B3) | Start value must be negative (cash out), end value positive |
The RATE sign convention trips people up. RATE treats the beginning value as money paid out and the ending value as money received, so if both are entered as positive numbers it returns a #NUM! error. RRI has no such requirement.
Example: Calculating a 3-Year CAGR in Excel
Suppose you have year-end values for an account:
| A | B | |
|---|---|---|
| 1 | Year-end | Value |
| 2 | 2020 | 100.00 |
| 3 | 2021 | 110.00 |
| 4 | 2022 | 126.50 |
| 5 | 2023 | 121.44 |
There are four years in the table, but only three completed years of growth: end of 2020 to end of 2021, 2021 to 2022, and 2022 to 2023. The exponent is therefore 1/3, not 1/4. This miscount is the most common CAGR error.
The formula is:
=(B5/B2)^(1/3)-1
It returns 6.69%. To avoid hard-coding the period count, let Excel count the rows and subtract one:
=(B5/B2)^(1/(ROWS(B2:B5)-1))-1
That version keeps working if you add another year to the range.
When your data is percentage returns
Annual returns are often listed as percentages instead of values, for example 10% in 2021, 15% in 2022 and −4% in 2023. Each figure is a full year’s return, so three returns mean three years of growth.
You have two options:
- Helper column. Start with any base value, such as 100, and build each year from the previous one: =B2*(1+C3), copied down. That produces the value column shown above (110, 126.50, 121.44), and the standard formula gives 6.69%.
- Single formula. Multiply the growth factors directly: =PRODUCT(1+C3:C5)^(1/3)-1. In Excel 365 this calculates as an array automatically. In older versions, confirm it with Ctrl+Shift+Enter.
Do not average the three percentages. Their simple average is 7%, which overstates the compounded result.
Partial Years and Exact Dates
Annual sales figures fall neatly into whole years, but investment holdings rarely do. If you bought on one date and are measuring on another, the period includes completed years plus a partial “stub” year. Excel can calculate the fraction from the dates.
Hypothetical example: a position worth $100 on January 15, 2021 is worth $150 on June 30, 2024. With the start date in A2, end date in A3, and values in B2 and B3:
=(B3/B2)^(1/YEARFRAC(A2,A3,1))-1
YEARFRAC with basis 1 uses actual days, giving about 3.46 years, and the CAGR comes to roughly 12.4%. A simpler alternative, =(A3-A2)/365 for the year count, gives nearly the same result. Using 3 or 4 whole years instead would noticeably overstate or understate the rate.
Situations Where the Simple Formula Gives the Wrong Answer
Money added or withdrawn during the period. CAGR assumes one starting amount and one ending amount. If you contributed to a fund monthly, the ending balance reflects both your deposits and investment growth, and the basic formula credits the deposits as returns. List each cash flow with its date (contributions negative, final value positive) and use =XIRR(values, dates), which returns an annualized rate that accounts for timing.
Negative or zero starting value. A zero start causes a division error. A negative start (for example, a company moving from a loss to a profit) produces a ratio that is either meaningless or negative, and Excel returns #NUM! when a negative number is raised to a fractional power. CAGR isn’t a valid measure in these cases; report the absolute change instead.
Confusing GROWTH with CAGR. Excel’s GROWTH function fits an exponential trend line to a data series and predicts values. It does not return a compound annual growth rate, even though the name suggests it.
Nominal vs. real growth. CAGR on prices or balances is nominal. To estimate growth after inflation, use =(1+CAGR)/(1+inflation rate)-1, with an average inflation rate over the same years from a source such as U.S. inflation rate by year.
What CAGR Hides
CAGR’s smoothing conceals the path the investment took. Consider three years of returns: +25%, −40%, +54%. Chained together, 1.25 × 0.60 × 1.54 = 1.155, a CAGR of about 4.9%. The simple average of those returns, known as average annual growth rate (AAGR), is 13%, because AAGR ignores compounding. The 4.9% is correct but gives no hint that the investment lost 40% in one year, so pair CAGR with a volatility or drawdown measure such as value at risk when the path matters.
The measurement window also matters. Because the start and end dates are chosen by whoever runs the calculation, moving the window by one year can exclude a bad year or include an unusually strong one. When comparing two investments, use the same start and end dates for both.
FAQs
Does the CAGR formula work in Google Sheets?
Yes. Google Sheets accepts the same =(End/Start)^(1/Years)-1 formula, as well as RRI, RATE, POWER, YEARFRAC and XIRR with the same argument order.
What does a 10% CAGR mean?
It means the value grew at the equivalent of 10% per year, compounded, over the stated period. A 10% CAGR over five years turns $100 into about $161, even if individual years were higher or lower than 10%.
Is a 30% CAGR good?
It depends on what is measured and the period. For a broad stock portfolio held over many years, 30% a year would be exceptional compared with an index like the S&P 500. For a young company's revenue, investors may expect more. Compare any CAGR against a relevant benchmark over the same dates.
The Bottom Line
Anyone measuring an investment or business series in Excel can get CAGR from one short formula or the RRI function. The result holds only when the period count is right and there is a single starting and ending amount. Use YEARFRAC for exact dates and XIRR when cash moved in or out. Pair CAGR with a volatility or drawdown measure, since its smoothing can hide a steep loss.
