Practical Tips for Excel's "NPV" Function

Excel's "NPV" function is the standard tool to compute the present value of a series of cash flows. While its use is straightforward for people who are familiar with the basics of Excel, there are still a few things that we need to keep in mind when estimating Present Values with that function.

The purpose of this document is to:

• show how the NPV function works 
• Point out possible areas of caution when using that function.

To do so, we use a simple example of a project that has an opportunity rate of return of 10% and generates the following cash flows between today and the end of year 5. The accompanying Excel file can be downloaded here.

Benchmark: Manual Computation

To make sure that our following application of the NPV-function yields the correct result, let us "quickly" compute the PV of this cash flow stream "by hand:"

\( PV = -10'000 + \frac{5'000}{1.1^1}+ \frac{7'000}{1.1^2}+ \frac{-3'000}{1.1^3}+ \frac{0}{1.1^4}+ \frac{5'000}{1.1^5} = 1'181.24 \)

The "NPV" Function

• To trigger the NPV function in Excel, go to the cell in which you want to show the present value of the cash flow stream and enter =NPV(.
   
  
• Once you open the parenthesis, Excel shows a little window that tells you how to enter the relevant arguments:
   

• The first argument is the "rate." That's the discount rate that we apply to the cash flow stream. In our example, the discount rate is in cell C2, so we enter C2 (or click on the cell C2) to tell Excel that it should use the value from cell C2 as discount rate for the cash flow stream.
    
• Next, the little comment window indicates how to separate the various arguments of the function. In the system that was used to create this example, the separator is a semicolon (;). In many other language settings, the separator is a comma (,). Enter the appropriate separator.
    
• Finally, you have to enter the values associated with the cash flow stream. Here, some words of caution are in order:
  • Excel discounts value1 over one period, value2 over 2 perdiods, value3 over 3 periods, etc.
  • Consequently, the first value we enter is the cash flow in 1 period, and NOT the cash flow we observe today!
  • This first cash flow is in cell D5. The subsequent cash flows for years 2 to 5 are in cells E5 to H5.
  • To tell Excel that it should use the values D5 to H5, simply left-click on D5 and then move the mouse over to H5 while holding the left button clicked. Alternatively, you can manually enter D5:H5 as the second argument.
      
• Once you have entered the cash flow stream, close the parenthesis. This ends the NPV function.
    
• Now you have to remember that the NPV function implemented above only starts with the cash flow of year 1 and ignores the cash flow of year 0 from Cell C5. Therefore, we have to add the cash flow of year 0 "manually." Since this is a cash flow that occurs today, no discounting is needed, so that we simply append the formula with +C5.
    
• Now the formula for the present value is complete and should look as follows:
    
  
    
•  Press Enter. Excel should now show the present value of 1'181.24.

This is the standard approach to compute present values using Excel's "NPV" function. Below, we discuss 2 challenges that users occasionally face when dealing with this function:

Challenge 1: Include today's cash flow in the function

Users could be tempted to include all cash flows in the built-in Excel function, including the first cash flow, which occurs today. So the second argument of the function would read C5:H5 instead of what we have entered above (D5:H5).

Under this approach, the computation of the present value would be conducted as follows:

=NPV(C2;C5:H5)

This approach is implemented in cell C23 of the accompanying Excel file. The result shows a present value of 1'073.86.

This result is wrong. It is wrong because all cash flows are discounted to heavily.

• As we have mentioned above, the NPV function discounts the first cash flow over one year, the second cash flow over two years, etc.
   
• Instead, it should not discount the first cash flow at all, since it occurs today, and all subsequent cash flows should be discounted over one year less.
    
• But there is no direct way to tell Excel.
    
• Consequently, we should either use the original approach outlined above, which simply excludes today's cash flow from the NPV function and adds it separately.
   
• Alternatively, we can recognize that this second approach over-discounts all cash flows by one year. Put differently, they reflect a present value one year ago (PV_-1), and not a present value today (PV₀).  To convert PV_-1 into PV₀, we can simply compound it over one year at the discount rate of 10%:
   
  \( PV_0 = PV_{-1} \times (1+R) = 1'073.86 \times 1.1 = 1'181.24 \)
   
• This then yields the correct result, which is implemented in Cell C27 of the accompanying Excel file.

Challenge 2: Empty Cells

Another challenge could be empty cells. In the example above, we have assumed a cash flow of 0 for year 4. Some users could be tempted to leave the cell of year 4's cash flow empty instead of entering "0". This is shown in rows 33 of the accompanying Excel file, where we leave G33 (the cash flow of year 4) empty instead of entering 0:

The problem is that Excel does not understand that an empty cell actually means a cash flow of 0.

• Consequently, the NPV function simply ignores G33 and assumes that the value in the next cell, H33, is the cash flow of year 4.
• The result is that the cash flow of year 5 is discounted over 4 years instead of 5 years.
• This is wrong, as can be seen in the resulting Present value of cell C55. Instead of the correct present value of 1'181.24, the cell reports a present value of 1'491.70. Since the displayed approach insufficiently discounts the cash flow of year 5, it is not surprising that the resulting NPV function overestimates the true present value of the cash flow stream.
• Note that the difference between the correct present value (1'181.24) and the wrong present value (1'491.70) of 310.46 indeed corresponds to the present value difference if cash flow 5 is discounted over 4 years instead of 5 years: 

\( \frac{5'000}{1.1^4} - \frac{5'000}{1.1^5} = 3'415.07 - 3'104.61 = 310.46 \)

• This mistake disappears once we enter "0" for the cash flow of year 4:
    

Therefore, whenever we use the NPV function (and other Excel functions), we have to make sure that values of zero are entered as such, and not as empty cells.