IRR- Adding purchase price to the beginning of cashflows

[James17Martin1]

Hi all,

I have multiple investments (A, B, C...) and I want to calculate the IRR/XIRR for each one individually. I have a few problems:

1) All my receipts for all my investments fall under the same variable from the qvd.- receipts

2) I want to add the purchase price of the invest at the beginning  that investments receipts for the XIRR formula to work. Investment A, B and C

3) This is where it gets tricky, some of my investment inflows occur before the purchase date(outflow) because the investment took time to process but the money was still entitled to me and I know for the IRR calculation, the first entry has to be a negative outflow. I was thinking of grouping all my cash flows that occurred before purchase date and bringing them forward to the purchase date but I am unsure how to do that

4) the IRR calculation does not allow aggregations to be done in the formula so I was thinking of doing all the above into a new variable or column and putting that into the XIRR formula.

I know this is a bit of a nightmare problem but I really don't want to resort so low as to export it to excel and calculate  manually every time!! I will REALLY appreciate any help!

[Brett_Bleess]

You did not mention having gone back through the Help, so including that:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Finan...

There are some examples etc. tied to the detailed items.  I checked the Design Blog, nothing on IRR/XIRR there, and I check the Documents area of the QlikView forum, but not much there either.  The only thing I could suggest is some sample data etc. to give folks a bit more to work with on this one.  Sorry I do not have anything better for you.  You may need to rework the data model a bit when loading int he QVD file in order to get things into the proper model such that you can get what you want, only way I really see to go at things on this one.  If you do want to dig around in the Design Blog area to see if you can find anything that may be useful there, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Regards,
Brett