Excel NPV Function
NPV is an Excel function that calculates net present value of a project by discounting an array of cash flows values at a given discount rate.
NPV stands for net present value, a capital budgeting technique that measures the net increase in a company’s value resulting from an investment. It equals the difference between the (a) present value of future cash flows of the investment estimated based on a discount rate that reflects the equity and debt mix used to finance the investment and the risk inherent in the investment and (b) the amount of total initial investment required.
NPV syntax is:
NPV(rate, value1, [value2],…)
Rate is the discount rate appropriate for the investment used to discount the project net cash flows to time 0.
Values refer to the stream of cash flows that the project generate. The stream of cash flows must be consistent and equidistant i.e. there should be equal time between two cash flows.
NPV function discounts all the future net cash flows of the project using the given discount rate. In fact, it is a misnomer to call the function NPV because the value we get is not yet net of the initial investment. NPV function just finds the present value of the future cash flows stream and we need to subtract the initial investment outlay manually to arrive at the correct financial definition of net present value.
Let's say you have a projec that has the following cash flows schedule:
- Year 0: cash out flow of $10,000
- Year 1: net cash flow of $2,000
- Year 2: net cash flow of $2,400
- Year 3: net cash flow of $3,200
- Year 4: net cash flow of $3,000
- Year 5: net cash flow of $5,000 and terminal cash flow of $2,000
You determine that 10% is the appropriate discount rate for the project.
The following figure illustrates application of NPV function in the above scenario:
As you can see the NPV function itself gives us the present value of the future net cash flows in Year 1-5 i.e. D7:H7 and we have manually subtracted the initial investment. It would be wrong to enter the formula as NPV(C2,-C9,D7:H7) because the NPV function inherently assumes that cash flows occur at the end of the year so it will discount the $10,000 initial investment for one year too, which is wrong.
NPV function is different from PV function because PV requires a fixed periodic cash flow i.e. PMT while NPV allows for different cash flow values in different periods.
Written by Obaidullah Jan, ACA, CFA and last revised on