Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: XIRR returns zero when returns are negative
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default XIRR returns zero when returns are negative

    Hi,

    I am really struggling with the XIRR function at the moment. When the return is negative, the XIRR function returns zero.

    I paste the example below:

    =XIRR(IF($E$4:$E$1453=$J5,$H$4:$H$1453,-POWER(0.1,50)),$G$4:$G$1453) where column E is a country parameter which gets chosen depending which country I am looking at, H column is cashflows and G column is dates. I am trying to calculate the pooled IRR. So for certain countries when I take the data from the table and manually calculate the IRR, it returns a negative IRR, but the formula above returns zero. I do not know how to fix this and yet calculating this manually is too painful as I have a lot of data.

    Really appreciate your help.

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,427
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    Quote Originally Posted by gwen1 View Post
    I am really struggling with the XIRR function at the moment. When the return is negative, the XIRR function returns zero. I paste the example below:
    =XIRR(IF($E$4:$E$1453=$J5,$H$4:$H$1453,-POWER(0.1,50)),$G$4:$G$1453)
    First, XIRR does not always return zero when a negative IRR is expected. For example,
    =XIRR({-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})
    returns about -0.926. (Dates are in the form m/d/yyyy.)

    So unless someone gets lucky with a wild guess, we cannot help you unless you provide some sample data that demonstrates the problem.

    Obviously, you should not post 1452 lines of data. Instead, upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here.

    Hint: If E4:E1453 and J5 contain proprietary names, set E4:E1453 to the letter A, set J5 to the letter B, then change appropriate rows in E4:E1453 to the letter B in order to duplicate the problem.

    Second, the design of your formula is flawed, although it might seem to work by coincidence.

    The IRR is always be calculated with the date in G4 as the "present value". Even if that corresponds to the near-zero values (-1E-50) -- another dubious practice -- it will misrepresent the time over which legitimate values are discounted.

    This may or may not be fixable easily. Again, we need to see the data.

    An alternative might be to use a VBA user-defined function. Would that be acceptable?
    Last edited by joeu2004; Oct 20th, 2015 at 05:50 PM. Reason: Hint

  3. #3
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,984
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    I can't deduce what that POWER function is doing in there. You should note that what is required here is a 'function argument array operation' for the XIRR function.


    This works, I believe:

    A B C D E
    1 CRITERIA CAN IRR 2.5336%
    2
    3 Country Cash Flow Date
    4 CAN (31,000.00) 1/1/2014
    5 CAN 6,800.00 2/16/2014
    6 CAN 4,200.00 4/3/2014
    7 USA 5,000.00 5/19/2014
    8 CAN (2,000.00) 7/4/2014
    9 UK 6,000.00 8/19/2014
    10 CAN 9,000.00 10/4/2014
    11 CAN 4,500.00 11/19/2014
    12 CAN 4,520.00 1/4/2015
    13 CAN 4,536.00 5/19/2015
    Sheet2

    Array Formulas
    Cell Formula
    E1 {=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,427
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    PS: I forgot to mention....
    Quote Originally Posted by gwen1 View Post
    I am really struggling with the XIRR function at the moment. When the return is negative, the XIRR function returns zero. [....] So for certain countries when I take the data from the table and manually calculate the IRR, it returns a negative IRR
    Quote Originally Posted by joeu2004 View Post
    unless someone gets lucky with a wild guess, we cannot help you unless you provide some sample data that demonstrates the problem.
    1. It might also be helpful if you "show your work" for the manual calculation that you believe returns the correct negative IRR. That is, show the data (values and dates) and formula that you use.


    2. In your examples, Excel XIRR might return negative IRRs, but they only appear to be zero due to cell formatting. For example, -0.001% appears to be 0.00% when the cell is formatted as Percentage with 2 decimal places.

    To avoid msunderstandings, I suggest that you format the cell as Scientific with 14 decimal places.

    Nevertheless, those negative value still might not be the negative IRRs that you expect, based on manual calculations.


    3. As you might know, Excel XNPV always returns an Excel error when the discount value (first parameter) is negative. It is a defect, IMHO.

    So use SUMPRODUCT to verify that the value returned by Excel XIRR causes the NPV to be "close" to zero relative to the magnitude of the cash flows. Note:It is usually prudent to do this check, because Excel XIRR occassionally returns a completely bogus numerical value instead an Excel error when Excel XIRR cannot find a value that causes the NPV to be "close" to zero within its implementation limitations.

    Consider my example (again, dates are in the form m/d/yyyy):
    Code:
    =XIRR({-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})
    returns about -92.6186835020781%. But is that a valid IRR?

    If that formula is in A1, note that the following incorrectly return a #NUM error:
    Code:
    =XNPV(A1,{-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})
    But the following formula in B1 returns about -0.00013585, which is "relatively close" to zero:
    Code:
    =SUMPRODUCT({-10000,1000,2000,3000}/(1+A1)^(({"1/1/2015","2/1/2015","3/1/2015","4/1/2015"}-"1/1/2015")/365))
    Aside.... If we change A1 to C1 in that formula, and we copy the value of A1 into C1, Goal Seek can drive B1 down to about -1.818989E-12, truly close to zero, by setting the value in C1 to -92.6186840021697%. A1 and C1 differ only by about 0.0000005%.

    Last edited by joeu2004; Oct 20th, 2015 at 06:49 PM. Reason: cosmetic

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    Thank you all. I just uploaded the file on the shared drive. the link is below. perhaps you are right and the function itself is wrong. I am not that very familiar with array functions, it seems to work for positive returns. Please see the excel and let me know what you think.

    https://app.box.com/s/0l4p73xx80oxizz9z1pqoauneumh7r7z

    Many thanks in advance.



    Quote Originally Posted by joeu2004 View Post
    PS: I forgot to mention....




    1. It might also be helpful if you "show your work" for the manual calculation that you believe returns the correct negative IRR. That is, show the data (values and dates) and formula that you use.


    2. In your examples, Excel XIRR might return negative IRRs, but they only appear to be zero due to cell formatting. For example, -0.001% appears to be 0.00% when the cell is formatted as Percentage with 2 decimal places.

    To avoid msunderstandings, I suggest that you format the cell as Scientific with 14 decimal places.

    Nevertheless, those negative value still might not be the negative IRRs that you expect, based on manual calculations.


    3. As you might know, Excel XNPV always returns an Excel error when the discount value (first parameter) is negative. It is a defect, IMHO.

    So use SUMPRODUCT to verify that the value returned by Excel XIRR causes the NPV to be "close" to zero relative to the magnitude of the cash flows. Note:It is usually prudent to do this check, because Excel XIRR occassionally returns a completely bogus numerical value instead an Excel error when Excel XIRR cannot find a value that causes the NPV to be "close" to zero within its implementation limitations.

    Consider my example (again, dates are in the form m/d/yyyy):
    Code:
    =XIRR({-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})
    returns about -92.6186835020781%. But is that a valid IRR?

    If that formula is in A1, note that the following incorrectly return a #NUM error:
    Code:
    =XNPV(A1,{-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})
    But the following formula in B1 returns about -0.00013585, which is "relatively close" to zero:
    Code:
    =SUMPRODUCT({-10000,1000,2000,3000}/(1+A1)^(({"1/1/2015","2/1/2015","3/1/2015","4/1/2015"}-"1/1/2015")/365))
    Aside.... If we change A1 to C1 in that formula, and we copy the value of A1 into C1, Goal Seek can drive B1 down to about -1.818989E-12, truly close to zero, by setting the value in C1 to -92.6186840021697%. A1 and C1 differ only by about 0.0000005%.


  6. #6
    New Member
    Join Date
    Oct 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    I read somewhere on on this forum that XIRR doesn't work properly when the cashflows are zero, hence, they suggested this power function to make it work. I just adopted it. the link below where I read about the power function.

    http://www.mrexcel.com/forum/excel-q...62-xirr-2.html

    Quote Originally Posted by DRSteele View Post
    I can't deduce what that POWER function is doing in there. You should note that what is required here is a 'function argument array operation' for the XIRR function.


    This works, I believe:

    A B C D E
    1 CRITERIA CAN IRR 2.5336%
    2
    3 Country Cash Flow Date
    4 CAN (31,000.00) 1/1/2014
    5 CAN 6,800.00 2/16/2014
    6 CAN 4,200.00 4/3/2014
    7 USA 5,000.00 5/19/2014
    8 CAN (2,000.00) 7/4/2014
    9 UK 6,000.00 8/19/2014
    10 CAN 9,000.00 10/4/2014
    11 CAN 4,500.00 11/19/2014
    12 CAN 4,520.00 1/4/2015
    13 CAN 4,536.00 5/19/2015
    Sheet2

    Array Formulas
    Cell Formula
    E1 {=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  7. #7
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    Hi.

    You need to reduce the array of values to be considered to only those which match your criterion.

    In K5, array formula**:

    =XIRR(INDEX(H:H,N(IF(1,MODE.MULT(IF(E$4:E$1453=J5,{1,1}*ROW(E$4:E$1453)))))),INDEX(G:G,N(IF(1,MODE.MULT(IF(E$4:E$1453=J5 ,{1,1}*ROW(E$4:E$1453)))))))

    Copy down as required.


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    New Member
    Join Date
    Oct 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    Thank you so much. It works. thanks a lot you saved me. I don't quite understand this function that well especially mode.mult and row but it works.

    many thanks again.

    Quote Originally Posted by XOR LX View Post
    Hi.

    You need to reduce the array of values to be considered to only those which match your criterion.

    In K5, array formula**:

    =XIRR(INDEX(H:H,N(IF(1,MODE.MULT(IF(E$4:E$1453=J5,{1,1}*ROW(E$4:E$1453)))))),INDEX(G:G,N(IF(1,MODE.MULT(IF(E$4:E$1453=J5 ,{1,1}*ROW(E$4:E$1453)))))))

    Copy down as required.


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  9. #9
    New Member
    Join Date
    Oct 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    when you get a chance could you please explain the function especially N(IF(1,MODE.MULT(IF(E$4:E$1453=J5,{1,1}*ROW(E$4:E$1453)))) so that in case I need to replicate this I can also do it. thank you in advance.

  10. #10
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: XIRR returns zero when returns are negative

    Sure.

    To make it easier to explain, using the workbook you linked let's change the entry in J5 from "Kazakhstan" to "Poland", and also amend the upper row reference in the formula to just 20, i.e. using, in K5:

    =XIRR(INDEX(H:H,N(IF(1,MODE.MULT(IF(E$4:E$20=J5,{1,1}*ROW(E$4:E$20)))))),INDEX(G:G,N(IF(1,MODE.MULT(IF(E$4:E$20=J5,{1,1} *ROW(E$4:E$20)))))))

    This part:

    E$4:E$20=J5

    simply returns an array of Boolean TRUE/FALSE values as to whether each entry within that range is equal to the value in J5, i.e. "Poland", and so resolves to:

    {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

    Since we are interested in only those rows where this is TRUE, we construct:

    IF(E$4:E$20=J5,{1,1}*ROW(E$4:E$20))

    which is:

    IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE},{1,1}*{4;5;6;7;8 ;9;10;11;12;13;14;15;16;17;18;19;20})

    When we multiply the column-vector array of row numbers by the orthogonal row-vector array {1,1}, we effectively duplicate all those row numbers, the above becoming:

    IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE},{4,4;5,5;6,6;7,7 ;8,8;9,9;10,10;11,11;12,12;13,13;14,14;15,15;16,16;17,17;18,18;19,19;20,20})

    which is then, resolving the IF statement:

    {FALSE,FALSE;5,5;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;10,10;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;F ALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;19,19;FALSE,FALSE}

    The reason for the duplication becomes clear when we now pass this array to MODE.MULT, a function which returns an array of the most frequently occurring values within a range and which also ignores Boolean TRUE/FALSE values. Unfortunately, however, it also returns an error if no one value within that range occurs more than once.

    Hence the "artificial" doubling of our values, which ensures that our array is of a valid form to pass to MODE.MULT, the above resolving to:

    {5;10;19}

    i.e. an array of three values, those values being precisely the row numbers corresponding to an entry in column E of "Poland". And, importantly, no extraneous entries surrounding those values.

    We then pass this array to INDEX, such that:

    INDEX(H:H,N(IF(1,MODE.MULT(IF(E$4:E$20=J5,{1,1}*ROW(E$4:E$20))))))

    which is:

    INDEX(H:H,N(IF(1,{5;10;19})))

    and which, thanks to the necessary coercion leant by the construction N(IF(1 (see here if interested: INDEX: Returning an array of values EXCELXOR), resolves to:

    {-848.79;-7144699.21;7465548}

    as desired.

    A similar logic for XIRR's dates parameter.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •