NPV / IRR
NPV discounts a series of future cash flows back to today's value at a chosen rate; IRR finds the rate that would make that same series' NPV exactly zero.
NPV and IRR answer closely related questions about the same cash-flow timeline, but they structure that timeline differently, which is the single most common source of a wrong answer with either one. NPV's values start one period in the future: if there's an upfront cost today (period zero), it's deliberately left out of the NPV() call itself and subtracted afterward, since NPV assumes the first value handed to it already happens one period from now. IRR, by contrast, wants that period-zero outflow included right inside its own values list, as the first, negative, entry.
Syntax
| Argument | Description |
|---|---|
| rate | NPV: Required. The discount rate per period. |
| value1, ... | NPV: Required. The cash flows, one per period, starting one period from now (NPV does not include a period-zero outflow, subtract that separately). |
| values | IRR: Required. The full cash-flow series, including the initial outflow as a negative number in the first position. |
| guess | IRR: Optional. A starting estimate for the solver. Rarely needed. |
A real example: whether a project is worth it at an 8% discount rate
A $10,000 upfront cost followed by three years of returns, discounted at 8%:
The upfront cost (A2) is added outside the NPV() call, after discounting the three future years inside it; a positive result, as here, means the project clears its hurdle rate and is worth taking on at that discount rate.
Worked examples
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Upfront cost | Year 1 | Year 2 | Year 3 | Discount rate |
| 2 | -10000 | 4000 | 4500 | 5000 | 0.08 |
(the upfront cost is added separately since NPV's own arguments start one period out)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Upfront cost | Year 1 | Year 2 | Year 3 |
| 2 | -10000 | 4000 | 4500 | 5000 |
Common mistakes
- Including the period-zero outflow inside NPV's own argument list. NPV assumes the first value already happens one period out: add period zero separately, after the NPV() call.
- Leaving the period-zero outflow out of IRR's range. IRR needs it included as the first (negative) value, the opposite convention from NPV.
- Comparing an IRR directly against a rate without checking the periods line up (annual IRR against an annual hurdle rate, not a monthly one).
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice NPV / IRR →