NPV and IRR in Excel: net present value and internal rate of return
Is a $50,000 machine worth buying if it saves money over the next five years? Net present value turns every future cash flow into today's money, and the internal rate of return tells you what yearly return the project earns.
In short: NPV is =NPV(rate,cash flows from year 1)+initial investment, because Excel's NPV discounts the very first value it gets. IRR is =IRR(all cash flows from year 0). For cash flows on irregular dates, use XNPV and XIRR with a column of dates.
Every figure on this page is a made-up practice example, not investment advice.
Try it now: NPV of a machine purchase
The machine costs $50,000 today and brings in cash for five years. Work out what the project is worth in today's money at an 8% discount rate.
Task A new packaging machine costs $50,000 today (year 0) and returns the cash flows in B3:B7 over the next five years. In E3, use NPV to calculate the net present value at the discount rate in E1.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow $ | Discount rate | 0.08 | |
| 2 | 0 | -50000 | |||
| 3 | 1 | 12000 | NPV $ | ||
| 4 | 2 | 14000 | |||
| 5 | 3 | 15000 | |||
| 6 | 4 | 13000 | |||
| 7 | 5 | 11000 |
How the NPV function works
NPV divides each value by (1+rate) raised to its position: the first by 1.08, the second by 1.08², and so on. It assumes the cash flows are evenly spaced and that each one arrives at the end of its period.
| Argument | Meaning | In the example |
|---|---|---|
| rate | Discount rate per period: the return you require, or what the money costs you | E1 = 8% |
| value1, value2, … | Cash flows at the end of periods 1, 2, 3 and so on, as a range or one by one. Money in is positive, money out negative. | B3:B7 |
| + investment | The payment at the start (year 0) stays outside NPV and is added as it is. Microsoft's own help article sets it up the same way. | +B2 = -50,000 |
=NPV(E1,B3:B7)+B2 returns $2,063.14. This is the classic trap. Finance textbooks and calculators usually include the year 0 cash flow in their NPV, so people put it into the range, and Excel then discounts it by a full year.
NPV decision rule
- NPV above zero: the project earns more than the discount rate, so on these numbers it adds value.
- NPV of zero: it earns exactly the discount rate.
- NPV below zero: the money would do better elsewhere at that rate.
The answer is only as good as the inputs. The cash flows are forecasts, and a different discount rate can flip the sign. Try a few rates side by side before you trust a single number. In most companies, finance sets the rate to use, often based on the cost of capital.
IRR: the rate where NPV hits zero
The internal rate of return is the discount rate that makes NPV exactly zero. Think of it as the yearly return the project earns on the money tied up in it. The syntax is =IRR(values,[guess]). This time the initial investment goes inside the range, and the range needs at least one negative and one positive number. The rule: if the IRR is higher than your required rate, the NPV at that rate is positive.
Practice: IRR of a warehouse upgrade
Task A warehouse upgrade costs $120,000 and saves money for the next six years, see B3:B8. In E2, use IRR to calculate the project's internal rate of return.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow $ | Discount rate | 0.06 | |
| 2 | 0 | -120000 | IRR | ||
| 3 | 1 | 18000 | |||
| 4 | 2 | 26000 | |||
| 5 | 3 | 31000 | |||
| 6 | 4 | 30000 | |||
| 7 | 5 | 28000 | |||
| 8 | 6 | 24000 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
IRR does not solve an equation directly. It tries rates until the result is accurate to 0.00001 percent, starting from the guess (10% if you leave it out). If nothing works after 20 attempts, you get #NUM!, and Microsoft suggests trying a different guess.
XNPV and XIRR for irregular dates
NPV and IRR only know positions, not dates. When cash arrives on arbitrary days, switch to the X versions and add a column of real dates:
| A: Date | B: Cash flow $ |
|---|---|
| 2/1/2026 | -20,000 |
| 7/15/2026 | 6,000 |
| 12/31/2026 | 8,000 |
| 9/30/2027 | 10,000 |
=XIRR(B2:B5,A2:A5) returns about 18.25% per year and =XNPV(8%,B2:B5,A2:A5) about $2,054.13. The first date is the starting point, and every later cash flow is discounted by the exact number of days on a 365-day year. That is why the investment belongs inside XNPV: it sits on the first date and is not discounted at all. A date earlier than the first one returns #NUM!.
Common NPV and IRR mistakes
Putting year 0 inside NPV
=NPV(E1,B2:B7) discounts today's payment too and gives $1,910.32 instead of $2,063.14 in the first example. The higher the rate, the bigger the gap.
Blank cells for years with no cash
NPV skips blank cells and text. If one year is empty, every later cash flow moves up a position and is discounted too little. Type a 0 for a year without cash.
Annual rate with monthly cash flows
Monthly cash flows need a monthly rate in NPV. IRR on monthly data returns a monthly rate, which you annualize with =(1+IRR(B2:B37))^12-1.
More than one sign change
If the cash flows switch between negative and positive more than once, for example because of clean-up costs at the end, there can be several IRRs. The series -100, 230, -132 has two: 10% and 20%. Which one Excel shows depends on the guess: 10% without one, 20% with =IRR(B1:B3,25%). In that situation NPV is the more reliable measure. MIRR, the modified IRR with separate finance and reinvestment rates, is another way out.
NPV and IRR in German Excel
| English | German |
|---|---|
=NPV(E1,B3:B7)+B2 | =NBW(E1;B3:B7)+B2 |
| IRR | IKV |
| XNPV | XKAPITALWERT |
| XIRR | XINTZINSFUSS |
| MIRR | QIKV |
German Excel separates arguments with semicolons.
Which Excel versions have NPV, IRR, XNPV and XIRR?
Microsoft documents NPV, IRR and XNPV for Microsoft 365 and Excel 2016, 2019, 2021 and 2024, on Windows and Mac. For XIRR, the current help page lists Microsoft 365, Excel 2021 and Excel 2024 on Windows and Mac.
FAQ
What is the difference between NPV and PV in Excel?
PV gives the present value of equal, regular payments. NPV handles a series of different amounts. Neither subtracts the initial investment for you, so you add it yourself.
Why does IRR show 0.08 instead of 8%?
It is the same number. Format the cell as a percentage and it shows 8%.
Why does IRR return #NUM!?
Either the range has no negative or no positive value, or Excel could not find a rate in 20 tries. Check the signs and give a guess as the second argument.
Should I rank projects by NPV or IRR?
For projects of different size the two can disagree. A small project can show a high IRR and still create less value than a larger one. When choosing between projects, NPV is usually the safer guide.
How do I calculate a mortgage payment or amortization schedule?
That is covered with PMT, FV and PV in CAGR, interest and loan payments.