XIRR with Dynamic Ranges

I have managed to hack my way most of my issues myself but I have two outstanding issues that I’m posting under separate threads which deal with the same spreadsheet.

This issue deals with using XIRR in dynamic ranges. For each investment in my sample spreadsheet I have quarterly investment cash flows in rows K to AM, however the initial cash flow does not always occur in the first column, K. The problem is that whenever the first value in the XIRR function begins with a zero cash flow, XIRR returns either 0% or -100%. So I need a formula to begin the XIRR calculation on the first column with a cash flow. The argument to the XIRR function has to be dynamic as I cannot predict which quarter the cash flow will begin for each investment (other than one investment will not be any earlier than a previous investment).

Any ideas?

Originally Posted by gjlindn

Maybe I'm just getting too tired, but I couldn't come up with a way to do what you're looking for without a UDF. In conjunction with a new function FIRSTNONZERO and the INDIRECT function, I think I have it doing what you're looking for. The file had to be saved as xlsm, though, in order to allow the UDF.

Thanks. I didn't see your response so this morning I tinkered with it and figured that I needed to use OFFSET to create the ranges I needed as arguments for my XIRR function.

=IFERROR(XIRR(OFFSET($K$38,ROW()-38,MATCH($D39,$I$3:$I$31,0)-1,1,F39+1),OFFSET(K$37,0,MATCH($D39,$I$3:$I$31,0)-1,1,F39+1)),-1)