XIRR function for changing date (image included)

[jht0005]

I've built a dynamic model where the dates between an acquisition and the revenue producing go live change (acquisition date D58, go live E58). I am attempting to summarize the XIRR for every year for 5 years on another tab, so I know the XIRR range must start at D58 and go to wherever 12 months ends (and eventually 24, 36, etc). I was attempting to use address function plus an hlookup for the number 12 and then an offset to go from K57 to K58 in my formula (needing to read as xirr(D58:K58,D78:K78)) but I can't seem to get it to work. I've gone through ~200 Mr. Excel examples and can't seem to back into this function.

I'd like for the formula to know to start at D58 and then find 12 months out (for both the date and cash flows) and eventually 24, 36, and so on. Any help would be greatly appreciated as I'm under a MAJOR TIME CRUNCH!

 

[joeu2004]

No image seems to have been provided. For this forum, I believe you must upload the image to a file-sharing website, then insert the share/public URL as an image object by clicking Insert Image in the toolbar.

The example image should clarify one point: are the 12-month cash flows always in consecutive 12 cells; or could there be more or fewer cash flows over the range of dates specified in D58 and E58? If the latter, will D58 and E58 always match dates in column D (if that is where the 5 years of dates are); or are you looking for the "closest" actual dates to the dates specified in D58 and E58?

And the mock formula that you wrote -- xirr(D58:K58,D78:K78) [sic] -- should be XIRR(K[D58]:K[E58],D[D58]:D[E58]) in pseudocode. The point is: XIRR(values,dates), not XIRR(dates,values).

Finally, in addition to selecting the value and date range dynamically, another problem you will likely face is the fact that all of your values in column K (if that is where the 5 years of values are) are presumably positive, whereas XIRR requires that at least two values have opposite signs; for example, negative inflows (investments and initial valuation) and positive outflows (distributions and final valuation).

All of these factors might lead to an unduly complicated XIRR expression, if it is even feasible. Would you be amenable to using a VBA function instead?