Net present value and the internal rate of return
A dollar received in the future is worth less than a dollar today, because today’s dollar could be earning a return in the meantime. Net present value (NPV) adds up a set of cash flows after scaling each one back to today’s terms at a chosen discount rate:
NPV = C0 + C1 ÷ (1 + r) + C2 ÷ (1 + r)2 + … + Cn ÷ (1 + r)n
C0 is the cash flow today (usually the outlay, a negative number), C1 to Cn are the flows at the end of each following year, and r is the discount rate as a decimal. A positive NPV means the flows are worth more than they cost at that rate; a negative one means they are worth less.
The internal rate of return (IRR) is the discount rate at which the NPV is exactly zero:
0 = C0 + C1 ÷ (1 + IRR) + … + Cn ÷ (1 + IRR)n
With more than a couple of cash flows this equation cannot be rearranged to isolate the rate, so it is solved numerically. This calculator scans discount rates from −99% to 1,000%, notes every place the NPV changes sign, and closes in on each crossing by repeated halving. That finds every IRR in the range, not just the first.
The modified internal rate of return (MIRR) replaces the IRR’s built-in assumptions with rates you choose:
MIRR = (FV of inflows at the reinvestment rate ÷ −PV of outflows at the finance rate)1 ÷ n − 1
Money received is carried forward to the final year at the reinvestment rate, money paid out is brought back to today at the finance rate, and n is the number of years between the first and last flows.
Worked example: $10,000 out, then $3,000, $4,000, $4,000 and $3,000 back
- Undiscounted total. −10,000 + 3,000 + 4,000 + 4,000 + 3,000 = $4,000.
- Present values at 8%. $3,000 ÷ 1.08 = $2,777.78. $4,000 ÷ 1.082 = $3,429.36. $4,000 ÷ 1.083 = $3,175.33. $3,000 ÷ 1.084 = $2,205.09.
- NPV. −$10,000 + $2,777.78 + $3,429.36 + $3,175.33 + $2,205.09 = $1,587.55 (from the unrounded figures).
- Bracket the IRR. At 14% the NPV is $185.58; at 15% it is −$21.41. The IRR lies between them, much nearer 15%.
- IRR. Narrowing the range gives 14.895%.
- MIRR at 8% and 8%. Inflows carried to year 4: $3,000 × 1.083 = $3,779.14, $4,000 × 1.082 = $4,665.60, $4,000 × 1.08 = $4,320.00 and $3,000, a total of $15,764.74. Then (15,764.74 ÷ 10,000)1 ÷ 4 − 1 = 12.053%.
The MIRR is lower than the IRR because the IRR quietly assumes each year’s inflow is reinvested at 14.895% until the end. Assume a more modest 8% and the overall rate drops to 12.053%.
More than one IRR, or none
A conventional project pays out first and receives afterwards: the signs of the cash flows change once, and there is exactly one IRR. When the signs change more than once, for example a project with a large clean-up cost at the end, the equation can have several solutions or none. By Descartes’ rule of signs, the number of IRRs above −100% is at most the number of sign changes.
Take −$1,000, +$2,300, −$1,320. The signs change twice, and both 10% and 20% make the NPV zero. Between them the NPV is slightly positive ($1.89 at 15%); outside them it is negative (−$20.00 at 0% and −$11.83 at 30%). Neither figure is “the” return. Change the flows to −$1,000, +$3,000, −$2,500 and no discount rate brings the NPV to zero at all.
In these cases the NPV at your own discount rate still has a single clear meaning, and so does the MIRR: for the first set of flows at 8% and 8% it is 7.948%.
How to read the result
Compare the IRR with your discount rate
For a conventional project, an IRR above the discount rate and a positive NPV are the same statement made two ways. The NPV table shows this: the value falls as the rate rises and crosses zero at the IRR.
IRR ignores scale
Paying $1,000 to receive $1,500 a year later is an IRR of 50% and an NPV of $388.89 at 8%. Paying $100,000 to receive $125,000 is an IRR of only 25% but an NPV of $15,740.74. A percentage cannot show how much value is involved; the NPV can.
Year 0 is not discounted
Here the first cash flow happens today and counts at full value. Some spreadsheet NPV functions instead treat the first value in the list as arriving one period from now, which for the example would give $1,469.96 in place of $1,587.55. If your figures disagree with a spreadsheet by a factor of exactly (1 + r), this is the reason.
Timing is yearly
Every flow is assumed to fall a whole number of years after the first. For monthly flows the rate found would be a monthly rate, and for irregular dates a date-based method is needed.
Limits of the method
IRR, NPV and MIRR are only as good as the cash flows fed into them, and forecasts of later years are guesses. None of the three measures risk: two projects with the same IRR can differ greatly in how likely the cash flows are to arrive. The figures describe the flows you entered and nothing more.