Forum Discussion
Need help in XIRR calculation
I have 2 tables now in my model.
One contains transaction data and second contains the history rates.
There is a transaction date, instrument and units in transaction table. I need to go to History rate table(current price sheet in excel) for each row in Transaction table, pick up rate/value as of transaction date and for particular instrument and then multiply that rate/value with the units and then append it in temp table. On top of temp table XIRR needs to be calculated.
PFA excel sheet in wich I have taken sample data.
If we talk about 2nd row of transaction sheet in excel : we have 3548 units of instrument id 2 on 06-AUG-2018. I need to go to CurrentPriceSheet in Excel, pick up rate of instrument id 2 as of 06-AUG-2018 which is 286.4062 multiply with 3548(units) and then append it. I need to do this for each row in transaction and take XIRR on top of it.
Here is the link of the sample dataset : https://ufile.io/xxowcfmv
Please do let me know if anything else is required.
Thanks in Advance!
7 Replies
Why not just use LOOKUPVALUE?
Value = LOOKUPVALUE(CurrentPrice[value],CurrentPrice[date],[date],CurrentPrice[InstrumentName],[Instrument Name])Attached the PBIX I created.
Hi Greg,
Thank you very much for your responce.
I dont think that Lookup value will work because for XIRR calculation those values are also need in same column not in other column.
In short each row will be coming twice from transaction table, one containing original data and one containing calculated value which you calculated with help of Lookup.
Then on top of this temporary table XIRR needs to calculated in which I will pass date column and Value.
Thanks!
So you just want a table of values and no other information? You do understand that the solution is the same. You just use ADDCOLUMNS and then do a SELECTCOLUMNS.
