Excel NPV & IRR — Discounted Cash Flow and the Rate of Return

TL;DR — NPV discounts a series of future cash flows back to today at a given rate; IRR finds the rate that makes that NPV zero — the project's built-in return. The one mistake that sinks more models than any other: Excel's NPV assumes the first value in the range arrives one period in the future, not today. So a time-zero investment must sit outside the NPV call: =initial_outlay + NPV(rate, future_flows). Put the outlay inside and every answer is discounted one period too many — silently, with no error. For cash flows on real calendar dates, use XNPV and XIRR instead.

=NPV(10%, C2:C6)               ' PV of 5 future cash flows in C2:C6, discounted at 10%
=-50000 + NPV(10%, C2:C6)      ' CORRECT project NPV: t=0 outlay OUTSIDE, future flows inside
=NPV(10%, -50000, C2:C6)       ' WRONG: discounts the t=0 outlay by a year it shouldn't be
=IRR(C1:C6)                    ' the rate where NPV = 0; C1:C6 includes the t=0 outlay
=XNPV(10%, C1:C6, B1:B6)       ' same idea, but with actual dates in B1:B6

Almost every "Excel finance" tutorial teaches NPV by dropping a whole cash-flow column — the initial investment included — inside the function. That single habit is wrong, and it produces a valuation that's off by one full period every time. This page leads with the fix, because a discounted-cash-flow model that's quietly wrong is worse than no model at all.

What you'll learn

Why NPV and IRR exist: irregular cash flows the PMT family can't handle
The off-by-one-period trap — Excel's NPV timing assumption, and the fix
IRR — the rate that zeroes NPV, and why it sometimes returns #NUM!
The multiple-IRR problem and when to reach for MIRR
XNPV and XIRR — discounting on real dates, what analysts actually use

Why NPV and IRR exist

The PMT / FV / PV family assumes a level payment — the same amount every period. Real investments don't behave that way: you spend $50,000 up front, then earn $8,000, $12,000, $18,000, $20,000, $15,000 over five uneven years. NPV and IRR are built for exactly these irregular streams.

The idea is discounting: a dollar next year is worth less than a dollar today, so each future cash flow is divided down by (1 + rate) raised to how many periods away it is. NPV adds up all those discounted values; if the total is positive, the project earns more than your required rate and creates value.

The off-by-one-period trap

Here's the thing every tutorial gets wrong. Excel's NPV does not treat its first value as "today." It assumes value 1 is one period in the future, value 2 two periods out, and so on. That's fine for genuinely future flows — but a project's initial investment happens at time zero, today, and should not be discounted at all.

So the initial outlay belongs outside the NPV, added at full value:

' Cash flows: C1 = -50000 (today), C2:C6 = future years 1-5
=-50000 + NPV(10%, C2:C6)      ' CORRECT -> the true project NPV
=NPV(10%, C1:C6)               ' WRONG -> treats the -50000 as if it happens in year 1

The wrong version discounts every cash flow — the outlay and all the returns — one period too many, which divides the entire NPV by an extra (1 + rate). It doesn't just misplace the outlay; it quietly scales the whole valuation down by about 9% at a 10% rate. And nothing flags it — both formulas return a clean number. The rule to burn in: NPV is for future flows only; the time-zero amount is added on the outside.

IRR — the rate that zeroes NPV

IRR answers the inverse question. Instead of "what's this worth at 10%," it asks "what rate would make this worth exactly zero?" That rate is the internal rate of return — the project's own break-even discount rate, which you compare against your cost of capital.

=IRR(C1:C6)                    ' C1:C6 = -50000, 8000, 12000, 18000, 20000, 15000 -> ~12.5%
=IRR(C1:C6, 10%)               ' the same, with a starting guess to aid convergence

