• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR for Non Contiguous Data [SOLVED]

Status
Not open for further replies.
Hi ,

I am sorry , but I don't seem to have the file ; you can see if the OP comes back to this thread , since he will surely have the file.

Narayan
 
I came back to this thread to actually post some updates on the XIRR formula last time.

Previously I needed to find out the XIRR of a series of portfolio transactions i.e. XIRR of the entire portfolio. Now recently we needed to expand this to find out XIRR of all stocks/ asset classes respectively to understand which stocks/ asset classes are driving portfolio performance. One way to do it is to keep filtering the stocks/ asset classes columns and note down the respective performance.

That clearly isn't the best way to do it. So I created 2 new columns - one for stock and one for the asset class in the table and calculated for each stock/ asset class the performance.

So for a data table structured as below,
A B C D E F G
Security - Asset Class - Sub Type - Outflow Date - Outflow Amt - Inflow Date - Inflow Amt

the code for column H (Security XIRR) is

=XIRR(CHOOSE({1,2},OFFSET(E$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)),OFFSET(G$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2))),(CHOOSE({1,2},OFFSET(D$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)),OFFSET(F$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)))))

Notes:
This method works only for sorted entries and does not search through the entire list.
The Match function only works to identify the first instance of the searched term and its respective row, thereafter the countif counts the total number of entries from the identified row above and takes them into account while computing XIRR
It does not Match the searched terms across the entire table and return XIRR of those entries.

The above might be useful for someone with a similar issue hence I have posted the solution I have worked on.

So far so good.

Two issues come up now,
Firstly, Offset is very volatile and any change takes at least 4-8 seconds to reflect on the sheet. So I need to find a way of replacing Offset with Index function or Index-Match function of another less volatile function

Secondly, I need the new formula to search the entire column for matching entries and then use those in the range which will compute the XIRR.
For example,
stock ABC is repeated multiple times in an unsorted table, say 10 rows out of 200 rows. I need the formula to find every instance of the searched term and then create a range which will be used in the XIRR calculation

I would appreciate if someone (Narayan, Haseeb, etc) can help me find the final piece of this puzzle. If you require a sample sheet again, I will create and upload it afresh.


PS
spoofhopper which file do you need? Not sure I have the old files anymore as they were samples created for posting here and deleted thereafter.
 
Hi ,

I think a file with enough data to understand both of your problems ( sluggish response , and criteria based calculation of XIRR ) will be helpful to whoever wishes to try their hand at solving this problem. Please upload a workbook.

Narayan
 
As required, latest sample file is attached. Calculation is set on Manual because it takes too long to respond. So please press F9 after making any changes.
 

Attachments

  • XIRR Workings - Sample.xlsx
    172.3 KB · Views: 73
the code for column H (Security XIRR) is

=XIRR(CHOOSE({1,2},OFFSET(E$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)),OFFSET(G$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2))),(CHOOSE({1,2},OFFSET(D$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)),OFFSET(F$2,MATCH(A2,P[Sec],0)-1,0,COUNTIF(P[Sec],A2)))))

A non-volatile alternative which should also allow for unsorted data....

