Portfolio XIRR calculation for non contiguous data using criteria matching

[ultimatefind17]

I have been an long user of this forum - generally most of the issues faced by me are already solved here. However, I haven't been able to fully solve my current problem despite going through multiple posts here.

First, the background
I am using Excel 2010, Windows 8. I am working on a data table which has a long list of portfolio transactions for each client from my office. On an average there are about 800 entries per client. All data tables are structured in the exact same way. Each transaction in particular security is recorded separately, so there are multiple entries of the same Security and obviously multiple entries of Asset Class as well.

Columns
A - B - C - D - E - F - G - H - I
Security - Asset Class - Sub Type - Outflow Date (OD) - Outflow Value (OV) - Inflow Date (ID) - Inflow Value (IV) - Security XIRR - Asset Class XIRR

Rows
ABC - Equity - Preference/ Ordinary etc
DEF - Debt - Sovereign/ Muni/ Unsecured etc
GHI - etc
...
...
XYZ

The data is sorted alphabetically (Column A).
Currently the column H 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)))))

where P[Sec] is the column Sec in table P.
The above formula is missing the guess portion for the sake of brevity only.
A2 is the security name which needs to be checked against the entire column A for repeating entries and then Offset creates the range which is used as the input for XIRR.

However, this method works only for sorted entries and does not search through the entire list. So while the above works for my security XIRR (as long as the column is sorted), it doesnt work for calculating XIRR of a particular asset class or any other criteria for evaluating performance.
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.


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 since there are on an average 800 calculations of just the Security XIRR in a single data table


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 really appreciate if someone can suggest the best way to calculate XIRR which is fast and takes into account my requirements within this structure.

I have used
{=XIRR(CHOOSE({1,2},IF(P[Sec]=P[@Sec],P[OV],0),IF(P[Sec]=P[@Sec],P[IV],-POWER(0.001,50))),(CHOOSE({1,2},IF(P[Sec]=P[@Sec],P[OD],0),IF(P[Sec]=P[@Sec],P[ID],0))))}
but it only works for first condition as I think XIRR needs first transaction to be non zero

Many others as well, from the references below.

I realise XIRR is buggy and many times it doesnt provide the correct answer (the infamous 0.000000298 error which displays incorrect XIRR), so if there is a VBA method for reliable XIRR I would be happy to implement that - since I am new to VBA would appreciate simple instructions.

References used so far
http://www.mrexcel.com/forum/excel-questions/709438-xirr-dynamic-range.html

http://www.mrexcel.com/forum/excel-...rray-return-non-continguous-values-dates.html

http://www.mrexcel.com/forum/excel-questions/505660-conditional-xirr.html

http://www.mrexcel.com/forum/excel-questions/335762-xirr-2.html

http://www.mrexcel.com/forum/excel-questions/708616-xirr-only-visible-cells-filtered-data.html

excel - Dynamically construct range to use in XIRR formula - Stack Overflow

How to replace Offset() with non-volatile formulas?

Excel 2007 : Using XIRR with Dynamic Ranges

XIRR for Non Contiguous Data [SOLVED] | Chandoo.org Excel Forums - Become Awesome in Excel

 

[sijpie]

This might help with changing from OFFSET to INDEX
Left Lookup with Index, Match and Offset Excel Functions

 

[Awkuthepap]

I needed to do the same to calculate the return on stocks in my investment portfolio,
I use Google Sheets to do so, however you can do this as well in Excel.

I have all stock market transactions in sheet P. Row 1 has column headers, records are as of row 2. Column G contains the Stock Names.

Step 1: extract a list of stock names. I do this in sheet S by putting this formula in cells A1:A21 (if you have more than 20 different stock names you need to fill down more cells).
=ArrayFormula(INDEX(P!G:G,MATCH(0,COUNTIF(A$1:A1,P!G:G),0)))

Step 2:
In sheet P, transaction amounts are in column M and transaction dates in column F
In cell S in cell B2 enter following formula
=ArrayFormula(if($A3="","",xirr(if(P!G:G=A3,P!M:M),if(P!G:G=A3,P!F:F),0.01)))
Copy and paste this formula in cells B3:B21
If the formula shows an error, change the xirr feed from 0.01 to something else.

Note: Sheet P contains the full history of transactions in sheet P. No need to sort. For each stock I own today, I had to add a record to sheet P with the current date and current value of that stock in my portfolio to reflect the sale transactions of the stocks that I own today.