NPV, IRR and the time-zero convention

◈ 8 cards

NPV starts at period 1; IRR starts at time 0. Handle the outlay outside the function, and see the wrong answer that including it produces.

A function whose name is a lie

Excel's NPV does not compute a net present value. It computes the present value of a list of cash flows assuming the first one arrives at the end of period 1. The name is inherited, the behaviour is fixed, and the difference is the single most common Excel error on a finance paper.

$NPV(rate, CF_{1}, CF_{2}, \dots, CF_{n}) = t = 1 \sum n \frac{C F _{t}}{( 1 + rate ) ^{t}}$

A true net present value has a time-0 cash flow — the outlay — which is not discounted at all. So the correct Excel form adds it outside the function:

$NPV = C F_{0} + NPV(rate, CF_{1} \dots C F_{n})$

Worked example — $1, 000 o u t,$ 400 a year back

Harbourline Cafés can spend $1,000 today on an espresso machine that returns $400 at the end of each of the next three years. Its required return is 8 %.

The right way. =NPV(0.08,400,400,400)-1000. The function discounts 400 by $1.08$ , $1.0 8^{2}$ and $1.0 8^{3}$ — that is $370.37 + 342.94 + 317.53 = 1, 030.84$ — and the outlay is subtracted undiscounted: 30.84. Positive, so the machine earns more than 8 %.

The wrong way. =NPV(0.08,-1000,400,400,400). Now the −1,000 sits in the first slot and is discounted by $1.08$ as if paid a year from now; every 400 is pushed one period later too. The result is 28.55. Still positive, still plausible-looking, and wrong — every flow was discounted one period too many. Under answer-only marking that plausibility is exactly the problem: nothing about 28.55 looks broken.

IRR starts where NPV does not

IRR is the rate at which the net present value is zero, and — inconsistently — Excel's IRR does take the time-0 flow as its first value. =IRR({-1000,400,400,400}) returns 9.70 %, and you can confirm it by evaluating the right-way NPV at 9.70 %: it comes out to zero. The range asymmetry is therefore:

=NPV(rate, CF1:CFn) + CF0      the outlay is OUTSIDE
=IRR(CF0:CFn)                  the outlay is INSIDE, first

In a worksheet with the flows in A1:A4 (A1 = −1,000), that is =NPV(0.08,A2:A4)+A1 and =IRR(A1:A4). The two functions read the same column from different starting rows.

Why the one-period shift always understates

Divide the correct NPV formula through by $(1 + r)$ and you have the wrong one: $28.55 = 30.84/1.08$ . Putting $C F_{0}$ inside the range discounts the entire project by one extra period. For a positive-NPV project the wrong answer is smaller; for a negative-NPV project it is less negative; either way the sign can flip when the true NPV is close to zero — which is precisely when the decision matters.

The Python exercise below computes the same list twice, with $t$ starting at 0 and at 1, so you can watch Excel's answer appear from the shift.

NPV’s range is bracketed from t = 1; the outlay is added outside. IRR’s range begins at t = 0.