Las 15 funciones financieras principales en Excel

Las 15 funciones financieras principales en Excel

Microsoft Excel es la herramienta más importante de los banqueros de inversión y analistas financieros. Pasaron más del 70% del tiempo preparando modelos de Excel, formulando supuestos, valoraciones, cálculos, gráficos, etc. Es seguro asumir que los banqueros de inversión son expertos en fórmulas y atajos de Excel. Aunque hay más de 50 funciones financieras en Excel, aquí está la lista de las 15 funciones financieras principales en Excel que se utilizan con más frecuencia en situaciones prácticas.

Sin mucho preámbulo, echemos un vistazo a todas las funciones financieras una por una:

    # 1 - Valor futuro (FV): función financiera en Excel 

    Si desea averiguar el valor futuro de una inversión en particular que tiene una tasa de interés constante y un pago periódico, utilice la siguiente fórmula:

    FV (Tasa, Nper, [Pmt], PV, [Tipo])

    • Tasa = Es la tasa de interés / período
    • Nper = Número de períodos
    • [Pmt] = Pago / período
    • PV = valor actual
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)

    Ejemplo de FV

    A ha invertido los US $ 100 en 2016. El pago se ha realizado anualmente. La tasa de interés es del 10% anual. ¿Cuál sería el VF en 2019?

    Solución: en Excel, pondremos la ecuación de la siguiente manera:

    = FV (10%, 3, 1, - 100)

    = 129,79 $

    # 2 - FVSCHEDULE : Función financiera en Excel 

    Esta función financiera es importante cuando necesita calcular el valor futuro con la tasa de interés variable. Eche un vistazo a la función a continuación:

    FVSCHEDULE = (Director, Horario)

    • Principal = Principal es el valor actual de una inversión en particular
    • Schedule = Una serie de tasas de interés juntas (en el caso de Excel, usaremos diferentes casillas y seleccionaremos el rango)

    Ejemplo de FVSCHEDULE:

    M ha invertido US $ 100 a fines de 2016. Se espera que la tasa de interés cambie cada año. En 2017, 2018 y 2019, las tasas de interés serían del 4%, 6% y 5% respectivamente. ¿Cuál sería la FV en 2019?

    Solución: en Excel, haremos lo siguiente:

    = FVSHEDULE (C1, C2: C4)

    = 115,752 dólares

    # 3 - Valor presente (PV) : función financiera en Excel 

    Si sabe cómo calcular el FV, le resultará más fácil averiguar el PV. Así es cómo -

    PV = (Tasa, Nper, [Pmt], FV, [Tipo])

    • Tasa = Es la tasa de interés / período
    • Nper = Número de períodos
    • [Pmt] = Pago / período
    • FV = Valor futuro
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)

    Ejemplo de PV:

    El valor futuro de una inversión es de US $ 100 en 2019. El pago se ha realizado anualmente. La tasa de interés es del 10% anual. ¿Cuál sería el VP a partir de ahora?

    Solución: en Excel, pondremos la ecuación de la siguiente manera:

    = PV (10%, 3, 1, - 100)

    = 72,64 dólares EE.UU.

    # 4 - Valor actual neto (VPN) : función financiera en Excel 

    El valor actual neto es la suma total de los flujos de efectivo positivos y negativos a lo largo de los años. Así es como lo representaremos en Excel:

    VPN = (Tasa, Valor 1, [Valor 2], [Valor 3]…)

    • Tasa = Tasa de descuento por un período
    • Valor 1, [Valor 2], [Valor 3]… = Flujos de caja positivos o negativos
    • Aquí, los valores negativos se considerarían pagos y los valores positivos se tratarían como entradas.

    Ejemplo de VPN

    Aquí hay una serie de datos a partir de los cuales necesitamos encontrar el VPN:

    DetallesEn US $
    Tasa de descuento5%
    Inversión inicial-1000
    Regreso del 1er año300
    Regreso del 2do año400
    Regreso del 3er año400
    Regreso del 4to año300

    Descubra el VPN.

    Solución: en Excel, haremos lo siguiente:

    = VAN (5%, B4: B7) + B3

    = 240,87 dólares EE.UU.

    Además, eche un vistazo a este artículo: NPV vs IRR

    # 5 - XNPV : Función financiera en Excel 

    Esta función financiera es similar al VPN con un toque. Aquí el pago y los ingresos no son periódicos. Se mencionan fechas más bien específicas para cada pago e ingreso. Así es como lo calcularemos:

    XNPV = (Tasa, Valores, Fechas)

    • Tasa = Tasa de descuento por un período
    • Valores = Flujos de efectivo positivos o negativos (una matriz de valores)
    • Fechas = fechas específicas (una serie de fechas)

    Ejemplo de XNPV

    Aquí hay una serie de datos a partir de los cuales necesitamos encontrar el VPN:

    DetallesEn US $fechas
    Tasa de descuento5%
    Inversión inicial-10001 de diciembre de 2011
    Regreso del 1er año3001 de enero de 2012
    Regreso del 2do año4001 de febrero de 2013
    Regreso del 3er año4001 de marzo de 2014
    Regreso del 4to año3001 de abril de 2015

    Solución: en Excel, haremos lo siguiente:

    = XNPV (5%, B2: B6, C2: C6)

    = 289,90 dólares EE.UU.

    # 6 - PMT : función financiera en Excel 

    En Excel, PMT denota el pago periódico requerido para liquidar por un período de tiempo particular con una tasa de interés constante. Echemos un vistazo a cómo calcularlo en Excel:

    PMT = (Tasa, Nper, PV, [FV], [Tipo])

    • Tasa = Es la tasa de interés / período
    • Nper = Número de períodos
    • PV = valor actual
    • [FV] = Un argumento opcional que trata sobre el valor futuro de un préstamo (si no se menciona nada, FV se considera "0")
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)

    Ejemplo de PMT

    Se deben pagar 1000 dólares estadounidenses en su totalidad en 3 años. La tasa de interés es del 10% anual y el pago debe realizarse anualmente. Descubra el PMT.

    Solución: en Excel, lo calcularemos de la siguiente manera:

    = PAGO (10%, 3, 1000)

    = - 402,11

    # 7 - PPMT : Función financiera en Excel 

    Es otra versión de PMT. La única diferencia es esta: PPMT calcula el pago sobre el principal con una tasa de interés constante y pagos periódicos constantes. He aquí cómo calcular PPMT:

    PPMT = (Tasa, Por, Nper, PV, [FV], [Tipo])

    • Tasa = Es la tasa de interés / período
    • Per = El período para el cual se calculará el principal
    • Nper = Número de períodos
    • PV = valor actual
    • [FV] = Un argumento opcional que trata sobre el valor futuro de un préstamo (si no se menciona nada, FV se considera "0")
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)

    Ejemplo de PPMT

    Se deben pagar 1000 dólares estadounidenses en su totalidad en 3 años. La tasa de interés es del 10% anual y el pago debe realizarse anualmente. Descubra el PPMT en el primer y segundo año.

    Solución: en Excel, lo calcularemos de la siguiente manera:

    1er año,

    = PPMT (10%, 1, 3, 1000)

    = US $ -302.11 

    Segundo año,

    = PPMT (10%, 2, 3, 1000)

    = US $ -332,33

    # 8 - Tasa Interna de Retorno (TIR) : Función Financiera en Excel 

    Para comprender si un nuevo proyecto o inversión es rentable o no, la empresa utiliza la TIR. Si la TIR es mayor que la tasa de rentabilidad (tasa aceptable / costo promedio de capital), entonces es rentable para la empresa y viceversa. Echemos un vistazo, cómo averiguamos la TIR en Excel:

    TIR = (valores, [adivinar])

    • Valores = Flujos de efectivo positivos o negativos (una matriz de valores)
    • [Adivina] = Una suposición de lo que crees que debería ser la TIR

    Ejemplo de TIR

    Aquí hay una serie de datos a partir de los cuales necesitamos encontrar la TIR:

    DetallesEn US $
    Inversión inicial-1000
    Regreso del 1er año300
    Regreso del 2do año400
    Regreso del 3er año400
    Regreso del 4to año300

    Descubra la TIR.

    Solución: así es como calcularemos la TIR en Excel:

    = TIR (A2: A6, 0,1)

    = 15%

    # 9 - Tasa interna de retorno modificada (MIRR) : función financiera en Excel 

    La Tasa Interna de Retorno Modificada está un paso por delante de la Tasa Interna de Retorno. MIRR significa que la inversión es rentable y se utiliza en los negocios. La MIRR se calcula asumiendo el VPN como cero. Aquí se explica cómo calcular MIRR en Excel:

    MIRR = (Valores, Tasa de financiación, Tasa de reinversión)

    • Valores = Flujos de efectivo positivos o negativos (una matriz de valores)
    • Tasa financiera = Tasa de interés pagada por el dinero utilizado en los flujos de efectivo
    • Tasa de reinversión = Tasa de interés pagada por la reinversión de los flujos de efectivo

    Ejemplo de MIRR

    Aquí hay una serie de datos de los que necesitamos encontrar MIRR:

    DetallesEn US $
    Inversión inicial-1000
    Regreso del 1er año300
    Regreso del 2do año400
    Regreso del 3er año400
    Regreso del 4to año300

    Tasa de financiación = 12%; Tasa de reinversión = 10%. Descubra la TIR.

    Solución: Veamos el cálculo de MIRR -

    = TIRM (B2: B6, 12%, 10%)

    = 13%

    # 10 - XIRR : Función financiera en Excel 

    Aquí tenemos que averiguar la TIR que tiene fechas específicas de flujo de caja. Esa es la única diferencia entre IRR y XIRR. Eche un vistazo a cómo calcular XIRR en la función financiera de Excel:

    XIRR = (valores, fechas, [adivinar])

    • Valores = Flujos de efectivo positivos o negativos (una matriz de valores)
    • Fechas = fechas específicas (una serie de fechas)
    • [Adivina] = Una suposición de lo que crees que debería ser la TIR

    Ejemplo de XIRR

    Aquí hay una serie de datos a partir de los cuales necesitamos encontrar XIRR:

    DetallesEn US $fechas
    Inversión inicial-10001 de diciembre de 2011
    Regreso del 1er año3001 de enero de 2012
    Regreso del 2do año4001 de febrero de 2013
    Regreso del 3er año4001 de marzo de 2014
    Regreso del 4to año3001 de abril de 2015

    Solución: echemos un vistazo a la solución:

    = XIRR (B2: B6, C2: C6, 0,1)

    = 24%

    # 11 - NPER : Función financiera en Excel 

    Es simplemente el número de períodos que uno requiere para liquidar el préstamo. Veamos cómo podemos calcular NPER en Excel -

    NPER = (Tarifa, PMT, PV, [FV], [Tipo])

    • Tasa = Es la tasa de interés / período
    • PMT = Importe pagado por período
    • PV = valor actual
    • [FV] = Un argumento opcional que trata sobre el valor futuro de un préstamo (si no se menciona nada, FV se considera "0")
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)

    Ejemplo de NPER

    Se pagan US $ 200 por año por un préstamo de US $ 1000. La tasa de interés es del 10% anual y el pago debe realizarse anualmente. Descubra el NPER.

    Solución: Necesitamos calcular NPER de la siguiente manera:

    = NPER (10%, -200, 1000)

    = 7,27 años

    # 12 - TARIFA : Función financiera en Excel 

    A través de la función TASA en Excel, podemos calcular la tasa de interés necesaria para liquidar el préstamo en su totalidad durante un período de tiempo determinado. Echemos un vistazo a cómo calcular la función financiera TASA en Excel:

    TASA = (NPER, PMT, PV, [FV], [Type], [Guess])

    • Nper = Número de períodos
    • PMT = Importe pagado por período
    • PV = valor actual
    • [FV] = Un argumento opcional que trata sobre el valor futuro de un préstamo (si no se menciona nada, FV se considera "0")
    • [Tipo] = Cuando se realiza el pago (si no se menciona nada, se supone que el pago se realizó al final del período)
    • [Adivina] = Una suposición de lo que crees que debería ser RATE

    Ejemplo de tarifa

    Se pagan US $ 200 por año por un préstamo de US $ 1000 durante 6 años y el pago debe hacerse anualmente. Descubra la TARIFA.

    Solución:

    = TASA (6, -200, 1000, 0.1)

    = 5%

    # 13 - EFECTO : Función financiera en Excel 

    A través de la función EFECTO, podemos comprender la tasa de interés anual efectiva. Cuando tenemos la tasa de interés nominal y el número de capitalizaciones por año, resulta fácil encontrar la tasa efectiva. Echemos un vistazo a cómo calcular la función financiera EFECTO en Excel:

    EFECTO = (Nominal_Rate, NPERY)

    • Nominal_Rate = Tasa de interés nominal
    • NPERY = Número de capitalización por año

    EFECTO Ejemplo

    El pago debe pagarse con una tasa de interés nominal del 12% cuando la cantidad de capitalización anual es 12.

    Solución:

    = EFECTO (12%, 12)

    = 12,68%

    # 14 - NOMINAL : Función financiera en Excel 

    Cuando tenemos una tasa anual efectiva y el número de períodos de capitalización por año, podemos calcular la tasa NOMINAL para el año. Echemos un vistazo a cómo hacerlo en Excel:

    NOMINAL = (Effect_Rate, NPERY)

    • Effect_Rate = Tasa de interés anual efectiva
    • NPERY = Número de capitalización por año

    Ejemplo NOMINAL

    El pago debe pagarse con una tasa de interés efectiva o una tasa equivalente anual del 12% cuando el número de capitalización por año es 12.

    Solución:

    = NOMINAL (12%, 12)

    = 11,39%

    # 15 - SLN : Función financiera en Excel 

    A través de la función SLN, podemos calcular la depreciación mediante un método de línea recta. En Excel, analizaremos la función financiera de SLN de la siguiente manera:

    SLN = (Costo, Salvamento, Vida)

    • Costo = Costo del activo cuando se compra (monto inicial)
    • Salvamento = Valor del activo después de la depreciación
    • Vida = Número de períodos durante los cuales se deprecia el activo

    Ejemplo de SLN

    El costo inicial de la maquinaria es de US $ 5000. Se ha depreciado según el método de línea recta. La maquinaria se utilizó durante 10 años y ahora el valor residual de la maquinaria es de 300 dólares. Encuentre la depreciación cobrada por año.

    Solución:

    = SLN (5000, 300, 10)

    = US $ 470 por año

    También puede consultar la Guía completa de depreciación