Cuadro de amortización en excel de interés fijo
Crear el cuadro de amortización en Excel.
En primer lugar se modela la hoja creando los siguientes encabezados para poder crear el cuadro de amortización en Excel.
- Pago Mensual (Mes).
- Número de Pago.
- Interés Principal (Intereses).
- Cuota Mensual.
- Monto Restante.
A parte crearemos encabezados adicionales que posteriormente necesitamos para completar la tabla.
- Monto del préstamo (Préstamo).
- Interés nominal anual (Interés).
- Interés nominal mensual.
- Mensualidades o periodos (Periodos).
Bajo el encabezado “Mes” rellenaremos las dos primeras celdas con los meses uno y dos del préstamo. Dicho de otra manera serán los meses en los que nos pasarán los dos primeros recibos. En este ejemplo el primer recibo se pasa en el mes de marzo-2015 y el segundo recibo en el mes de abril-2015. Copiaremos el valor de las dos celdas y arrastrando completamos las siguientes celdas hasta llegar al mes de finalización del préstamo. En el ejemplo que puedes descargar al final de esta entrada finaliza febrero-2022.
Bajo el encabezado “Núm.Pago” se rellena con los valores desde 1 hasta 84 y corresponde con los vencimientos de las cuotas del préstamo. Como el préstamo del ejemplo tiene una duración de siete años y realizamos pagos mensuales o dicho de otra manera los vencimientos son mensuales entonces en total tenemos 12×7=84 vencimientos para nuestro cuadro de amortización en Excel.
Bajo el encabezado “Intereses” figuran los intereses que pagamos en cada cuota. En este ejemplo se utiliza el sistema de amortización francés para generar el cuadro de amortización en Excel y en la hoja podemos utilizar la “Función PagoInt” para averiguar el valor de cada celda. La “Función PagoInt” la completamos como se indica en la imagen.
El primer argumento de la función pagoint de Excel responde al interés mensual, el segundo argumento de la función es el número de vencimiento que para el ejemplo correspondería con 1 o lo que es lo mismo el primer recibo, el tercer argumento se refiere al número total de cuotas que previamente habíamos calculado como 84. Finalmente el último argumento es el monto total del préstamo que debe llevar signo negativo.
Para calcular los valores referidos al encabezado “Principal” vamos a utilizar la “Función PagoPrin de Excel” muy similar a la “Función PagoInt”. Esta función nos permite averiguar de cada cuota que pagamos al banco que cantidad es la destinada a amortizar el capital prestado sin tener en cuenta los intereses ya que los hemos calculado con la función PagoInt de Excel previamente.
El primer argumento de la “Función PagoPrin de Excel” corresponde con el interés nominal mensual. El segundo argumento se refiere al vencimiento o mes, para este ejemplo nos encontramos en primer recibo luego seleccionamos el número de pago 1.
El cuarto argumento se refiere al número total de periodos, meses o cuotas del préstamos que como hemos indicado anteriormente se trata de 84. Para finalizar, el último argumento, se trata del monto total del préstamo solicitado al banco o entidad financiera y que igual que en la función PagoInt debe ir con signo negativo.
El encabezado referido a “Cuota” nos indica la cantidad total que debemos abonar en el recibo que nos domicilia la entidad financiera o banco. Podemos conseguir este valor con la suma de los valores anteriores los intereses de la cuota y el principal de la cuota. Como se trata de un interés fijo el valor de todas las cuotas será el mismo pero al amortizar mediante el sistema francés vemos como cada vez destinamos menos cantidad de la cuota en el pago de intereses. Es decir, los intereses relativos al préstamo, se pagan de más a menos. Por el contrario el dinero destinado en la cuota a amortizar el proyecto es creciente y se paga de menos a más.
Para averiguar los valores del encabezado “Monto restante”. Simplemente restaremos al capital del préstamo la cantidad destinada de nuestras cuotas a su amortización en nuestro cuadro de amortización en Excel.