Two things are different from NPV. First, IRR takes the whole cash-flow range including the time-zero value — there's no "outside the function" trick here, because IRR isn't discounting to today, it's solving for a rate across the entire series. Second, IRR is iterative: Excel guesses and refines up to 20 times. If it can't converge it returns #NUM! — usually because the series never changes sign (you need at least one negative and one positive flow) or the default guess is too far off. Supply a guess argument to nudge it.

The multiple-IRR trap

IRR has a subtler failure. When the cash-flow signs flip more than once — say an outflow, inflows, then a big cleanup cost at the end — the equation can have several mathematically valid rates, and Excel returns whichever one its guess lands near. You can get a plausible-looking IRR that's meaningless.

The fix is MIRR, which sidesteps the problem by assuming explicit rates for financing outflows and reinvesting inflows:

=MIRR(C1:C6, 8%, 12%)          ' finance rate 8%, reinvestment rate 12% -> one unambiguous rate

MIRR always returns a single answer, and its reinvestment assumption is more realistic than plain IRR (which implicitly reinvests every inflow at the IRR itself). When cash flows change direction more than once, prefer MIRR.

XNPV and XIRR — discounting on real dates

Plain NPV and IRR assume every cash flow is exactly one period apart. Real cash flows land on actual dates — a payment on March 3, another on November 20, unevenly spaced. XNPV and XIRR take an explicit dates column and discount by the real number of days (actual/365):

=XNPV(10%, C1:C6, B1:B6)       ' values in C, their dates in B -> date-accurate NPV
=XIRR(C1:C6, B1:B6)            ' date-accurate IRR
=XIRR(C1:C6, B1:B6, 15%)       ' with a guess if it won't converge

Two conveniences make these the analyst's default. First, XNPV uses the first date as today, so — unlike NPV — you include the time-zero outlay right in the range and the off-by-one trap disappears. Second, they handle irregular timing that annual NPV/IRR simply can't represent. If your cash flows have real dates attached, reach for XNPV/XIRR first.

How ExcelMaster helps

Discounted-cash-flow models fail in quiet, specific ways: the initial outlay buried inside NPV, an IRR that converged to the wrong root, annual functions used on dated cash flows. Tell ExcelMaster "value this project at a 10% discount rate," and it puts the time-zero investment outside the NPV, wires IRR across the full series, and — if your flows carry dates — switches you to XNPV/XIRR so the timing is exact. Paste an IRR returning #NUM! and it checks that the series changes sign and adds a guess.

Frequently asked questions

Why is my Excel NPV wrong?

Almost always the off-by-one-period trap: Excel's NPV assumes the first value happens one period in the future, so if you include the time-zero investment inside the function it gets discounted a period it shouldn't. Put the initial outlay outside: =-50000 + NPV(rate, future_flows). Or use XNPV, which treats the first date as today.

What is the difference between NPV and IRR?

NPV gives you a dollar value — the net present value of the cash flows at a rate you choose. IRR gives you a rate — the discount rate at which NPV would equal zero. Use NPV to decide if a project beats your required return; use IRR to express the project's return as a single percentage.

Why does IRR return #NUM!?

IRR is iterative and gave up after 20 tries. The two common causes are cash flows that never change sign (you need at least one negative and one positive value) and a starting point too far from the answer. Add a guess argument, e.g. =IRR(range, 10%), and confirm the series actually has both an outflow and an inflow.

When should I use XNPV and XIRR instead?

Whenever your cash flows fall on actual dates rather than neat, equal periods. XNPV and XIRR take a dates column and discount by the real number of days, and XNPV treats the first date as today — so you include the initial investment in the range and avoid the off-by-one trap entirely. They're the standard for real financial models.

What is the difference between IRR and MIRR?

IRR can return several valid answers when the cash-flow signs change more than once, and it assumes inflows are reinvested at the IRR itself. MIRR fixes both: you supply an explicit finance rate and reinvestment rate, and it always returns a single, more realistic rate. Prefer MIRR when cash flows change direction more than once.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-23.