1

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

Shawnshaw
  • 17
  • 1
  • 5
  • https://docs.google.com/spreadsheets/d/e/2PACX-1vQaTYDDXM7AoZXhwvv0RWkausnx2HWBYuKRb4zp_KZKdD6FXtQ8NQDNK0Vp1aNCuvXcPxuhpwvu5WWs/pubhtml – Shawnshaw Feb 17 '22 at 17:47
  • The provided sample sheet is a published sheet which we cannot try to modify or replicate with the given data, would you be able to provide another sample sheet with sanitized values for us to further test ? – Century Tuna Feb 17 '22 at 20:39
  • Also, id suggest looking into the [XIRR formula](https://support.google.com/docs/answer/3093266?hl=en) for Google Sheets, just make sure that the dates are formatted correctly. – Century Tuna Feb 17 '22 at 20:41
  • https://docs.google.com/spreadsheets/d/1LnTHOuw5FROyZ8tNo1Zl270RhTDX1gfB2m7jtEU9F_k/edit?usp=sharing – Shawnshaw Feb 17 '22 at 21:27
  • I tried using your sample data and using `=XIRR` formula on Google Sheets. The data is formatted correctly, but the issue is that the cash flow data isn't organized since the top row should contain the original invested amount and is represented by a `-` sign, and in the following rows below enter the returns for each period. Take note that all data should be organized by date to properly compute invested returns – Century Tuna Feb 17 '22 at 21:48
  • Thank you for looking into the sheet. This is how I have invested the amount. I have this much data and no more. Is it not possible to sort by date? There is no original invested amount. I have basically bought and sold equity at those dates. – Shawnshaw Feb 17 '22 at 23:34
  • Futher, the negative amount is to indicate equity bought and Positive amount is for equity sold. I don't care if you reverse this. I don't understand when you say "Take note that all data should be organized by date to properly compute invested returns" – Shawnshaw Feb 17 '22 at 23:36
  • For returns for each period, I wanted to avoid just using 1 formula to get return for each investment. I want to use this for further investments and the data will expand.I was hoping the formula will calculate return as we include more and more data. – Shawnshaw Feb 17 '22 at 23:38
  • @DiegoSanchez I believe all that's missing is the current holding and today's date at the end. I've added an answer to that effect. – MattKing Feb 18 '22 at 16:53
  • @Shawnshaw Note that I just updated my formula slightly on the sheet as well as below in my answer. – MattKing Feb 18 '22 at 18:46
  • Thanks Matt King. I have included the holding shares in my database. Thank you once again – Shawnshaw Feb 18 '22 at 18:56
  • MattKing Thanks for helping out! @Shawnshaw If MattKing answered your question, please click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [how to accept answer](https://stackoverflow.com/help/accepted-answer) – Century Tuna Feb 18 '22 at 21:12

1 Answers1

1

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.

MattKing
  • 7,373
  • 8
  • 13