FORMULAS & FUNCTIONS
NPV and IRR in Excel (Plus XNPV, XIRR and Payback Period)
Should you buy the new van, the machine or the software? NPV and IRR answer that by putting future cash in today’s money. Excel has a function for each, but NPV has a trap that gives the wrong answer in most spreadsheets. This guide walks through one small-business investment: NPV done right, a manual check, IRR, payback and discounted payback, and XNPV and XIRR for real dates, with a free calculator.
NPV, IRR and payback in one minute
| Measure | Question it answers | Rule of thumb |
|---|---|---|
| NPV (net present value) | How much value does the project add, in today’s money, after earning the hurdle rate? | Accept if NPV > 0 |
| IRR (internal rate of return) | What return does the project earn? | Accept if IRR > hurdle rate |
| Payback period | How many years until the cash comes back? | Shorter is safer; ignores the time value of money |
| Discounted payback | How long until the discounted cash comes back? | Must be within the project’s life |
The hurdle rate (or discount rate) is the return you need to justify the risk: your cost of borrowing or the return you could get elsewhere. This example uses 10%.
Step 1: Lay out the cash flows by year
Put the years in a row (0 to 5) and the cash flows underneath. Year 0 is today: the 50,000.00 van, entered as a negative number. Years 1 to 5 are the extra cash the van brings in after running costs and tax, and year 5 includes what you expect to sell it for. Use cash, not profit: depreciation is not a cash flow, so leave it out (it only matters through its effect on tax).
Step 2: Calculate NPV
=NPV(C3,C6:G6)+B6Excel’s NPV function assumes the first value in the range is one period away. That is right for years 1 to 5, so they go inside the function. The year-0 investment happens today and is already in today’s money, so it is added outside, undiscounted.
Step 3: Check it, and avoid the common mistake
Rows 8 to 10 build the same answer by hand. The discount factor for each year is:
=1/(1+$C$3)^B5Each cash flow times its factor gives the discounted cash flow, and their sum in C13 is 2,066.24, the same as the NPV function. Now the mistake almost everyone makes:
| Formula | Result | Why |
|---|---|---|
=NPV(C3,C6:G6)+B6 | 2,066.24 | Correct: year 0 is not discounted |
=NPV(C3,B6:G6) | 1,878.40 | Wrong: every cash flow, including the investment, is pushed one year later |
The wrong version is exactly the right answer divided by 1.1. The gap grows with the size of the investment and the rate, and it can turn a good project into a bad one.
Step 4: Find the IRR
=IRR(B6:G6)Unlike NPV, IRR takes the whole range, year 0 included: it needs at least one negative and one positive cash flow. IRR is the discount rate at which NPV is exactly zero, which C16 proves by recalculating NPV at the IRR.
=IRR(B6:G6,0.1). If the cash flows change sign more than once, there can be two IRRs; trust NPV instead. Avoid hiding the error with IFERROR (see IFERROR vs IFNA).Step 5: Payback and discounted payback
Row 7 is the cumulative cash flow, =SUM($B$6:B6) filled right. It stays negative for three years (-9,000.00 after year 3) and turns positive in year 4. Payback is the full years plus the fraction of the next year needed to cover the shortfall:
=IF(G7<0,"Not recovered",COUNTIF(C7:G7,"<0")+(-INDEX(B7:G7,COUNTIF(C7:G7,"<0")+1))/INDEX(B6:G6,COUNTIF(C7:G7,"<0")+2))COUNTIF(C7:G7,"<0")counts the years still negative: 3.INDEX(B7:G7,3+1)is the cumulative shortfall after year 3: 9,000.00.INDEX(B6:G6,3+2)is year 4’s cash flow: 15,000.00.- So payback = 3 + 9,000 / 15,000 = 3.60 years. The IF returns Not recovered if the project never pays back (see how to build an IF formula).
The same formula on the discounted rows 9 and 10 gives the discounted payback: 4.74 years. It is longer because later cash is worth less today.
Step 6: XNPV and XIRR for real dates
NPV and IRR assume equal periods. Real projects rarely work that neatly: on the Dated sheet the van is bought on Jan 15, 2026 and the yearly cash arrives each March 31, starting in 2027. Use the date-based versions:
=XNPV(Project!C3,B5:B10,A5:A10)=XIRR(B5:B10,A5:A10)Note the difference in how the range works: XNPV discounts everything to the first date, so the investment stays inside the range and is not discounted. There is no need to add it separately.
| Equal years | Actual dates | |
|---|---|---|
| Net present value | 2,066.24 (NPV) | 1,045.94 (XNPV) |
| Rate of return | 11.57% (IRR) | 10.75% (XIRR) |
The first inflow now arrives after 14.5 months instead of 12, and every later one is 2.5 months later too, so both results drop. For monthly or irregular cash flows, XNPV and XIRR are the honest numbers.
Where the cash flows come from
The calculator is only as good as its inputs. Build the yearly cash flows from a forecast (see the 12-month cash flow forecast), include the resale value, and use after-tax cash. If the purchase is financed, keep the loan out of the project cash flows and compare the IRR with the loan rate instead; the loan amortization schedule gives you the payments. For the tax effect of the asset, use the depreciation schedule, and to see how much you must sell each year to cover fixed costs, use break-even analysis.
Frequently asked questions
Why is my Excel NPV different from my calculator?
Most likely the year-0 investment is inside the NPV function, so it is discounted by one period. Use =NPV(rate,year1:yearN)+year0.
What is the difference between NPV and XNPV?
NPV assumes equal periods and discounts the first value by one period. XNPV uses actual dates, discounts to the first date and does not discount the first value.
What is the difference between IRR and XIRR?
IRR assumes equal periods; XIRR uses the actual date of each cash flow. Use XIRR when cash flows are monthly, irregular or do not start exactly one period after the investment.
Which is better, NPV or IRR?
NPV, when you compare projects of different sizes or the cash flows change sign more than once. IRR is easy to explain as a percentage. Use both and check that they agree with your hurdle rate.
How do I calculate payback period in Excel?
Add a cumulative cash flow row. Payback = years with a negative cumulative total + the remaining shortfall divided by the next year’s cash flow, as in Step 5.
Final checklist
- Year 0 is negative and is added outside NPV().
- Cash flows are after-tax cash, including resale value, without financing or depreciation.
- The manual check (sum of discounted cash flows) equals NPV.
- IRR and NPV give the same decision against the hurdle rate.
- Irregular dates use XNPV and XIRR.
- Payback is within the project’s life.
Make better investment decisions
Browse the ready-to-use Excel templates for more finance tools, visit the OneXcel Tutorial Hub for step-by-step guides, or present the numbers in the OneXcel Dashboard Builder.