Tabla de amortización en Excel: crea tu propio simulador

Cuando pides un préstamo, el banco te devuelve una tabla en la que cada cuota se reparte entre los intereses que cobra y el capital que realmente amortizas. Esa misma tabla la puedes montar tú en una hoja de Excel para saber qué pagas cada mes, cuánto destinas a intereses y cuánto reduces la deuda, y para simular cómo quedaría la cuota si cambiaras el plazo o el tipo de interés. Al terminar este tutorial tendrás una tabla de 180 cuotas lista para tu préstamo y sabrás cambiarla a método de capital constante si te conviene. Funciona en Microsoft 365, Excel 2024, 2021, 2019, 2016 y 2013; en todas ellas existen las funciones y formatos que usamos aquí.

Qué es una tabla de amortización y para qué sirve

Una tabla de amortización es el calendario de un préstamo: una fila por cada mensualidad con la cuota que pagas, qué parte son intereses y qué parte reduce el capital pendiente, y el saldo que aún debes después de esa mensualidad. El caso más habitual es el préstamo personal o hipotecario con cuotas mensuales iguales (el llamado método francés): tú firmas 150.000 a devolver en 180 meses al 5% anual y la cuota se queda en 1.058,53 al mes. La tabla reparte ese importe mes a mes: al principio casi todo va a intereses (el saldo aún es alto) y al final casi todo va a capital (el saldo ya es pequeño). El saldo, columna a columna, va bajando hasta llegar a cero en la última fila. Esa curva descendente del saldo y la curva creciente del capital amortizado son las dos cifras que te interesan al comparar ofertas: ves de verdad cuánto pagas por el dinero y en qué momento dejas de deber.

La fórmula de la cuota mensual y sus variables

La cuota mensual se calcula con una fórmula que parece larga pero tiene solo cuatro piezas: M = P · [ r(1+r)^n / ((1+r)^n − 1) ]. P es el capital que te prestan (en el ejemplo, 150.000). r es la tasa de interés mensual, que se obtiene dividiendo la tasa anual entre 12 (5% anual = 0,05/12 = 0,0041667 al mes). n es el plazo en meses: 15 años son 180 meses, no 15. M es lo que pagarás cada mes. La fracción entre corchetes es el factor que convierte el capital en una anualidad: cuanto mayor es el plazo o el tipo, mayor es M. Cuando divides r entre 12 y elevas (1+r) a n, obtienes un número muy grande en el numerador y otro casi igual en el denominador; su resta, 1, es lo que mantiene la fórmula estable. Entender las cuatro variables es lo que te permite luego cambiar el tipo de interés o el plazo y recalcular al instante la cuota sin tocar la tabla.

Preparar la hoja con los datos del préstamo

Empezamos con la hoja limpia. En la columna B vamos a poner los datos del préstamo que la tabla necesita para funcionar: capital, tasa anual, plazo y cuota calculada. Las cuatro celdas serán los anclas a las que luego haremos referencia con dólares.

1. En B1 escribe Capital y en B2 150000.
2. En B1, justo debajo, escribe Tasa anual y en B2 pon 5% (Excel lo guarda como 0,05).
3. En B3 escribe Plazo (meses) y en B4 pon 180.
4. En B5 deja la etiqueta Cuota y en B6 escribe la fórmula que reparte el préstamo en 180 mensualidades iguales.
5. Pulsa Entrar y verás 1.058,53.

Construir la tabla mes a mes con cuatro columnas

Ahora montamos la tabla. En la fila 6 vamos a poner los encabezados y desde la fila 7 hasta la 186 escribiremos los 180 meses, cada uno con su cuota, la parte de intereses, la parte de capital y el saldo que va quedando.

1. En A6 escribe Mes, en B6 Cuota, en C6 Interés y en D6 Capital.
2. En A7 pon 1 y en A8 2. Selecciona A7:A8, pon el cursor sobre el controlador de relleno de A8 y arrastra hasta A186 para que se rellene la serie 1, 2, 3… 180.
3. En B7 escribe =B$4 (la cuota ya calculada, fija con dólares en la fila 4) y copia hacia abajo hasta B186: la cuota se repite idéntica.
4. En C7 escribe =B$4-($B$1/180) y en D7 =B7-C7. La idea es que el capital total B1 se devuelve a partes iguales (150.000 / 180 = 833,33), y el resto de la cuota es el interés del mes. Selecciona C7:D7 y arrastra hasta la fila 186.
5. Comprueba la fila 7: cuota 1.058,53, interés 225,00, capital 833,33. En la fila 186: cuota 1.058,53, interés 4,43, capital 1.054,11.

El saldo pendiente que se va reduciendo

Ahora añadimos la quinta columna: el saldo vivo después de cada mensualidad. Es la cifra que te dice cuánto te queda por pagar y la que, al final, tiene que llegar a cero.

1. En E6 escribe Saldo.
2. En E7 pon =$B$1 para empezar con el capital total prestado.
3. En E8 escribe =E7-D8 (el saldo del mes anterior menos el capital que acabas de amortizar).
4. Selecciona E8 y arrastra el controlador de relleno hasta E186.
5. La última celda, E186, debería mostrar 0,00 o un valor muy cercano a cero.

=$B$1

=E7-D8

