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.

## 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.

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?

Can you post an example?

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…

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 ?

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/real-estate-proforma/

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.”

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.”

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

what value should dates be in xnpv and xirr? tks

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:

http://people.stern.nyu.edu/adamodar/

Can you please help for calculating NPV and IRR

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?

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

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)

thank you, for share this information. it is really usefull.

Does your comments above still apply if you are using the xnpv function? Also what about the situation with a capital development project were the capital spend is over more than one year – does your above advice change at all?

Is that really so? If I deposited 100 at the bank at 5% interest and get back after 1 year my initial outlay 100 plus 5$ as interest, Excel 2016 NPV calculates just fine:

0 -100

1 105

NPV=0 =NPV(5%,B1:B2)

Try adding a second year and then calculate NPV your way:

0 -100

1 105

2 110.25

What happens? You’ll find that you are actually just calculating the PV of those cash flows as of time period -1. You can compound the result forward one year by multiplying by 1.05. Or, you can use the other method described in the article.

This is not correct sequence of cash flows. I’d use:

0 -100

1 5

2 105

Here’s an easy way to test your method. Leave your cash flows the same but change your discount rate from 5% to 10%. Now compare your result versus the result using the method suggested in this article. If both NPVs are equal then they are both doing the same thing. If the NPVs are different then one of us is wrong. Here’s what you’ll get:

https://uploads.disquscdn.com/images/5cdf0190d22cfe2afb45b60dbc566e0e5532b8b013e276c5155f7322bd68d9bf.png

Why are the two NPVs different? And which one is correct?

I believe it’s because my way takes into consideration cash flows occuring at the beginning of the period, your way – at the end of the period.

So, here are the cash flows if they occur at the end of the period:

https://uploads.disquscdn.com/images/4fbb879998d0973b43a7394bf6873fbabef1e14d4f910472188e65a90acefdb1.png

We make a $100,000 investment today, then at the end of year 1 we get $5,000, and at the end of year 2 we get $105,000.

Notice that all three methods of calculating the net present value here are the same. However, with your method, the calculation is actually as of time period -1. To see this you can divide each present value by 1.05 to move them back one more period, then sum them up and you’ll still get 0. To clarify, here are the cash flows if they occur at the beginning of the period:

https://uploads.disquscdn.com/images/00e73432191a28d515afb7c99cc2dc35e051c1b2b693aa728bddb613d62a11e1.png

We make a $100,000 investment today at the beginning of year 1, and we also get our first cash flow of $5,000 immediately since our cash flows occur at the beginning of each period. Then we get our next cash flow at the beginning of year 2 of $105,000.

This effectively makes our initial out of pocket investment only $95,000 (since we get an immediate payment of $5,000). So, if we invest $95,000 today and we’ll get $105,000 back in exactly 1 year, our IRR is 10.53%. How much more would you be willing to pay for this if you only needed to earn 5%?

If we use your method we’d pay $4,762 more. But that would bring our initial investment to $99,762, which results in an IRR of 5.25% so that can’t be right. Using the other methods tells us we can pay $100,000, or $5,000 more than $95,000 in order to get a 5% IRR. And indeed this is correct.

Also, notice that your NPV of $4,762 is exactly $5,000 if you compound it forward one year by multiplying by 1.05. Why is this? Because you are calculating the NPV as of time period -1.

that was easy thanks!