mik82

I have a dataset of portfolio transactions going back nearly 20 years with three columns:

Column A = date
Column B = cash flow (+/-)
Column C = Portfolio NAV immediately following the cash flow, this would be the sum of the portfolio NAV immediately prior to the cash flow + the cash flow. Additionally this dataset contains the portfolio's month-end NAV, in which case Column A = month-end date, Column B = 0, Column C = NAV

The NAV (column C) includes all capital growth in between dates.

For example, if the portfoio NAV was 100 on 1/31 and $10 was invested 2/1, the CF would be -10 and the NAV would be 110 (assuming no capital growth between 1/31 and 2/1) and would look like:
A B C
1/31 0 100
02/11 -10 110

I would like to add a fourth column (D) to this dataset containing a calculation of the portfolio's IRR since inception at each date in the spreadsheet ("rolling IRR").

The main reason I'm having trouble using the XIRR function is because I have a series of cash flows and dates (A & B), but I also need to include the portfolio NAV as the terminal value in the IRR calculation, but this data is located in a separate column (C) from the cash flow series.

I hope this better explains the problem.