Name in M5, Fund Name in N5, then Annualised Return in O5
=XIRR(IFERROR(INDEX(IF(ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))>ROWS(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5)),INDEX(Table110[Total Current Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))-ROWS(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5))),INDEX(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2)))),SMALL(IF(IF(ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))>ROWS(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5)),INDEX(Table110[Total Current Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))-ROWS(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5))),INDEX(-Table110[Total Purchase Value]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))))<>0,ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2)))),0),IFERROR(INDEX(IF(ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))>ROWS(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5)),INDEX(Table110[Current Price Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))-ROWS(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5))),INDEX(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2)))),SMALL(IF(IF(ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))>ROWS(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5)),INDEX(Table110[Current Price Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))-ROWS(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5))),INDEX(Table110[Purchase Date]*(Table110[Name]=$M5)*(Table110[Fund Name]=$N5),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))))<>0,ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2))),ROW(INDIRECT("1:"&COUNTA(Table110[Name])*2)))),0),0.1)
Bookmarks