What Most People Miss About How IRR Works in Excel

IRR calculates the discount rate that makes net present value zero — but only if your cash flows follow strict rules. But most real-world project models break those rules without warning.

The Setup

Sarah Chen at Acme Corp is evaluating a warehouse retrofit project. Her team tracked six quarterly cash flows starting Q1 2023. She entered them in column B, with dates in column A for context (though IRR ignores dates). Here’s what she actually typed into Excel:

Quarter	Cash Flow	Date
Q1 2023	-$245,000	2023-03-15
Q2 2023	$18,200	2023-06-15
Q3 2023	$22,500	2023-09-15
Q4 2023	$31,700	2023-12-15
Q1 2024	$44,900	2024-03-15
Q2 2024	$52,300	2024-06-15
Q3 2024	$68,100	2024-09-15
Q4 2024	$92,400	2024-12-15

She placed this data in A1:C9. The outflow is in B2; inflows run B3:B9. That’s eight rows total — not seven, not nine. Important, because IRR needs at least two values and expects the first to be negative (initial investment).

The Challenge

Sarah ran =IRR(B2:B9) and got #NUM!. She tried =IRR(B2:B9,10%) and got 14.2%. She emailed her manager saying “project returns 14.2% annually.” But her finance lead flagged it: “Did you verify sign changes? And are those quarterly flows truly evenly spaced?” That’s the trap. IRR assumes equal time intervals — quarterly here — but Excel doesn’t validate spacing. It also requires at least one sign change between values. If all numbers are positive after the first, or if there are multiple sign flips (e.g., outflow → inflow → outflow), IRR may return an incorrect or non-unique solution. Worse: it won’t tell you. Also — big one — IRR treats all periods as identical length. So if your actual cash flows land on March 15, June 22, September 10… IRR ignores that. It just counts “period 1, period 2…”

Walking Through It

Let’s fix this step-by-step, using Sarah’s real data. Step 1: Check sign pattern Select B2:B9. Scan manually: B2 is negative, B3–B9 are all positive. ✅ One sign change. Good. Step 2: Verify period regularity Look at column C. Differences between dates:

C3−C2 = 92 days
C4−C3 = 91 days
C5−C4 = 92 days
C6−C5 = 91 days
C7−C6 = 92 days
C8−C7 = 91 days
C9−C8 = 92 days

Close enough — quarterly IRR is acceptable. If gaps varied by >10 days, use XIRR instead. Step 3: Add a guess (and why it matters) Type =IRR(B2:B9,0.1) in cell D2. Why 10%? Because default guess is 10%, but if your expected return is wildly different (say, 2% or 45%), omitting the guess can make IRR converge on the wrong root — especially with nonstandard cash flow shapes. Step 4: Confirm with NPV In E2, enter =NPV(D2,B3:B9)+B2. This should be near zero. With D2 = 14.2%, E2 shows $23.78 — close enough (rounding error). If it’s >$500, your IRR is unstable. Here’s what changes after each correction:

Action	IRR Result	NPV(B2:B9)
=IRR(B2:B9)	`#NUM!`	—
=IRR(B2:B9,5%)	13.8%	$1,240
=IRR(B2:B9,10%)	14.2%	$23.78
=IRR(B2:B9,20%)	14.2%	$23.78

Notice: once you hit 10% or higher guess, result stabilizes. That’s your signal.

The Result

Final clean output in her model looks like this — no formulas buried, clear labeling, cross-verified:

Metric	Value	Notes
IRR (quarterly)	3.41%	=IRR(B2:B9,10%)
IRR (annualized)	14.2%	=(1+0.0341)^4−1
NPV @ IRR	$23.78	Tolerance OK
XIRR (for comparison)	14.3%	=XIRR(B2:B9,C2:C9)
Payback Period	5.2 quarters	Cumulative sum in B

She put this block in F1:H6. No hidden assumptions. Anyone reviewing can trace back to B2:B9 and C2:C9 instantly.

What Could Go Wrong

Here are three mistakes Sarah almost made — and how to spot them before sending the file to leadership: Mistake #1: Forgetting the initial outflow is required in the range She once moved the -$245,000 to B1 and ran =IRR(B2:B9). Result: 42.6%. Wildly inflated. IRR needs the full stream — including the upfront cost — or it treats the first inflow as “investment.” Always confirm your range starts with the negative number. Use Ctrl+` (grave accent) to toggle formula view — fast way to audit. Mistake #2: Using IRR when cash flows aren’t periodic Her colleague used IRR on a SaaS renewal model with payments on Jan 10, Apr 3, Jun 22, Oct 15… Excel counted those as “periods 1–4” — but they’re 93, 80, 115, and 107 days apart. Result: 21.8% instead of true XIRR of 18.4%. Fix: Use =XIRR(values,dates,[guess]). Alt+M+V opens Formulas > Insert Function — type “xirr” and tab. Mistake #3: Assuming IRR = ROI or profit margin The 14.2% isn’t profit. It’s the compound annual growth rate that zeroes out NPV. Her gross profit was $227,100 on $245,000 invested — that’s 92.7% gross return over 2 years, not 14.2%. Confusing these leads to bad capital allocation. Always pair IRR with absolute dollars and payback timing. If you’re auditing someone else’s IRR model tomorrow, here’s your 60-second checklist:

Check	✅ Pass	❌ Fail
Range includes first negative value	Yes	No
At least one sign change in range	Yes	No
All periods are roughly equal (±5 days)	Yes	No → use XIRR
NPV(@IRR) ≤ $100 (for <$1M projects)	Yes	No → recalc with better guess