Dear all,
I'm trying to use the following formula (suggested by Chihiro user on this forum) in order calculate the IRR, as a measure within a pivot table, as of all the dates of an investment (having the fair values of the investment as of each date). The used formula is the following:
=VAR date_Max=MAX ( IRR_dinamico[fecha] )
VAR neg_Flow=FILTER ( (IRR_dinamico);IRR_dinamico[Attribute]="Flujo de caja"&& IRR_dinamico[fecha]<= date_Max)
VAR pos_Flow =FILTER ((IRR_dinamico);IRR_dinamico[Attribute] ="Distribución total neta acumulada (simulada + real)" && IRR_dinamico[fecha]=date_Max)
VAR temp_Table=UNION (neg_Flow; pos_Flow)
RETURN
IFERROR (XIRR ( temp_Table; [Value];[fecha]; 0,0 ); "error" )
where:
- IRR_dinamico is the name of the reference table (that cames out of a query), please see the attached file
- fecha = date
- Flujo de caja = outflows of the investments
- Distribución total neta acumulada (simulada + real) = the fair value of the investment as of each date
The problem is that, using the above mentioned measure (please see the pivot table in sheet "IRR dinamic calculation" of the attached file), results in a right calculation on the whole investment (reported - I don't know why - on the grand total line of the pivot table; 16% for the filtered investment as an example), but does not properly calculate the IRR as of each intermediate date as expected, giving an error.
I'm not an expert at all, but probably it could be related with the two following conditions put in the formula above: "IRR_dinamico[fecha]<= date_Max" and "IRR_dinamico[fecha]=date_Max".
Could please anyone help me with this?
Many thanks in advance,
Francesco
I'm trying to use the following formula (suggested by Chihiro user on this forum) in order calculate the IRR, as a measure within a pivot table, as of all the dates of an investment (having the fair values of the investment as of each date). The used formula is the following:
=VAR date_Max=MAX ( IRR_dinamico[fecha] )
VAR neg_Flow=FILTER ( (IRR_dinamico);IRR_dinamico[Attribute]="Flujo de caja"&& IRR_dinamico[fecha]<= date_Max)
VAR pos_Flow =FILTER ((IRR_dinamico);IRR_dinamico[Attribute] ="Distribución total neta acumulada (simulada + real)" && IRR_dinamico[fecha]=date_Max)
VAR temp_Table=UNION (neg_Flow; pos_Flow)
RETURN
IFERROR (XIRR ( temp_Table; [Value];[fecha]; 0,0 ); "error" )
where:
- IRR_dinamico is the name of the reference table (that cames out of a query), please see the attached file
- fecha = date
- Flujo de caja = outflows of the investments
- Distribución total neta acumulada (simulada + real) = the fair value of the investment as of each date
The problem is that, using the above mentioned measure (please see the pivot table in sheet "IRR dinamic calculation" of the attached file), results in a right calculation on the whole investment (reported - I don't know why - on the grand total line of the pivot table; 16% for the filtered investment as an example), but does not properly calculate the IRR as of each intermediate date as expected, giving an error.
I'm not an expert at all, but probably it could be related with the two following conditions put in the formula above: "IRR_dinamico[fecha]<= date_Max" and "IRR_dinamico[fecha]=date_Max".
Could please anyone help me with this?
Many thanks in advance,
Francesco