NPV & IRR — Excel guide
Project economics: discount future cash flows to today (NPV), or find the rate a project earns (IRR). Powerful, and booby-trapped in famous ways — every tip below is a real-world scar.
NPV
The present value of a series of future cash flows.
- Rate must match flow frequency — monthly flows want rate/12.
- NPV = 0 means the project earns exactly the discount rate. That rate should be your cost of capital, not a round number.
- Irregular real dates? XNPV takes a dates range and discounts by actual day counts.
- A 5-year DCF carries years 6-∞ as a terminal value added into the final flow.
IRR
The discount rate at which the flows break even (NPV = 0).
- Accept when IRR > your hurdle rate (for conventional flows).
- Flows that change sign twice can have MULTIPLE valid IRRs — use NPV at your hurdle, or MIRR.
- MIRR also fixes IRR's fantasy that interim cash reinvests at the IRR itself.
- Monthly flows → a monthly IRR: annualize by compounding, (1+irr)^12 - 1, not ×12.
- Ranking projects of different sizes? NPV measures dollars created; a big IRR on a tiny project can still be the wrong pick.
XIRR
IRR for cash flows on real, irregular dates.
- Each value pairs with its date; same-length ranges; the sign rule still applies.
- It returns an ANNUAL rate directly — comparable across deals with no conversion.
- Payback period ("when am I whole?") is a fine risk screen but ignores time value and everything after payback — never the decision rule.