Forum Discussion
XIRR used in an array
It's entirely possible, but hard to show without seeing how you have arranged your data. (By the way, there's nothing fancy meant by the word "array" in connection with data: I just mean, do you have the dates in a column, and the cash flow figures in another column.That would be how you have the data arrayed. "Arranged" is another word meaning, in this connection, the same thing.)
The INDIRECT function can be used to set ad hoc start and finish dates, find the appropriate rows and for starting and finishing an XIRR calculation.
But you need to be aware that the XIRR function is not all that meaningful as a YTD or 1 year snapshot; think about it, one year by itself might only show dividends. XIRR really only makes sense for the life of the investment, from initial purchase down through today--whatever has transpired in the interim--with today's market value marking a hypothetical sale as the final (hypothetical) transaction.
Anyway, without knowing how you have arranged/arrayed your data, there's not a lot somebody can do from afar, other than say "It's possible."
Before I take more of your time, I think that I'll brush up on arrays. If I can't do any good with arrarys, then I'll come back with an example of what I'm talking about. I truly thank you for your help...at least you got me to thinking.
Before I take more of your time, I think that I'll brush up on arrays. If I can't do any good with arrarys, then I'll come back with an example of what I'm talking about.
I fear that you're making too much of a big deal out of the word "array." Yes, Excel has introduced "Dynamic Array Functions"....and those do involve those things call "arrays." But an array is nothing special. Especially when it comes to things like a record of stock transactions, it's just columns with headings like:
Date....Symbol......Action......CashFlow
And if you have worked with XIRR, as you say, you most certainly have worked with an array like that (I can't imagine XIRR working without something like that!).