Calcular el cuadro de amortización de un préstamo de cuotas constantes

Vamos a ver la manera de calcular el cuadro de amortización de un préstamo de cuotas constantes.

Los préstamos de cuotas constantes son aquellos en los que siempre se paga el mismo importe a la hora de devolver un préstamo. La cuota está compuesta por la parte de amortización de capital y la parte de intereses.

Si bien la cuota es constante, las cantidades correspondientes a capital e intereses sí que van variando. Conforme pasa el tiempo el importe correspondiente a intereses va disminuyendo. En cambio la parte correspondiente a devolución de capital cada vez es mayor.

Este tipo de préstamos corresponde al denominado sistema francés.

Vamos a ver cómo se calcularía el cuadro de amortización de un préstamo de cuotas constantes en Excel.

Datos del préstamo

Los datos de partida del préstamo para este ejercicio serán los siguientes.

El total del préstamo asciende a 36.000 euros, con un tipo de interés anual del 6,5% a devolver mensualmente en 5 años.

Preparación del cuadro de amortización

Introduciremos los datos de partida en Excel.

Para crear el cuadro de amortización tendremos las columnas: cuota, fecha, cuota, interés, devolución de capital y capital pendiente.

Ya podemos rellenar el número de cuota, desde la cuota inicial (cero) a la última que sería la 60 (5 años corresponde a 60 meses).

En la columna de la fecha podemos indicar las 60 fechas.

Y en cuanto al capital pendiente en la primera fila (cuota 0) podemos indicar el total del préstamo, dado que debemos la devolución de la totalidad del préstamo.

Calcular la cuota del préstamo

Para calcular la cuota del préstamo utilizaremos la función PAGO. Los argumentos obligatorios de la función son:

Tasa: Es el tipo de interés. Dado que queremos calcular las cuotas mensuales y el tipo de interés es anual, debemos dividir este entre 12, para obtener el tipo de interés mensual.
Nper: Número de periodos. 5 años, que en meses son 60 periodos.
Va: El valor actual del préstamo. El importe total del préstamo.

Con todo ello obtenemos una cuota constante de 704,38 euros. Este importe aparece siempre en negativo, por lo que si queremos tener el dato en positivo, una posibilidad es que delante de la función incluyamos el signo negativo.

Para poder arrastrar hacia abajo esta fórmula tendremos que fijar las referencias como absolutas.

Y ya podemos arrastrar hacia abajo esta fórmula.

Calcular el interés

Para el cálculo del tipo de interés utilizaremos la función PAGOINT. Los argumentos obligatorios son:

Tasa: tipo de interés mensual.
Período: corresponde al número de cuota. Este valor no se puede incluir como referencia absoluta dado que debe variar con cada cuota.
Nper: Número de períodos.
Va: Importe del préstamo.

También en este caso escribiremos un signo negativo delante de la fórmula para que el importe aparezca en positivo. De la misma forma fijaremos todas las referencias menos el Período, para poder arrastrar la fórmula hacia abajo.

Cálculo del la devolución del capital

Dado que la cuota es constante y ya hemos calculado el interés, mediante una simple resta calculamos el importe correspondiente a amortización o devolución del capital.

Cálculo del capital pendiente

Para calcular el importe de capital pendiente, y partiendo del importe total del préstamo, únicamente tendremos que restar al importe de capital pendiente, la cantidad de devolución de capital de cada periodo.

Para comprobar que hemos calculado correctamente el cuadro de amortización recomiendo que observemos que el capital pendiente después del pago de la última cuota es cero.