Error de cálculo de XIRR

Hola,

Estoy tratando de calcular XIRR para un conjunto dado de flujos de caja, que cumplen con todas las reglas de la función XIRR, pero sigo recibiendo un error.

Consulte mis datos a continuación. ¿Alguien puede ayudarme con esto?

Cuando se carga en Excel la fórmula XIRR, el valor devuelto es 13.1%,

pero powerbi arroja un error, Error de cálculo: no se puede encontrar una solución; con formula

IRR = XIRR (Tabla, Tabla[Amount],Tabla[Transaction Date]), incluso cuando se agrega un valor de conjetura

Fecha de Transacción	Monto
05/11/2018	– $ 4,811,400
05/11/2018	$ 4,811,400
30/09/2018	– $ 33,600
10/01/2018	– $ 18,400,500
10/01/2018	– $ 17.448.750
12/11/2018	– $ 1,089,214
31/12/2018	– $ 282,112
31/12/2018	– $ 115,680
31/12/2018	$ 42,266
31/03/2019	– $ 232,941
31/03/2019	– $ 147,441
31/03/2019	$ 96,201
31/03/2019	$ 438,675
05/10/2019	$ 412,695
28/05/2019	– $ 6.043.410
28/05/2019	– $ 4,608
30/06/2019	– $ 208,622
30/06/2019	– $ 96,290
30/06/2019	– $ 7,828
30/06/2019	$ 878,247
16/08/2019	– $ 5,981,850
16/08/2019	– $ 69,390
24/09/2019	$ 3,547,828
30/09/2019	– $ 328,670
30/09/2019	– $ 203,422
30/09/2019	– $ 18,664
30/09/2019	$ 979,257
29/11/2019	– $ 1,784,835
29/11/2019	– $ 405
20/12/2019	$ 1,495,058
31/12/2019	– $ 222,257
31/12/2019	– $ 222
31/12/2019	$ 309,150
31/12/2019	$ 919.578
24/03/2020	– $ 579,447
24/03/2020	– $ 21,897
31/03/2020	– $ 158,850
31/03/2020	– $ 20,028
31/03/2020	$ 892
31/03/2020	$ 742,211
12/06/2020	– $ 1,215,810
12/06/2020	– $ 4,806
17/06/2020	$ 498,669
30/06/2020	– $ 5.929
30/06/2020	$ 21,136
30/06/2020	$ 760,991
30/09/2020	– $ 35,691
30/09/2020	– $ 716
30/09/2020	$ 852,155
30/09/2020	$ 2,073,625
30/09/2020	$ 17.405.685
30/09/2020	$ 34,811,370

El problema es que esa primera fecha es una transacción neta cero. Intente filtrar esos días.

IRR =
VAR Summary =
    SUMMARIZE ( Table, Table[Transaction Date], "@Amt", SUM ( Table[Amount] ) )
VAR FilterZeros =
    FILTER ( Summary, [@Amt] <> 0 )
RETURN
    XIRR ( FilterZeros, [@Amt], [Transaction Date] )

La primera transacción vuelve a ser la culpable. Tener un valor pequeño como ese como primera transacción es desestabilizador y la función XIRR no es muy robusta. No conozco una buena solución general para solucionar esta deficiencia, pero he aplicado algunos trucos en mis informes que a veces funcionan.

Por ejemplo, si modifica ligeramente los datos para invertir $ 100 adicionales por un día de modo que en lugar de -1 el 1 de septiembre de 2009 tenga -101 el 1 de septiembre de 2009 y 100 el 2 de septiembre de 2009, entonces XIRR converge. Esta modificación no cambia materialmente el resultado, pero mejora la estabilidad de la convergencia XIRR.

Aprecio su pronta respuesta sobre este tema, bueno, modificar los flujos de efectivo en sí mismos sería un desafío, pero probablemente pueda modificarlos a un nivel de powerquery.

Gracias de nuevo,

Brian

Sí, también hice la modificación en Power Query (antes de eventualmente empujar esa lógica en sentido ascendente a una vista SQL).

Gracias Alexis,

Similar al anterior, si los primeros elementos siempre son un número entero negativo, a continuación tengo una lista de otras transacciones, con las primeras 3 filas como datos positivos.

Cuando incluyo todas las filas, la fórmula XIRR arroja un error,

Cuando excluyo las primeras 3 filas, obtengo un valor de 63,7%, que es el valor exacto.

La documentación XIRR no menciona que

Sí. XIRR es meticuloso, no robusto y está mal documentado. He escrito funciones de TIR mucho más robustas en SQL, pero DAX no es adecuado para cálculos iterativos de búsqueda de objetivos.

Un consejo que puede resultarle útil es que puede mejorar su solidez proporcionando una suposición decente para comenzar. Como estimación del reverso de la servilleta, puede anualizar el valor total a pagar (TVPI) también conocido como Múltiplo del costo de inversión (MOIC) como un retorno de uso para la estimación de XIRR.

