These three metrics answer the same underlying question, whether an investment is worth making, but they answer it in different currencies: time, percentage return, and dollar value created. Used together they cover each other's blind spots. Used alone, each one can quietly point you toward the wrong decision. This guide works through one consistent example across all three, so you can see exactly where they agree, where they conflict, and which one to trust when they do.
On this page
The Example Used Throughout This Guide
A company is considering a $100,000 investment in new equipment, expected to generate the following cash inflows over five years. The company's required rate of return, its discount rate, is 10%.
Payback Period
How long until you get your money back
Payback period answers a simple question: how many years until the cumulative cash inflows equal the initial investment. It says nothing about what happens afterward and nothing about the time value of money.
Payback Period = Years before full recovery + (Remaining amount to recover / Cash flow in the recovery year)
| Year | Cash flow | Cumulative cash flow |
|---|---|---|
| 0 | -$100,000 | -$100,000 |
| 1 | $30,000 | -$70,000 |
| 2 | $35,000 | -$35,000 |
| 3 | $28,000 | -$7,000 |
| 4 | $32,000 | $25,000 |
| 5 | $40,000 | $65,000 |
The cumulative balance crosses zero partway through year 4. With $7,000 still to recover and $32,000 of cash flow arriving that year:
Payback Period = 3 + (7,000 / 32,000) = 3 + 0.22 = about 3.2 years ' Roughly 3 years and 3 months
Net Present Value (NPV)
How much value the project creates in today's dollars
NPV discounts every future cash flow back to its present value using the required rate of return, then subtracts the initial investment. A dollar received in year 5 is worth less than a dollar today, and NPV is the metric that actually accounts for that.
NPV = -Initial Investment + Σ [ Cash Flow(t) / (1 + r)^t ] where r = discount rate, t = year number
| Year | Cash flow | Discount factor at 10% | Present value |
|---|---|---|---|
| 1 | $30,000 | 0.9091 | $27,273 |
| 2 | $35,000 | 0.8264 | $28,926 |
| 3 | $28,000 | 0.7513 | $21,037 |
| 4 | $32,000 | 0.6830 | $21,856 |
| 5 | $40,000 | 0.6209 | $24,837 |
| Total present value of inflows | $123,929 | ||
NPV = $123,929 - $100,000 = $23,929
An NPV of about $23,929 means this project is expected to create roughly $23,929 of value above and beyond the 10% return already required. Since NPV is positive, the project clears the bar.
Excel's NPV() function discounts every value you feed it, including the first one. Do not include the initial investment inside the function. The correct formula is:
=NPV(10%, Year1_CF:Year5_CF) - Initial_Investment ' NOT: =NPV(10%, Initial_Investment, Year1_CF:Year5_CF)
Including the initial investment as the first argument discounts it by one period, which understates its true cost and inflates NPV.
Internal Rate of Return (IRR)
The break-even discount rate
IRR is the discount rate at which NPV equals exactly zero. There is no direct algebraic formula for uneven cash flows, so it is solved by trial and error, or by a spreadsheet function that does the trial and error automatically.
Find r such that: -Initial Investment + Σ [ Cash Flow(t) / (1 + r)^t ] = 0
Testing the same cash flows at increasing discount rates narrows in on the answer. At 10% the NPV is a strongly positive $23,929. At 20% it turns negative. The break-even point falls between 18% and 19%, at approximately:
IRR ≈ 18.7%
Since the project's IRR of about 18.7% exceeds the required 10% rate of return, the project clears the bar on this metric as well.
Comparing All Three Side by Side
| Metric | Result | Decision rule | Main weakness |
|---|---|---|---|
| Payback period | About 3.2 years | Accept if within the company's maximum acceptable payback window | Ignores time value of money and ignores all cash flows after payback |
| NPV | $23,929 | Accept if greater than zero | Expressed in dollars, which makes comparing projects of very different sizes less intuitive |
| IRR | About 18.7% | Accept if greater than the required rate of return | Assumes interim cash flows are reinvested at the IRR itself, which can be unrealistic |
When NPV and IRR Disagree
For a single, independent project, NPV and IRR almost always agree on whether to accept it. The conflict shows up when ranking mutually exclusive projects of different sizes or cash flow timing. A smaller project can show a higher IRR while a larger project shows a higher NPV, because IRR is a percentage and NPV is a dollar amount, and percentages do not account for scale.
The reinvestment assumption is the deeper issue: NPV assumes cash generated along the way is reinvested at the discount rate, which is usually close to the company's actual cost of capital. IRR assumes that same cash is reinvested at the IRR itself, which for a high-IRR project is an optimistic and often unrealistic assumption.
When the two disagree on ranking mutually exclusive projects, use NPV. It directly answers the only question that ultimately matters: how much value does this project add.
Including the initial investment inside Excel's NPV function. This is the single most common NPV error and quietly overstates the result.
Using IRR alone to rank projects of very different sizes. A tiny project with a huge percentage return can look better than a much larger, more valuable one. Check NPV before making the final call.
Assuming a project only has one IRR. If cash flows change sign more than once, for example a large outflow in a later year for decommissioning or a major overhaul, the project can have multiple mathematically valid IRRs.
Using NPV or IRR for cash flows that do not fall on even annual or monthly periods. Use XNPV and XIRR instead, which take actual calendar dates rather than assuming equal spacing.
Picking the wrong discount rate. The discount rate should reflect the actual cost of capital or required return for the specific risk of the project, not an arbitrary round number.
Key Takeaways
- Payback period measures time to recovery only, ignoring both the time value of money and any cash flows after that point.
- NPV measures value created in today's dollars and is the most reliable metric when ranking mutually exclusive projects.
- IRR expresses the same underlying result as a percentage return, which is intuitive but carries a reinvestment assumption that can be unrealistic.
- When NPV and IRR disagree on which project to choose, trust NPV.
- Use XNPV and XIRR whenever cash flows do not land on evenly spaced periods.
Frequently Asked Questions
What is a good NPV for a project?
Any NPV greater than zero means the project is expected to create value above the required rate of return used in the calculation. There is no universal good number since it depends entirely on the size of the initial investment and the discount rate applied.
Why does Excel's NPV function give the wrong answer?
Excel's NPV function discounts every cash flow supplied to it, including the first one. If the initial investment at time zero is included inside the NPV function, it gets discounted by one period when it should not be, which understates the size of the outlay. The initial investment should be subtracted outside the function instead.
Can a project have more than one IRR?
Yes, if the cash flows change sign more than once, for example an initial outflow, followed by inflows, followed by another large outflow later, the project can mathematically have multiple IRRs, which makes IRR unreliable for that specific cash flow pattern.
Why do NPV and IRR sometimes rank projects differently?
NPV and IRR can disagree when comparing mutually exclusive projects that differ significantly in size, timing, or cash flow pattern. This happens because IRR assumes interim cash flows are reinvested at the IRR itself, while NPV assumes reinvestment at the discount rate, which is usually the more realistic assumption.
What is the main weakness of payback period?
Payback period ignores the time value of money and ignores any cash flows that occur after the payback point, which means it can favor a project with a fast but small return over one that creates far more value over its full life.
Should I use XNPV and XIRR instead of NPV and IRR?
Yes, if cash flows do not fall on regular, evenly spaced periods. XNPV and XIRR account for the actual calendar dates of each cash flow, while NPV and IRR assume equal time intervals between them.
Related Articles
External References
- Corporate Finance Institute, Net Present Value (NPV) Guide: corporatefinanceinstitute.com
- Microsoft Support, NPV function: support.microsoft.com
- Microsoft Support, IRR function: support.microsoft.com
0 Comments