XIRR with different cutoff dates for Flows and Valuation, and non-contiguous data entries.

Hello to All,

I am trying to find a way to calculate the XIRR for many investments using a vast Data Input Structure that already exists and which is constantly being updated with new inputs for the respective investments.

The challenge is to find a standard formula that produces XIRR calculations under such structure, which includes Cashflows and Valuations that are mostly not contiguous and contain Inputs in the ranges that sometimes go beyond the desired XIRR calculation cutoff dates.

Please refer to the attached example workbook.

Each Investment has an individual column for Inputs that include Cashflow transactions and different period end Valuations. The dates for the Investment Inputs are all entered into a single column shared by all Investments.

The Valuation values are typically in the end of each Input column, after the Cashflows, and often are not in order.

There is also the challenge that Cashflow transactions 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 very much for your help!

I just did that, thank you!

Originally Posted by AliGW

Administrative Note:

Please check that your profile is up-to-date with your current Excel version.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.