Por ejemplo, si su múltiplo es 1.33x durante tres años, entonces la estimación inicial para generar el cálculo de XIRR sería aproximadamente el 10% ya que (1 + 0.1) ^ 3 = 1.331.

El problema es que esa primera fecha es una transacción neta cero. Intente filtrar esos días.

IRR =
VAR Summary =
    SUMMARIZE ( Table, Table[Transaction Date], "@Amt", SUM ( Table[Amount] ) )
VAR FilterZeros =
    FILTER ( Summary, [@Amt] <> 0 )
RETURN
    XIRR ( FilterZeros, [@Amt], [Transaction Date] )

Hola Alexis,

Intenté usar la fórmula anterior y funcionó como un encanto, pero no funciona para otro fondo que estoy tratando de calcular. ¿Podría ayudarme a arrojar algo de luz sobre esto?

Importe de la fecha de la transacción
1/9/2009 -1
23/9/2009 -96987,5
7/10/2009 -106860674.3
9/10/2009 -74964867.59
15/10/2009 -16924977.06
16/10/2009 -3770502.678
19/10/2009 -82438531.51
21/10/2009 -37109881.69
30/10/2009 -8664165,28
5/11/2009 -4271473.502
8/11/2009 -4296460.461
10/11/2009 -20361351,93
18/11/2009 -24625360,42
19/11/2009 -30582,63
7/12/2009 -23885552.27
31/12/2009 -25118,5
13/01/2010 -26550
17/02/2010 -102625
25/2/2010 -176850
16/3/2010 -20315.51
23/3/2010 -23404.5
6/5/2010 10202480.69
7/5/2010 -2963,63
7/7/2010 -43325,43
10/7/2010 -42658,78
20/7/2010 -14857.45
24/9/2010 -96987.5
27/10/2010 -86771.63
29/10/2010 -42506.04
2/11/2010 45413.82
5/11/2010 -2424000000
8/11/2010 -12500000
9/11/2010 539398612.8
18/11/2010 -23508,48
8/12/2010 -11900000
15/12/2010 -11100000
27/1/2011 1623339.691
29/4/2011 2176198.1
29/7/2011 6008117.7
31/10/2011 6030485.21
13/01/2012 6056051.99
13/03/2012 -38483010
30/4/2012 24168605
30/7/2012 24979547
29/10/2012 27396408.57
9/11/2012 13048000
30/11/2012 11783474.66
3/12/2012 876314.3231
4/12/2012 1818556.73
5/12/2012 807511.3968
6/12/2012 324488.3162
7/12/2012 532867.44
11/12/2012 1943493.548
12/12/2012 2563192.03
13/12/2012 1528912.854
14/12/2012 732183.55
17/12/2012 2058550.56
18/12/2012 1838991.26
19/12/2012 2833561.341
20/12/2012 3750221.93
21/12/2012 548385,996
24/12/2012 530594.4
31/12/2012 1361033.669
1/2/2013 1257049.905
1/3/2013 967845.8237
1/4/2013 27534382.83
7/1/2013 1872588.09
8/1/2013 1534601.118
9/1/2013 1311506.674
10/1/2013 1712662.109
11/1/2013 97338.3586
14/1/2013 1327735.712
15/1/2013 5196238.58
16/1/2013 2188493.701
17/1/2013 3595664.39
18/01/2013 1795744.73
23/1/2013 494193.4646
24/1/2013 191230.62
25/1/2013 1095558.001
28/1/2013 7298293.225
29/1/2013 1332780.581
30/1/2013 1054751.246
1/2/2013 3494861.37
4/2/2013 3668036.66
5/2/2013 2616699.749
6/2/2013 3139008.23
8/2/2013 1331450.442
11/02/2013 5450516.72
12/02/2013 7398108.4
14/02/2013 7854891.18
15/02/2013 1957658.61
19/02/2013 2536601.389
20/02/2013 645374.984
22/02/2013 9693995.456
25/2/2013 1927946.77
26/02/2013 4456544.073
27/02/2013 3078530.96
28/02/2013 18843539.62
1/3/2013 6958880.943
3/4/2013 13479962.34
5/3/2013 13029385.63
6/3/2013 116182428.5
29/4/2013 1506037
30/4/2013 29002326
30/7/2013 29002326
31/7/2013 1506037
29/10/2013 31419186.5
1/11/2013 6041507261

La primera transacción vuelve a ser la culpable. Tener un valor pequeño como ese como primera transacción es desestabilizador y la función XIRR no es muy robusta. No conozco una buena solución general para solucionar esta deficiencia, pero he aplicado algunos trucos en mis informes que a veces funcionan.

Por ejemplo, si modifica ligeramente los datos para invertir $ 100 adicionales por un día de modo que en lugar de -1 el 1 de septiembre de 2009 tenga -101 el 1 de septiembre de 2009 y 100 el 2 de septiembre de 2009, entonces XIRR converge. Esta modificación no cambia materialmente el resultado, pero mejora la estabilidad de la convergencia XIRR.

Entradas relacionadas

Deja un comentario Cancelar respuesta