Spreadsheet jockeys keep one number close at all times: the compound annual growth rate. You may know it as CAGR. It smooths out the jagged ups and downs of a multi-year run and tells you the steady annual pace that would have produced the same final result. Investors lean on it. Founders quote it in pitch decks. Finance teams use it to compare two products that look nothing alike on a year-by-year basis — and somehow Excel still ships without a single button labelled "CAGR".

That gap is the reason this guide exists. You will learn the formula by hand, the shortcut function that hides in plain sight, two backup methods for irregular periods, and the small judgement calls that keep your number honest. By the end you should be able to drop a CAGR cell into any workbook in under ten seconds — and explain to a sceptical boss exactly what it is measuring.

Here is the core idea first. CAGR answers a deceptively simple question: at what fixed yearly rate would an amount grow from a starting value to an ending value over a chosen number of years? It is geometric, not arithmetic, so a 50% gain followed by a 50% loss does not net to zero — the loss eats a larger base. CAGR captures that asymmetry.

Average annual return does not. If somebody tells you their portfolio "averaged" 18% you should ask whether they mean arithmetic mean or CAGR; the gap can be enormous.

The classic textbook formula is =((End/Start)^(1/Years))-1. Three inputs, one output, no add-ins required. Start value goes in the denominator. End value sits on top. Raise the ratio to one over the number of years — that is the geometric mean step — then subtract one to convert from a multiplier into a percentage growth rate. Format the cell as a percentage with two decimal places and you have your answer.

Worked example. A small SaaS line item billed $124,000 in 2021. By the end of 2025 it billed $312,500. That is four full years of growth. The cell becomes =((312500/124000)^(1/4))-1 which returns 0.2596 — a CAGR of 25.96%.

Looks tidy. But notice the year count. The math counts intervals, not endpoints. From end-of-2021 to end-of-2025 is four steps, not five. Get that wrong and your number drifts by several percentage points.

Excel hides a one-line shortcut for exactly this calculation. The function is called RRI, which stands for "rate of return on investment", and it has been available since Excel 2013. The syntax is =RRI(nper, pv, fv). Three arguments: number of periods, present value, future value. That is it.

For the SaaS example above, the call becomes =RRI(4, 124000, 312500) and Excel returns 0.2596 — the same 25.96% you got from the manual formula. The advantage of RRI is clarity.

Anyone auditing your workbook can read the arguments left to right and understand what you intended. The disadvantage is portability. If your file ever needs to open in Google Sheets, OpenOffice, or an older Excel version, RRI may not resolve and the cell will throw #NAME?. For shared or legacy environments the manual formula is the safer choice.

One quiet trap with RRI: the sign of present value. RRI follows the same time-value-of-money sign convention as PV, FV, and PMT, which means present value and future value should normally have opposite signs. In practice, because you are measuring an investment that grew, putting both as positive numbers works fine and Excel still returns the right answer.

If you ever feed it a negative pv (an outflow) and a positive fv (an inflow), the result is identical. Just stay consistent inside a single workbook.

People stumble on CAGR for one of three reasons: they mis-count years, they pick a noisy start or end point, or they apply it where it does not belong. The math is forgiving on the first two — if you are wrong you will see it — but the third is sneaky and worth a paragraph.

CAGR assumes a single starting investment, a single ending value, and no in-between contributions or withdrawals. That makes it perfect for revenue lines, share prices, asset values, and population counts.

It is the wrong tool for a savings account where you keep adding money every month, or for a portfolio with cash flows in and out across the period. Use IRR or XIRR for those. We will come back to the IRR distinction in a moment, because it confuses even senior analysts.

Then there is the time-window question. Pick a CAGR start point at the bottom of a recession and an end point at the top of a bubble and the number flatters everybody. Pick the inverse and the same business looks like it is dying.

Honest analysts publish CAGR over rolling windows — three-year, five-year, ten-year — so the reader can spot whether the headline figure is a fluke. If you are presenting CAGR to anyone who will act on the number, do the same.

