NPV vs IRR: which should you use, and the spreadsheet NPV() trap

Net present value (NPV) and internal rate of return (IRR) are the two most widely used tools for deciding whether an investment is worth making. They usually agree — but not always, and when they don't, NPV is the one to trust.

Net present value

NPV is the value an investment adds today: the present value of all its future cash flows, minus what it costs.

NPV = CF₀ + CF₁ ÷ (1 + r) + CF₂ ÷ (1 + r)² + … + CFₙ ÷ (1 + r)ⁿ

Discount at the cost of capital — the return investors could earn elsewhere at similar risk. If NPV is positive, the project earns more than that required return and creates value.

Internal rate of return

IRR is the discount rate that makes NPV exactly zero. It is the project's own break-even rate of return. If IRR exceeds the cost of capital (the hurdle rate), NPV is positive. IRR has no closed-form formula: it is found by trial and error, which is why spreadsheet IRR() sometimes can't find an answer.

When they disagree

• Different sizes. A small project with a 25% IRR may add less value than a large one with a 15% IRR. For mutually exclusive projects, choose the higher NPV.
• Different timing. IRR implicitly assumes interim cash flows are reinvested at the IRR itself, which can flatter short, high-return projects.
• Non-conventional cash flows. If cash flows change sign more than once (for example, a large clean-up cost at the end), there can be several IRRs — or none. NPV still gives one clear answer.

The spreadsheet NPV() trap

The spreadsheet function NPV(rate, values) assumes its first value arrives one period from now. Analysts often include today's investment in the range, which wrongly discounts it too.

For cash flows of -10000, 3000, 4200, 6800 at 10%:

• Correct (investment today, not discounted): 1,307.29 — written as =B1+NPV(10%,B2:B4).
• Wrong (everything inside NPV()): 1,188.44 — written as =NPV(10%,B1:B4).

The difference is the whole result discounted by one extra year. Keep the time-0 cash flow outside NPV(), or build the discount factors yourself.

Which should you use?

Use NPV to decide. Report IRR as well — it's intuitive and widely understood — but check that both point the same way, and fall back on NPV when projects differ in size or timing, or when cash flows change sign more than once.