How to Calculate Growth Rate in Excel: MoM, YoY, CAGR — and Why AVERAGE Overstates It

How-to · Updated July 2026 · ~11 min read

Ask "what's our growth rate?" and one revenue history can honestly answer 55%, 48%, or "that number is meaningless" — depending on whether you averaged the periodic rates, compounded them, or divided by a period in which the baseline was a loss. All three come out of the same column. Only one belongs in a board deck.

This post builds every common growth calculation on one revenue dataset: the simple percentage change, month-over-month, quarter-over-quarter, year-over-year, CAGR worked cell by cell, and AAGR set against CAGR so you can see exactly why a plain AVERAGE of growth rates always reads high. It ends with the trap that produces confidently wrong numbers — a negative baseline — and a decision table for which rate answers which question.

The formula everything else is built on

Growth rate is percentage change: how much the new value moved, expressed as a fraction of the old one.

=(new - old)/old

With last period's revenue in B2 and this period's in B3:

=(B3-B2)/B2

An algebraically identical version with one fewer reference:

=B3/B2-1

Both return a decimal — 0.05 for 5% growth, -0.05 for a 5% decline. Do not multiply by 100. Format the cell as a percentage instead: Home tab > Number group > Percent Style (or Ctrl+Shift+%), then Increase Decimal once so 5.0% and 5.4% stop looking identical. A cell holding 0.05 formatted as 5.0% still behaves as 0.05 in any later arithmetic; a cell holding 5 because you multiplied by 100 will quietly wreck the first compounding formula you point at it. If that behaviour is new to you — it is the reason Excel shows 0.45 when you expected 45% — the percentage formulas accountants use post unpicks it in full, alongside percent-of-total, margin and VAT.

That is also the answer to "what is the revenue growth formula": current-period revenue minus prior-period revenue, divided by prior-period revenue. Every rate in this post is that formula applied to a different pair of periods — the skill is choosing the pair.

Set up the dataset: 24 months of revenue

One layout serves every calculation below. Months in column A (real dates — type 01/01/2024 and format as mmm-yy — not text, so date functions and charts behave), revenue in column B, rows 2 to 25 covering January 2024 to December 2025. MoM goes in column C, YoY in column D.

If the history lives in your accounting system rather than a sheet, get it out as monthly totals first — the QuickBooks general ledger export walkthrough covers pulling a clean ledger, and a P&L built with SUMIFS gives you the monthly revenue line as a by-product. One discipline before any growth maths: check the revenue column is genuinely numeric. =SUMPRODUCT(--ISTEXT(B2:B25)) should return 0; anything else means text-formatted numbers that will silently drop out of averages.

MoM, QoQ and YoY on the same column

Month-over-month

In C3, then fill down to C25:

=(B3-B2)/B2

February reads −5.0%, March +10.0%. Neither tells you much on its own — MoM is the noisiest rate you can compute, because one delayed invoice or one 31-day month swings it. Use it for spotting step changes (a price rise landing, a client churning), not for judging trajectory.

Quarter-over-quarter

Sum the quarters before dividing — never average the three MoM rates inside a quarter, for reasons the AAGR section makes painful. Q2 2024 against Q1 2024:

=SUM(B5:B7)/SUM(B2:B4)-1

Q1 totals 125,790 and Q2 totals 137,400, so QoQ is +9.2%. Three-month sums smooth invoice timing without hiding a real trend for long.

Year-over-year

Compare each month to the same month one year earlier — twelve rows back. In D14, fill down to D25:

=(B14-B2)/B2

January 2025 against January 2024: (51,450 − 42,000) / 42,000 = +22.5%. YoY is the workhorse rate for any seasonal business, because December is compared with December and the seasonality cancels. Its blind spot is the opposite of MoM's: a slowdown that started four months ago still shows healthy YoY because most of the comparison window predates it. Report both, and when a YoY number needs explaining line by line, that is variance analysis — same subtraction, decomposed into causes.

CAGR, worked cell by cell

Compound annual growth rate answers a different question from every rate above: at what single steady rate, compounded every period, would the start value have become the end value? It ignores the path entirely — only the endpoints and the elapsed time matter.

CAGR = (end/start)^(1/n) - 1

The fencepost is where most spreadsheets go wrong: n is the number of intervals, not the number of values. Twenty-four monthly values span 23 month-to-month intervals; three year-end balances span two years. Divide by 24 here and every downstream number is quietly flattered.

On the dataset — 42,000 in B2, 68,500 in B25:

=(B25/B2)^(1/23)-1

which returns 2.15% per month. Step by step: 68,500 / 42,000 = 1.6310, the 23rd root of 1.6310 is 1.0215, minus 1 is 0.0215. A version that survives rows being added, because it counts the intervals itself:

=(B25/B2)^(1/(COUNT(B2:B25)-1))-1

Excel has no CAGR function, but RRI (Excel 2013 onwards) is exactly this calculation dressed as a finance function — RRI(nper, pv, fv):

=RRI(23, B2, B25)

Same 2.15%. (The function actually named GROWTH is not this: it fits an exponential trend line through all the points by regression and predicts new ones. Useful for forecasting, wrong for "what was our CAGR".)

A monthly rate impresses nobody, so annualise it — either compound the monthly rate twelve times, or put the annualisation straight into the exponent:

=(1+2.15%)^12-1
=(B25/B2)^(12/23)-1

