Course / Money & Finance

Compare investments with NPV and IRR

Discount future cash flows with NPV, find each project's own return with IRR, and let IF pick the better buy. Advanced · 6 min · NPV, IRR, Discount rate, IF

A dollar next year is worth less than a dollar today, because today's dollar could be earning a return in the meantime. Net present value (NPV) shrinks each future cash flow back to today's money using a discount rate, the return you could get elsewhere, and adds them up. A positive NPV means the investment beats that alternative.

The internal rate of return (IRR) comes at it from the other side: it is the discount rate that would make the NPV exactly zero. Think of it as the investment's own yearly return.

Your print shop can afford one new machine this year. Compare the two using the cash flows in A1:C7 and the discount rate in F1.

> Money going out, like the purchase price, is negative. Money coming in is positive.

Steps

1. In B9, find the printer's NPV: =NPV($F$1, B3:B7) + B2. NPV discounts every value you give it, starting one year from now, so it only gets years 1 to 5. The purchase happens today and is already in today's money, so it is added on outside. Then fill right into C9 for the engraver: the $ signs keep both projects on the rate in F1.

2. In B10:C10, find each machine's IRR. Unlike NPV, IRR takes the whole column of cash flows, Year 0 included, because it treats the first value as today.

3. When two projects compete for the same money, pick the one with the higher NPV: it is measured in dollars, so it reflects the size of the payoff as well as the rate. In B12, use IF to show Printer or Engraver, whichever has the higher NPV.

4. Your bank has just raised its lending rates, so money now costs you 14% a year. Change the discount rate in F1 to 14% and watch the verdict in B12.

Remember

• =NPV(rate, year1:yearN) + year0: NPV discounts every value it is given, so leave today's cost out and add it on.
• IRR takes all the cash flows, Year 0 included, and returns the rate at which the NPV is zero.
• Positive NPV, or an IRR above your rate, means the project beats the alternative. To choose between projects, take the higher NPV.
• NPV changes with the discount rate; IRR does not.

Previous: Build a loan amortization schedule · Next: Spilling arrays: UNIQUE and SORT