[Solved] how to calculate XIRR dynamically in excel and in google sheets
Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. “B”, is in J2, array formula**: =XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8)))))),TODAY())) Copy down to give similar results for Funds in J3, J4, etc. I tend to prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), … Read more