Share via

NPV vs XNPV

Anonymous
2018-08-08T11:07:27+00:00

I'm just developing a course on financial modelling in Excel and have discovered the XNPV function, which is a big improvement on NPV.

This article explains the difference well https://www.wallstreetmojo.com/npv-vs-xnpv/

A key statement in this article is: "NPV assumes that the future cash inflows happen at the end of the year (from today). Let’s assume that today is 3rd July 2017, then the first cash inflow of $4000 is expected to come after one year from this date"

HOWEVER: following the logic of this article, I tried to create a scenario in which NPV and XPV were exactly the same, but, so matter what positive discount rate I used, the calculated NPV was always slightly greater than XNPV see below.

Can anyone explain why NPV & XNPV aren't the same in this scenario?

 

Thanks for your help!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2018-08-08T14:23:33+00:00

    Your NPV formula is incorrect.  It should be =NPV(B3,C10:L10)+B10.  Same result in this case because B10 equals -B9.  But it's the "principal" of the matter. (smile)

    NPV assumes that each period is equal; one year, in you example.

    XNPV uses the exact difference in days/365.

    So, for example, NPV(B3,C10)+B10 returns exactly the same value as XNPV(B3,B10:C10,B7:C7) because 2019 has exactly 365 days.

    On the other hand, NPV(B3,C10:D10)+B10 differs from XNPV(B3,B10:D10,B7:D7) because 2020 has 366 days.

    BTW, I would not say that XNPV is a "big improvement".  For no good reason, XNPV does not allow negative discount rates.  NPV does.

    (That is true for Excel 2010 and earlier.  I believe someone confirmed that is still true for Excel 2016.  But you might want to double-check yourself.)

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-08-09T11:10:57+00:00

    BTW I have tested XNPV negative discounts in Excel 2016 and they don't work, you get a #NUM error

    0 comments
  2. Anonymous
    2018-08-08T15:23:15+00:00

    Thanks Joue, that's very helpful.

    0 comments
  3. Anonymous
    2018-08-08T14:39:51+00:00

    Vijay wrote:

    NPV doesn't assume this. From Excel help - 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.

    I think you and the article are saying the same thing.  The article says, in effect:  the first cash flow (in the NPV parameters) comes one period (year, in this case) after "today" (the PV date).

    And the OP did what you describe, at least insofar as excluding the first cash flow (B10) from the NPV range.  As I noted, the OP should have added B10 instead of subtracting B9.  But the arithmetic is the same because B10 equals -B9.

    The point is:  Excel NPV applies the discount rate to the first cash flow.  That is, for NPV(B3,C10:L10), the PV of the first cash flow is C10/(1+B3).  Most people would not discount the first cash flow, as if it occurred at the beginning of the first period, not after the first period.

    In other words, the mathematical definition of NPV is usually SUM(CFi/(1+r)^i, for i=0 to n),

    whereas Excel NPV is SUM(CFi/(1+r)^i, for i=1 to n).

    0 comments
  4. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2018-08-08T14:20:03+00:00

    NPV doesn't assume this. From Excel help - 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.

    0 comments