Comprobar que la tabla cierra a cero

Para confirmar que la tabla está bien hecha, basta una suma: el capital total amortizado (columna D) debe coincidir con el capital prestado (B1). Si queda un céntimo de diferencia, viene del redondeo de la cuota con dos decimales.

1. En una celda cualquiera, por ejemplo G2, escribe =SUMA(D7:D186).
2. Compara el resultado con B1: deberían ser iguales o diferir como mucho en 0,01.
3. Si quieres cerrar la tabla al céntimo exacto, ajusta la última cuota a mano: en B186 escribe =E185+B186 para que cubra exactamente lo que falta.

=SUMA(D7:D186)

Variantes de la tabla según el tipo de préstamo

No todos los préstamos se amortizan con la cuota fija del método francés. La elección del método cambia la cuota, el perfil de intereses y el capital que amortizas cada mes. Esta tabla resume los tres métodos más usados:

El método francés con la función PAGO

Excel trae una función específica para la cuota del método francés: PAGO (en inglés, PMT). Te evita escribir la fórmula larga y deja claro qué es cada argumento.

1. En B4 sustituye la fórmula larga por =PAGO(B2/12;B3;B1).
2. Pulsa Entrar: el resultado es −1.058,53 (negativo, porque PAGO interpreta B1 como un flujo que sale de tu bolsillo).
3. Si prefieres verlo en positivo, envuélvelo así: =ABS(PAGO(B2/12;B3;B1)) y te queda 1.058,53.

=PAGO(B2/12;B3;B1)

=ABS(PAGO(B2/12;B3;B1))

El método de capital constante

En este método decides que el préstamo se devuelve en partes iguales de capital cada mes, y el interés se calcula sobre el saldo vivo. La cuota, por tanto, baja mes a mes.

1. Borra las columnas C y D que calculamos antes.
2. En B7 escribe la fórmula de cuota con capital constante: =$B$1/B$3+($B$1-(A7-1)*($B$1/$B$3))*$B$2/12.
3. Copia B7 hacia abajo hasta B186. La cuota del mes 1 es 1.391,67 y la del mes 180 baja a 837,15.
4. En C7 calcula el interés del mes: =($B$1-(A7-1)*($B$1/$B$3))*$B$2/12 y copia hasta C186.
5. En D7 calcula el capital amortizado: =$B$1/B$3 y copia hasta D186: el capital es siempre 833,33.
6. En E7 mantén =$B$1 y en E8 =E7-D8, arrastrando hasta E186.

=$B$1/B$3+($B$1-(A7-1)*($B$1/B$3))*$B$2/12

=$B$1/B$3

Errores típicos al montar la tabla

Estos son los tres tropiezos que más se repiten al copiar una tabla de amortización de Internet o al montarla a mano.

La cuota no se mantiene fija al arrastrar

Lo que se ve: la columna B muestra cifras distintas cada fila (1.058,53, luego 1.057,89, luego 1.057,25…), en lugar de la cuota fija. La causa: la fórmula =B$4 perdió el ancla al copiar porque alguien tecleó =B4 sin dólar, o porque al arrastrar las columnas C y D arrastró también la columna B sin querer. El arreglo: vuelve a la celda B7, escribe =B$4 (con F4 una vez para fijar la fila), pulsa Entrar y vuelve a arrastrar hasta B186.

=B$4

El saldo final no llega a cero

Lo que se ve: E186 muestra 0,32 o un número cercano, en vez de 0,00. La causa: la cuota se redondeó a céntimos y, multiplicada por 180 meses, devuelve una pequeña diferencia. El arreglo: o redondeas la cuota al principio con =REDONDEAR(PAGO(B2/12;B3;B1);2), o ajustas la última cuota a mano para que cubra exactamente el saldo pendiente: en B186 escribe =E185 (el saldo del mes anterior, que se convierte en la última cuota).

=REDONDEAR(PAGO(B2/12;B3;B1);2)

Los intereses parecen demasiado altos o bajos

Lo que se ve: en la primera fila, los intereses son de 750,00 en lugar de 225,00, o al revés, son ridículamente bajos. La causa: la tasa anual no se dividió entre 12, así que r = 0,05 en lugar de r = 0,00417. También pasa si el plazo está en años (15) en vez de meses (180). El arreglo: revisa la fórmula de la cuota en B4 y comprueba B2/12 (tasa mensual) y que B3 contiene 180, no 15.

=B2/12

=B3*12

Lo que ya sabes hacer con la tabla de amortización

• Calcular la cuota de un préstamo con la fórmula financiera y con la función PAGO.
• Montar una tabla mes a mes con cuota, interés, capital y saldo que se va reduciendo.
• Fijar referencias con dólares y la tecla F4 para que la tabla no se rompa al arrastrar.
• Comprobar que la tabla cierra a cero y ajustar el redondeo de la última cuota.
• Cambiar entre el método francés y el de capital constante según te convenga.

El siguiente paso natural es añadir una columna de "Intereses acumulados" (con =SUMA($C$7:C7) arrastrada hacia abajo) para ver en qué mes dejas de pagar más en intereses que en capital, y luego graficar la evolución del saldo y de los intereses para enseñárselo de un vistazo a quien tengas que enseñárselo.

Dudas al montar una tabla de amortización en Excel