• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR with non-contiguous entries for Flows (ranges) and Valuation (single period) based on a cutoff date.

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!
 

Attachments

  • XIRR help.xlsx
    20.8 KB · Views: 18
Please try at D5

=XIRR(IF((INDEX($B$32:$B$51,D$21):$B$51<=$B48)*(INDEX($C$32:$C$51,D$21):$C$51<>$C48)+(INDEX($C$32:$C$51,D$21):$C$51=$C48)*(INDEX($B$32:$B$51,D$21):$B$51=$B48),INDEX(D$32:D$51,D$21):D$51,0),INDEX($B$32:$B$51,D$21):$B$51)

confirm with Ctrl+Shift+Enter

75586
 

Attachments

  • XIRR help.xlsx
    21.5 KB · Views: 16
p.s. The proposed solution allows the sort order of the data to change whereas the manual calculation is specific to the layout.

Thank you Peter for sharing this file. I have been working on this for weeks and you have provided the exact solution I am looking for - thank you so much!

One question - I am modifying my data so that it matches your layout, but how would you modify the formula if all investment $$s were in 1 column, instead of 3, and investment 1, investment 2, and investment 3 were laid out in rows instead of as column headers?
 
I claim no ownership of the layout! Much of the calculation was focussed on picking information out of table, so a different layout requires a different formula. Why don't you open a new discussion and post an example workbook? You are clearly using Excel 365 or 2021, but that would be worth stating to help other potential contributors.
 
I claim no ownership of the layout! Much of the calculation was focussed on picking information out of table, so a different layout requires a different formula. Why don't you open a new discussion and post an example workbook? You are clearly using Excel 365 or 2021, but that would be worth stating to help other potential contributors.

Thanks Peter.

New thread posted here:

 
Back
Top