Podemos usar las fórmulas PMT, IPMT e IF de Excel para crear un cronograma de deuda. Primero, necesitamos configurar el modelo ingresando algunos supuestos de deuda. En este ejemplo, asumimos que la deuda es de $ 5,000,000, el plazo de pago es de 5 años y la tasa de interés Tasa de interés Una tasa de interés se refiere al monto que cobra un prestamista a un prestatario por cualquier forma de deuda dada, generalmente expresada como un porcentaje del principal. ser 4.5%.
1. El saldo inicial en nuestro programa de deuda es igual al monto del préstamo de $ 5 millones, por lo que en la celda E29 ingresamos = B25 para vincularlo con la entrada del supuesto. Entonces, podemos usar la fórmula PMT para calcular el pago total para el primer período = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . La fórmula calcula el monto del pago utilizando el monto del préstamo, el plazo y la tasa de interés indicados en la sección de supuestos.
2. En la celda E28, ingrese el período en el que estamos, que es 1. En la celda E29, ingrese = E28 + 1 y complete la fórmula a la derecha. Luego, use la fórmula de IPMT para averiguar el pago de intereses para el primer período = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. El pago de capital es la diferencia entre el pago total y el pago de intereses, que es = E30-E31 . El saldo de cierre es el saldo de apertura más el pago de principal que se realiza, que es = E29 + E32 . El saldo inicial del período 2 es el saldo final del período 1, que es = E33 .
4. Copie todas las fórmulas de la celda E29 a E33 en la siguiente columna, luego copie todo a la derecha. Verifique si el saldo de cierre del período 5 = 0 para asegurarse de que se estén utilizando fórmulas y números correctos.
5. Observe que hay algunos mensajes de error a partir del período 6 porque el saldo inicial es 0. Aquí podemos usar la función SI para limpiar los errores. En la celda E30, escriba = SI (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . La fórmula establece que si el saldo inicial es menor que 0, entonces el valor total del pago se mostrará como 0.
6. En la celda 31, escriba = SI (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Esta fórmula es similar a la anterior, que establece que si el saldo inicial es menor que 0, el pago de intereses se mostrará como 0.
7. Copie las celdas E30 y E31, presione MAYÚS + flecha derecha y luego CTRL + R para llenar a la derecha. Debería ver que todos los mensajes de error ahora se muestran como 0.
XNPV y XIRR con funciones DATE e IF
Podemos calcular el VPN y la TIR en base a fechas específicas usando las funciones de Excel XNPV y XIRR con las funciones FECHA e SI.
8. Vaya a la celda E6 e ingrese = FECHA (E5,12,31) para mostrar la fecha. Copiar a la derecha. Verá el #VALOR! mensaje después de 2021. Podemos arreglar esto usando la función IFERROR = IFERROR (DATE (E5,12,31), ””) .
9. Ahora podemos comenzar a calcular el VPN y la TIR. Primero, necesitamos ingresar los montos del flujo de efectivo libre. Suponemos que las cantidades de FCF del período 1 al 5 son -1,000, 500, 600, 700, 900. En la celda C37, ingresaremos una tasa de descuento del 15%. En la celda B37, calcule el VPN usando la fórmula XNPV = XNPV (C37, E35: I35, E6: I6) .
10. En la celda B38, calcule la TIR usando la fórmula XIRR = XIRR (E35: I35, E6: I6) .
Agregar OFFSET a XNPV y XIRR
Podemos cambiar a las fórmulas XNPV y XIRR para hacer fórmulas más dinámicas usando la función OFFSET.
11. En la celda B42, cambie la fórmula a = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . La fórmula es más dinámica porque si aumenta el número de períodos, también aumentarán los períodos de flujo de caja libre. No es necesario cambiar la fórmula del VPN si el período de pronóstico es más largo. Para la función TIR, cámbiela a = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Después de ajustar la fórmula por el número de períodos, debemos compensar las fechas. En la celda B42, cambie la fórmula a = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Esto permite que las fórmulas VAN y TIR recojan el número correcto de flujo de caja libre con el cambio en el número de períodos.
Resumen de fórmulas clave del cronograma de deuda
- Fórmula PMT para calcular el monto del pago de la deuda: = PMT (tasa de interés, número de términos, valor presente)
- Fórmula IPMT para calcular el pago de intereses: = IPMT (tasa de interés, período, número de términos, valor actual)
- Fórmula XNPV para encontrar el valor actual neto: = XNPV (tasa de descuento, flujos de efectivo libres, fechas)
- Fórmula XIRR para encontrar la tasa interna de rendimiento: = XIRR (flujos de efectivo libres, fechas)
- Fórmula OFFSET para calcular el VPN dinámico: = XNPV (tasa de descuento, 1er FCF: OFFSET (1er FCF, 0, # períodos - 1), 1ra fecha: OFFSET (1er fecha, 0, # períodos - 1))
- Fórmula OFFSET para calcular la TIR dinámica: = XIRR (1er FCF: OFFSET (1er FCF, 0, # periodos - 1), 1ra fecha: OFFSET (1er fecha, 0, # periodos - 1))
Otros recursos
Gracias por leer la guía de Finanzas sobre el programa de deuda con fórmulas PMT, IPMT e IF. Para seguir aprendiendo y avanzando en su carrera, los siguientes recursos financieros serán útiles:
- Fórmulas básicas de Excel Fórmulas básicas de Excel El dominio de las fórmulas básicas de Excel es fundamental para que los principiantes se vuelvan competentes en el análisis financiero. Microsoft Excel se considera el software estándar de la industria en el análisis de datos. El programa de hoja de cálculo de Microsoft también es uno de los programas preferidos por los banqueros de inversión.
- Mejores prácticas de modelado financiero Mejores prácticas de modelado financiero Este artículo tiene como objetivo proporcionar a los lectores información sobre las mejores prácticas de modelado financiero y una guía paso a paso fácil de seguir para crear un modelo financiero.
- Lista de funciones de Excel Funciones Lista de las funciones de Excel más importantes para los analistas financieros. Esta hoja de trucos cubre cientos de funciones que son fundamentales para conocer como analista de Excel
- Descripción general de los accesos directos de Excel Descripción general de los accesos directos de Excel Los accesos directos de Excel son un método que se pasa por alto para aumentar la productividad y la velocidad en Excel. Los atajos de Excel ofrecen al analista financiero una herramienta poderosa. Estos atajos pueden realizar muchas funciones. tan simple como navegar dentro de la hoja de cálculo para completar fórmulas o agrupar datos.
