Cómo crear un programa de deuda con PMT, IPMT e IF?
Podemos utilizar Excel’s PMT, IPMT, y fórmulas IF para crear un calendario de deuda. En primer lugar, tenemos que configurar el modelo introduciendo algunos supuestos de deuda. En este ejemplo, suponemos que la deuda es de 5.000.000 de dólares, el plazo de pago es de 5 años y el tipo de interésTipo de interésUn tipo de interés se refiere a la cantidad que cobra un prestamista a un prestatario por cualquier forma de deuda contraída, generalmente expresada como un porcentaje del principal. para que sea 4.5%.
1. El saldo inicial de nuestro calendario de deuda es igual al importe del préstamo de 5 millones de dólares, por lo que en la celda E29, introducimos =B25 para enlazarlo con la entrada del supuesto. A continuación, podemos utilizar la fórmula PMT para calcular el pago total del primer período =PMT($B$27,$B$26,$B$25). La fórmula calcula el importe del pago utilizando el importe del préstamo, el plazo y el tipo de interés indicados en la sección de supuestos.
2. En la celda E28, introduce el periodo en el que nos encontramos, que es el 1. En la celda E29, introduzca =E28+1 y rellena la fórmula de la derecha. A continuación, utiliza la fórmula IPMT para averiguar el pago de intereses del primer período =IPMT($B$27,E28,$B$26,$B$25).
3. El pago del principal es la diferencia entre el pago total y el pago de intereses, que es =E30-E31. El saldo final es el saldo inicial más el pago del principal, que es =E29+E32. El saldo inicial del periodo 2 es el saldo final del periodo 1, es decir =E33.
4. Copia todas las fórmulas de las celdas E29 a E33 a la siguiente columna, y luego copia todo a la derecha. Comprueba si el saldo final del periodo 5 = 0 para asegurarte de que se utilizan las fórmulas y los números correctos.
5. Observe que hay algunos mensajes de error a partir del periodo 6 porque el saldo inicial es 0. Aquí podemos utilizar la función IF para limpiar los errores. En la celda E30, escribimos =IF(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 del pago total se mostrará como 0.
6. En la celda 31, escribe =IF(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, entonces el pago de intereses se mostrará como 0.
7. Copiar las celdas E30 y E31, pulsar SHIFT + flecha derecha, luego CTRL + R para rellenar a la derecha. Debería ver que todos los mensajes de error se muestran ahora como 0.
XNPV y XIRR con las funciones DATE y IF
Podemos calcular el VAN y la TIR en función de fechas concretas utilizando las funciones de Excel XNPV y XIRR con las funciones DATE e IF.
8. Ve a la celda E6 e introduce =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 empezar a calcular el VAN y la TIR. En primer lugar, tenemos que introducir los importes del flujo de caja libre. Suponemos que las cantidades de FCF del periodo 1 al 5 son -1.000, 500, 600, 700, 900. En la celda C37, introduciremos una tasa de descuento del 15%. En la celda B37, calcula el VAN utilizando la fórmula XNPV =XNPV(C37,E35:I35,E6:I6).
10. En la celda B38, calcula la TIR utilizando la fórmula XIRR =XIRR(E35:I35,E6:I6).
Cómo añadir OFFSET a XNPV y XIRR
Podemos cambiar las fórmulas XNPV y XIRR para hacer fórmulas más dinámicas utilizando 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 el número de períodos aumenta, los períodos de flujo de caja libre también aumentarán. No’No es necesario cambiar la fórmula del VAN si el período de previsión es más largo. Para la función TIR, cámbiala por =XIRR(E40:OFFSET(E40,0,$F$3-1),E6:I6).
12. Después de ajustar la fórmula para el número de períodos, debemos compensar las fechas. En la celda B42, cambia 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 las fórmulas clave del calendario de deuda
Otros recursos
Gracias por leer nuestro sitio web’Guía de cómo crear un calendario de deuda con fórmulas PMT, IPMT y IF. Para seguir aprendiendo y avanzar en su carrera, los siguientes recursos de nuestro sitio web le serán útiles:
Tutorial gratuito de Excel
Para dominar el arte de Excel, consulte nuestra página web’s FREE Excel Crash CourseExcel Fundamentals – Formulas for Finance¿Está buscando un curso acelerado de Excel? Obtenga formación gratuita en Excel para una carrera en finanzas corporativas y banca de inversión del Instituto de Finanzas Corporativas., que te enseña a convertirte en un usuario avanzado de Excel. Aprende las fórmulas, funciones y atajos más importantes para tener confianza en tus análisis financieros.
Acceda a nuestro sitio web’Curso gratuito de Excel ahoraFormulas de Excel para Finanzas¿Está buscando un curso intensivo de Excel?? Obtenga formación gratuita en Excel para una carrera en finanzas corporativas y banca de inversión del Instituto de Finanzas Corporativas.
para llevar su carrera al siguiente nivel y ascender en el escalafón!