[Solved] how to calculate XIRR dynamically in excel and in google sheets

Question

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), but also, more importantly, non-volatile.

See here for more if you’re interested:

https://excelxor.com/2016/02/16/criteria-with-statistical-functions-growth-linest-logest-trend/

Regards

**Array formulas are not entered in the same way as ‘standard’ formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you’ve done it correctly, you’ll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Accepted Answer