Money today is worth more than the same money next year. NPV brings future cash flows back to today's value using a discount rate. If the total is positive, the project earns more than your required return.
Syntax
=NPV(rate, value1, [value2], ...)
The rate is the discount rate per period, such as 10% a year.
The mistake almost everyone makes
NPV assumes the first value is received at the end of period 1. The money you spend today (period 0) must therefore be added separately, not placed inside NPV.
Worked example
You invest 100,000 today. You expect 30,000, 40,000, 50,000 and 40,000 over the next four years. Discount rate is 10%. Cash flows are in C3:C6 and the investment is in C2 as a negative number (-100000).
=NPV(10%, C3:C6) + C2
The four inflows are worth about 125,200 in today's money, so the result is about +25,200. The project is worth doing at a 10% required return.
NPV, XNPV and IRR
- Use XNPV when cash flows come on irregular dates.
- Use IRR to find the rate at which NPV becomes zero.
Common mistakes
- Mixing a yearly rate with monthly cash flows. Divide the rate by 12 for monthly flows.
- Entering outflows as positive numbers. Money going out must be negative.