The POWER variant deserves a closer look because it is genuinely useful when the exponent itself is a calculated value. =POWER(End/Start, 1/Years)-1 does exactly the same arithmetic as the caret-based formula, but if you have something like 1/(YEAR(B2)-YEAR(A2)) sitting inside, POWER reads more naturally and is easier for a future you to debug at 11 pm.

GEOMEAN is the dark-horse option. Say you have a column of annual returns — 0.12, −0.04, 0.18, 0.07, 0.22 — rather than a start and end balance. Adding 1 to each return turns them into growth multipliers (1.12, 0.96, 1.18, 1.07, 1.22).

Feed that range into GEOMEAN, subtract 1, and you get the CAGR for the period: =GEOMEAN(B2:B6)-1. The reason this works is that CAGR is mathematically a geometric mean of the annual growth multipliers; GEOMEAN computes exactly that. It is also tolerant of irregular years — though you should still confirm each row really does represent a single year.

One catch. GEOMEAN refuses to operate on zero or negative values. If any annual return is −100% or worse (so the multiplier hits zero or goes negative), the function throws #NUM!.

That is mathematically correct — a real-world business that lost everything cannot have a meaningful geometric mean — but it can surprise people the first time they hit it. Wrap GEOMEAN in IFERROR if you need a graceful fallback.

That last tab points at the heart of the IRR-versus-CAGR confusion. Both return a percentage that sounds like growth. Both can be quoted as "annualised". But they answer different questions.

CAGR asks: if I invested once and waited, what steady rate would produce this end value? IRR asks: given this exact stream of inflows and outflows on specific dates, what discount rate makes the present value of the stream equal zero?

If your cash flow is one outflow followed by one inflow years later, CAGR and IRR converge to the same number. The moment you add a second contribution, a partial withdrawal, or any non-trivial pattern, they diverge — sometimes sharply.

A pension fund with annual contributions and one big terminal value should be measured by XIRR, not CAGR. A founder reporting on a single product's revenue trajectory should use CAGR, not IRR. Get this right and you sound credible. Get it wrong and a sharp CFO will catch it inside ninety seconds.

There is also the question of when CAGR makes sense in the first place. Two years is the absolute floor — anything shorter is just a year-over-year change with extra steps. Five years gives you enough cycle for a useful number.

Ten years is where CAGR really shines, because it smooths through one or two recessions and tells you the through-cycle pace. Quoting a one-year "CAGR" is, technically, just last year's growth rate; doing so is a small but telling sign that the speaker does not understand the concept.

Time to build a reusable calculator. Open a blank sheet and put labels in column A: Start Value, End Value, Start Date, End Date, Years, CAGR. Beside each label in column B, leave the input cells empty for now.

In B5 (Years) put =YEARFRAC(B3,B4,1) — YEARFRAC computes a precise fractional year between two dates using the actual day count, which is what you want for honest CAGR over partial years. In B6 (CAGR) put =POWER(B2/B1,1/B5)-1 and format the cell as a percentage with two decimals.

Now wrap it in error handling so the calculator does not throw ugly errors when cells are blank. The polished version of B6 is =IFERROR(POWER(B2/B1,1/B5)-1,"") — returns blank if any input is missing, returns the CAGR otherwise.

Add a second formula in B7 that returns the equivalent doubling time: =IFERROR(LOG(2)/LOG(1+B6),""). Doubling time is one of the most intuitive ways to convey what a CAGR means to a non-finance audience — "this business is doubling roughly every three years" lands harder than "this business is growing at 26%".

Save the file as a template (.xltx). Next time anyone on the team needs a quick CAGR, they open the template, drop in start value, end value, two dates, and read off the answer. You have just removed a small but meaningful source of analyst error from your organisation.

