XIRR with Variable Ranges and Non Contiguous Data

[mrfourpointo]

I'm Googling in circles today trying to figure out a solution to the attached. In column H, I am trying to calculate the IRR (using XIRR) if the Future Inflow (B3) were to be received on the date for each row. I can run the XIRR using offsets, but it doesn't bring in the final future inflow because it is not inline with the other cashflows and dates. The "Years" column (column G) is servicing as a reference for the offset formulas I've been toying with. The example formula in H9 is missing the necessary inflow from B3. In my mind, I am trying to create a formula that does the following: =XIRR((OFFSET($F$8,0,0,G9,1)~AND THE ONE VALUE THAT IS LOCATED IN B3~),OFFSET($E$8,0,0,G9,1)~AND THE ONE DATE THAT IS LOCATED IN THIS SAME ROW~,0.1). Any help is greatly appreciated.

[MrShorty]

Perhaps it is just my biases (towards using helper ranges), but I have always felt that the easiest solution to this kind of problem is to get the needed data into a contiguous range. So, here's how I approached this problem:

1) Select G8:G38-> Ctrl-C to copy to clipboard -> Select I7 -> Paste Special -> Transpose to make a horizontal copy of the years.
2) In I8, I enter =$F8 (note the mixed reference so the column is fixed when copying).
3) In I9, I enter =IF($G9>I$7,IF($G9=I$7+1,$B$3,"no"),$G9). Copy I9 down to I38
4) Select I8:I38 -> copy -> paste into J8:AL8
5) Enter an XIRR() function with OFFSET() functions for the input ranges into I5. =XIRR(OFFSET(I$8,0,0,COUNT(I$8:I$38),1),OFFSET($E$8,0,0,COUNT(I$8:I$38),1)). Copy I5 across to AL5.
6) I notice that the later years return a value very near 0 -- indicating that the XIRR() function's internal algorithm failed to converge. In Excel, I can fix this by adding an optional guess (maybe -0.4) to the XIRR() function. I also observe that other spreadsheets (I tested in LibreOffice) have a more robust algorithm behind the XIRR() function and do not need a guess for those years. Just be aware of this possibility.

That's how I (one who is somewhat fond of using helper cells and ranges) would do it.