I have a table of investment returns, which contains chronological dates in one column, and amounts invested or withdrawn in an adjacent column. I want to use XIRR to calculate the overall annual return on the investment. The problem: The current account balance is in a separate cell, above the table of returns. (I should note that the "table" of returns is not an Excel table, but merely a range of cells.) For example, dates might be in A5:A30, amounts in cells B5:B30, and the current (remaining) account balance in cell B2.
XIRR needs to reference the values and dates. How can I construct (within the formula) arrays of the values and dates, referring to my "table" of returns, but with the current account balance (and today's date) appended at the end of the table's values? Something like: =XIRR({B5:B30,B2},{A5:A30,TODAY()})... but curly brackets apparently don't work to create an array of values.
Is there any way to create a reference (within a formula) to a range of non-contiguous cells?
Bookmarks