How do I get a function to use a discontiguous set of range data in XIRR?

Hi Everyone,

So I'm trying to develop a function that will return the internal rate of return for a discontinuous set of data. Basically I want to use the function XIRR() with a filtered range of data plus a custom value at the front and back. What this function uses will vary with the input dates, and since i want to use this function for a range of dates it's just not possible for me to create a "holding sheet" that contains selectable data that the functions can access.

Conversely, if I create a subroutine that does it then I can't just use it as a function, and since I can't use a function to create a worksheet, put data into it, work with it, then delete the sheet I can't use that either. Nor can I just use arrays because excel's XIRR function takes ranges as inputs. And I sure as hell don't want to write my own XIRR function from scratch.

So I'm stuck; how do I get around this?

Thanks for the help

Ok I just discovered that I can in fact just pass arrays to functions instead of ranges. So now I built the array from within the function, then passed it on to the XIRR function and receive a credible result.