The Excel NPV function has the syntax shown below.
NPV(Rate, Value1, Value2, ...Value n)
Rate = Discount rate
Value n = Cash flow at the end of period n
Use of the Excel NPV Function
The Excel NPV function is used to calculate present value of a series of cash flows for a given discount rate. The cash flows do not have to be the same, but must occur at the end of a period. The discount rate is the rate for a period.
The NPV function is used instead of the PV function when the cash flows each period are unequal.
Net Present Value of an Investment Project
The Excel NPV function can be used to calculate the net present value of an investment project.
Despite its name, the Excel NPV function calculates the present value of the cash flows starting with the first cash flow at the end of period 1 . The function does not include the original investment at the start of period 1. To calculate net present value the original investment must be deducted from the answer given by the Excel NPV function.
Suppose for example, a business plans to invest 400 today (start of year 1) at a discount rate of 8%. It will receive cash flows of 500, 1800, 600, and 300 in the following 4 years. The present value of the cash flows is given by the Excel NPV function as follows:
Rate = 8% Value 1 = 500 Value 2 = 1,800 Value 3 = 600 Value 4 = 300 Present value = NPV(8%, 500, 1800, 600, 300) Present value = 2,702.98
This is the present value of the future cash flows. Consequently to calculate the net present value of the project the original investment of 400 must be deducted
Net present value = Present value - Original investment Net present value = 2,702.98 - 400.00 Net present value = 2,302.98
It should be noted that the original investment is negative as it represents a cash flow out.
The NPV function can be used with positive or negative values. To illustrate suppose for example the cash flow in year 3 above had been a cash flow out rather than a cash flow in. In this case the present value of the cash flows would be given by NPV(8%, 500, 1800, -600, 300) = 1,750.38.
In addition, if a value is zero, then it has to be entered as zero (0) and not left blank. For example had the cash flow in year 2 above been zero, then the present value of the cash flows would be given by NPV(8%, 500, 0, 600, 300) = 1,159.77
Using the Excel NPV Function with Variable Arguments
Excel is more useful when the arguments can be varied without having to enter the Excel function each time. To do this a simple spreadsheet can be set up as shown below.
In this example spreadsheet, the variable arguments are entered in cells B4 to B10, and the NPV function is entered at cell B13 as =NPV(B4,B7:B10). Note that the NPV function could equally well be written as =NPV(B4,B7,B8,B9,B10).
By changing any of the variables in B4 to B10, the present value of the cash flows can be recalculated without having to enter the NPV function each time. It is important to realize that if a cash flow is zero it has to be entered as zero (0). The argument cannot be left blank otherwise the NPV function will return the wrong answer.
To complete the calculation of the net present value, the original investment at the start of year 1 is entered at cell B15. Additionally B17 is used to deduct this from the present value of the cash flows in B13 to arrive at the net present value of the project.
The Excel NPV function is one of many Excel financial functions used in time value of money calculations. Further Excel functions can be discovered at the links below.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.