¿Buscas una forma eficiente de administrar tus préstamos y pagos? ¡No busques más! En este tutorial de entrenamiento de Excel te mostraremos cómo crear un plan de pagos de préstamos. Si siempre has querido optimizar tu administración financiera y tener un control preciso de tus deudas, este artículo es para ti. No pierdas más tiempo y descubre cómo utilizar Excel para organizar y planificar tus pagos de una manera sencilla y efectiva. ¡Comienza a tomar el control de tus finanzas hoy mismo!
¿Qué es un plan de pago?
El Plan de pago del préstamo describe los gastos por intereses y los pagos de principal adeudados por un préstamo, como una hipoteca, incluido el saldo pendiente del financiamiento.
¿Cómo calcular el calendario de amortización?
El plan de pago del préstamo describe la distribución de los pagos de intereses y el pago del principal durante la vigencia del préstamo.
El Prestatario está obligado a cumplir con sus obligaciones de pago de acuerdo con el cronograma establecido en el acuerdo contractual con el Prestamista en virtud del Acuerdo de Financiamiento.
En particular, existen dos tipos de pago de préstamos: 1) gastos por intereses y 2) reembolso del principal.
- Gastos por intereses → El componente de intereses refleja los costos de endeudamiento, es decir, el riesgo crediticio asociado con proporcionar financiamiento de deuda al prestatario lo incluye el prestamista en la tasa de interés.
- Recuperación del principal → Los pagos de amortización, por otro lado, representan la amortización gradual del capital original a lo largo del plazo.
Durante la vida del préstamo, el valor en libros del préstamo disminuye gradualmente hasta que el saldo restante llega a cero en la fecha de vencimiento.
Si el saldo realmente llega a cero, significa que el prestatario ha cumplido con todas sus obligaciones de deuda a tiempo y no ha incumplido, es decir, no ha dejado de pagar ningún interés ni principal.
¿Qué es el plan de pago de tasa fija?
El monto de los intereses adeudados disminuye cada período en proporción al monto del principal reembolsado, a pesar de la tasa de interés fija, por lo que el interés adeudado disminuye a medida que el prestamista reembolsa el monto del préstamo.
Si bien los intereses pagados en cada período son en realidad una función del saldo de capital pendiente, los pagos de intereses NO reducen el capital.
Por lo tanto, el principal en riesgo (el dinero que podría perderse en caso de incumplimiento) es el propio principal del préstamo. El prestamista corre el riesgo de perder el préstamo original porque supone que el prestatario podrá cumplir con los requisitos de intereses y pagar el préstamo. capital en su totalidad al vencimiento.
Con un plan de pago de tasa de interés fija, que es más estándar para los préstamos hipotecarios, el préstamo se paga en cuotas iguales.
Sin embargo, el valor de los pagos de capital e intereses variará entre los períodos de pago.
En primer lugar, una mayor proporción del pago total se destina al servicio de intereses. Sin embargo, a lo largo del plazo del préstamo, la proporción de reembolsos aumenta (y los pagos de intereses disminuyen).
Funciones de Excel para el plan de pago de préstamos.
Para crear un plan de pago de préstamo en Excel podemos utilizar las siguientes funciones integradas:
1. Fórmula funcional PMT de Excel
La función PAGO en Excel determina el pago total adeudado para cada período, incluidos los pagos de intereses y principal. A diferencia de los otros dos componentes, el pago total permanece constante durante todo el plazo del préstamo.
=PMT(Tasa, Nper, PV,[fv],[type])
2. Fórmula funcional PPMT de Excel
La función PPMT en Excel calcula el reembolso periódico del principal del préstamo, que, para reiterar, debe aumentar después de cada período de pago.
=PPMT(Tasa, por, nper, pv, [fv], [type])
3. Fórmula funcional IPMT de Excel
La función PAGO de Excel calcula la parte de interés de cada pago periódico. A diferencia de los pagos de principal, los pagos de intereses deberían disminuir después de cada período de pago.
=IPMT(Tasa, Nper, PV,[fv],[type])
Calculadora excel para planes de pago
Pasamos ahora a un ejercicio de modelado, al que puede acceder rellenando el siguiente formulario.
1. Supuestos de financiación de préstamos hipotecarios
Por ejemplo, tiene la tarea de crear un plan de pago de préstamo en nombre de un consumidor que ha decidido obtener un préstamo totalmente amortizado, a tasa fija y a 30 años.
El préstamo hipotecario es por $400.000 con una tasa de interés anual del 5,00% y capitalización mensual.
- Préstamo hipotecario = $400,000
- Plazo del préstamo = 30 años
- Tasa de interés anual (%) = 5,00%
- Frecuencia de pago = 12x
Nuestro primer paso es convertir la tasa de interés anual en una tasa de interés mensual dividiéndola por 12, lo que nos da una tasa de interés mensual del 0,42%.
- Tasa de interés mensual (%) = 5,00% ÷ 12 = 0,42%
Dado que el plazo del préstamo se expresa en años, también ajustamos este dato para expresarlo mensualmente multiplicándolo por doce.
- Número de períodos = 30 años x 12 = 360 períodos (es decir, meses)
Por tanto, el número total de periodos de interés compuesto es de 360 periodos.
2. Calcular el calendario de pagos en Excel
Después de convertir nuestras entradas a las unidades correctas, ahora podemos crear nuestra tabla de pagos de hipoteca en Excel.
- Mes → En la primera columna ingresamos el número del primer mes, agregamos uno en la columna a continuación y luego arrastramos la fórmula hacia abajo hasta llegar a nuestro número total de períodos (360).
- pago → La siguiente columna contiene el pago periódico total de cada período. Esto significa que el importe permanece fijo durante todo el plazo del préstamo. Usando la función PAGO, podemos determinar que cada pago a plazos asciende a $2,147.
- interés → La columna de intereses se calcula utilizando la función IPMT. Como ya se mencionó, los intereses inicialmente deberían ganar una proporción mayor en los períodos anteriores, pero gradualmente perderían valor a medida que se reembolsa el capital.
- Rector → El pago principal utiliza la función PPMT. Cuantos más pagos se realizan, mayor es el reembolso del principal, mientras que los pagos de intereses pierden valor. Tenga en cuenta que sólo el reembolso del principal reduce el importe pendiente del préstamo, es decir, la columna de la derecha.
- balance → Para el mes 1, primero vinculamos el monto de nuestro préstamo original ($400,000) y restamos el primer pago de capital, que es $481. No podemos arrastrar la fórmula desde aquí. En cambio, la celda a continuación está vinculada al saldo del mes 1 ($399,5 mil) y resta el pago de capital en el período de comparación. Luego podemos completar las celdas restantes a continuación y debemos confirmar que el saldo pendiente es cero en el último período.
La fórmula de cada función de Excel utilizada en cada columna es la siguiente.
=PMT(0,42%, 360, 400.000 dólares)
=IPMT(0,42%, cifra mensual, 360, $400.000)
=PPMT(0,42%, cifra mensual, 360, $400.000)
Excepto el número del mes, todas las demás entradas deben ser una referencia absoluta, es decir, ancladas presionando “F4” una vez.
Aunque este es un paso opcional, también hemos agregado dos columnas adicionales a la derecha (columnas “G” y “H”) para observar visualmente el cambio porcentual en el interés y la contribución del principal durante la vida del préstamo.
En el primer mes, la división interés-principal fue del 77,65% y el 22,38%, pero en el mes 360 cambia al 0,41% y el 99,59%, como se muestra a continuación.
Como verificación rápida de cordura, debemos confirmar dos elementos en nuestra hoja de cálculo para asegurarnos de que no haya errores en nuestro plan de pago.
- Intereses (Columna D) + Principal (Columna E) = Pago (Columna C)
- Capital total (total de la columna E) = $400,000
3. Análisis en Excel del calendario de pagos de préstamos.
Finalmente, podemos calcular la suma de cada componente para encontrar los montos totales de pago de la hipoteca y llegar a los siguientes números:
- Pago total = $773,023
- Interés total = $373,023
- Capital total = $400,000
