spoofhopper
New Member
Hi - could someone please repost the workbook so I can try to understand Haseeb's solution?
XIRR for Non Contiguous Data [SOLVED]
- Thread starter juzzvinay
- Start date
- Status
NARAYANK991
Excel Ninja
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 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.
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.
NARAYANK991
Excel Ninja
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
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
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.
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
- 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
Lori is on a roll!!
Works great - a bit resource intensive, but not too much.
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!
Works great - a bit resource intensive, but not too much.
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!
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!!
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!!
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
- Status