How to Calculate IRR in Excel: IRR vs XIRR and the #NUM! Error Fix

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

The board wants one number for the new machine: "what return does it make?" You put the cash flows in a column, type =IRR(B2:B7), and Excel returns #NUM!. Or worse, a confident 15.6% that is quietly wrong, because the money did not arrive on the anniversary of the outlay — it arrived on the dates in the ledger, and the plain IRR function has no idea what a date is.

This post walks a capex appraisal from a column of cash flows to a defensible rate: the IRR syntax and a worked example, the three assumptions IRR applies without telling you, XIRR for the dated flows real ledgers produce, a diagnosis of #NUM!, and the cases where IRR is the wrong number entirely.

What IRR actually is

The internal rate of return is the discount rate at which a project's net present value equals zero. Discount the cash flows at 10% and the project shows a surplus; at 20% a deficit; somewhere between is the rate where the surplus is exactly nil.

Two consequences matter more than the definition:

• It is a break-even rate, not a profit figure. An IRR of 15.6% means the project survives a cost of capital up to 15.6% and no further. Compare it against your hurdle rate.
• There is no closed-form formula. Past two periods the equation cannot be rearranged to solve for the rate, so Excel finds it by trial and error. Hence #NUM! as a possible answer, and the guess argument.

IRR consumes cash flows, not profit. Depreciation is not a cash flow; an unpaid supplier invoice is not one yet. Lifting numbers off a management P&L means adding back non-cash charges and adjusting for working capital first (building the P&L is a separate job).

The IRR function: syntax and a worked capex example

=IRR(values, [guess])

• values — cash flows in period order, with at least one negative and one positive number.
• guess — optional starting point for the search. Omit it and Excel starts at 10%.

You are appraising a 100,000 machine paid for up front, with five years of uneven net cash inflows:

1. Enter the outlay as a negative number in B2. This is the step people skip, and the commonest cause of #NUM!.
2. Enter the inflows in B3:B7 in period order, one row per year, with no blank rows.
3. In B9, type =IRR(B2:B7) and press Enter.
4. Format B9 as a percentage — Home > Number group > %, or Ctrl+Shift+% — then click Increase Decimal twice. Percent Style applies zero decimals, so 0.1558 shows as 16% and hides the precision you need near a hurdle rate. (The raw result reads 0.1558 for the reason behind every percentage formula in Excel: the stored value is a decimal and the format does the ×100.)

The answer is 15.58%. The project returns 155,000 on a 100,000 outlay, and the timing of those receipts is worth a break-even discount rate of 15.58% a year. Against a 10% cost of capital it clears comfortably; against a 16% hurdle it fails.

One sanity check: add 5,000 to year 1 and the rate should rise more than adding it to year 5, because early money is worth more.

The three rules IRR applies without telling you

IRR has no dates. It has a column, and it makes three assumptions about that column that are invisible on screen.

Rule 1: every row is one equal period, of whatever length you decided. Feed it monthly flows and the result is a monthly rate. To annualise, compound — do not multiply:

Correct:   =(1+B10)^12-1
Wrong:     =B10*12

On twelve monthly receipts of 4,600 against a 50,000 outlay, IRR returns 1.556% per month. Compounded that is 20.36% a year; multiplied by twelve, 18.67%. The naive version understates by nearly 1.7 points and is the one most people reach for. Quarterly flows take ^4, weekly ^52.

Rule 2: blank cells are ignored, not treated as zero. Microsoft's documentation states plainly that text, logical values and empty cells in the array are ignored. A year generating nothing, left blank because the cell "is" nothing, does not become a zero — it vanishes, and every flow after it is pulled a year earlier.

Take the same project with a genuinely nil year 3, a planned refit:

Same project, 1.29 percentage points of phantom return, nothing on screen to show which version you are looking at. Type an explicit 0 into every empty period.

Rule 3: position is chronology. IRR reads the order of the values as the order of the cash flows, so sorting that range by amount or supplier does not re-sort a list — it rebuilds the project's timeline.

If the three rules feel fragile, that is the correct reaction, and it is what XIRR fixes.

XIRR: the version real ledgers need

=XIRR(values, dates, [guess])

XIRR takes the dates as an argument, discounts each flow by its actual day count on a 365-day year, and returns an annual effective rate however the flows are spaced. No period grid, no annualisation arithmetic, no blank-cell landmine.

Run the same amounts again with the dates the transactions really carry: the machine paid for on 15 March 2025, receipts landing at 31 March year ends.

=XIRR(B2:B7,A2:A7) returns 15.32%, against IRR's 15.58%.

A quarter of a point sounds like rounding. It is not: every receipt sits 16 days later than the anniversary IRR assumed, plus a leap day in 2028. If your hurdle rate is 15.5%, the two functions disagree about whether the project is approved.

The gap widens once the flows are genuinely irregular — which in a real ledger they always are. A staged purchase with quarterly returns:

XIRR returns 20.13%. IRR cannot express this series at all: two outflows 49 days apart are not two periods, and forcing them onto a grid means padding the column with invented zero months. Run =IRR on it anyway and you get 4.65% "per period" — a rate per one-eighth of a project — which someone will annualise as though it were quarterly and land at 19.95%: close enough to survive review, arrived at by luck.

Two rules keep it working: values and dates must be the same length, and the first date starts the schedule — every other must be later, though they need not be sorted. Both are #NUM! when broken.

