Sep 09 2023 11:15 AM
Have a column of multiple symbols with dates and returns. Can be single column or multiple columns. I have the required data such as opening amount, closing amount and interim amounts.
Sep 11 2023 06:53 AM
Since the title of your inquiry refers to the XIRR function, it makes some sense to assume that you're asking how to do so. But the rest of what you provide is quite incomplete. Would you be willing to describe more fully what you do have?
Ideally, post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link here that grants access.
Sep 11 2023 11:37 AM
Sorry I wasn't more explicit. I was too much in a hurry.
I am familiar with XIRR... used it many times, but I want to use it in conjunction to a couple of different situations. My data consists of investments with Symbol, Dates and applicant Amounts. I want to extract data for XIRR for different periods, eg, YTD, 1 Yr, 3 Yr. I can combine data into a single column column or with separate Symbols...whichever is easier. Hope this helps.
Sep 11 2023 02:26 PM
If you've used XIRR many times, it puzzles me why you're asking the question. And frankly, it's still not clear exactly what you're asking. If you've used it many times, you know you need a series of cash flows, containing at least one positive and one negative number. What's standing in your way?
You could do it by symbol, if you want a rate of return by individual stock (accounting for purchases, sales, dividends), or as a collective if you're looking for a single comprehensive portfolio return.
You could have a single comprehensive data base of all stock transactions, each with its associated cash flow (positive or negative) and then create a "dashboard" sheet that enables you to look comprehensively OR selectively, for life of the whole account, or just for various periods.
As I said, though, it's not clear what exactly it is you're wanting to do. That's why it's hard to answer.
Is it possible for you to post a mockup of the data you have (or the actual, but I would imagine you'd rather keep that confidential), which would help understand the raw data or input end of this. Post it on OneDrive or GoogleDrive, with a link here that grants access.
Just so you know my own use: I track my own investments, most specifically investments in Options, and I keep separate pages for each individual position. Some of them have very few individual transactions; some have twenty or more. I'm attaching an example--without formulas--just to demonstrate the layout. Since you've used XIRR, I'm assuming you know how to array your info, but that brings me back to my confusion as to what it is that's hindering you from just doing whatever it is that you want to do.
Sep 11 2023 06:41 PM
Sep 11 2023 06:45 PM
Sep 11 2023 07:23 PM
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."
Sep 12 2023 10:29 AM
Sep 12 2023 11:31 AM
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!).