Forum Discussion
XIRR used in an array
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.
- Thanks for the rapid response. In your example you provided, what if you bought SBUX on 1-15-2015 instead of 11/17/2020; if the dividends you received were over the next eight years, including two of them in 2023. Now you want XIRR for YTD or 1 year. I assume you would have to go through a selection process to set up the data to the corresponding time period. (Select my prior purchases as negatives plus the 2023 transactions and YTD ending balance). I could do it manually to set up the data, for XIRR but I have 20 securities to do this with. I prefer not to do this manually. I prefer to have a formula do the selecting and XIRR calculation for me if I can while the data is still in its original form either in a single column of all 20 or individual columns of symbols.
Perhaps this is not possible or too complex for a formula. Maybe an advanced filter would do the job. If so how do I do this? Thanks for your patience.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."
- Mathetes
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.