IRR, XIRR and NPV in Excel: The Formulas and Their Traps

Excel’s NPV function does not do what its name suggests. It discounts every value you give it, including the first one, as if each cash flow arrived at the end of a period. Put today’s investment inside the range and the answer is quietly wrong. The fix is to keep the initial outlay outside the function and add it separately.

That one behaviour causes more wrong project appraisals than any other spreadsheet error. The same spreadsheet habits also break IRR and XIRR in predictable ways. Everything below works identically in Google Sheets.

The example used throughout

A business is considering a machine that costs ₹10,00,000 today and is expected to generate ₹3,00,000 at the end of each of the next four years. The required return is 10%.

NPV: the wrong way and the right way

Wrong: =NPV(10%, B2:B6) returns about −44,582.

Excel treats B2 as arriving at the end of year one and discounts it by 10%, then pushes every later flow one year further out than it really is.

Right: =B2 + NPV(10%, B3:B6) returns about −49,040.

The present value of four ₹3,00,000 inflows at 10% is ₹9,50,960. Subtract the ₹10,00,000 paid today and the project destroys ₹49,040 of value at a 10% hurdle rate.

The wrong version is exactly the right answer divided by 1.10: every flow has been discounted one period too many. That means it never flips a positive NPV to negative or the reverse, and at the same rate it will not change which of two projects ranks higher. So the mistake survives review, because the decision still looks sensible.

Where it does damage is in rupees. If the NPV feeds a valuation, a comparison with the price you are being asked to pay, or a sum with figures computed correctly elsewhere, its size is shrunk by a whole period of the discount rate: a positive NPV looks smaller than it is, and a negative NPV looks less bad, as in the example above. On a large project at a 12% hurdle that is a large number to get wrong.

The rule to remember: NPV’s first argument after the rate is the end of period one, not today.

IRR: periods, not dates

=IRR(B2:B6) returns about 7.71%, below the 10% hurdle, consistent with the negative NPV.

IRR has no such off-by-one problem, because it includes the first flow at time zero. Its assumption is different: every cash flow is exactly one period apart. The period is whatever your rows represent.

That matters when the rows are months. A loan or investment of ₹5,00,000 repaid in twelve monthly instalments of ₹45,000 gives =IRR(...) of about 1.20%. That is a monthly rate. It is not 1.2% a year.

• Multiplying by twelve gives 14.45%, the nominal annual rate.
• Compounding, =(1+1.2043%)^12−1, gives 15.45%, the effective annual rate.

Which one you report depends on what you are comparing against, but reporting 1.2% as though it were annual is a mistake that shows up in real proposals more often than it should.

XIRR: when the dates are uneven

Real cash flows rarely arrive on neat anniversaries. SIP instalments skip a holiday, a customer pays three weeks late, a top-up happens mid-year. For those, use =XIRR(values, dates).

XIRR works from the actual dates, measures time from the first date in days, and expresses the result as an annual rate on a 365-day year. It is the right function for personal portfolio returns, which is why XIRR, not CAGR, is the honest measure of a SIP.

Its companion =XNPV(rate, values, dates) does what most people expect NPV to do: the first cash flow is not discounted, and every later flow is discounted by its actual distance from the first date. If your flows have dates, XNPV avoids the NPV off-by-one trap entirely.

The five errors behind most wrong answers

1. All cash flows with the same sign. IRR and XIRR need at least one negative and one positive value. Enter everything as positive and you get an error, not a return.
2. Missing closing value. A portfolio return needs today’s value entered as a final positive flow on today’s date. Without it there is nothing to solve against.
3. Blank cells in an IRR range. A skipped year should be a zero, not an empty cell. IRR ignores blanks, which silently shortens the timeline.
4. Dates stored as text. XIRR needs real dates. A date typed in a format the spreadsheet did not recognise is text, and the function fails or misreads it. Check alignment: real dates usually sit right-aligned.
5. More than one sign change. A project that costs money, earns, then costs money again for decommissioning can have two mathematically valid IRRs. The function returns one of them, depending on the guess. Use NPV at your required rate for these instead of relying on IRR.

When the function returns an error

An #NUM! from IRR or XIRR usually means it could not converge. Check the signs first. If the signs are right, supply a guess close to the answer you expect, for example =XIRR(B2:B20, A2:A20, 0.1). If it still fails, the cash flows may genuinely have no real rate of return, or more than one.

A layout that avoids most of this

• Column A: dates. Column B: amounts, with money you pay out negative and money you receive positive.
• Put the discount rate in its own labelled cell and refer to it, rather than typing 10% inside formulas.
• Compute NPV as =B2+NPV(rate, B3:Bn) or use XNPV, and put a comment on the cell saying which.
• Next to any IRR computed on monthly rows, show the annualised figure explicitly.

Where this fits

• The concepts behind the formulas: payback, IRR and NPV
• Check a result without a spreadsheet: NPV calculator · IRR calculator · XIRR calculator
• Why XIRR is the right measure for a SIP: XIRR or CAGR
• Converting rates: rate converter