excelhelppls
New Member
Hello everyone:
I am trying to find a way to calculate the 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.
Please refer to the attached example which makes the problem much easier to understand.
Each Investment has an individual column for Inputs that include Cashflow transactions (we would look at them over a range) and different period end Valuations (we would only consider one of these for each investment) The dates for the Investment Inputs are all entered into a single column that is shared by all Investments.
The Valuation value Inputs are found in the end of each Investment column after the Cashflows, and often are not in chronological order.
As stated before there is also a challenge because Cashflow entries beyond the last Valuation date often times exist in the Investment Input columns. This has to be accounted for when calculating the XIRR, as the calculation should ignore all entries that are more recent than the Valuation date Input.
Thank you All very much!
I am trying to find a way to calculate the 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.
Please refer to the attached example which makes the problem much easier to understand.
Each Investment has an individual column for Inputs that include Cashflow transactions (we would look at them over a range) and different period end Valuations (we would only consider one of these for each investment) The dates for the Investment Inputs are all entered into a single column that is shared by all Investments.
The Valuation value Inputs are found in the end of each Investment column after the Cashflows, and often are not in chronological order.
As stated before there is also a challenge because Cashflow entries beyond the last Valuation date often times exist in the Investment Input columns. This has to be accounted for when calculating the XIRR, as the calculation should ignore all entries that are more recent than the Valuation date Input.
Thank you All very much!