Share via

Hi! Why do npv and xnpv give different results even if the periods are identical?

Anonymous
2011-04-03T17:28:09+00:00

Hi!

I've got this little spreadsheet:

Interest    3,00%

NPV(B1;B6:B16)     100,00 €

XNPV(B1;B6:B16;A6:A16)     102,98 €

Dates    Payments

01.01.2011     3,00 €

01.01.2012     3,00 €

01.01.2013     3,00 €

01.01.2014     3,00 €

01.01.2015     3,00 €

01.01.2016     3,00 €

01.01.2017     3,00 €

01.01.2018     3,00 €

01.01.2019     3,00 €

01.01.2020     3,00 €

01.01.2021     103,00 €

Can anybody tell me why the results of npv and xnpv differ?

Lots of Greetings!

Volker

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
    2011-04-03T18:43:01+00:00

    Volker wrote:

    Interest    3,00%

    NPV(B1;B6:B16)     100,00 €

    XNPV(B1;B6:B16;A6:A16)     102,98 €

    [....]   

    Can anybody tell me why the results of npv and xnpv differ?

    There are really are two reasons.

    First and foremost, you are misusing NPV if B6 is the first cash flow, as your use of XNPV suggests.

    As the NPV help page states:  "The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list.   [....] 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".

    That is why there is so much difference in your example.

    Secondly, even if you correct that to be B6+NPV(B1,B7:B16), there will still be some difference between NPV (103.00) and XNPV(102.98).

    That is because XNPV uses the actual date differences, which are 366 instead of 365 in some instances in your example, whereas NPV treats all periods as equal.

    One final note:  In your example, NPV and XNPV are close only because your NPV periods are one year each.  If instead your periods were monthly, there would be a significant difference between NPV (103.00) and XNPV(130.17) because NPV assumes that B1 is a periodic rate, whereas XNPV assumes that B1 is an annual rate.

    20+ people found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-03T20:04:50+00:00

    If you look at the documentation for the 2 functions, the following stand out...

    The periods are not the same.  XNPV takes into account the exact differences in *days*.  So, when you have leap years, the durations between the 2 formulas becomes slightly different.

    Also, XNPV assumes the payments are at the beginning of a period whereas NPV assumes the payments are at the end.

    Finally, XNPV assumes it is given the annual rate and it computes a daily rate using the formula (1+R)^(1/365)-1, whereas NPV uses the rate given to it "as is."

    So, you could redo your example as follows.

    Suppose the annual rate (0.03) is in B1.  In C1 compute the daily rate using the same method that XNPV uses with the formula =(1+B1)^(1/365)-1

    Suppose you have the dates and payments as below in B3:C13.  I've conveniently avoided the leap year issue.

    1/1/2011 3
    1/2/2011 3
    1/3/2011 3
    1/4/2011 3
    1/5/2011 3
    1/6/2011 3
    1/7/2011 3
    1/8/2011 3
    1/9/2011 3
    1/10/2011 3
    1/11/2011 103

    Also, add in B2:C2 add an additional date at the beginning of the actual dates:

    12/31/2010 0

    Now, calculate the NPV with =NPV(C1,C3:C13) and the XNPV with =XNPV(B1,C2:C13,B2:B13).

    The two results will be

    132.8949 132.8949

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2011-04-03T18:09:56+00:00

    It all has to do with WHEN the payment is made. A quote from Help show that NVP assume cash flow is at end of a period while XNVP occurs on the actual date. best wishes

    QUOTE:

    NPV(rate, value1, [value2], …) Determine the net present value using cash flows that occur at regular intervals, such as monthly or annually. Each cash flow, specified as a value, occurs at the end of a period. <br><br><br>If there is an additional cash flow at the start of the first period, it should be added to the value returned by the NPV function. See Example 2 in the<br>NPV Help topic.
    XNPV(rate, values, dates) Determine the net present value using cash flows that occur at irregular intervals. Each cash flow, specified as a value, occurs at a scheduled payment date.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-01-21T05:02:51+00:00

    It is best to start a new thread, rather than append to a 5-year-old discussion, especially when the topic is very different.  This thread discusses differences between NPV and XNPV.  Your question is about differences between IRR and XIRR.

    We can only speculate about the difference.  IRR and XIRR are physically different implementations; infinitesimal differences can arise simply due to the order of arithmetic operations.  But the algorithms might also be very different.  Frankly, I'm surprised the difference is as small as it is.

    [EDIT] Differences due to order of operations.  For example, (a+b)/c is often not the same as a/c + b/c.  Another example:  a-b+c is often not the same as a+c-b.  The anomalies arise due to limitations of 64-bit binary floating-point representation.

    Differences in algorithms.  You might not know:  IRR usually cannot be calculated algebraically.  Instead, it is calculated by iterating over converging approximations.  There are several different algorithms; and different ways to implement the same algorithm.

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2011-04-03T18:49:30+00:00

    Hi!

    Still working on it. I now got a sheet that shows at least the same values: 

    Interest    3,00%   

    NPV(B1;B7:B17)    100,00 €   

    XNPV(B1;B6:B17;A6:A17)     100,00 €    

    Dates    Payments   

    01.01.2011     -   €     <-for XNPV only

    01.01.2012     3,00 €     <-x+365 days

    31.12.2012     3,00 €     <-x+365 days

    31.12.2013     3,00 €     <-x+365 days

    31.12.2014     3,00 €     <-x+365 days

    31.12.2015     3,00 €     <-x+365 days

    30.12.2016     3,00 €     <-x+365 days

    30.12.2017     3,00 €     <-x+365 days

    30.12.2018     3,00 €     <-x+365 days

    30.12.2019     3,00 €     <-x+365 days

    29.12.2020     3,00 €     <-x+365 days

    29.12.2021     103,00 €     <-x+365 days

    I understand the year vs 365days stuff, the yearly period was chosen to avoid massing things up with effective vs nominal interest.

    So, npv was correct (first cash flow occurs at end of period) and I needed to add a value/date pair for xnpv in order to push the payment to the end of a npv like period.

    Thanks both of you for your help!

    1 person found this answer helpful.
    0 comments