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:
Detalles | En US $ |
Tasa de descuento | 5% |
Inversión inicial | -1000 |
Regreso del 1er año | 300 |
Regreso del 2do año | 400 |
Regreso del 3er año | 400 |
Regreso del 4to año | 300 |
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:
Detalles | En US $ | fechas |
Tasa de descuento | 5% | |
Inversión inicial | -1000 | 1 de diciembre de 2011 |
Regreso del 1er año | 300 | 1 de enero de 2012 |
Regreso del 2do año | 400 | 1 de febrero de 2013 |
Regreso del 3er año | 400 | 1 de marzo de 2014 |
Regreso del 4to año | 300 | 1 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:
Detalles | En US $ |
Inversión inicial | -1000 |
Regreso del 1er año | 300 |
Regreso del 2do año | 400 |
Regreso del 3er año | 400 |
Regreso del 4to año | 300 |
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:
Detalles | En US $ |
Inversión inicial | -1000 |
Regreso del 1er año | 300 |
Regreso del 2do año | 400 |
Regreso del 3er año | 400 |
Regreso del 4to año | 300 |
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:
Detalles | En US $ | fechas |
Inversión inicial | -1000 | 1 de diciembre de 2011 |
Regreso del 1er año | 300 | 1 de enero de 2012 |
Regreso del 2do año | 400 | 1 de febrero de 2013 |
Regreso del 3er año | 400 | 1 de marzo de 2014 |
Regreso del 4to año | 300 | 1 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