Uneven cash flows: NPV and IRR

◈ 9 cards

Sum the PVs, or let =NPV do it — from period 1, with the outlay added outside — then =IRR from time 0, and the PV × (1 + r)ⁿ shortcut for the stream’s future value.

When the payments are not level

A project's cash flows are rarely an annuity. Value an uneven stream the long way — discount each flow by its own $(1 + r)^{t}$ and add — or let =NPV(rate, value1, value2, …) do the adding. The function's one rule, repeated from Lesson 1.4 because the paper tests it every year: NPV treats its first value as arriving at the end of period 1. A time-0 outlay does not belong inside the range; add it outside.

$N P V = C F_{0} + t = 1 \sum n \frac{C F _{t}}{( 1 + r ) ^{t}}$

Worked example — Lakehead's robot

Lakehead Robotics buys a welding robot for $15,000 today. It returns 4,000 / 5,000 / 6,000 / 7,000 at the ends of years 1–4. The required return is 9 %. With the flows in B1:B5 (B1 = −15,000):

$P V_{inflows} = \frac{4 , 000}{1.09} + \frac{5 , 000}{1.0 9 ^{2}} + \frac{6 , 000}{1.0 9 ^{3}} + \frac{7 , 000}{1.0 9 ^{4}} = 17, 470.20$

=NPV(0.09,B2:B5) → 17,470.20, and =NPV(0.09,B2:B5)+B1 → NPV = 2,470.20. The robot adds $2,470.20 of value at 9 %: accept.

The trap, in numbers: =NPV(0.09,B1:B5) — the outlay inside the range — returns 2,266.24. Every flow, including the outlay, has been discounted one period too many; the answer is 2,470.20 ÷ 1.09. Too low, and marked wrong.

The rate that makes NPV zero

The internal rate of return is the discount rate at which the NPV is exactly zero — the project's own yield. =IRR(B1:B5) → 0.1562, or 15.62 %. IRR does take the time-0 flow: its range starts at $C F_{0}$ , because without an outlay there is no rate to find. The asymmetry — NPV from B2, IRR from B1 — is the whole of the callout below. Since 15.62 % > 9 %, the IRR agrees with the NPV: accept.

The future value of the stream

What will the four inflows be worth at the end of year 4, reinvested at 9 %? Compound each one forward — 4,000 × 1.09³ + 5,000 × 1.09² + 6,000 × 1.09 + 7,000 — or take the shortcut: the FV of a stream is its PV × (1 + r)ⁿ.

$17, 470.20 \times 1.0 9^{4} = 17, 470.20 \times 1.411582 = 24, 660.62$

=NPV(0.09,B2:B5)*(1+0.09)^4. The exponents fall toward the horizon — the last flow earns nothing — exactly as in the annuity FV.

At 12 %

=NPV(0.12,B2:B5)+B1 = 1,276.71 — still positive, still below the 15.62 % IRR. Raise the rate past the IRR and the NPV turns negative; that is what "the rate that makes NPV zero" means.