CAGR — compound annual growth rate — answers a simple question: if an investment, a revenue figure, or any value grew at one steady rate every year, what rate would take it from where it started to where it ended? It smooths a bumpy history into a single annual rate, which is why it is the standard way to compare growth over different periods. This guide gives the CAGR formula, shows how to calculate it in Excel four ways — the direct formula, the RATE function, the XIRR function for irregular dates, and the RRI function — works it for 3, 5, and 10 years with the same numbers, and explains what CAGR hides: volatility, the path taken, and the difference between a smoothed rate and the returns actually experienced year by year.

The CAGR formula

CAGR is defined by the compound-growth identity: ending value equals beginning value times (1 plus CAGR) to the power of the number of years. Solving for CAGR gives: CAGR equals (ending value divided by beginning value) to the power (1 divided by number of years), minus 1. The ratio of end to start is the total growth factor — 2.0 means the value doubled — and raising it to the power 1 divided by years takes the annual root of that total growth, which is the constant annual rate that reproduces the total. Subtracting 1 converts the growth factor to a rate: a growth factor of 1.10 per year is a 10% rate.

The number of years must be the elapsed time, not the count of data points. From the start of 2020 to the start of 2025 is 5 years, and from the end of 2020 to the end of 2025 is also 5 years; from the start of 2020 to the end of 2025 is 6 years. Using 6 data points (2020, 2021, 2022, 2023, 2024, 2025) with 5 years is the common error, and it understates the rate, because the growth is spread over an extra year that did not elapse. Count the intervals between the dates, not the dates themselves. For fractional periods — 3 years and 6 months is 3.5 years — use the fractional year count in the exponent, and the formula still holds.

Excel method 1: the direct formula

Put the beginning value in one cell, the ending value in another, and the number of years in a third, then write the formula with cell references so it can be reused. With the beginning value in B2, the ending value in B3, and the years in B4, the formula in B5 is: =(B3/B2)^(1/B4)-1. Format B5 as a percentage with two decimal places. The parentheses matter: the exponent is 1 divided by years, applied to the ratio, and the minus 1 comes after the power, not inside it. Writing =B3/B2^(1/B4)-1 without parentheses around the ratio gives a different, wrong answer, because Excel evaluates the power before the division.

Work it with numbers: beginning value $10,000, ending value $16,105, years 5. The ratio is 1.6105. 1.6105 to the power 0.2 is 1.10. Minus 1 is 0.10, or 10%. The $10,000 growing at 10% per year for 5 years gives $10,000 times 1.10 to the power 5, which is $16,105.10 — the check that the formula and the growth identity agree. Change the years to 10 with the same ratio and the rate falls to about 4.88%, because the same total growth spread over twice the time is a lower annual rate. That sensitivity to the year count is why the year count must be exact, and why two CAGRs over different periods are not directly comparable without noting the periods.

Format B5 as a percentage. The same layout works for any values and any year count.

Excel method 2: RATE and RRI

The RATE function calculates the interest rate for an annuity, and with no periodic payments it calculates CAGR directly: =RATE(nper, pmt, pv, fv). With years in B4, beginning value in B2, and ending value in B3, the formula is =RATE(B4,0,-B2,B3). The beginning value is entered as a negative number, because RATE treats the present value as a cash outflow and the future value as an inflow; the minus sign is a sign convention, not a negative value. The payment argument is 0, because there are no periodic payments. The result is the same 10% as the direct formula, and the two methods should always agree — if they do not, one of them has a sign or a year-count error.

Newer Excel versions also offer RRI, which calculates the equivalent interest rate directly: =RRI(nper, pv, fv), or =RRI(B4,B2,B3). RRI takes the present value as a positive number, so no sign change is needed, and it returns the same rate. RRI is the cleanest of the three for a CAGR-only worksheet, because its arguments are exactly the CAGR inputs and nothing else. All three methods — direct formula, RATE, RRI — are the same mathematics, and the choice between them is a matter of readability and of what else the worksheet does. A worksheet that also models payments should use RATE; a worksheet that only reports growth should use RRI or the direct formula.

