excel - spreadsheets - tablas en google sheets
cómo calcular XIRR dinámicamente en excel y en hojas de google (3)
Asumiré basado en su tabla que la fecha es la columna A, el fondo (o "Script") es la columna B y el "ForXIRR" es la columna F. Lo siguiente lo hará por usted:
{=XIRR(IF(B:B="A",F:F,0),IF(B:B="A",A:A,0))}
Asegúrate de ingresar esto con Shift + Ctrl + Enter ya que es una fórmula de matriz.
Nota: Lo anterior se calculará para "A", pero sustituya esa parte de la fórmula por cualquier otro fondo, o use una referencia de celda para calcular para otros.
EDITAR:
Me di cuenta de que lo anterior solo funcionará para la primera letra de la secuencia, por favor use debajo, y nuevamente, ajuste la "B" a lo que necesite. Esto funcionará para cualquiera de las letras presentes. Además, ajuste todos los rangos para que coincidan con sus necesidades, solo hice filas 1:10 para mis propósitos de prueba. Lo siguiente ajusta el inicio del rango, por lo que siempre es el primer valor para cualquier fondo que se ingrese (es decir, "A", "B", "C") para permitir que se calcule. Sin este cambio, lo anterior siempre comenzará con un cero a menos que sea el primero en la lista ("A" en el ejemplo de OP), y XIRR solo calcula con un número negativo como primer valor.
=XIRR(IF(OFFSET($B$1:$B$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1)="B",OFFSET($F$1:$F$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1),0),IF(OFFSET($B$1:$B$10,MATCH("B",B1:B10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1)="B",OFFSET($A$1:$A$10,MATCH("B",$B$1:$B$10,0)-1,0,ROWS($B$1:$B$10)-MATCH("B",$B$1:$B$10,0)+1,1),0))
Estoy tratando de calcular XIRR para los datos que tengo en el siguiente formato
PurchaseDate Script No.ofunits PurchNAVrate NetAmount ForXIRR TotalReturn
17/11/2014 A 2241 33 75000 -75000 96000
8/1/2015 B 53 649 35000 -35000 43000
14/1/2015 B 75 658 50000 -50000 61500
14/10/2014 C 2319 32 75000 -75000 108000
8/1/2015 D 318 109 35000 -35000 40000
14/1/2015 D 450 110 50000 -50000 57000
8/6/2015 D 175 114 20000 -20000 22000
Probé muchas permutaciones y combinaciones diferentes pero no pude calcular XIRR para cada Fondo. ¿Hay alguna manera de hacerlo tal vez usando compensación o búsqueda? Esta es una lista dinámica que sigue cambiando según los nuevos fondos que se compran o se venden.
Values for Fund A should be around 14%
Values for Fund B should be around 13%
Values for Fund C should be around 21%
Values for Fund D should be around 8%
Actualización Dado que la solución mencionada por @ xor-lx funcionaba muy bien en ms excel pero no en las hojas de google, ahora he modificado la estructura de mis datos en base al asesoramiento de @kyle y ahora puedo calcular XIRR fácilmente para cada fondo como un todo . Consulte a continuación
PurchaseDate Today Script No.ofunits PurchNAVrate NetAmount ForXIRR TotalReturn XIRR
17/11/14 31/08/2016 A 2241 33 75000 -75000 96000 "=XIRR(G2:H2,A2:B2)"
Total for above fund "=XIRR(G2:H3,A2:B3)"
8/1/2015 31/08/2016 B 53 649 35000 -35000 43000 "=XIRR(G4:H4,A4:B4)"
14/1/2015 31/08/2016 B 75 658 50000 -50000 61500 "=XIRR(G5:H5,A5:B5)"
Total for above fund "=XIRR(G4:H6,A4:B6)"
14 Oct 14 31/08/2016 C 2319 32 75000 -75000 108000 "=XIRR(G7:H7,A7:B7)"
Total for above fund "=XIRR(G7:H8,A7:B8)"
8 Jan 15 31/08/2016 D 318 109 35000 -35000 40000 "=XIRR(G9:H9,A9:B9)"
14 Jan 15 31/08/2016 D 450 110 50000 -50000 57000 "=XIRR(G10:H10,A10:B10)"
8/6/2015 31/08/2016 D 175 114 20000 -20000 22000 "=XIRR(G11:H11,A11:B11)"
Total for above fund "=XIRR(G9:H12,A9:B12)"
XIRR requiere dos matrices de argumentos: valores y fechas (con un tercer argumento opcional "adivinar"). Intentar juntar esas matrices de rangos no contiguos de fechas de compra, devoluciones totales, montos de compra y varias "fechas de hoy" sería todo un desafío para una fórmula de Excel. Por lo tanto, proporciono una Función definida por el usuario escrita en VBA.
Los argumentos para la función son el fondo o la secuencia de comandos ( sFund ) y el rango de la tabla de datos distribuidos como lo hizo en su ejemplo anterior.
Como sugerí en uno de sus comentarios, utilicé TODAY como la fecha correspondiente a la columna Total Return . Se puede cambiar fácilmente si ese no es el caso.
Además, tenga en cuenta que no utilicé la columna "forXIRR", sino que acabo de utilizar el negativo de la columna NetAmount . Si elimina la columna forXIRR , tendrá que cambiar la referencia a .TotalReturn
Creé una Clase personalizada para hacer las cosas un poco más comprensibles.
Después de insertar el Módulo de clase, debe cambiarle el nombre cFUND
Módulo de clase
Option Explicit
''Rename cFUND
Private pFund As String
Private pPurchDt As Date
Private pPurchDts As Collection
Private pPurchAmt As Double
Private pPurchAmts As Collection
Private pTotalReturn As Double
Private pTotalReturns As Collection
Public Property Get Fund() As String
Fund = pFund
End Property
Public Property Let Fund(Value As String)
pFund = Value
End Property
Public Property Get PurchDt() As Date
PurchDt = pPurchDt
End Property
Public Property Let PurchDt(Value As Date)
pPurchDt = Value
End Property
Public Function ADDPurchDt(Value As Date)
pPurchDts.Add Value
End Function
Public Property Get PurchDts() As Collection
Set PurchDts = pPurchDts
End Property
Public Property Get PurchAmt() As Double
PurchAmt = pPurchAmt
End Property
Public Property Let PurchAmt(Value As Double)
pPurchAmt = Value
End Property
Public Function ADDPurchAmt(Value As Double)
pPurchAmts.Add Value
End Function
Public Property Get PurchAmts() As Collection
Set PurchAmts = pPurchAmts
End Property
Public Property Get TotalReturn() As Double
TotalReturn = pTotalReturn
End Property
Public Property Let TotalReturn(Value As Double)
pTotalReturn = Value
End Property
Public Function ADDTotalReturn(Value As Double)
pTotalReturns.Add Value
End Function
Public Property Get TotalReturns() As Collection
Set TotalReturns = pTotalReturns
End Property
Public Function CalcXIRR()
Dim v, d, w
Dim I As Long
With Me
ReDim d(.PurchDts.Count * 2 - 1)
ReDim v(UBound(d))
I = 0
For Each w In .PurchAmts
v(I) = w
I = I + 1
Next w
For Each w In .TotalReturns
v(I) = w
I = I + 1
Next w
I = 0
For Each w In .PurchDts
d(I) = w
I = I + 1
Next w
Do Until I > UBound(d)
d(I) = Date
I = I + 1
Loop
End With
CalcXIRR = WorksheetFunction.XIRR(v, d)
End Function
Private Sub Class_Initialize()
Set pPurchDts = New Collection
Set pPurchAmts = New Collection
Set pTotalReturns = New Collection
End Sub
Módulo regular
Option Explicit
Function fundXIRR(sFund As String, rTable As Range) As Double
Dim vSrc As Variant
Dim cF As cFUND
Dim I As Long
vSrc = rTable
Set cF = New cFUND
For I = 2 To UBound(vSrc, 1)
If vSrc(I, 2) = sFund Then
With cF
.PurchDt = vSrc(I, 1)
.ADDPurchDt .PurchDt
.Fund = vSrc(I, 2)
.PurchAmt = -vSrc(I, 5)
.ADDPurchAmt .PurchAmt
.TotalReturn = vSrc(I, 7)
.ADDTotalReturn .TotalReturn
End With
End If
Next I
fundXIRR = cF.CalcXIRR
End Function
Teniendo en cuenta sus datos anteriores, XIRR para B se calculará como:
XIRR({-35000;-50000;43000;61500},{"1/8/2015";"1/14/2015";"8/29/2016";"8/29/2016"}
Estos son los resultados para sus datos anteriores:
Suponiendo que su tabla está en A1:G8 (con encabezados en la fila 1), y que su Fondo preferido, por ejemplo, "B", está en J2 , matriz de fórmula **:
=XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8)))))),TODAY()))
Copie para obtener resultados similares para Fondos en J3 , J4 , etc.
Tiendo a preferir esto a las configuraciones que implican OFFSET ; no solo es más breve (y por lo tanto más eficiente), sino también, lo que es más importante, no volátil.
Consulte aquí para obtener más información si está interesado:
https://excelxor.com/2016/02/16/criteria-with-statistical-functions-growth-linest-logest-trend/
Saludos
** Las fórmulas de matriz no se ingresan de la misma manera que las fórmulas ''estándar''. En lugar de presionar simplemente ENTER, primero mantenga presionadas las teclas CTRL y SHIFT, y solo luego presione ENTER. Si lo ha hecho correctamente, notará que Excel coloca corchetes {} alrededor de la fórmula (aunque no intente insertarlos manualmente).