XIRR with Dynamic Ranges

[coldcanuck]

I have managed to hack my way most of my issues myself but I have an outstanding issue that I’m in need of assisance.

This issue deals with using XIRR with dynamic ranges. Each investment is listed in the rows and in my sample spreadsheet I have quarterly investment cash flows in columns K to AM. However, the initial cash flow does not always occur in the first column, K as some of the investments are made in future quarters ( 1Q12, 2Q12, etc.). 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?

 

[coldcanuck]

I have managed to hack my way most of my issues myself but I have an outstanding issue that I’m in need of assisance.

This issue deals with using XIRR with dynamic ranges. Each investment is listed in the rows and in my sample spreadsheet I have quarterly investment cash flows in columns K to AM. However, the initial cash flow does not always occur in the first column, K as some of the investments are made in future quarters ( 1Q12, 2Q12, etc.). 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?

Solved it myself. I needed to use OFFSET to create a range to use in my formula.

=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)