XIRR returns zero when returns are negative

[gwen1]

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.

 

[joeu2004]

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?

 

[DRSteele]

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
CAN
CAN
CAN
USA
CAN
UK
CAN
CAN
CAN
CAN

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #9BC2E6"]CRITERIA[/TD]
[TD="bgcolor: #DDEBF7"]CAN[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4"]IRR[/TD]
[TD="bgcolor: #A9D08E, align: right"]2.5336%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFD966"]Country[/TD]
[TD="bgcolor: #FFD966"]Cash Flow[/TD]
[TD="bgcolor: #FFD966"]Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] (31,000.00)[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 6,800.00 [/TD]
[TD="align: right"]2/16/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 4,200.00 [/TD]
[TD="align: right"]4/3/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 5,000.00 [/TD]
[TD="align: right"]5/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] (2,000.00)[/TD]
[TD="align: right"]7/4/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 6,000.00 [/TD]
[TD="align: right"]8/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 9,000.00 [/TD]
[TD="align: right"]10/4/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] 4,500.00 [/TD]
[TD="align: right"]11/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"] 4,520.00 [/TD]
[TD="align: right"]1/4/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"] 4,536.00 [/TD]
[TD="align: right"]5/19/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

PS: I forgot to mention....

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

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):

=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:

=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:

=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%.

 

[gwen1]

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.

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):

=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:

=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:

=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%.

 

[gwen1]

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-questions/335762-xirr-2.html

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
CAN
CAN
CAN
USA
CAN
UK
CAN
CAN
CAN
CAN

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #9BC2E6"]CRITERIA[/TD]
[TD="bgcolor: #DDEBF7"]CAN[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4"]IRR[/TD]
[TD="bgcolor: #A9D08E, align: right"]2.5336%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFD966"]Country[/TD]
[TD="bgcolor: #FFD966"]Cash Flow[/TD]
[TD="bgcolor: #FFD966"]Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] (31,000.00)[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 6,800.00 [/TD]
[TD="align: right"]2/16/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 4,200.00 [/TD]
[TD="align: right"]4/3/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 5,000.00 [/TD]
[TD="align: right"]5/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] (2,000.00)[/TD]
[TD="align: right"]7/4/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 6,000.00 [/TD]
[TD="align: right"]8/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 9,000.00 [/TD]
[TD="align: right"]10/4/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] 4,500.00 [/TD]
[TD="align: right"]11/19/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"] 4,520.00 [/TD]
[TD="align: right"]1/4/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"] 4,536.00 [/TD]
[TD="align: right"]5/19/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

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

 

[gwen1]

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.

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

 

[gwen1]

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.

 

[XOR LX]

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;FALSE,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