Both give roughly 29.1% per year. Sense-check the whole thing by compounding forward: 42,000 × 1.0215^23 ≈ 68,500. If that multiplication does not land back on your end value, the exponent is wrong — almost always the fencepost. CAGR is also the rate to feed a projection, since compounding forward is literally how a financial model's revenue build works.

There is a third route worth knowing because it connects CAGR to the next section. Convert each MoM rate to a growth factor (1 + rate) and take the geometric mean:

=GEOMEAN(1+C3:C25)-1

In Microsoft 365 that spills as a dynamic array; in older Excel confirm it with Ctrl+Shift+Enter. It returns exactly the same 2.15% as the endpoint formula — the geometric mean of the factors is the CAGR — with one practical caveat: GEOMEAN errors if any factor is zero or negative, so a month with zero revenue breaks it while the endpoint formula shrugs.

AAGR vs CAGR: why AVERAGE overstates growth

The tempting shortcut is to average the periodic rates — =AVERAGE(C3:C25) — and call that the growth rate. That number has a name, average annual growth rate (AAGR), and a reliable property: it is never below CAGR, and it overstates whenever growth varies at all.

Watch it happen on three clean year-ends. Revenue 100,000 → 200,000 → 220,000, so the yearly rates are +100% and +10%:

Now audit both by compounding forward from 100,000 for two years:

• At the AAGR of 55%: 100,000 × 1.55² = 240,250. Actual revenue is 220,000 — the "average" rate invents 20,250 that never existed.
• At the CAGR of 48.3%: 100,000 × 1.4832² = 220,000. Lands exactly.

The distortion gets worse with volatility, to the point of changing the sign. Revenue 100,000 → 150,000 → 75,000 is +50% then −50%:

• AAGR: =AVERAGE(50%,-50%) = 0% — flat, apparently.
• Reality: revenue is down a quarter. CAGR: =(75000/100000)^(1/2)-1 = −13.4% per year.

The reason is arithmetic, not accounting. Growth compounds by multiplication — the factors 1.5 and 0.5 multiply to 0.75 — but AVERAGE adds. And the arithmetic mean of any set of positive factors is always greater than or equal to their geometric mean, with equality only when every period grew at the identical rate. Since real revenue never does, AAGR carries a built-in upward bias, and the gap widens with the variance of the rates (fund managers call the same effect volatility drag). The person averaging almost always means the geometric mean; AVERAGE just isn't it.

AAGR is not useless — it is a fair description of the typical single-period swing, which is genuine information about how bumpy the ride is. Quote it as that, next to CAGR, never instead of it. Anyone compounding your AAGR forward will reconstruct revenue you never earned, and anyone diligencing your numbers will do exactly that multiplication.

The negative-baseline trap

Every formula above divides by the prior period. The moment that prior period is zero or negative, the output stops meaning anything — and Excel will not warn you, because the arithmetic is perfectly legal.

Take a company that lost 50,000 in Q1 last year and made 30,000 this year — the best story in the building:

=(30000-(-50000))/(-50000)  →  -160%

Minus 160% "growth" for a swing from loss to profit. Flip the story — losses deepen from −50,000 to −80,000:

=(-80000-(-50000))/(-50000)  →  +60%

Plus 60% "growth" while the business deteriorates. The negative denominator reverses the sign of every answer, so the metric is not just noisy, it points the wrong way. A zero baseline is at least honest enough to fail loudly with #DIV/0! — a brand-new product line has no growth rate in its first period, however much "infinite growth" would brighten the deck.

Three defensible ways to handle it:

1. Report the absolute change. "Improved by 80,000, from a 50,000 loss to a 30,000 profit" — =B2-B1 — carries more information than any percentage here.
2. Print "n/m" (not meaningful) and guard the formula so the nonsense never renders: =IF(B1<=0,"n/m",(B2-B1)/B1).
3. The ABS-denominator variant, =(B2-B1)/ABS(B1), at least points the right direction (+160% for the recovery, −60% for the deterioration), but the magnitude is not comparable with ordinary growth rates — flag it if you use it.

Revenue rarely goes negative, so this trap lives in profit, EBITDA, cash-flow and net-margin growth — precisely the rows a P&L review dwells on. Any growth column on a measure that can cross zero should carry the IF guard from day one.

How HISAB 360 helps

The mechanical half of this job — getting two clean years of monthly revenue out of the accounting system and into column B — usually takes longer than the growth maths. HISAB 360 is a paid Excel add-in (Windows desktop Excel, Microsoft 365 or 2016+) with an AI assistant docked in a panel inside the workbook. It connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, and pulls invoices and general-ledger history straight into a sheet — so the 24-month revenue column comes from the ledger, not from a stack of manual exports.

From there the maths in this post is a plain-English request: ask for MoM, YoY and CAGR on the revenue column and the assistant writes the formulas into the sheet — the fencepost and the AVERAGE-vs-GEOMEAN distinction included — and builds the trend chart alongside. Because it reads the live workbook, it can also point at the rows behind a number, and it applies the same treatment to aging analysis, financial statements and reconciliation work.

Honest limits: it is Windows desktop Excel only — no Mac, no Excel on the web — and it is paid, with a 15-day free trial (50 AI credits, no card). Connecting an accounting system uses your own OAuth authorisation, and a growth rate going into a board pack deserves the same eyeball you would give a junior's formula.

Which growth rate to use when

Two rules travel with the table. Cite the periods with the number — "+22.5% YoY, Jan-25 vs Jan-24" is auditable, "+22.5% growth" is not. And any headline multi-period rate should be CAGR, cross-checked by compounding it forward onto the end value before it leaves your desk.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.