Worked examples: 3, 5, and 10 years

Use one history to show all three periods, so the effect of the period is visible. Suppose an investment starts at $50,000. After 3 years it is $66,550. After 5 years it is $80,526. After 10 years it is $129,687. The 3-year CAGR is (66,550 divided by 50,000) to the power (1 divided by 3), minus 1: the ratio is 1.331, the cube root is 1.10, so 10.00%. The 5-year CAGR is (80,526 divided by 50,000) to the power 0.2, minus 1: the ratio is 1.61052, the fifth root is 1.10, so 10.00%. The 10-year CAGR is (129,687 divided by 50,000) to the power 0.1, minus 1: the ratio is 2.59374, the tenth root is 1.10, so 10.00%. The same 10% rate sustained for 3, 5, and 10 years produces those three endpoints, and the CAGR recovers the rate from any of them — which is the definition working in reverse.

Now a history that is not smooth. Start at $50,000. After 3 years it is $60,000, after 5 years it is $55,000, after 10 years it is $110,000. The 3-year CAGR is (1.20) to the power (1 divided by 3), minus 1, about 6.27%. The 5-year CAGR is (1.10) to the power 0.2, minus 1, about 1.92% — the dip in years 4 and 5 drags the 5-year rate down even though the 3-year rate looked healthy. The 10-year CAGR is (2.20) to the power 0.1, minus 1, about 8.21%. The 10-year figure hides the mid-period fall entirely: an investor who lived through years 4 and 5 experienced a loss, and the CAGR reports only the smoothed outcome. That is the central limitation of CAGR, and it is why it should never be quoted without the period and, where the path matters, the year-by-year figures alongside it.

Same-rate history above the line; uneven history below it. CAGR reports endpoints only.

Irregular dates: XIRR

When cash flows arrive on irregular dates — contributions in some months, withdrawals in others — CAGR over a single start and end value no longer describes the experience, and XIRR is the right tool. XIRR takes a list of cash flows with their dates and returns the annualized internal rate of return: the rate that makes the net present value of the flows zero. The layout is two columns: dates in one, amounts in the other, with outflows negative and inflows positive, and the formula =XIRR(amounts, dates). XIRR assumes a 365-day year and handles irregular spacing exactly, which is why it is the standard for personal investment returns with contributions and withdrawals.

XIRR and CAGR agree in the one case where both apply: a single outflow at the start and a single inflow at the end, with no flows between. In every other case they differ, and the difference is the point: CAGR ignores interim flows, XIRR prices them by their timing. A contribution made just before a rise earns less than a contribution made years earlier, and XIRR reflects that while CAGR cannot. For a mutual fund or a retirement account with regular contributions, report XIRR; for a single lump sum held for a whole number of years, CAGR and XIRR coincide, and either is correct.

Negative and zero values: where CAGR breaks

CAGR requires a positive beginning value and a positive ending value. If the beginning value is zero, the ratio divides by zero and CAGR is undefined — no rate can grow zero into anything. If the ending value is zero, the ratio is zero and the power gives zero, so CAGR is minus 100%, which is correct in the limited sense that the value was entirely lost, but it says nothing about the path. If either value is negative — a loss-making business with negative earnings, for example — the ratio can be negative or the root can be undefined for even roots, and CAGR is meaningless: growth rates are not defined across zero. In those cases, report the absolute change, the change in dollars, or the year-by-year figures, and do not force a CAGR. Excel will return a #NUM! error for a negative ratio with a fractional exponent, which is the spreadsheet correctly refusing a meaningless calculation.

A related trap is a beginning value that is very small. A business growing from $1,000 to $100,000 has a CAGR of over 200% per year over 3 years, which is arithmetically correct and practically meaningless as a forecast: the rate reflects the tiny base, not a sustainable pace. Small-base CAGRs should always be reported with the dollar figures alongside them, so the reader can see that the percentage is a base effect. The same caution applies to any growth rate quoted without its starting value.

Comparing CAGRs fairly

