XIRR portfolio performance, Non Continuous Data

[Sachet Parida]

Hi,
I have the investment transactions for 3 Investment folios in the Transaction_Report sheet. I want to automate the calculation of XIRR in the dashboard sheet taking into account the value of investment as on date from dashboard sheet.
The hard way is to take filter the data, sort it and calculate XIRR in separate sheet.
Using this method, I have calculated the required XIRR in XIRR_1, XIRR_2 and XIRR_3 sheets.
Can some help me in a XIRR formula in I3 : I5 (Dashboard sheet) , which gives the same result as J3 : JF.

I have read the solution at https://chandoo.org/forum/threads/xirr-for-non-contiguous-data-solved.7022/ , however this does not help as my data are in different sheets. (The final value of investment has to come from Dashboard Sheet).

Thank You.

 

[bosco_yip]

Try,

In Dashboard sheet I3, array formula (CTRL+SHIFT+ENTER) copied down :

=XIRR(INDEX(Transaction_Report!$J$2:$J$122,N(IF(1,AGGREGATE(15,6,ROW(Transaction_Report!$A$2:$A$122)-ROW(Transaction_Report!$A$1)/(Transaction_Report!$A$2:$A$122=C3),ROW(INDIRECT("1:"&COUNTIF(Transaction_Report!$A$2:$A$122,C3))))))),INDEX(Transaction_Report!$C$2:$C$122,N(IF(1,AGGREGATE(15,6,ROW(Transaction_Report!$A$2:$A$122)-ROW(Transaction_Report!$A$1)/(Transaction_Report!$A$2:$A$122=C3),ROW(INDIRECT("1:"&COUNTIF(Transaction_Report!$A$2:$A$122,C3))))))),12%)

Regards
Bosco

 

[Lori]

For a formula without inserting any extra rows, you could first select the range Transaction_Report!$A$1:$J$117 and choose:
Formulas > Defined Names > Create From Selection > Check Top Row Only > OK.

Then fill down this formula from I3 on the dashboard sheet (non-cse):

=XIRR(INDEX(IF(FREQUENCY(0,-Date),-SUMIF(Fund_Name,C3,Effective_Units)*G3,Effective_Amount),N(IF(1,MODE.MULT(ROW(Date)*{1,-1}^(Fund_Name<>C3)-MIN(ROW(Date))+1,ROWS(Date)+{1,1})))),INDEX(IF(FREQUENCY(0,-Date),E3,Date),N(IF(1,MODE.MULT(ROW(Date)*{1,-1}^(Fund_Name<>C3)-MIN(ROW(Date))+1,ROWS(Date)+{1,1})))))