Cuadro de amortización de préstamo en Excel: guía práctica

Un cuadro de amortización muestra cómo se divide cada pago de un préstamo entre capital e intereses, y cómo disminuye el saldo pendiente hasta su cierre. En este tutorial construirás uno para un préstamo de 150.000 euros al 3% anual, con cuotas mensuales durante 20 años.

Usaremos las funciones PAGO (PMT), PAGOINT (IPMT) y PAGOPRIN (PPMT), disponibles en las versiones de Excel de escritorio. El método está pensado para cuotas constantes y no incorpora por sí mismo seguros, comisiones ni otros gastos del préstamo.

Qué es un cuadro de amortización y qué información muestra

El cuadro de amortización es una tabla que sigue el préstamo periodo a periodo. Para nuestro caso, cada fila representa un mes e indica el saldo pendiente al comenzar, el pago, los intereses, la parte de capital y el saldo después de.abonar.

El libro tendrá dos hojas:

• Supuestos: importe, tasa anual, plazo, frecuencia y cuota.
• Amortización: calendario mensual con el desglose de cada pago.

Al terminar, la hoja Supuestos alimentará todas las fórmulas del cuadro. Así puedes cambiar una condición y revisar sus efectos.

Cómo calcular el pago periódico con PAGO

La función PAGO (PMT) devuelve el pago periódico de una anualidad. Su sintaxis es:

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

• tasa: interés de cada periodo.
• nper: número total de pagos.
• va: valor actual, es decir, el capital prestado.
• vf: valor futuro; usamos 0 porque el préstamo termina liquidado.
• tipo: 0 para pagos al final del periodo; 1 para pagos al principio.

El 3% anual debe convertirse a una tasa mensual. Los 20 años se convierten en 240 pagos:

=PAGO(3%/12;20*12;-150000)

El resultado aproximado es 831,90 euros mensuales. El capital se introduce con signo negativo para que Excel muestre la cuota como un importe positivo.

Qué columnas necesita el cuadro de amortización

El cuadro necesita seis columnas. Cada mes comienza con el saldo que quedó pendiente y termina con el nuevo saldo:

El primer saldo inicial es 150.000 euros, el importe prestado. En el segundo mes, el saldo inicial será el saldo final del primero, y así sucesivamente.

Cómo calcular intereses, capital y saldo en cada periodo

Primero preparamos los supuestos y una fórmula que reutilizarán todas las filas.

1. En la hoja Supuestos, selecciona B2 y escribe 150000.
2. Selecciona B3, escribe 3% y asígnale el formato Porcentaje.
3. Selecciona B4, escribe 20 para representar los años.
4. Selecciona B5 y escribe =PAGO(3%/12;20*12;-150000).
5. En la hoja Amortización, escribe los encabezados de A1:F1.
6. En B2, escribe =Supuestos.$B$2 para traer el importe.
7. En C2, escribe =Supuestos.$B$5 para traer la cuota.

Ahora calcula el primer mes:

=D2*Supuestos.$B$3/12

=C2-D2

=B2-E2

Cómo rellenar el cuadro hasta el último pago

Para continuar el cuadro:

1. En A2, escribe 1.
2. En A3, escribe =A2+1.
3. En B3, escribe =F2.
4. Selecciona D2:F2 y arrastra el controlador de relleno hasta F241.
5. En A3:A241, arrastra hacia abajo para completar los 240 meses.
6. Revisa la columna Interés: debe disminuir progresivamente.
7. Revisa Capital: debe aumentar a medida que queda menos deuda.

El saldo final no se redondea para conservar la precisión interna. Por eso, el último mes puede mostrar un residuo microscópico en lugar de exactamente 0.

=A2+1

=F2

Si las cifras de tu pantalla difieren levemente, no fuerces el resultado: comprueba primero que la tasa sea mensual y que la cuota proceda de los mismos supuestos.

Cómo separar el capital y los intereses con funciones financieras

El cálculo manual anterior utiliza las columnas del propio cuadro. Las funciones PAGOINT (IPMT) y PAGOPRIN (PPMT) realizan el mismo reparto de forma independiente.

• PAGOINT: devuelve los intereses de un periodo.
• PAGOPRIN: devuelve el capital pagado en un periodo.
• PAGO: devuelve el pago total.

=PAGOINT(3%/12;1;240;-150000;0;0)

=PAGOPRIN(3%/12;1;240;-150000;0;0)

=PAGO(3%/12;240;-150000;0;0)

Para el primer mes, PAGOINT devuelve 375,00 euros, PAGOPRIN devuelve 456,90 euros y PAGO devuelve 831,90 euros. La comprobación clave es:

=PAGOINT(3%/12;1;240;-150000;0;0)+PAGOPRIN(3%/12;1;240;-150000;0;0)

Para usar estas funciones en el cuadro completo, el número de pagos debe ser 240 y el periodo debe cambiar en cada fila. Por eso, al comparar resultados, utiliza el mismo importe, tasa, plazo y signo del valor actual que en la hoja Supuestos.

Por qué el cuadro de amortización no cuadra al final

Un residuo final suele deberse al redondeo. Si cada interés se redondea a dos decimales antes de restarlo, los pequeños errores se acumulan durante los 240 pagos.

Para comprobar el cierre sin alterar la precisión interna, utiliza una fórmula que compare el saldo final con una tolerancia:

=SI(ABS(F241)<0,01;"Cuadra";"Revisar")

También puede aparecer una diferencia si la tasa anual se introduce como 3 en lugar de 3%. En ese caso, Excel interpreta 3 como 300%, no como 3%.

Otro error frecuente es escribir directamente 3% en la fórmula de interés mensual. Como el pago es mensual, corresponde 3%/12. Si vas a cambiar la frecuencia, la tasa debe ajustarse también.

Qué debe revisarse antes de usar el cuadro de amortización

Este modelo sirve para un préstamo con cuota constante, tipo de interés constante y pagos mensuales. No incorpora seguros, comisiones de apertura, servicios vinculados ni otros gastos, aunque puedes añadirlos como filas separadas en Supuestos y calcular su impacto aparte.

Antes de tomar decisiones, comprueba:

• Que la tasa escrita en Excel sea 3%, no 3.
• Que el plazo y la frecuencia usen la misma unidad.
• Que el número de pagos sea 240 para 20 años mensuales.
• Que el capital se haya introducido como negativo en PAGO.
• Que no falten seguros, comisiones ni otros costes.
• Que el residuo final se interpreta como una diferencia de precisión.

Las funciones PAGO, PAGOINT y PAGOPRIN están disponibles en Excel de escritorio desde versiones antiguas. Excel para la web, Mac y móvil actualiza sus funciones con el servicio, pero la disponibilidad concreta puede variar según la versión y el canal de actualización.

Para comparar escenarios, duplica el libro o añade otra hoja Supuestos. Por ejemplo, puedes conservar el préstamo a 20 años y crear otro a 15 años para comparar cuota, saldo final e intereses.

Qué queda listo al terminar el cuadro de amortización

• Ya puedes calcular la cuota mensual con PAGO.
• Ya puedes separar los intereses y el capital de cada pago.
• Ya puedes seguir el saldo pendiente durante los 240 periodos.
• Ya puedes comprobar que el último saldo quede aproximadamente en cero.
• Ya puedes identificar diferencias provocadas por una tasa o frecuencia incorrecta.
• Ya puedes comparar dos escenarios de plazo o tasa duplicando el libro o sus supuestos.

Dudas al crear un cuadro de amortización en Excel