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.

 

[gwen1]

thanks a million.

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

 

[DRSteele]

I hate to muddy the waters here, but I'm confused.

The formula I created above works in my small sample (as does that from XOR LX), but when I apply it to gewn1's data it fails. XOR LX, can you please illuminate things for me?

 

[XOR LX]

I hate to muddy the waters here, but I'm confused.

The formula I created above works in my small sample (as does that from XOR LX), but when I apply it to gewn1's data it fails. XOR LX, can you please illuminate things for me?

Because you chose to employ a "product" version for your criteria, rather than IF statements, it means that the resulting array passed to XIRR contains zeroes for any row which does not meet the criteria. And of course zeroes are perfectly valid values to pass to XIRR, although of course you were most likely hoping that they would not be considered at all.

This is one of the reasons why I tend to always prefer to construct conditional statements within array formulas using multiple IF functions, rather than the "product" version that you chose, since IF statements always return Boolean FALSEs if the criteria are not met, not zeroes. And Boolean FALSEs are ignored by the vast majority of functions, whereas zeroes are not.

So, for the data you posted:

=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)

becomes:

=XIRR({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}*B$4:B$13,$C$4:$C$13)

and then this becomes, after multiplication:

=XIRR({-31000;6800;4200;0;-2000;0;9000;4500;4520;4536},$C$4:$C$13)

which, although not erroring, nevertheless produces an incorrect result, thanks to the presence of those zeroes in the first array.

Unfortunately, we cannot correct this even by using the equivalent IF set-up, since:

=XIRR(IF($A$4:$A$13=B$1,B$4:B$13),$C$4:$C$13)

which is:

=XIRR({-31000;6800;4200;FALSE;-2000;FALSE;9000;4500;4520;4536},$C$4:$C$13)

and although we no longer have the issue of numerical zeroes being present, which would normally resolve the issue, nevertheless returns a #VALUE! error.

In fact, this will also be the case with most financial functions, which, for whatever reason, cannot handle Boolean FALSEs either. Hence the need to redimension the array after the conditional statement so that it consists of numerical values only.

Regards

 

[joeu2004]

And of course zeroes are perfectly valid values to pass to XIRR

That is not entirely correct. There is nothing wrong with interstitial zero values.

But Excel XIRR has a problem with initial zero values, as demonstrated below.

So, for the data you posted:

=XIRR(($A$4:$A$13=B$1)*B$4:B$13,$C$4:$C$13)
[....]
becomes, after multiplication:

=XIRR({-31000;6800;4200;0;-2000;0;9000;4500;4520;4536},$C$4:$C$13)

which, although not erroring, nevertheless produces an incorrect result, thanks to the presence of those zeroes in the first array.

The latter formula returns the correct IRR, as demonstrated by the table below.

The problem arises when we use the same paradigm with gwen1's data, not because of the product v. IF expression, but because Excel XIRR does not handle leading zero values correctly, even when the date matches the date of the initial non-zero value date.

	A	B	C	D	E
1		0	1/1/2014	2.9802E-09	D1: =XIRR(B1:B11,C1:C11)
2	CAN	-31000	1/1/2014	2.5336%	D2: =XIRR(B2:B11,C2:C11)
3	CAN	6800	2/16/2014	2.5336%	D3: =XIRR({-31000;6800;4200;0;-2000;0;9000;4500;4520;4536},C2:C11)
4	CAN	4200	4/3/2014
5	USA	0	5/19/2014
6	CAN	-2000	7/4/2014
7	UK	0	8/19/2014
8	CAN	9000	10/4/2014
9	CAN	4500	11/19/2014
10	CAN	4520	1/4/2015
11	CAN	4536	5/19/2015
12
13	CAN	-31000	1/1/2014	2.5336%	D13: =XIRR(B13:B20,C13:C20)
14	CAN	6800	2/16/2014	TRUE	D14: =D2-D13=0
15	CAN	4200	4/3/2014	TRUE	D15: =D3-D13=0
16	CAN	-2000	7/4/2014
17	CAN	9000	10/4/2014
18	CAN	4500	11/19/2014
19	CAN	4520	1/4/2015
20	CAN	4536	5/19/2015

<tbody>
</tbody>

The construction in rows 13:20 constains the minimum data for the XIRR formula.

As demonstrated in D14 and D15, the XIRR formulas in D2 and D3, based on only rows 2:11, produce exactly the same binary result.

The error arises in the XIRR formula in D1, which is based on rows 1:11. Even though row 1 has the same date as row 2, Excel XIRR returns about 2.98E-9, which seems to be a constant in the algorithm or the lowest limit of its internal calculations.

It has never been clear why Excel XIRR does not work with an initial cash flow of zero. We can derive the correct IRR with the same data using Solver and a SUMPRODUCT formula to calculate the NPV.

Arguably, one problem might be the change in sign: zero v. negative. But Excel XIRR continues to return 2.98E-9 even if we reverse the signs of all of the other values.

As gwen1 learned from an earlier discussion, Excel XIRR will return about the same (if not the same) IRR if we replace the initial zero with -1E-50.

At least, that works with DRSteele's example.

It does not work with gwen1's data, even if I reduce it to the minimum. I am still experimenting to see if I can intuit why not.

Be that as it may, using -1E-50 instead of zero is a poor work-around for the reasons I stated previously, to wit: the date of the "present value" is incorrect.

XOR LX formula using MODE.MULT is a clever way to ensure that only the intended values are included in the IRR calculation.

 

[XOR LX]

@joeu2004

I stand corrected. Excellent and enlightening post, and clearly this function requires further investigation.

Regards

 

[longjohn2]

Awesome - just what I was looking for. Unfortunately, this doesn't seem to work for me. I checked all the individual steps and here is where it goes wrong:

Sure.
...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}

In my file, the "FALSES" are not duplicated, e.g. I get:
{FALSE;5,5;FALSE;FALSE;FALSE;FALSE;10,10;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;19,19;FALSE}

I'm not quite sure why I do get a different result. It may be worth noting that I had to translate the formula into German, where ";" and "," are at times switched and other times not...

Do you know what my problem might be? If you could provide me with a working Excel file, I could also just see if it works if I open it on my computer with a German language Excel.

Thanks a ton!

 

[longjohn2]

Anyone? You could do me a huge favor just by uploading an Excel file with working formulas... A 3 minute job for a bored internet junkie

 

[XOR LX]

Awesome - just what I was looking for. Unfortunately, this doesn't seem to work for me. I checked all the individual steps and here is where it goes wrong:



In my file, the "FALSES" are not duplicated, e.g. I get:
{FALSE;5,5;FALSE;FALSE;FALSE;FALSE;10,10;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;19,19;FALSE}

I'm not quite sure why I do get a different result. It may be worth noting that I had to translate the formula into German, where ";" and "," are at times switched and other times not...

Do you know what my problem might be? If you could provide me with a working Excel file, I could also just see if it works if I open it on my computer with a German language Excel.

Thanks a ton!

On your system, since I assume the comma represents a decimal separator, the intended array constant comprising two values, i.e.:

{1,1}

will instead be interpreted as the single value equivalent to the fraction one and one tenth.

I can't quite recall what you should instead use on your system to represent a column-separator. Perhaps "\", i.e.:

{1\1}

though I'm afraid I can't be sure. Perhaps you can test and/or research a bit and get back to me?

Regards

 

[longjohn2]

Thanks for the advice... {1.1} indeed worked. Awesome!!!

 

[XOR LX]

Ah, yes. Now I remember.

Great!

Cheers