Excel like a finance pro.
NPV & IRR
FinanceValue an evenly-spaced stream of cash and get its return — with the off-by-one that catches nearly everyone.
Difficulty
1What is it?
NPV discounts a series of future cash flows back to today at a rate you choose; IRR finds the rate that would make that same series worth exactly zero. The catch is that NPV discounts the FIRST value by one full period, so a cash flow happening today — the investment itself — does not belong inside the function. Put it outside and add it: =A2 + NPV(rate, B2:F2). Get that wrong and every number in the model is one period too small, which is the most common mistake in the whole family.
2What it looks like
NPV(rate, value1, [value2], …) · IRR(values, [guess])- rate
- The discount rate PER PERIOD. Monthly flows need an annual rate divided by 12.
- value1, …
- The cash flows, evenly spaced, the first one landing at the END of period 1. A payment made today belongs outside the function.
- values (IRR)
- A range holding the whole series, in order, with at least one negative and one positive.
- [guess]
- A starting estimate for IRR (default 10%). Only ever needed when IRR can't settle on an answer.
3When you use it
- Price an evenly-spaced stream — a lease, a loan, a subscription cohort.
- Compare two investments at one agreed discount rate instead of arguing about gut feel.
- Give a project's return as the single percentage a board will recognise.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
The outlay happens TODAY, so it belongs outside NPV. Watch the two answers diverge as you change the rate.
=A2 + NPV(rate, B2:D2)Right — outlay outside: $9,053| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Today | Yr 1 | Yr 2 | Yr 3 | Result |
| 2 | Right — outlay outside: $9,053 | ||||
| 3 | Wrong — =NPV(rate, A2:D2): $8,230 | ||||
| 4 | The mistake costs you $823 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Answer is one period too smallThe period-zero outlay was included inside NPV, so Excel discounted it as if it happened a year from now.Fix: Take it out of the range and add it: =A2 + NPV(rate, B2:F2).
#NUM!IRR can't find a rate — usually because the series is all one sign, or it didn't converge in 20 tries.Fix: Make sure there's at least one negative and one positive flow, then pass a guess closer to the answer (e.g. 0.2).
The rate doesn't match the periodsAn annual rate was used on monthly cash flows.Fix: Divide the rate by 12 for monthly, 4 for quarterly. IRR returns a PERIOD rate too — multiply it back up.
Blanks quietly skippedA period with no cash flow was left empty inside the range.Fix: Type a 0. An empty cell is ignored rather than treated as a zero period, which shifts every later flow one period earlier.
6Better functions & alternatives
- XNPV / XIRR — Use these when the flows carry real dates rather than sitting on an even grid — which is most actual cash.
- MIRR — IRR assumes you reinvest at the IRR. MIRR lets you say what you'd really earn on the proceeds.
Want NPV & IRR already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.