¿Te encuentras en una situación de deuda financiera y no sabes por dónde empezar para pagarla? No te preocupes, estás en el lugar indicado. En este artículo te mostraremos cómo crear un plan de deuda utilizando las fórmulas PMT, IPMT e IF, herramientas esenciales que te ayudarán a calcular y organizar tus pagos de manera efectiva. Con esta guía, podrás tomar el control de tus finanzas y comenzar a liberarte de la carga de la deuda. ¡Sigue leyendo para descubrir cómo hacerlo!
Utilice las fórmulas PMT, IPMT e IF de Excel para crear un plan de deuda
Más de 1,8 millones de profesionales utilizan CFI para aprender contabilidad, análisis financiero, modelado y más. Comience con una cuenta gratuita para explorar más de 20 cursos siempre gratuitos y cientos de plantillas financieras y hojas de trucos.
¿Cómo crear un plan de deuda con PMT, IPMT e IF?
Podemos utilizar las fórmulas PMT, IPMT e IF de Excel para crear un plan de deuda. Primero necesitamos configurar el modelo ingresando algunos supuestos de deuda. En este ejemplo, asumimos que la deuda es de .000.000, el plazo de pago es de 5 años y la tasa de interés es del 4,5%.
1. El saldo inicial en nuestro cronograma de deuda es el monto del préstamo de $5 millones, por lo que lo ingresamos en la celda E29. =B25 para asociarlo con la entrada de aceptación. Luego podemos usar la fórmula PMT para calcular el pago total para el primer período. =Pago($B$27,$B$26,$B$25). La fórmula calcula el monto del pago en función del monto del préstamo, el plazo y la tasa de interés especificados en la sección «Supuesto».
2. En la celda E28, ingrese el período de tiempo en el que nos encontramos, entonces 1. En la celda E29, ingrese =E28+1 y complete la fórmula a la derecha. A continuación, utilice la fórmula IPMT para determinar el pago de intereses para el primer período. =IPMT($B$27,E28,$B$26,$B$25).
3. El pago de principal es la diferencia entre el pago total y el pago de intereses. =E30-E31. El saldo final es el saldo inicial más el pago de principal realizado. =E29+E32. El saldo inicial del Período 2 es el saldo final del Período 1 =E33.
4. Copie todas las fórmulas de la celda E29 a E33 a la siguiente columna y luego todo lo que está a la derecha. Verifique que el saldo final del período 5 = 0 para garantizar que se utilicen fórmulas y números correctos.
5. Tenga en cuenta que hay algunos mensajes de error a partir del período 6 porque el saldo inicial es 0. Aquí podemos usar la función IF para limpiar los errores. En la celda E30, ingrese lo siguiente: =SI(E29>0,PAGO($B$27,$B$26,$B$25),0). La fórmula establece que si el saldo inicial es inferior a 0, el valor total del pago se mostrará como 0.
6. Ingrese en la celda 31 =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 completar los campos de la derecha. Debería ver que todos los mensajes de error ahora se muestran como 0.
XNPV y XIRR con funciones FECHA e IF
Usando las funciones de Excel XNPV y XIRR con las funciones FECHA e IF, podemos calcular el NPV y la TIR en función de datos específicos.
8. Vaya a la celda E6 e ingrese =FECHA(E5,12,31) para mostrar la fecha. Copiar a la derecha. ¡Verás el #VALOR! Mensaje posterior a 2021. Podemos solucionar este problema utilizando la función IFERROR =SIERROR(FECHA(E5,12,31)»,»).
9. Ahora podemos empezar a calcular el VAN y la TIR. Primero debemos ingresar los montos del flujo de efectivo libre. Suponemos que los montos del FCF del período 1 al 5 son -1000, 500, 600, 700, 900. En la celda C37 ingresamos una tasa de descuento del 15%. En la celda B37, calcule el VAN 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
Usando la función OFFSET podemos modificar las fórmulas XNPV y XIRR para crear fórmulas más dinámicas.
11. En la celda B42, cambie la fórmula a =XNPV(C42,E40:COMPENSACIÓN(E40,0,$F$3-1),E6:I6). La fórmula es más dinámica porque a medida que aumenta el número de períodos, también aumentan los períodos de flujo de caja libre. No necesitamos 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:DESPLAZAMIENTO(E40,0,$F$3-1),E6:I6).
12. Después de ajustar la fórmula para el número de períodos, debemos compensar los datos. En la celda B42, cambie la fórmula a =XNPV(C42,E40:DESPLAZAMIENTO(E40,0,$F$3-1),E6:DESPLAZAMIENTO(E6,0,$F$3-1)). Esto permite que las fórmulas VAN y TIR determinen la cantidad correcta de flujo de caja libre a medida que cambia la cantidad de períodos.
Resumen de fórmulas clave del plan de deuda
- Fórmula PMT para calcular el monto de pago de la deuda: =PMT(tasa de interés, número de plazos, valor en efectivo)
- Fórmula IPMT para calcular el pago de intereses: =IPMT (tasa de interés, período, número de plazos, valor presente)
- Fórmula XNPV para determinar el VPN: =XNPV(tasa de descuento, flujos de efectivo libres, fechas)
- Fórmula XIRR para determinar la tasa interna de retorno: =XIRR(flujos de efectivo libres, fechas)
- Fórmula de COMPENSACIÓN para calcular el VPN dinámico: =XNPV(Tasa de descuento, 1calle FCF: COMPENSACIÓN(1calle FCF, 0, # períodos – 1), 1calle Fecha: DESPLAZAMIENTO(1calle Fecha, 0, # Períodos – 1))
- Fórmula OFFSET para calcular la TIR dinámica: =XIRR(1calle FCF: COMPENSACIÓN(1calle FCF, 0, # períodos – 1), 1calle Fecha: DESPLAZAMIENTO(1calle Fecha, 0, # Períodos – 1))
Otros recursos
Gracias por leer la guía de Finanzas para crear un plan de deuda con fórmulas PMT, IPMT e IF. Para continuar aprendiendo y avanzar en su carrera, los siguientes recursos de CFI son útiles:
