XIRR with dynamic range

Hi,

I work in the investment industry and am trying to calculate the XIRR of our various mutual funds using the dates and the monthly withdrawal amounts that change over time, with the ending amount being the last number from the post-withdrawal column. The issue is that I have to manually adjust the range in the XIRR formula to fit exactly, or else the formula errors. Is there a way to use an XIRR formula that is flexible enough to accommodate the different number of periods without having to manually adjust the range to perfectly fit the number or rows?

Attached in an example with two tabs with a different amount of periods. Basically, the only parameters I need to manually adjust are the initial investment amount, withdrawal %'s, and the monthly return stream. The goal is to be able to dump the number of returns without having to manually adjust the end point of the XIRR formula.

If you have any questions, please let me know! Thanks so much!

=xirr(m2:index(m2:m150,match("",i2:i150,)-1),i2:index(i2:i150,match("",i2:i50,)-1))

Originally Posted by tim201110

=xirr(m2:index(m2:m150,match("",i2:i150,)-1),i2:index(i2:i150,match("",i2:i50,)-1))

YES!!! You rock!!! THX SO MUCH!!!