PrefInvestor wrote:Part of my problem with implementing the XIRR calculation is that the data required needs to be drawn from three different tables on my spreadsheet, the purchase prices from one table, the dividends from a second table and the current stock price from a third. My original plan was to use queries to combine each of these elements into a single table which could then be sorted by the stock code to produce the necessary dates and values in a contiguous range of cells suitable to pass into the XIRR function. The ability to include blank cells as in your example and the formatting approach outlined above might provide an alternative solution eliminating the need for some sorting.
A simple solution would be to write a VBA function (MyXIRR ?) possibly passing the Epic Code as an argument. This function could then build the date/values in local variables (making them contiguous) and return the XIRR value. I might try this approach but I am worried that it might disrupt the operation of undo which I find essential. In my last major set of updates to my spreadsheet I spent a lot of time removing all my VBA functionality for this very reason.
ATB
Pref
I think that you may be reinventing the wheel here. I have a sheet for each share, with dates at the left, purchases or sales and dividends in columns to the right, share prices on the dates (announcement dates for dividends), etc. and a cash flow column at the right, picking up numbers from the appropriate columns.
When something happens, I add a row. The current price and value are below all this and the XIRR is worked out automatically. I can do it for the holding or just the single share first purchased.
Then I have a summary sheet for all the shares held, which picks up the value of XIRR from each individual share's sheet.
No need to undo anything.
TJH