[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), 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).

solved how to calculate XIRR dynamically in excel and in google sheets