Two CAGRs are comparable only if they cover the same period, the same kind of value, and the same treatment of interim flows. A 5-year CAGR of 12% and a 10-year CAGR of 8% do not mean the first investment is better: the 5-year period may be a bull market and the 10-year period may include a crash, and the 10-year figure is the more complete record. Compare like with like: same start and end dates, same definition of value (price only, or price plus dividends reinvested — total return CAGR is higher than price CAGR for a dividend-paying investment, and the difference is the dividend contribution), and same handling of fees and taxes (gross CAGR before fees is higher than net CAGR after fees, and the gap compounds). A CAGR quoted without its period, its value definition, and its fee treatment is not comparable to any other CAGR.

For revenue or earnings growth, the same discipline applies: state the period, state whether the figures are nominal or inflation-adjusted, and state whether acquisitions, divestitures, or currency effects are included. A company CAGR that includes an acquisition is a growth rate of the combined entity, not of the underlying business, and comparing it with an organic-growth CAGR compares different things. The formula is trivial; the comparability is the work.

What CAGR does not tell you

CAGR does not tell you about volatility. Two investments with the same 10% CAGR over 10 years can have wildly different experiences: one growing 10% every year, the other falling 30% one year and rising 50% the next, repeatedly. The investor in the second lived through losses, margin calls, and the temptation to sell at the bottom; the CAGR reports the same number for both. It does not tell you about risk, drawdown, or the probability of the path repeating. It does not tell you about the future: a past CAGR is a description of endpoints, not a forecast, and extrapolating it forward assumes the same rate continues, which is an assumption, not a calculation. And it does not tell you about interim cash flows, which is why XIRR exists for accounts with contributions and withdrawals.

Use CAGR for what it is good at: summarizing growth between two points as a single annual rate, comparing growth over the same period, and converting a total return into an annual figure that can be compared with interest rates and inflation. Pair it with the year-by-year figures, the period, and the value definition, and it is an honest summary. Quote it alone, without those, and it is a number that looks precise and means less than it appears to.

Frequently asked questions

What is the Excel formula for CAGR?

With the beginning value in B2, the ending value in B3, and the years in B4, the formula is =(B3/B2)^(1/B4)-1, formatted as a percentage. The equivalent functions are =RATE(B4,0,-B2,B3) and, in newer Excel versions, =RRI(B4,B2,B3). All three give the same answer when the inputs are the same.

How do you calculate CAGR for 3 years in Excel?

Use the same formula with 3 as the year count: =(End/Start)^(1/3)-1. For example, $50,000 growing to $66,550 in 3 years gives (66,550/50,000) to the power one third, minus 1, which is 10.00%. The year count is the elapsed time, 3, not the count of data points, which would be 4 including both endpoints.

How do you calculate CAGR for 10 years in Excel?

Use the same formula with 10 as the year count: =(End/Start)^(1/10)-1. For example, $50,000 growing to $129,687 in 10 years gives (129,687/50,000) to the power 0.1, minus 1, which is 10.00%. A 10-year CAGR smooths the whole decade into one rate and hides any falls within it, so report the year-by-year figures alongside it.

What is the difference between CAGR and average growth rate?

The average, or arithmetic mean, of annual growth rates overstates compound growth, because it ignores compounding. An investment that rises 50% one year and falls 50% the next has an average growth rate of 0% but has lost 25% of its value. CAGR is the geometric rate that compounds the start value to the end value exactly, so it is the correct summary of total growth, while the average is not.

Can CAGR be negative?

Yes, when the ending value is lower than the beginning value. A value falling from $50,000 to $40,000 over 5 years has a CAGR of (0.8) to the power 0.2, minus 1, which is about minus 4.36% per year. CAGR is undefined when the beginning value is zero or when either value is negative, because growth rates are not defined across zero.

Should I use CAGR or XIRR?

Use CAGR for a single lump sum held over a whole period with no interim cash flows, and XIRR for an account with contributions and withdrawals on irregular dates. XIRR accounts for the timing of each flow, while CAGR uses only the start and end values. For a single outflow at the start and a single inflow at the end, the two agree.