excel - spreadsheets - tablas en google sheets

cómo calcular XIRR dinámicamente en excel y en hojas de google

Question

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)"

Answer 1

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))

Answer 2

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:

Answer 3

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).