Hello,
This question was previously posted here: https://chandoo.org/forum/threads/x...n-single-period-based-on-a-cutoff-date.46651/
The attached file shows the solution to this problem and my new question.
Note that I take no credit for proposing the initial question or the elegant solution.
To reiterate the initial problem, "How do I calculate XIRR for different investments over a vast Data Input structure that already exists and that is constantly being updated with new Inputs for the respective investments? The challenge is to find a standard formula for XIRRs using such Data Input structure, which contains entries for Cashflows (ranges) and Valuations (a single and final value) that are mostly not contiguous.Furthermore, the Data Input structure also includes Cashflow entries for dates that go beyond the Valuation entries. There has to be a cutoff date for the XIRR calculations and such dates are equal to the single Valuation inputs."
So, after reviewing the file, I have a minor adjustment that I would like to make, but I have not been able to properly update the XIRR formula. My question is how do I update the existing XIRR formula (in the attached file) to calculate XIRR when the funds and cash flows are laid out vertically in two columns, instead of the cash flows being separated by fund columns?
This question was previously posted here: https://chandoo.org/forum/threads/x...n-single-period-based-on-a-cutoff-date.46651/
The attached file shows the solution to this problem and my new question.
Note that I take no credit for proposing the initial question or the elegant solution.
To reiterate the initial problem, "How do I calculate XIRR for different investments over a vast Data Input structure that already exists and that is constantly being updated with new Inputs for the respective investments? The challenge is to find a standard formula for XIRRs using such Data Input structure, which contains entries for Cashflows (ranges) and Valuations (a single and final value) that are mostly not contiguous.Furthermore, the Data Input structure also includes Cashflow entries for dates that go beyond the Valuation entries. There has to be a cutoff date for the XIRR calculations and such dates are equal to the single Valuation inputs."
So, after reviewing the file, I have a minor adjustment that I would like to make, but I have not been able to properly update the XIRR formula. My question is how do I update the existing XIRR formula (in the attached file) to calculate XIRR when the funds and cash flows are laid out vertically in two columns, instead of the cash flows being separated by fund columns?