Why doesn’t Excel’s NPV function give the same answer?

Excel’s NPV(rate, value1, value2, …) assumes the first cash flow happens at the end of period 1, so it is discounted too. An initial investment made today shouldn’t be discounted, so the correct formula is =initial investment + NPV(rate, period1:periodN). The main result here uses that convention, and the value from passing every cash flow to Excel’s NPV is listed as well; the two differ by a factor of (1 + r).

What’s the difference between IRR and XIRR?

IRR assumes equally spaced cash flows (one per year or per month) and returns the rate per period; an IRR from monthly flows is a monthly rate, annualised as (1 + IRR)¹² − 1. XIRR uses each cash flow’s actual date, measuring time as (date − first date)/365, and returns an annual rate directly — right for irregular investments such as periodic fund purchases or staggered buys and sells. This XIRR uses the same 365-day convention as Excel and gives the same result.

Why can there be several IRRs, and which one should I use?

The IRR is the discount rate that makes NPV zero, which means finding a root of a polynomial. If the cash flows change sign only once (invest, then receive) there’s at most one positive IRR. With a second investment midway or a large cost at the end, the sign changes several times and there can be two or more IRRs — −100, 230, −132 has IRRs of both 10% and 20%. IRR can’t rank such a project; look at the NPV at your discount rate, or at MIRR with an explicit reinvestment rate.

How are payback and discounted payback calculated?

The payback period is how long it takes the cumulative cash flow to turn non-negative: find the period where it turns positive and add the fraction “amount still unrecovered at the start ÷ that period’s cash flow”. Discounted payback does the same with each cash flow discounted to present value, so it accounts for the time value of money and is never shorter. If the cumulative total is still negative at the end, it shows “never paid back”. Payback ignores everything after break-even, so use it alongside NPV, not instead of it.

💹

💹IRR & NPV Calculator - XIRR, MIRR, Payback Period

Net present value and internal rate of return are the standard ways to judge a project, an investment or a savings product. In periodic mode, enter one cash flow per period — outflows negative, inflows positive, period 0 being the initial investment — and get the NPV (period 0 undiscounted, the same as Excel’s =B2+NPV(r, B3:Bn), with the Excel NPV-of-everything value alongside), the IRR (Newton’s method with a sign-change scan and bisection as a fallback), MIRR with separate finance and reinvestment rates, the payback and discounted payback periods (interpolated within the period) and the profitability index. Dated mode handles irregular cash flows such as regular fund purchases or staged payments: enter dates and amounts to get XNPV and XIRR using actual days / 365, matching Excel’s XNPV and XIRR. When the cash flows change sign more than once — extra investment midway or a decommissioning cost at the end — NPV = 0 can have several solutions, so the tool scans from −99.99% to 1000% to find every IRR and warns that IRR is unreliable in that case. The NPV profile shows net present value against the discount rate with all IRRs and your rate marked, and the detail table lists each period’s discount factor, present value, cumulative cash flow and cumulative PV, with the equivalent Excel formulas for checking.

Free · No sign-up · Runs in your browserLast updated: 2026-10-26

Loading tool...

How to use IRR & NPV Calculator

1Choose periodic or dated, and enter the cash flows row by row (outflows negative) or paste them in bulk
2Set the discount rate and, if needed, the MIRR finance and reinvestment rates and an IRR guess
3Read the NPV, IRR, MIRR, XIRR, payback periods and PI, with the NPV profile and detail table

FAQ

Why doesn’t Excel’s NPV function give the same answer?: Excel’s NPV(rate, value1, value2, …) assumes the first cash flow happens at the end of period 1, so it is discounted too. An initial investment made today shouldn’t be discounted, so the correct formula is =initial investment + NPV(rate, period1:periodN). The main result here uses that convention, and the value from passing every cash flow to Excel’s NPV is listed as well; the two differ by a factor of (1 + r).
What’s the difference between IRR and XIRR?: IRR assumes equally spaced cash flows (one per year or per month) and returns the rate per period; an IRR from monthly flows is a monthly rate, annualised as (1 + IRR)¹² − 1. XIRR uses each cash flow’s actual date, measuring time as (date − first date)/365, and returns an annual rate directly — right for irregular investments such as periodic fund purchases or staggered buys and sells. This XIRR uses the same 365-day convention as Excel and gives the same result.
Why can there be several IRRs, and which one should I use?: The IRR is the discount rate that makes NPV zero, which means finding a root of a polynomial. If the cash flows change sign only once (invest, then receive) there’s at most one positive IRR. With a second investment midway or a large cost at the end, the sign changes several times and there can be two or more IRRs — −100, 230, −132 has IRRs of both 10% and 20%. IRR can’t rank such a project; look at the NPV at your discount rate, or at MIRR with an explicit reinvestment rate.
How are payback and discounted payback calculated?: The payback period is how long it takes the cumulative cash flow to turn non-negative: find the period where it turns positive and add the fraction “amount still unrecovered at the start ÷ that period’s cash flow”. Discounted payback does the same with each cash flow discounted to present value, so it accounts for the time value of money and is never shorter. If the cumulative total is still negative at the end, it shows “never paid back”. Payback ignores everything after break-even, so use it alongside NPV, not instead of it.