# How Not to Use NPV in Excel

NPV in Excel is widely used but commonly misunderstood. Even top MBA grads commonly misuse NPV in Excel. In this short article we’ll look at the problem with NPV in Excel, show you where most people go wrong, and we’ll also walk through the correct way to use the NPV function in Excel, step-by-step.

## The Problem With NPV in Excel

First of all, what’s the problem with NPV in Excel? Why do so many people get it wrong? Well, contrary to popular belief, NPV in Excel does not actually calculate the Net Present Value (NPV). Instead, it calculates the present value of a series of cash flows, even or uneven, but it does NOT net out the original cash outflow at time period zero. This original cash outflow actually needs to be manually subtracted out when using the NPV formula in Excel.

Before we jump into an example, let’s first take a step back to get some context and understand what NPV actually means in finance. NPV is simply the difference between value and cost. In other words, to find NPV we just take the present value of a series of future cash flows at a particular discount rate, then simply subtract out what our original cost is to acquire that stream of cash flows. Check out the Intuition Behind IRR and NPV for more on how this works.

Unfortunately, Excel does not define the NPV function in this way where it automatically nets out the original investment amount. This is where most people get stuck. Instead, NPV in Excel is just a present value function that gives you the present value of a series of cash flows. Then, it’s up to you to net out the original investment amount in order to find the actual NPV.

## How Not to Use NPV in Excel

Let’s take an example. Suppose we have the following series of cash flows:

The above NPV calculation of -\$44,845 incorrectly includes the \$515,000 initial cash outlay in the series of cash flows. Here’s the exact formula used in cell C18 to incorrectly calculate NPV:

=NPV(B18,C5:C15)

Rather than calculating the correct NPV at time period 0, which is what we are interested in, what the above formula actually does is calculate the NPV at time period -1. Sure, you can manually adjust this forwards in time 1 period by multiplying by (1+r), substituting in our discount rate for r (12% in this case). This will adjust the formula above to give you the correct result, but it’s pretty confusing to most people and there is an easier way to do this.

## How to Correctly Calculate NPV in Excel

The easier way to correctly calculate NPV in Excel is to exclude the initial cash outflow from your NPV formula. This will give you the present value of only the future cash flows. Then, you can simply net our your initial cash outlay from this present value of future cash flows calculation. Because the net present value is simply value minus cost, this approach is much more intuitive to most people.

The above NPV calculation of -\$50,226 correctly excludes the \$515,000 initial cash outlay in the series of cash flows and then nets it out from the result of the NPV formula in Excel. Here’s the exact formula used in cell C18 to correctly calculate NPV above:

=NPV(B18,C6:C15)+C5

This correctly calculates the present value of our future cash flows (time periods 1 thru 10), which we can then take and net against our initial cash outlay of \$515,000 (time period 0) in order to find the correct NPV.

### NPV in Excel Cheat Sheet

Fill out the quick form below and we'll email you our free NPV in Excel cheat sheet containing helpful calculations from this article.

## Conclusion

NPV in Excel is widely used but commonly misunderstood. In this post we discussed the problem with NPV in Excel, talked about why it’s so commonly misused, and then we walked through an example to illustrate the right and wrong way to use NPV in Excel. Keep this in your toolkit whenever you find yourself working with NPV in Excel, and avoid any unnecessary mistakes.

• Scott

This is a great tutorial. Thanks! The IRR function in excel also assumes the first payment is made at the end of the first year as opposed to happening today. Do you have suggestions for correctly calculating an IRR?

• Rob

Can you post an example?

• Octo

Not an expert here, however, IRR consider cash flow throughout the whole 1 year period, therefore, it doesn’t really matter if its at the start or the end of the period… Hope it helps…

• Ryan Hughes

I believe you can use =XIRR to do irregular time period cash flow returns

• Thank you for posting this. I am completing an assignment and felt the NPV was off because of the year 0 initial investment. I’m glad I found this page and was able to correct it before submission!

