Excel’s NPV Function Doesn’t Calculate Net Present Value

One of the things that has always driven me nuts (because I have to repeatedly explain it in class) is that the NPV function in spreadsheets doesn’t really calculate NPV. Instead, despite the word “net,” the NPV function is really just a present value of uneven cash flow function. It has always been this way, so I’ve learned to live with it. I suspect that this goes all the way back to Visicalc (which you can download and run in a DOS window).

Net present value is defined as the present value of the expected future cash flows less the initial cost of the investment. “Net” always means that something has been subtracted, so it is puzzling that spreadsheets have done this (there are actually a number of crazy errors that have been propagated over the years to maintain compatibility). In any case, there are two common ways to calculate the real NPV in Excel:

1.  Use the NPV function, but leave out the initial outlay. Then, outside of the NPV function, subtract the IO. (Note, the initial outlay is often entered as a negative number, so it will actually be added.)
2.  Use the NPV function and include the initial outlay in the range of cash flows. In this case, the “NPV” will be in period -1 so we must bring it forward one period in time. So, multiply the result by (1 + i), where i is the per period discount rate.

You can download an example spreadsheet, or look at the picture below.

The spreadsheet demonstrates three methods of calculating NPV in Excel. The first two correspond to the two methods mentioned above. The formulas are:

1.   Method 1: =NPV(B1, B5:B9)+B4
2.   Method 2: =NPV(B1, B4:B9)*(1+B1)

Obviously, both give the same result and which one you use depends on personal preference.

For the third method, I used an array formula that I think is somewhat interesting. Most people probably wouldn’t use this method as it would likely confuse others. Still, it is worth mentioning and it may lead you to solutions to other problems. What I am doing is using the PV function, but as an array instead of the usual way of using the function. This way, I can get the NPV in one step.

The formula in B13 is: =SUM(PV(B1, A4:A9, 0, -B4:B9)). Remember to enter it by pressing Ctrl+Shift+Enter, otherwise you will get a #VALUE error. (Update: if you are using a recent version of Excel, then Ctrl+Shift+Enter is not required). You’ll note that I have two arrays in the function: The first is an array of the period numbers in A4:A9. The second is the array of cash flows in B4:B9. This will return 6 answers as you can see if you highlight the PV function in the formula bar and press F9. Surrounding it with the Sum function adds all six answers so that we end up with a single number (the NPV).

Essentially, the above formula runs the PV function six times – once for each cash flow – and then adds the results. However, notice that I have included the initial outlay in my range of cash flows. This works because the NPer argument of the PV function is 0 for the initial outlay so the formula calculates the net present value as of period 0, instead of period -1 as we saw in “Method 2.” For more information on calculating NPV, IRR, and MIRR in Excel please see the linked tutorial page.