Cuota a pagar en un préstamo hipotecario o personal en Excel

Tratando de construir una tabla con la amortización de un préstamo encontré una plantilla en la página de Microsoft Office. Sin embargo, los cálculos de la plantilla no son correctos. La suma de los pagos totales debe de ser igual a la suma del importe del préstamo más los intereses. La columna capital debe ser igual al total del importe del préstamo. Y las cuotas del préstamo (pago total), al seguir el sistema francés, deben de ser iguales.

Aunque el empleo de nombres debería facilitar la compresión de los cálculos, este no es el caso. Emplea 27 nombres para calcular realmente dos columnas: pago principal y de los intereses. Antes de rastrear los nombres y fórmulas con el error, he decidido crear los cálculos desde cero conservando un formato parecido.

Tabla de amortización y cuota mensual

En Entrada de datos, especificamos los argumentos: capital, años, nº de pagos al año, TIN (tipo de interés nominal) y fecha inicial del préstamo o hipoteca. Automáticamente genera la tabla de amortización con las cuotas (pago total), desglosado en capital (principal) e intereses y el capital pendiente de amortizar. En la imagen muestro solamente 4 columnas. Ahora el importe del último pago y de los totales son correctos. La columna pago total es la suma del capital más los intereses.

Cálculos

Hemos utilizado 5 funciones financieras:

PAGO (tasa;nper;va;vf;tipo)

Con esta función calculamos la mensualidad (o cuota) constante a pagar. Ponemos un signo negativo delante del capital para que devuelva el importe con signo positivo.

PAGOPRIN (tasa;período;nper;va;vf;tipo)

Para calcular el pago del capital (principal) en cada periodo. Signo negativo delante de capital para obtener un importe positivo. En la imagen para el periodo 1.

PAGOINT (tasa;período;nper;va;vf;tipo)

Emplea los mismos argumentos que la función PAGOPRIN, pero devuelve el pago de intereses en cada periodo. Signo negativo delante de capital para obtener un importe positivo. En la imagen para el periodo 1.

PAGO.PRINC.ENTRE (tasa;nper;vp;per_inicial;per_final;tipo)

Función similar a PAGOPRIN pero calcula el pago acumulado entre dos periodos. Como genera un error si ponemos un signo negativo delante del capital, escribimos un signo negativo delante de la fórmula. En la imagen entre el periodo 1 y 12.

PAGO.INT.ENTRE (tasa;nper;va;período_inicial;período_final;tipo)

Emplea los mismos argumentos que la función PAGO.PRINC.ENTRE y es similar a PAGOINT pero calcula el pago acumulado de intereses entre dos periodos. Como genera un error si ponemos un signo negativo delante del capital, escribimos un signo negativo delante de la fórmula. En la imagen entre el periodo 1 y 12.

Gráficos

Para el mismo capital durante un periodo de cuatro años:

Referencias:
Simulador hipotecario del banco de España