CAGR is the growth rate a number would have needed, compounding every year, to get from where it started to where it ended. It is the honest way to describe growth over several years, and it takes one line in Excel. The trap is not the formula. It is the exponent, and almost everyone gets it wrong the first time.
This walks through the arithmetic, three ways to write it in Excel, the RRI function most people have never heard of, what breaks when a value is negative, and why CAGR and a simple average of the annual returns disagree so violently that one of them is basically a lie.
What is the CAGR formula?
CAGR = (Ending Value / Beginning Value) ^ (1 / Number of Periods) - 1
Revenue of $100 million growing to $144 million over five years gives (144/100)^(1/5) - 1 = 7.6% a year. That is the rate which, applied five times over, turns 100 into 144.
Why is my CAGR slightly wrong?
Because you probably counted data points instead of periods. The exponent uses the number of compounding intervals BETWEEN your first and last value, not the number of values. If you have annual figures for 2019 through 2024, that is six data points but only five years of growth: 2019 to 2020, 2020 to 2021, and so on to 2023 to 2024. The exponent is 1/5, not 1/6.
Wall Street Prep puts the rule plainly: the beginning period is excluded from the count, because only the periods in which the value was actually compounding should be counted. Subtract the starting year from the ending year and use that. In a spreadsheet, if your first value is in B2 and your last is in B7, there are six cells and five periods.
Off by one here is not a rounding difference. On a five year series, using 6 instead of 5 understates the growth rate by roughly a sixth of itself, which is the difference between a 7.6% story and a 6.3% one.
Does Excel have a CAGR function?
No. There is no function called CAGR in Excel. There are three ways to get it, and they all return the same number.
| Method | Formula | Notes |
|---|---|---|
| Direct exponent | =(B7/B2)^(1/5)-1 | The formula written literally. Works in every version. |
| POWER function | =POWER(B7/B2,1/5)-1 | Identical math, easier for some people to read. |
| RRI function | =RRI(5,B2,B7) | Purpose built. Excel 2013 and later. |
B2 is the beginning value, B7 the ending value, and 5 is the number of periods, counted the way described above.
How do I use the RRI function in Excel?
RRI(nper, pv, fv) returns the equivalent interest rate for the growth of an investment. Microsoft defines nper as the number of periods, pv as the present value, and fv as the future value, and it solves FV = PV * (1 + RRI) ^ NPER for the rate. It arrived in Excel 2013, so it is missing from Excel 2010 and earlier.
One convenience worth knowing: unlike RATE, IRR, and the other cash flow functions, RRI does not want a negative sign on the present value. Write =RRI(5,100,144) and you get 7.6%. Write the same thing with RATE and you need the standard sign convention, =RATE(5,0,-100,144), with pv entered negative. That inconsistency has cost people more time than the formula ever saved.
RRI returns #NUM! when an argument value is invalid and #VALUE! when an argument is the wrong data type.
Why does my CAGR show 0.0743 instead of 7.43%?
Because the result is a decimal ratio, which is what the formula produces. Select the cell and apply Percentage formatting from the Number group on the Home tab, or press the % button. Do not multiply by 100 in the formula as well as applying percent formatting, or you will display 743%.
What happens if the beginning value is negative or zero?
CAGR breaks, and no formatting trick rescues it. The standard formula needs both the beginning and the ending value to be positive. A beginning value of zero gives you a division problem. A negative beginning value produces a result that is either undefined or mathematically real but economically meaningless.
A negative ending value is worse than it looks. You end up taking a root of a negative ratio. With an even number of periods there is no real root at all, only an imaginary one, and Excel returns an error. With an odd number of periods there is a single real root, and it is nonsense, because it implies the value passed smoothly through zero on the way.
This comes up constantly with companies that swing from a loss to a profit. The correct treatment is to mark the cell as not meaningful, usually "nm" or "n.a.", and to describe the change in words or in absolute dollars. A growth rate from negative earnings is not a growth rate. Analysts who paper over this with a made up number get caught by the first person who checks.
CAGR vs average annual return: which is right?
CAGR is the geometric mean of the growth factors. The simple average is the arithmetic mean. The geometric mean is always less than or equal to the arithmetic mean, and the gap widens with volatility. That gap has a name, volatility drag, and it is not a rounding error.
Take $100. It gains 50%, reaching $150. The next year it loses 50%, leaving $75. The arithmetic mean of +50% and -50% is zero, which says you broke even. You did not. You lost a quarter of your money, and the true compound rate is about -13.4% a year.
| Year | Return | Value |
|---|---|---|
| Start | $100.00 | |
| 1 | +50% | $150.00 |
| 2 | -50% | $75.00 |
| Arithmetic mean | 0.0% | Implies $100.00 |
| CAGR | -13.4% | Actual $75.00 |
The arithmetic mean answers a question nobody asked. CAGR answers the one you meant: at what steady annual rate would I have arrived where I actually am? For anything measured over time, CAGR is the correct measure and the average of the annual percentages is not.
When should I use XIRR instead of CAGR?
When money moved in or out along the way. CAGR looks at two numbers, the start and the end, and assumes nothing happened in between. That is fine for a revenue line, which has no cash flows to model, and fine for a lump sum invested once and left alone.
It is wrong the moment there are contributions, withdrawals, or staggered purchases at different dates. Then use XIRR, which takes each dated cash flow into account. Microsoft's own help page on calculating CAGR now points readers toward XIRR for exactly this reason. Where there are only two cash events, one out and one in, with nothing between them, CAGR, IRR, and XIRR all return the same annualized rate, which is a useful sanity check that you have set the sheet up correctly. Our walkthroughs of IRR in Excel and NPV in Excel cover the cash flow versions in full.
What are the limitations of CAGR?
Four, and a good analyst states them out loud rather than waiting to be asked.
It assumes growth was constant, which it almost never was. It hides the path entirely, so a line that tripled and then halved and a line that crept up steadily can share a CAGR. It says nothing about volatility or the drawdown in year three, which is the number that actually determined whether anyone kept their job. And because it uses only the first and last values, it is exquisitely sensitive to which endpoints you pick: start the series one year later and a mediocre record can become a strong one.
That last point is the one to watch in someone else's deck. When a CAGR is quoted with an unusual start year, ask what the number looks like a year either side. If the answer changes materially, the growth rate is a choice of endpoints rather than a fact about the business.
Getting the numbers into Excel in the first place
All of this presumes the historical figures are already sitting in cells. Often they are not. They are in a five year summary inside an annual report, a fund factsheet, or a lender's PDF, printed as a table nobody will hand you as a spreadsheet.
Retyping a revenue series is where CAGR errors are actually born, because a transposed digit in the beginning value moves the exponent's base and quietly changes the answer. Convert the source instead. Drop the file into the PDF to Excel converter at the top of this page, or use the guide to converting a PDF to Excel, and the figures land as numbers rather than as text that will not multiply. If the statements you are working from are financial reports or an income statement, those pages cover the specifics.
Then check it before you compute anything: total one column against the printed total and confirm the beginning and ending values match the source exactly. A CAGR is one formula sitting on top of two numbers, so those two numbers are the only thing that matters. If the growth you are measuring is a portfolio rather than a revenue line, and the money went in over time rather than all at once, an underwriter reviewing the cash flows would use XIRR and so should you.