-1

I am trying to follow my own mutual fund purchases and trying to input the results into a spreadsheet to calculate the XIRR (internal rate of return). I am planning on entering my data for every purchase I make and will have a table as follows; Fund Name Fund Date Purchase TD Balanced November 30, 2015 900 Scotia Balanced November 24, 2015 1000 Scotia Balanced November 03, 2015 5000 Scotia Balanced June 23, 2015 -2000 Scotia Balanced April 02, 2015 4000 TD Balanced January 04, 2015 4000 Scotia Balanced July 20, 2014 800 Scotia Balanced March 02, 2014 400 TD Balanced January 29, 2014 10000

I want to then be able to create multiple small tables from the data above as follows; Fund Name Fund Date Purchase Scotia Balanced November 24, 2015 1000 Scotia Balanced November 03, 2015 5000 Scotia Balanced June 23, 2015 -2000 Scotia Balanced April 02, 2015 4000 Scotia Balanced July 20, 2014 800 Scotia Balanced March 02, 2014 400 Fund Name Fund Date Purchase TD Balanced November 30, 2015 900 TD Balanced January 04, 2015 4000 TD Balanced January 29, 2014 10000

I have tried pivot tables which can do this, however I am unable to calculate the XIRR properly with pivot tables. Any help would be appreciated it please.

Future Steps: Ideally I would want to create this in such a way that I can add all my purchases to table 1. If the result is a new fund it will automatically create a separate table. I would want each of the tables to have there own XIRR calculation with a total XIRR calculation.

0
=query(A:C,"select * where A <>'' order by A asc") 

might help but you are not going to obtain XIRR from details of purchases only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.