- What NPV and IRR tell you
- The NPV function: syntax and rules
- The catch: NPV assumes your first cash flow comes a period later
- Worked NPV examples from Microsoft
- How to work out NPV and IRR in Excel
- The IRR function: syntax, guesses and #NUM!
- Reading IRR against your hurdle rate
- NPV vs PV, and when to use XNPV or XIRR
- My verdict on NPV and IRR
- Frequently asked questions
To use NPV in Excel, type =NPV(rate, values) to discount a series of future cash flows back to today, and to use IRR, type =IRR(values) to find the rate at which those cash flows have a net present value of zero. The one thing to watch: if your investment cost lands at the start, add it outside the NPV function, not inside it.
Get that timing right and both functions are straightforward. Here's how each one works, with Microsoft's own worked examples, and how to read the answer once you've got it.
What NPV and IRR tell you
CFI defines net present value as the value of all future cash flows, positive and negative, over the life of an investment, discounted to the present. Cash flows are discounted for two reasons: to adjust for risk, and to reflect that cash arriving later is worth less than cash today. CFI's example: an investment returning $10,000 a year for 10 years, at a 10% discount rate, is worth $61,446 today (CFI's US-dollar example, as of October 2026).
The internal rate of return is the discount rate that makes NPV zero. CFI calls it the expected compound annual rate of return. Microsoft ties the two together: IRR is the rate at which NPV equals zero.
The NPV function: syntax and rules
Microsoft's syntax is NPV(rate, value1, value2, ...). The rules that matter:
- Rate is the discount rate over one period.
- Values can be 1 to 254 arguments: payments out as negative numbers, income as positive ones.
- The values must be equally spaced in time and occur at the end of each period.
- NPV reads the order of the values as the order of the cash flows, so enter them in date order.
- Empty cells, logical values, text and error values are ignored.
The catch: NPV assumes your first cash flow comes a period later
This is the mistake that skews NPV sheets. Microsoft says the NPV investment begins one period before the date of the value1 cash flow. So Excel treats your first value as arriving at the end of period one and discounts it. If your first cash flow happens at the beginning of the first period, which is usually true of the upfront cost, Microsoft says it must be added to the NPV result, not included in the values.
Worked NPV examples from Microsoft
Microsoft gives two examples that show both cases. Its figures are US-dollar examples, as of October 2026.
| Example (Microsoft) | Cash flows | Formula | Result |
|---|---|---|---|
| 1: cost one year from today | Rate 0.1; cost -10,000 at the end of year one; returns 3,000, 4,200 and 6,800 | =NPV(A2, A3, A4, A5, A6) | $1,188.44 |
| 2: cost at the start | Rate 0.08; cost -40,000 now; returns 8,000, 9,200, 10,000, 12,000 and 14,500 | =NPV(A2, A4:A8)+A3 | $1,922.06 |
| 2 with a loss in year six | As above, plus -9000 in year six | =NPV(A2, A4:A8, -9000)+A3 | ($3,749.47) |
In example 1 the cost comes a year from today, so it belongs inside the function. In example 2 the cost is paid now, so it sits outside, added after: that's the +A3. Same function, different timing, and the formula changes to match.
How to work out NPV and IRR in Excel
- Enter the cash flows in order, with costs as negative numbers and income as positive ones.
- If the cost is paid at the start, keep it out of the NPV values and add it afterwards.
- Use =NPV(rate, later flows)+initial cost.
- Use =IRR(all flows), including the initial cost, to get the rate that makes NPV zero.
- Compare the IRR with your hurdle rate.
The IRR function: syntax, guesses and #NUM!
Microsoft's syntax is IRR(values, guess). The cash flows don't have to be even, but they must come at regular intervals, such as monthly or yearly, and the values must include at least one negative and one positive number. Excel works out IRR by trial, starting from your guess, or 10% if you leave it out, until the result is accurate within 0.00001%. If it can't find an answer after 20 tries, it returns #NUM!, and Microsoft suggests trying a different guess.
Microsoft's IRR example is a business that costs $70,000 to start (shown as -$70,000), with net income of $12,000, $15,000, $18,000, $21,000 and $26,000 over five years:
| Formula (Microsoft) | Covers | Result |
|---|---|---|
| =IRR(A2:A6) | The cost and four years of income | -2.1% |
| =IRR(A2:A7) | The cost and five years of income | 8.7% |
| =IRR(A2:A4,-10%) | The cost and two years, with a guess of -10% | -44.4% |
Microsoft also shows the check: NPV(IRR(A2:A7), A2:A7) comes out at 1.79E-09, which is effectively zero. That's the definition of IRR working exactly as it should.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Reading IRR against your hurdle rate
An IRR on its own doesn't tell you whether to go ahead. CFI says it's compared with the company's hurdle rate or cost of capital, and WACC is a common choice for that rate. If the IRR is at or above it, the project would be accepted; if it's lower, rejected.
CFI's example: equipment costing $500,000 is expected to generate $160,000 of extra profit a year for four years, then sell for $50,000 of salvage value in year five. Excel's =IRR() gives 13%, which beats both the company's 8% hurdle rate and a 10% alternative investment, so CFI says the company should buy the equipment.
NPV vs PV, and when to use XNPV or XIRR
Microsoft says NPV is similar to the PV function. The difference is that PV allows cash flows to start at either the end or the beginning of a period and needs them to be constant, while NPV lets each cash flow be different. If your cash flows don't fall at regular intervals, CFI points to XNPV, which works with specific dates, and to XIRR for the rate. CFI also lists Goal Seek as a way to find IRR by trying discount rates until NPV is zero.
My verdict on NPV and IRR
The one mistake is the timing of the first cash flow. Fix that and the functions are easy. Put the upfront cost outside NPV, inside IRR, and always check your IRR against a hurdle rate before you get excited about it. If you're comfortable with compound interest in Excel, you already understand the idea underneath both: cash today and cash later aren't worth the same.
Frequently asked questions
What is the NPV formula in Excel?
=NPV(rate, value1, value2, ...). If your first cash flow is at the start of the first period, Microsoft says to add it outside the function, as in =NPV(A2, A4:A8)+A3.
Why is my Excel NPV wrong?
The usual reason is timing. Microsoft says NPV assumes the investment begins one period before the first value, so an upfront cost inside the values gets discounted when it shouldn't be.
What is the IRR formula in Excel?
=IRR(values, guess). The values need at least one negative and one positive cash flow at regular intervals. The guess is optional and defaults to 10%.
Why does IRR return #NUM!?
Microsoft says Excel returns #NUM! if it can't find a result after 20 tries. Try again with a different guess.
How do I know if an IRR is good?
Compare it with your hurdle rate or cost of capital. CFI says a project is accepted if its IRR is at or above that rate and rejected if it's lower.
