=NPV(E2,B3:B5)+B2 discounts the cash flows of years 1 to 3 at the rate in E2 and adds the initial investment in B2, which is not discounted because it happens today. =IRR(B2:B5) returns the discount rate at which that net present value is exactly zero.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | Measure | Value | |
| 2 | 0 | -$10,000 | Rate | 10% | |
| 3 | 1 | $3,000 | NPV | $1,307.29 | |
| 4 | 2 | $4,200 | IRR | 16.34% | |
| 5 | 3 | $6,800 |
At 10% the project is worth 1,307.29 more than it costs, and its IRR is about 16.34%. Change the rate in E2 to 16% and the NPV falls to about 64; at 20% it turns negative. That is the link between the two: IRR is the rate where NPV crosses zero.
NPV syntax: the first cash flow is one period away
=NPV(rate, value1, [value2], ...)
Excel's NPV assumes every value is at the end of a period, starting one period from now. So the first value in the range is discounted once, the second twice, and so on. An investment made today (year 0) must not be in the range: add it after NPV, as the formula above does. The investment is negative because it is money going out.
Putting it inside the range is the most common NPV mistake in Excel, and it does not show an error, just a smaller number:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | Version | NPV at 10% | |
| 2 | 0 | -$10,000 | Rate | 10% | |
| 3 | 1 | $3,000 | Right | $1,307.29 | |
| 4 | 2 | $4,200 | Wrong | $1,188.44 | |
| 5 | 3 | $6,800 |
The wrong version gives 1,188.44, which is the right answer divided by 1.1: every flow, the investment included, has been pushed one year later. If the first cash flow really is at the end of year 1 (you pay for the machine a year from now), then the whole range belongs inside NPV.
How NPV is calculated
NPV divides each cash flow by (1 + rate) raised to its year and adds the results. This sheet does it by hand, so you can see what each year contributes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | Present value | Rate | |
| 2 | 0 | -$10,000.00 | -$10,000.00 | 10% | |
| 3 | 1 | $3,000.00 | $2,727.27 | ||
| 4 | 2 | $4,200.00 | $3,471.07 | ||
| 5 | 3 | $6,800.00 | $5,108.94 | ||
| 6 | Total | $1,307.29 |
The 6,800 of year 3 is worth only 5,108.94 today at 10%. The total in C6 is the same 1,307.29 as NPV gave. Year 0 is divided by (1.1)^0, which is 1, so it stays as it is.
IRR syntax and how to read it
=IRR(values, [guess])
values holds every cash flow in time order, the negative investment first. They must be evenly spaced (every year, or every month). guess is an optional starting point for Excel's search, 10% by default; give one only when IRR returns #NUM!.
A project is worth doing when its IRR is higher than the rate your money costs or could earn elsewhere (the hurdle rate). An IRR of 16.34% against a 10% cost of capital is a yes, which matches the positive NPV.
If the cash flows are monthly, IRR returns a monthly rate. Convert it to a yearly rate with =(1+IRR(B2:B13))^12-1, not by multiplying by 12.
IRR returns #NUM! when all the values have the same sign (there is no investment to earn back) or when it cannot find a rate in 20 tries. A series that changes sign more than once (invest, earn, invest again) can have two valid IRRs; which one Excel returns depends on the guess, which is a reason to trust NPV more in that case.
XNPV and XIRR for real dates
When cash flows do not fall on regular dates, use XNPV and XIRR. They take a date for each value and discount by the exact number of days, on a 365 day year. Unlike NPV, XNPV discounts every value back to the first date and leaves the first value undiscounted, so the investment goes inside the range.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Cash flow | Measure | Value | |
| 2 | 2026-01-15 | -$10,000 | XNPV at 10% | $1,609.73 | |
| 3 | 2026-09-01 | $3,000 | XIRR | 19.08% | |
| 4 | 2027-06-30 | $4,200 | |||
| 5 | 2028-12-31 | $6,800 |
XNPV comes out higher than the yearly NPV because every cash flow arrives earlier than a whole number of years: the first 3,000 after seven and a half months, the last 6,800 two weeks before the end of year 3. Move the last date a year later and both results drop: the same money arriving later is worth less today. XIRR is also the right function for the return on an investment account with deposits on random days.
Try it: NPV and IRR
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | Measure | Value | |
| 2 | 0 | -$24,000 | Rate | 8% | |
| 3 | 1 | $7,000 | NPV | ||
| 4 | 2 | $7,500 | |||
| 5 | 3 | $8,000 | |||
| 6 | 4 | $8,500 |
Your turn: The van costs B2 today and saves the amounts in B3:B6 at the end of years 1 to 4. In E3, calculate the net present value at the rate in E2.
Hint: year 0 stays outside NPV.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | Measure | Value | |
| 2 | 0 | -$50,000 | IRR | ||
| 3 | 1 | $9,000 | |||
| 4 | 2 | $9,500 | |||
| 5 | 3 | $10,000 | |||
| 6 | 4 | $10,500 | |||
| 7 | 5 | $25,000 |
Your turn: In E2, calculate the internal rate of return of the cash flows in B2:B7.
NPV vs IRR: which one to trust
| Question | Use | Why |
|---|---|---|
| Is this project worth it at our cost of capital? | NPV | A positive NPV adds that much value in today's money. |
| What return does this project make? | IRR | One percentage, easy to compare with a hurdle rate. |
| Which of two projects of different size? | NPV | IRR favours small projects: 50% on 1,000 is less money than 20% on 100,000. |
| Cash flows that change sign more than once | NPV | IRR can have two answers or none. |
| Payments on irregular dates | XNPV / XIRR | NPV and IRR assume equal periods. |
For a single growth rate between a start and an end value, with nothing in between, CAGR is simpler than IRR. For loan payments use PMT.
Frequently Asked Questions
How do I calculate NPV in Excel?
Use =NPV(rate, future cash flows) + initial investment, for example =NPV(10%,B3:B5)+B2 with the investment in B2 entered as a negative number. NPV treats its first value as arriving one period from now, so the money spent today must stay outside it.
Why does Excel's NPV give a different answer from my calculator?
Usually because the initial investment was put inside the range: =NPV(10%,B2:B5) discounts the year 0 amount by one year too. Excel's NPV is the present value one period before the first cash flow, not a finance-textbook NPV with a time 0 value.
How do I calculate IRR in Excel?
Put all cash flows, including the negative initial investment, in one range and use =IRR(B2:B5). The flows must be evenly spaced; for real dates use =XIRR(values, dates).
Why does IRR return #NUM! in Excel?
Either every cash flow has the same sign (there is no rate at which they cancel out) or Excel did not find a rate within 20 tries. Check that the investment is negative, then give a guess as the second argument: =IRR(B2:B5,0.1).
What is the difference between NPV and XNPV?
NPV assumes equal periods between cash flows and that the first one comes after one period. XNPV takes a date for each cash flow, discounts by the exact number of days, and discounts everything back to the first date, so the investment goes inside the range.
