es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

  • es.davy.ai/
  • Programación /
  • Recorra una lista de Inversionistas y calcule la TIR para cada uno de ellos para automatizar el proceso.

Recorra una lista de Inversionistas y calcule la TIR para cada uno de ellos para automatizar el proceso.

05 Aug, 2023 Programación

Entonces, he estado atascado en este problema durante unos días. He revisado algunos códigos de otras personas pero sigo sin encontrar la solución. Tampoco soy muy bueno en VBA.

Tengo una lista de inversores con sus pagos y fechas adjuntas. Estoy intentando ejecutar un botón que recorra cada cuenta, encuentre sus pagos y fechas relacionadas, ejecute la función XIRR y luego coloque el valor de XIRR en la parte inferior a la derecha de cada cuenta. Esto es bastante simple de hacer a mano, pero cuando tienes una hoja de cálculo de más de 15000 celdas, se vuelve tedioso y estoy intentando automatizar este proceso. Se vuelve difícil porque cada inversor tiene diferentes cantidades de pago, por lo que encontrar la ubicación correcta para colocar el valor de XIRR también me ha dejado perplejo.

Aquí tienes un ejemplo de mi hoja de cálculo.

Imagen de la hoja de cálculo

Dim i As Integer
Dim x As Double
Dim dateArray() As Date
Dim dateStrings() As String
Dim valArray() As Double

Redim dateArray(Dates.Count)
Redim valArray(Trans.Count)
Redim dateStrings(Dates.Count)

‘Sheets(“InvestorList”).PivotTables.GetPivotData(“Account”, “x”) = i
‘Sheets(“AccountPayments”).Find (“i”)
End Sub

Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double)

For i = 1 To Dates.Count
    dateArray(i - 1) = Dates.Item(i).Value
    Next i

For i = 1 To Trans.Count
    valArray(i - 1) = Trans.Item(i).Value
    Next i

'Establece la fecha en la línea "Balance" a un día después de la fecha de la última transacción
dateArray(Dates.Count) = DateAdd("d", 1, Dates.Item(Dates.Count))
 valArray(Trans.Count) = -1 * Balance

For i = 0 To Dates.Count
   dateStrings(i) = Format(dateArray(i), "mm/dd/yyyy")
   Next i

MyXIRR = Application.WorksheetFunction.Xirr(valArray, dateStrings)

End Function

Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Basándose en la información proporcionada, parece que el problema principal es determinar la ubicación correcta para colocar el valor de XIRR para cada cuenta. Un enfoque podría ser utilizar VBA para recorrer cada fila y encontrar los pagos correspondientes para cada cuenta, luego calcular el XIRR y colocarlo en la celda correcta. Aquí hay un código de muestra que podría adaptarse para este propósito:

    Sub CalcularXIRR()

    Dim currAcct As String
    Dim currRow As Long
    Dim lastRow As Long
    Dim payments As Range
    Dim dates As Range
    Dim balance As Double
    Dim xirrVal As Double

    lastRow = Sheets("InvestorList").Cells(Rows.Count, "A").End(xlUp).Row

    For currRow = 2 To lastRow
        currAcct = Sheets("InvestorList").Cells(currRow, "A").Value
        Set payments = Sheets("AccountPayments").Range("A2:A" & Sheets("AccountPayments").Cells(Rows.Count, "A").End(xlUp).Row)
        Set dates = Sheets("AccountPayments").Range("B2:B" & Sheets("AccountPayments").Cells(Rows.Count, "B").End(xlUp).Row)
        balance = Sheets("InvestorList").Cells(currRow, "B").Value

        ' Filtrar la cuenta actual y sumar los pagos
        payments.AutoFilter Field:=1, Criteria1:=currAcct
        balance = balance + WorksheetFunction.Sum(payments.Offset(0, 1).SpecialCells(xlCellTypeVisible))

        ' Crear arreglos para el cálculo de xirr
        Dim dateArray() As Date
        Dim valArray() As Double
        Dim numRows As Long
        Dim visibleRows As Range
        Set visibleRows = dates.Offset(0, 1).SpecialCells(xlCellTypeVisible)
        numRows = visibleRows.Count
        ReDim dateArray(numRows)
        ReDim valArray(numRows)
        Dim i As Long
        For i = 1 To numRows
            dateArray(i - 1) = visibleRows(i).Value
            valArray(i - 1) = -1 * payments.Offset(0, 1).SpecialCells(xlCellTypeVisible)(i).Value
        Next i

        ' Calcular xirr y colocar en la celda correcta
        xirrVal = MyXIRR(dateArray, valArray, balance)
        Sheets("InvestorList").Cells(currRow, "C").Value = xirrVal

        payments.AutoFilter
    Next currRow

End Sub

    Este código recorre cada fila en la hoja “InvestorList” y hace lo siguiente:

    1. Obtiene el número de cuenta actual y recupera todos los pagos y fechas para esa cuenta desde la hoja “AccountPayments”.
    2. Filtra los pagos solo para los de la cuenta actual, luego suma los pagos para obtener el saldo inicial para el cálculo de XIRR.
    3. Crea arreglos para las fechas y los valores de pago que se utilizarán en el cálculo de XIRR.
    4. Llama a la función MyXIRR con los argumentos apropiados y obtiene el valor de XIRR.
    5. Coloca el valor de XIRR en la celda correcta en la hoja “InvestorList”.
    6. Borra el filtro del rango de pagos.

    Ten en cuenta que este código asume que los números de cuenta están en la columna A de la hoja “InvestorList”, los saldos iniciales están en la columna B y los valores de XIRR deben colocarse en la columna C. Ajusta los rangos según sea necesario para adaptarse a tu diseño de hoja de cálculo específico.

    ¡Espero que esto ayude! Avísame si tienes alguna pregunta o necesitas más ayuda.

Comments are closed.