A note on rounding. Excel will happily hand you back 0.2596432... and you will be tempted to display it as 25.9643%. Don't. The four inputs to a CAGR formula — start, end, year count, and time window choice — together carry maybe three significant figures of real information.

Reporting CAGR to four decimal places implies a precision that does not exist. One or two decimals is honest; anything more is theatre.

A note on negatives. CAGR can be negative — if the end value is below the start value, the math is still valid and you get a negative rate. What the formula cannot handle is a sign flip: a positive start with a negative end (or vice versa).

That produces a complex number, which Excel sensibly refuses to compute. If you ever see #NUM! from a CAGR formula, the first thing to check is whether one of your inputs went negative.

A note on logs. Some analysts compute CAGR as =EXP(LN(End/Start)/Years)-1, which is mathematically identical to the caret form. The advantage is that intermediate logs sometimes integrate more naturally into other calculations — for instance, when you are aggregating CAGRs across multiple business units weighted by revenue.

The disadvantage is readability. Stick with the caret form for standalone cells; reach for logs only when log-space math is the natural language of the surrounding model.

You now have everything you need: the manual formula, the RRI shortcut, the POWER and GEOMEAN alternatives, the reusable template, and the sanity checks that separate trustworthy analysts from credulous ones.

The single most valuable habit you can build is to always publish a CAGR alongside its supporting context: the window, the rolling alternatives, and a quick eyeball check that the number passes the smell test. Doing that once turns CAGR from a buzzword into a tool. Doing it every time turns you into the person other people quote.

If you came here looking for the formula and you leave with the discipline, that is a fair trade. Compounding is the most powerful force in finance — CAGR is the cleanest way to measure it — and Excel makes the measurement free. The rest is just being careful.

One last practical pointer. If you are going to compute CAGR across a lot of rows — say, every product line in a 200-row revenue table — wrap your formula in IFERROR and reference column headers by structured names rather than cell addresses.

=IFERROR(POWER([@End]/[@Start],1/[@Years])-1,"") inside an Excel Table propagates automatically when you add new rows, never breaks when somebody sorts the data, and reads like English. Combine that with conditional formatting that flags any CAGR above 50% or below −20% for human review, and you have a self-policing growth dashboard.

For audit trails, drop a comment in the CAGR cell explaining the time window and any choices about partial years. Six months from now, when somebody asks why this number is 24.3% rather than 25.1%, you (or your replacement) will be able to answer in seconds.

That documentation habit is what separates analysts who get promoted from analysts who get audited.

If you want to compare CAGRs across multiple subsidiaries in a single dashboard view, build a small summary table at the top of your workbook with one row per business unit and three columns: 3-year CAGR, 5-year CAGR, and 10-year CAGR.

Pull each value via SUMIFS or INDEX/MATCH from the underlying data. Conditional-format the three columns side by side with a diverging colour scale — red below zero, white near zero, green above. The pattern that emerges tells a story no single number can: which units are accelerating, which are decelerating, which look healthy on a five-year basis but are quietly stalling on a three-year view.

The same trick works for stock-style data. Build a column of trailing CAGRs — one year, three years, five years, ten years — for any time series. If the short-window CAGR is materially below the long-window CAGR, the asset is decelerating; if the short-window number is above, it is accelerating.

This is the simplest, cleanest trend signal you can extract from a price series, and it takes about four formulas to set up. Quants do far more sophisticated things, of course, but for ninety per cent of business questions, a trailing-CAGR ladder beats fancy regressions on both clarity and defensibility.

Finally, a word on charts. CAGR is a number, not a picture — but you can illustrate it. Plot your actual series on one line and a synthetic series on a second line, where the synthetic series starts at the same value and grows at the computed CAGR each year. The two lines will start and end together but tell different stories in between.

Use this side-by-side view in any presentation where you need to show both that CAGR is a fair summary and that the actual journey was bumpier (or smoother) than the summary suggests. Audiences trust analysts who show their work; this is the visual equivalent of showing your work.