Tom’s Tutorials For Excel: The NPV (Net Present Value) Function

Tom’s Tutorials For Excel: The NPV (Net Present Value) Function

Excel’s NPV (Net Present Value) function calculates the sum of a series of net cash flows, each of which has been discounted to the present using a static discount rate. Future payments are regarded as negative values, and income is regarded as positive values. When the cash flows are different, such as receiving annual rental income for a piece of machinery you buy, you can find the net present value of that machinery item by using Excel’s NPV function.

The discount rate is a percentage factor that takes into account the rate of inflation, or maybe the interest rate of another investment that you would have gone for instead. Therefore, the NPV function tells you what your investment money is worth today, considering future cash flows.

Suppose you invest $35,000 to purchase a bulldozer that you will rent to your customers. As you build your clientele, you expect your annual cash flows over the next four years to be $18,000, $23,000, $26,000, and $32,000 respectively.

Further suppose you set your discount rate is 9% as pictured below.
The formula in cell C8 is =NPV(A6,B6:F6)*(1+A6), returning $43,618.78, making your investment of $35,000 look like a wise decision.



A cautionary note:
A quirk in the NPV function assumes the initial cash flow occurs at the end of the first period. However, in most cases including the scenario pictured above, the first cash flow event occurs at the start of the first period, such as when the bulldozer was purchased for $35,000, which is represented in cell B6 as a negative number. You can think of the start of the first period as Period 0, with Period 1 being the end of that first period when rental income was received.

Users make a common error with NPV by including that “Period 0” $35,000 cash outlay in the formula (which they should do), but NOT increasing the NPV by applying the discount rate to that initial investment. That is why you see the NPV function being multiplied in the formula with (1+A6), where A6 holds the 9% discount rate.