NPV and IRR
NPV(rate, value1, [value2, ...]) discounts a series of cash flows to today's value — but it assumes the FIRST value happens at the end of period 1, not at time zero, unlike textbook NPV. An initial investment at t=0 therefore belongs outside the brackets rather than inside the range: =NPV(rate,B3:B10)+B2, where B2 holds the upfront cost as a negative number. IRR(values,[guess]) asks the mirror question — the rate at which that same series breaks even — and reverses the rule: the t=0 investment belongs INSIDE its range, because IRR reads the whole series in order rather than assuming its first value lands a period from now. For cash flows on real, irregular dates rather than evenly spaced periods, XNPV and XIRR take an explicit dates array instead.
The formula
=NPV(B1,B3:B7)+B2 correct: B2 (initial investment) excluded from the range, added outside
=NPV(B1,B2:B7) WRONG: puts B2 inside the range, so it gets discounted a period too early
=IRR(B2:B7) correct for IRR: the initial investment (negative) stays INSIDE the range
=IRR(B2:B7,0.1) same, with an explicit starting guess if IRR returns #NUM!
=XNPV(B1,B2:B7,C2:C7) irregular real dates in C2:C7 instead of assumed equal periods
=XIRR(B2:B7,C2:C7) same idea for the rate; needs a matching date for every cash flowA worked example
B1 holds an 8% discount rate, B2 holds the initial investment of -50,000 made today, and B3:B7 hold the next five years of cash flow, one year per cell: 15,000, 18,000, 20,000, 17,000, 12,000.
=NPV(B1,B3:B7)+B2
15,860.14. B3 (15,000) is discounted as though it arrives at the end of year 1, not year 0, which is why B2 sits outside the NPV range instead of inside it: =NPV(B1,B2:B7) would discount the -50,000 too, as though the investment itself happened a year from now, and returns 14,685.31 instead — a different, still-plausible number, with no error to flag the mistake.
Which one do I need?
| If you want to… | Use |
|---|---|
| You want the present value of a future cash flow series at a fixed discount rate | =NPV(rate, value1, [value2, ...]) — keep any t=0 investment OUT of the range and add it after: =NPV(rate,B3:B10)+B2 |
| You want the rate of return that makes NPV zero for those same cash flows | =IRR(values, [guess]) — unlike NPV, the initial investment stays INSIDE the range as the first, negative value |
| IRR returns #NUM! instead of a rate | Check that values contains at least one negative and one positive number, and try a different starting guess: =IRR(values, 0.2) |
| Cash flows land on real calendar dates, not evenly spaced periods | =XNPV(rate, values, dates) and =XIRR(values, dates) — both take an explicit dates array; see the XIRR reference for the date-based variant |
| You want a single annualized growth rate between a start and end value, not a multi-year cash flow model | CAGR answers a different question — no year-by-year cash flows needed |
Frequently asked questions
Why does putting the initial investment inside NPV's range give the wrong answer?
NPV discounts the first value in its range as if it lands at the end of period 1, never at time zero — so a t=0 investment placed inside the range gets discounted by a period it never actually experienced. With B2 = -50,000 and B3:B7 as five years of income, =NPV(B1,B3:B7)+B2 returns 15,860.14; putting the investment inside the range instead, =NPV(B1,B2:B7), returns 14,685.31 — a different, still-plausible number, with no error to signal the mistake.
Why does IRR return a #NUM! error?
Two common causes: the values array has no sign change — every cash flow is positive or every one is negative, so there's no rate that zeroes NPV — or the iterative solver couldn't converge within 20 tries starting from the default 10% guess. Add the missing negative initial investment, or supply an explicit starting guess: =IRR(values, 0.2).
Does NPV work the same way in Google Sheets?
Yes — Google Sheets' NPV(discount, cashflow1, [cashflow2, ...]) uses the identical period-1 convention as Excel: the first value is assumed to occur one period from now, not today. The same fix applies — keep a t=0 investment out of the range and add it to the result instead.
What's the difference between NPV/IRR and XNPV/XIRR?
NPV and IRR assume cash flows land at even, regular intervals — one per period, in order. XNPV and XIRR take an extra argument, an array of actual calendar dates matched to each cash flow, so they stay correct when payments happen on irregular real-world dates instead of a clean once-a-year schedule.
New guides and tools, once a month
DE + EN · double opt-in · no spam