Hi – couple of questions:
1. What about if you initial investment is spread out over two years?
2. Is the NPV applied to net cash flow ( accounting fir interest & principal repayment ) or to NOI?
3.is the NPV applied to total purchase price or just to the equity investment?
4. What exactly is the difference between NOI and net CF ?

• Rob

To simplify your cash flows you could simply bring the second year cash outflow back to time period zero using a safe rate. Then you’d have one initial investment. The NPV itself can be applied to any set of cash flows, which should help clarify the remaining questions. Finally, this might help on the last question: https://www.propertymetrics.com/blog/2013/03/05/real-estate-proforma/

• John Deree

It seems to me that your correct approach is indeed more correct, of course, but the XIRR approach is the most precise because it takes into account exactly when all cash flows occur, leaving no doubt about the “in advance/in arrears” issue during each period. To put it another way, if one were to manually discount each cash flow on the basis of the exact time periods, an approach which by definition would yield the most precise result, wouldn’t one do exactly what XNPV does?
Also, to be fair, Excel gives guidance on the matter of correct usage of the NPV function in the help section of the function: “The NPV investment begins one period before the date of the value1 cash flow and
ends with the last cash flow in the list. The NPV calculation is based on future
cash flows. If your first cash flow occurs at the beginning of the first period,
the first value must be added to the NPV result, not included in the values
arguments.”

• Rob

This is a good observation. I believe the reason why there is a difference is because XNPV uses the actual days in a year, which in leap years is 366, whereas NPV treats all periods as equal. While this is good to understand, it does bring to mind Warren Buffet’s quote: “It is better to be approximately right than precisely wrong.”

• John Deree

..except when you can be precisely right without too much additional trouble 🙂

• Kevin

what value should dates be in xnpv and xirr? tks

• John Deree

ah, well, the discount rate to be used is, indeed, the six-million dollar question..:-)

In general, you use the rate that best represents the time value of money for the particular investment/loan/whatever cashflow, the present value of which you are trying to evaluate.

For example, if you are evaluating a series of cash flows related to a loan, the interest rate (or cost of debt, Kd) would be appropriate.
If on the other hand you are discounting a series of equity cash flows, the cost of equity capital (Ke) would be appropriate but a bit more difficult to calculate.

And if, on the third hand 🙂 you are evaluating an investment for which both debt and equity is employed, maybe you need to calculate the cost of capital as the weighted average of the first two, commonly called WACC, i.e. Weighted Average Cost of Capital.

To make things more interesting, you may use the same discount rate for the full duration of the analysis term (as the excel formulas do), or discount each period by a different discount rate manually if you feel that the time value of money will change materially in the future.

If you need to delve deep into the Cost of Equity question, and on valuation in general, you need to visit the website of the universal guru on the matter, Proff. Aswath Damodaran who teaches corporate finance and valuation at the Stern School of Business at New York University, at:

• Preeti Bhardwaj

For example i have invested a money of 100000 initially
1st year GP is 50000, Net Profit is 25000
2nd Year GP is 50000, Net Profit is 25000
100000 investment is done on Leasing Agreement so i have included Principal amount and Interest amount in the Cost of Operation

so how to calculate NPV on Net Profit or on GP?

• Kevin

Do you write the xnpv like the incorrect way, something like this:

=XNPV(B18,C6:C15,A6:A15)+C5
Or am thinking this is the way.
=XNPV(B18,C5:C15,A5:A15) – think you write it like this

• John Deree

the beauty of the XNPV/XIRR functions is that you need not concern yourself with such a question; rather you just tell excel when each cash flow happens and it takes it from there. Excel assumes of course that you need to calculate the NPV on the day of the first cashflow (but you can hack this by adding an initial zero cashflow at an earlier date if you need to calculate the NPV now, for example, for a series of cashflows that will start in the future)

• riky