Problem with XIRR formula

[Caravishah]

Hi,

I'm trying to calculate XIRR for individual bets and at the overall portfolio level in the attached sheet.

My problem in 1 line is - I want to use XIRR formula on filtered data i.e. XIRR should only consider the visible range and not complete range. To see overall performance of a portfolios.

I have tried to work out the XIRR in cell J247 & J249 going through the threads on this website. I'm not sure if it works correctly. Especially if there is a loss (i.e. negative IRR)

Can somebody help me checking and correcting the formula? thanks.

Ravi

 

[GraH - Guido]

Hi Ravi,
Sorry it took me a while to make some sense of your impressive formula in J249. Never had to use XIRR myself. So I'm on rather slippery ice, I admit.
I once had a template for business case evaluation, where XIRR was not used, but replaced by plain maths formulae. I misplaced it, and I do not recall those formulae or their logic. It could have helped here.

I did notice your workbook seems to be in manual calculation mode though, so I started off wrong. The formula does not adjust when filters apply.

When it comes down to using the filtered range only, the dynamic range reference works correctly as I see and understand it. I Ran some formula evaluation on different filters.

The thing I don't get is why you check if L247 - XIRR on the full range, using the subtotal of column G - equals J249, where you refer to the individual realised values. I'm not sure they should return the same. But here I don't have enough insight in the XIRR function and its application.

That formula in L247 returns 1.76% after adjusting it to=XIRR(F5:F247,D5:D247,0,1) like the one used in J247.

My cautious conclusion is that both your formulae are correct. Looking forward to the reply of others, though. Financial functions are on my learning wish list.

 

[Hui]

Your logic is ok
But I suspect the issues is that the Dates Should be in order

Make up a small sample of 3 dates and values and test it yourself

 

[Caravishah]

Your logic is ok
But I suspect the issues is that the Dates Should be in order

Make up a small sample of 3 dates and values and test it yourself

Thanks Hui. Logic does not work for -ve IRR. Eg: Filter portfolio "I" and the XIRR is 0% which looks wrong. All bets there have gone terribly wrong.

Re Dates, How do we fix that? The dates may or may not be in order.

 

[Hui]

Sort by dates

 

[Lori]

For the single period annualized return an alternative to XIRR(F5:G5,D5:E5) is:

=(-G5/F5)^(1/YEARFRAC(D5,E5,3))-1

For the filtered data you could try this with CTRL+SHIFT+ENTER which autosorts dates:

=XIRR(IFERROR(LARGE(F5:G246,MOD(AGGREGATE(15,6,INDEX(D5:E246*10^6+RANK(F5:G246,F5:G246),AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),C5:C246*2-{1,0}),10^6)),),IFERROR(AGGREGATE(15,1,D5:E246,C5:C246*2-{1,0}),))

 

[GraH - Guido]

9 distinct functions nested..., WTF?

 

[Lori]

9 distinct functions nested..., WTF?

@GraH Guido

I did spend some time trying to find an easier way but failed

If dates were unique a simple SUMIF function for values based off the array of dates would suffice.

Instead, due to the repeated dates, the formula works off the row index and a date/value rank combo to retrieve values corresponding to sorted dates.

The result still requires the CSE keystroke but i did manage to arrive at a non-volatile version at least.

 

[GraH - Guido]

I'm in owe when I see formulae like that, dear Lori. It is beyond my mental capacity to "visualize" what's going on. I need a lot of F9 to understand each part. So thanks a ton for sharing some inner insight. The fact the formula is non-volatile is already impressive to say the least.
I can't imagine being able to write them one day. I had a colleague who stated a few year back that I play champions league - haha - then you and some others here really do play at the intergalactic stage.

 

[Caravishah]

For the single period annualized return an alternative to XIRR(F5:G5,D5:E5) is:

=(-G5/F5)^(1/YEARFRAC(D5,E5,3))-1

For the filtered data you could try this with CTRL+SHIFT+ENTER which autosorts dates:

=XIRR(IFERROR(LARGE(F5:G246,MOD(AGGREGATE(15,6,INDEX(D5:E246*10^6+RANK(F5:G246,F5:G246),AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),C5:C246*2-{1,0}),10^6)),),IFERROR(AGGREGATE(15,1,D5:E246,C5:C246*2-{1,0}),))

Thanks lori. While the above works when filtered on portfolio I, it does not for portfolio U. XIRR of 0%. Is it possible for you to double check? I don't mind having a volatile version if that's required to solve the problem.

 

[Caravishah]

Thanks everyone for your effort.

 

[Lori]

Thanks lori. While the above works when filtered on portfolio I, it does not for portfolio U. XIRR of 0%. Is it possible for you to double check? I don't mind having a volatile version if that's required to solve the problem.

Looks like it's a deficiency in the method used by the XIRR function as you can check by copying the data from portfolio U to a new sheet and sorting dates manually.

XNPV also does not work with this data, however we can use the formula shown in the help on XIRR together with Goal Seek to find the value of -15.17% for portfolio U - see attached...

So to summarise: if XIRR fails, try sorting dates, and if that still fails, use goal seek.

PS. I just noticed you can also set the "guess" argument of XIRR to a negative number like -0.1 and it returns the same value as above
​

 

[Lori]

this version also seems ok with "guess" based off net value,

=XIRR(IFERROR(INDEX(F5:G246,AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),),IFERROR(INDEX(D5:E246,AGGREGATE(15,1,C5:C246,IF(1,+C5:C246)),N(IF(1,{1,2}))),),IF(SUBTOTAL(9,F5:G246)>0,0.1,-0.1))