Forum Discussion
XIRR used in an array
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.
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.
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.
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.
- I forgot to mention that I have tried an array but without success. They are complex for me but I can muddle my way through it if required.
