google-sheetsxirr

To find XIRR for different investments using google sheet

I am currently trying to calculate the XIRR of a huge portfolio containing non-periodic cashflows. The database contains lot of transactions and I want to calculate the XIRR for each one. This image contains the format and the last column contains the TICKER names of firms. I want to calculate the XIRR for these firms. The database on the left contains all the data for the ticker names

Please find the sample sheet here: https://docs.google.com/spreadsheets/d/1LnTHOuw5FROyZ8tNo1Zl270RhTDX1gfB2m7jtEU9F_k/edit?usp=sharing

Solution

  • on your sheet you will find a new tab called MK.Help.

    This is how you find XIRR for an investment like what you have:

    =XIRR({FILTER(D:D*E:E,A:A=H5);-I5},{FILTER(B:B,A:A=H5);TODAY()})

    The key is that you need to add the CURRENT HOLDING and todays date at the end of the arrays of cashflows. The idea is to imagine that you liquidated the position right NOW.