=XIRR(INDEX(P,N(IF(1,MODE.MULT(IF(P[Sec]=P[@Sec],ROW(P[Sec])*{1,1}))-ROW(P[#Headers]))),N(IF(1,MATCH({"OV","IV"},P[#Headers],0)))),
INDEX(P,N(IF(1,MODE.MULT(IF(P[Sec]=P[@Sec],ROW(P[Sec])*{1,1}))-ROW(P[#Headers]))),N(IF(1,MATCH({"OD","ID"},P[#Headers],0)))))

entered in H2 with Ctrl+Shift+Enter.
 
Wow.. Thanks Lori! Seems to work - will try in it one of the main sheets tomorrow and let you know how it goes.

Never in a million years would I have been able to structure this on my own!o_O

Awesome:):DD

:awesome::awesome::awesome::awesome::awesome:
 
Some extra tweaks can be made to the formula as below to make it:
- much faster to calculate (by looking up previous results in the same column)
- normal entry formula (without CSE)

=IFERROR(VLOOKUP(P[@Sec],P[#Headers]:H1,COUNTA(P[#Headers]:H$1),0),XIRR(INDEX(P,N(IF(1,MODE.MULT(IF(N(INDEX(P[Sec]=A2,)),MMULT(ROW(P)-ROW(P[#Headers]),{1,1}))))),N(IF(1,MATCH({"OV","IV"},P[#Headers],0)))),INDEX(P,N(IF(1,MODE.MULT(IF(N(INDEX(P[Sec]=A2,)),MMULT(ROW(P)-ROW(P[#Headers]),{1,1}))))),N(IF(1,MATCH({"OD","ID"},P[#Headers],0))))))

PS. the [SOLVED] part of the title should probably be removed until the solution has been checked :)
 
... or similar to HaseebA's approach: (also non-CSE and much simpler!)

=IFERROR(VLOOKUP(A2,A$1:H1,COLUMNS(A$1:H1),0),XIRR(CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),E2:E$1207,G2:G$1207,),CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),D2:D$1207,F2:F$1207,)))
 
Lori is on a roll!!

Some extra tweaks can be made to the formula as below to make it:
- much faster to calculate (by looking up previous results in the same column)
- normal entry formula (without CSE)
=IFERROR(VLOOKUP(P[@Sec],P[#Headers]:H1,COUNTA(P[#Headers]:H$1),0),XIRR(INDEX(P,N(IF(1,MODE.MULT(IF(N(INDEX(P[Sec]=A2,)),MMULT(ROW(P)-ROW(P[#Headers]),{1,1}))))),N(IF(1,MATCH({"OV","IV"},P[#Headers],0)))),INDEX(P,N(IF(1,MODE.MULT(IF(N(INDEX(P[Sec]=A2,)),MMULT(ROW(P)-ROW(P[#Headers]),{1,1}))))),N(IF(1,MATCH({"OD","ID"},P[#Headers],0))))))

Works great - a bit resource intensive, but not too much.

... or similar to HaseebA's approach: (also non-CSE and much simpler!)
=IFERROR(VLOOKUP(A2,A$1:H1,COLUMNS(A$1:H1),0),XIRR(CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),E2:E$1207,G2:G$1207,),CHOOSE(3+MMULT(-(A2:A$1207=A2),{2,1}),D2:D$1207,F2:F$1207,)))

Unfortunately, the above is not working error free - or maybe I haven't understood it well enough to implement it correctly.

I'm not sure about which portion of the file is requiring time to save but the file is still taking 5-8 seconds in every change cycle. Anyhow, that's an issue for another day.

Thanks Lori! The "Solved" status is well deserved!
 
On your test file both methods calculated the column in a fraction of a second on my machine so not sure why it's taking longer on your set up.

The basis of the last suggestion is that inserting zero values into the range won't change the result (provided the first value is not zero.) So for example:

=XIRR({-3;0;1;0},{"2013-01-01";"2014-01-01";"2015-01-01";"2016-01-01"})

is the same as:

=XIRR({-3;1},{"2013-01-01";"2015-01-01"})

In the formula dates are zero too but it might be better to leave the date columns and just set values to zero for rows that are not in the same section.

I think it would be worth seeing if you can get this approach to work as it would be more maintainable in the long run...

Glad the first suggestion is working for you in any case and thanks for an interesting challenge!
 
Last edited:
Yes I agree with you - I think there is some other formula which is slowing it down. I just tried it in the test files (without the main files open) and it works superfast. With 6GB of RAM, my pc has no reason to slouch.

Will try again to make the new formula work. It works fine for Security XIRR but in Asset XIRR it goes for a toss - maybe it has something to do with the Security list being sorted A-Z while Asset Class isn't sorted?

Appreciate all the help Lori - maybe will bug you with more interesting problems soon ;)

Thanks!!
 
Use CHOOSE like,


=XIRR(CHOOSE({1,2},L2:L27,N2:N27),CHOOSE({1,2},B2:B27,M2:M27),15%)


I got the answer 18.1%

Many thanks for providing such an easy solution for highly complex calculation.
My data has a column ("Stock Name") where names of stocks are mentioned. Is it possible to extend above formula to choose XIRR for individual stock (sort of a pivot table) based on "Stock Name"?
Regards
 
You ViBee
should open a new thread with sample from Your Excel-file
as You've read from Forum Rules.
You can paste there this threads link too.
This almost ten years old thread is closed.
 
Status
Not open for further replies.
Back
Top