Use XIRR by default. IRR is the special case that works when flows are exactly periodic.

Fixing the #NUM! error

Excel solves iteratively: try a rate, measure how far NPV is from zero, adjust, repeat. Microsoft's function reference documents a ceiling on that search — as of 2026, IRR stops after 20 iterations once it is within 0.00001 percent, and XIRR after 100 (worth re-checking against the current docs). Exhaust the iterations and you get #NUM! — as you do from structural problems that never had a solution.

The sign problem is the one you will hit most. An export from a fixed-asset register or purchase ledger often lists everything as a positive amount, direction implied by a "Type" column. IRR sees a column of positive numbers, correctly concludes no rate discounts them to zero, and returns #NUM!. Flip the outflows with a helper column: =IF(C2="Payment",-ABS(D2),ABS(D2)).

What guess actually does

guess is not a hint about the answer you want. It is the starting point for the search, and it matters in two situations. The first is an extreme rate: a project returning 8x in eighteen months, or a write-off returning 30% of its outlay, sits so far from the default 10% that twenty steps may not reach it.

=IRR(B2:B7, 1)        starts at 100% - for very high returns
=IRR(B2:B7, -0.5)     starts at -50% - for loss-making projects

If #NUM! persists on data you know is complete and correctly signed, walk the guess across the plausible range — -0.9, -0.5, 0, 0.5, 1, 3 — and see where an answer appears.

The second situation is more serious: when a series has more than one valid IRR, the guess decides which one Excel hands you — see When IRR misleads below.

Resist =IFERROR(IRR(B2:B7),""). #NUM! is usually telling you something true about the cash flows, and hiding it leaves a blank cell a board pack reads as "nil". If you must trap it, trap it with a label: =IFERROR(IRR(B2:B7),"check cash flows").

MIRR, and the reinvestment assumption

One criticism of IRR is settled by arithmetic rather than judgement: the maths implicitly assumes every interim cash flow is reinvested at the IRR itself, so a project showing 15.58% assumes each year's receipts go straight back out at 15.58%. If that money sits in a deposit account at 4%, the headline overstates what the project delivers. MIRR lets you state both rates:

=MIRR(values, finance_rate, reinvest_rate)

finance_rate is the cost of the money funding the outflows; reinvest_rate what you realistically earn on the receipts. With borrowing at 6% and reinvestment at 8%, =MIRR(B2:B7,0.06,0.08) returns 12.42% against IRR's 15.58% — three points of the headline were an assumption about money the project never controlled. MIRR shares IRR's equal-period rule, so it is annual only if your rows are.

How HISAB 360 helps

The formulas here are short; the work around them is not. It is building the cash-flow column in the first place — pulling capex payments and receipts out of the ledger onto their real dates, signing the outflows correctly, filling nil periods with explicit zeros — then rerunning it for every project the board asks about.

HISAB 360 is a paid AI add-in that sits in a chat panel inside desktop Excel, so that setup happens in the live workbook. Ask for a dated cash-flow schedule with XIRR, NPV and a payback row and it builds the sheet — signs, zero periods, date formatting and all — rather than returning text to paste. Because it connects to six accounting systems (QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting), the payments and receipts can land on that schedule from the ledger, on the dates they cleared, instead of being retyped from a printout.

For the part formulas handle badly it runs Python inside Excel: sensitivity tables across discount rates and volumes, or one appraisal repeated over a dozen competing projects and ranked, written back as values and charts.

Honest limits: Windows desktop Excel only (Microsoft 365 / 2016+, 64-bit), not Mac and not Excel on the web, and it is paid — the trial is 15 days with 50 AI credits, no card. A generated model deserves the review you would give a junior's: check the signs, check the dates, recompute one rate by hand.

When IRR misleads

Three failure modes, all of which survive review because the cell holds a plausible-looking percentage.

A project can have more than one IRR. Any series with more than one change of sign — an outlay, then returns, then a decommissioning or dilapidations cost at the end — can solve to zero at several rates. Take -40,000, then +120,000, then -84,000: NPV is zero at 11.27% and at 88.73%, both mathematically correct. Excel returns whichever it finds first, so the default guess of 10% yields 11.27% and a guess of 0.8 yields 88.73%. Same cells, same function, two answers, no warning. Count the sign changes before quoting any IRR. (Here NPV at 10% is -331: the project destroys value, which neither rate tells you.)

IRR is blind to scale. The 100,000 project above returns 15.58% and adds 15,672 of NPV at a 10% cost of capital. A 400,000 project returning 14.36% adds 39,943. IRR ranks the small one first; the business is 24,000 better off doing the large one. A percentage cannot see how much money is involved, so it should never be the sole ranking criterion for mutually exclusive projects.

The rate is only as good as the forecast. A 15.58% IRR carries the full uncertainty of five years of estimated receipts, and one optimistic terminal-year figure moves it more than most people expect. Hold the outlay, flex the inflows by ±10%, and quote the range rather than the point (variance analysis in Excel covers structuring that comparison; building a financial model covers keeping assumptions in labelled input cells).

The full decision rule — why NPV wins when the two disagree, and how to set the discount rate you test against — belongs with the function that produces it: see calculating NPV in Excel. Quote them together. IRR is the intuitive number in the room; NPV is the one that is right.

Quick reference

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.