Cómo hacer una calculadora de tabla de amortización en Excel

Una tabla de amortización reparte cada pago de un préstamo entre los intereses que genera el dinero aún prestado y la parte que devuelve capital, y va descontando ese capital del saldo hasta dejarlo a cero al terminar el plazo. En este tutorial la montas en tu Excel paso a paso: introduces capital, tipo de interés anual y plazo en meses, calculas la cuota con PAGO (PMT) y dejas que una tabla de Excel rellene las 60 mensualidades. Al terminar sabrás leer la cuota, los intereses de cada mes, los totales pagados y dónde añadir después una columna de amortización anticipada. Funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016; en Excel 2013 también, con los mismos pasos.

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

Una tabla de amortización es el calendario mes a mes de un préstamo con cuotas fijas. Para cada mensualidad te dice tres cosas: cuánto pagas de cuota total, qué parte son intereses y qué parte reduce el capital pendiente. La última fila acaba con saldo cero, porque entre todos los pagos has devuelto el dinero prestado más los intereses acordados.

Piénsalo con un ejemplo redondo: pides 10.000 a devolver en 5 años (60 meses) con un tipo de interés anual del 6%. La cuota mensual sale de unos 193,33. En la primera fila verás que pagas unos 50 de intereses y poco más de 143 de capital; en la fila 60, los intereses se han quedado en menos de 1 y casi todo el pago es capital. Esa trayectoria descendente de los intereses y ascendente del capital es la firma del sistema francés de amortización, el que usa la mayoría de préstamos personales e hipotecas a tipo fijo.

La tabla te sirve para tres cosas prácticas: saber cuánto pagas de intereses en total antes de firmar, ver cómo cambian las cuotas si bajan los años o sube el tipo, y planificar una amortización anticipada viendo qué mes conviene.

Los cuatro datos que mueven toda la tabla

Solo necesitas cuatro números para que toda la tabla se calcule sola: el capital prestado, el tipo de interés anual, el plazo en meses y la cuota mensual que sale de los tres anteriores. La fórmula que los relaciona es la del sistema francés:

M = P · [r(1+r)ⁿ / ((1+r)ⁿ − 1)]

Aquí P es el capital, r es la tasa de interés por periodo (mensual, en este caso) y n es el número total de periodos (meses). Para pasar del tipo anual al mensual divides entre doce: con un 6% anual, r = 0,06 / 12 = 0,005. Con ese dato, (1 + r)ⁿ se eleva a 60 y el resto sale solo.

Los cuatro datos se guardan en celdas para no escribirlos a mano en cada fila:

• Capital: 10.000.
• Tasa anual: 6% (en la celda como 0,06 o escrito 6% con formato porcentaje).
• Plazo en meses: 60.
• Cuota mensual: la que calcules con la fórmula o con la función PAGO.

Cómo se reparte cada pago entre intereses y capital

Cada mes el banco cobra intereses sobre el capital que aún te queda por devolver, no sobre el inicial. Por eso la cuota es constante pero la parte de intereses baja y la de capital sube: al principio debes casi todo, así que los intereses pesan mucho; al final debes poco, así que casi todo el pago es capital.

El reparto de cada fila se calcula con dos fórmulas:

• Interés del mes = saldo del mes anterior · tasa mensual.
• Capital devuelto = cuota − interés del mes.

Con el ejemplo de 10.000 al 6% a 60 meses, en el mes 1 el saldo es 10.000, el interés sale de 10.000 · 0,005 = 50,00, y como la cuota es 193,33, el capital devuelto es 193,33 − 50,00 = 143,33. El nuevo saldo queda en 9.856,67. En el mes 60, con un saldo cercano a 190, el interés baja a menos de 1 y casi toda la cuota es capital.

Dónde preparas los datos de entrada en la hoja

Abre un libro en blanco y renombra la primera hoja como Préstamo (clic derecho sobre la pestaña Hoja1 > Cambiar nombre). Después, en la esquina superior izquierda, escribe los rótulos y los valores en estas celdas:

Pasos que haces en tu Excel:

1. Selecciona A1 y escribe Capital. En B1 escribe 10000 y Entrar.
2. En A2 escribe Tasa anual. En B2 escribe 6% y Entrar; Excel lo guarda como 0,06 y le pone formato porcentaje.
3. En A3 escribe Plazo (meses). En B3 escribe 60.
4. En A4 escribe Cuota. La celda B4 la dejas vacía, la llenas en el siguiente apartado.
5. Selecciona A1:A4 y aplica negrita (Ctrl+N) para que los rótulos se lean mejor.

Calcular la cuota con la fórmula del préstamo

La fórmula del sistema francés, escrita directamente, queda así en B4:

=B1*POTENCIA(1+B2/12;B3)/(POTENCIA(1+B2/12;B3)-1)

POTENCIA (POWER) eleva (1 + r) al plazo n. Como la misma expresión aparece dos veces, copiarla a mano es cómodo y te recuerda qué está pasando. Con 10.000 al 6% a 60 meses sale 193,33, que es la cuota que se repite cada mes.

Comprobar la cuota con la función PAGO (PMT)

La función PAGO (PMT) hace el mismo cálculo con una sola línea, útil para verificar que la fórmula anterior está bien:

=PAGO(B2/12;B3;B1)

PAGO devuelve un número negativo porque, en contabilidad, los cobros y los pagos llevan signos opuestos. Para que la cuota salga en positivo, antepones el signo menos: =-PAGO(B2/12;B3;B1). El resultado, 193,33, es el mismo que con la fórmula escrita a mano.

Construir la tabla mes a mes con fórmulas

Ahora preparas los encabezados de la tabla de amortización. Fila 6, columnas A a E, una columna por dato: número de mes, saldo al empezar el mes, cuota que pagas, parte que es interés, parte que es capital y saldo al terminar. La primera mensualidad la escribes en la fila 7; las demás salen arrastrando.

Pasos:

1. En A6 escribe Mes, en B6 Cuota, en C6 Interés, en D6 Capital y en E6 Saldo.
2. Aplica negrita a A6:E6 con Ctrl+N.
3. En A7 escribe 1. En A8 escribe 2. Selecciona A7:A8 y arrastra el controlador de relleno hacia abajo hasta A66; Excel seguirá la serie hasta 60.
4. En B7 escribe =B$4 (la cuota calculada) y arrastra hasta B66.

La primera fila: saldo inicial e interés del mes 1

Rellenas la fila 7 con las cuatro fórmulas del primer mes. El saldo inicial es el capital del préstamo; el interés se calcula sobre ese saldo; el capital es la cuota menos el interés; el nuevo saldo es el inicial menos el capital devuelto.

Fórmulas en cada celda de la fila 7:

• A7 (saldo al empezar): =B1.
• C7 (interés del mes): =B$2/12*A7.
• D7 (capital devuelto): =B$4-C7.
• E7 (saldo al terminar): =A7-D7.

La columna B es la cuota, que ya apuntaste a B$4; queda como 193,33 en la fila 7.

Copiar hacia abajo hasta el último mes

Ahora arrastras la fila 7 hasta la fila 66 para que cada mensualidad use el saldo anterior. Antes de arrastrar conviene que las celdas de la fila 7 estén en formato número con dos decimales, para que el saldo cierre a cero en la fila 66.

Pasos:

1. Selecciona B7:E7.
2. Sitúa el cursor en el controlador de relleno (esquina inferior derecha de la selección).
3. Arrastra hacia abajo hasta la fila 66; Excel copia las fórmulas y las ajusta fila a fila.
4. Comprueba que E66 queda cerca de 0,00.

Redondear las celdas de dinero a dos decimales

Para que el saldo cierre exactamente a cero, las cuatro columnas de dinero se ven mejor con dos decimales. El formato no cambia los valores internos: si redondeas, el último saldo puede quedarse en 0,01 o irse a 0,02.

Pasos:

1. Selecciona B7:E66.
2. Pulsa Ctrl+1 para abrir Formato de celdas.
3. En la pestaña Número, elige Número con 2 decimales.
4. Pulsa Aceptar.

Convertir el rango en una tabla que crece sola

Si conviertes el rango A6:E66 en una tabla de Excel, las fórmulas usarán referencias estructuradas y, al añadir una fila nueva, las fórmulas se copiarán solas. Para el ejemplo de 60 meses no hace falta crecer, pero te ahorra trabajo si después quieres añadir una amortización anticipada o un mes extra.

Pasos:

1. Selecciona A6:E66.
2. En la cinta, abre Insertar > Tablas > Tabla.

1. Marca La tabla tiene encabezados y pulsa Aceptar.
2. Pulsa sobre una celda cualquiera dentro de la tabla y, en la pestaña Diseño de tabla que aparece, cambia el nombre de la tabla a Amortiz en el cuadro Nombre de tabla, a la izquierda.

Un resumen rápido al lado de la tabla

Para tener a mano los totales del préstamo, añade tres fórmulas en la columna G. Total pagado es la suma de la columna Cuota, total de intereses es la suma de la columna Interés y el porcentaje de intereses sobre el capital te dice cuánto del pago total son intereses.

Fórmulas:

• G1: =SUMA(Amortiz[Cuota]).
• G2: =SUMA(Amortiz[Interes]).
• G3: =G2/B1 con formato porcentaje.

Con el ejemplo sale 11.599,68 pagados, 1.599,68 de intereses y un 16% sobre el capital inicial. Esos números te sirven para comparar con otras ofertas.

Pintar con formato condicional la columna de capital

Una barra de datos sobre la columna Capital deja ver de un vistazo cómo esa parte va creciendo mes a mes: las primeras barras son cortas y las del final, largas.

Pasos:

1. Selecciona D7:D66.
2. Abre Inicio > Estilos > Formato condicional > Barras de datos y elige un color (el azul es claro y contrasta bien).

1. Pulsa Aceptar.

Por qué tu saldo no acaba en cero

Los tres motivos más comunes para que el último saldo no cierre a cero son: redondeos acumulados en las cuotas mostradas, redondeos en el capital mensual y una fila de más que descuenta un mes extra. La tabla bien hecha termina exactamente en cero, porque las fórmulas usan la cuota completa (con todos los decimales que Excel guarda por dentro) y el número de filas coincide con el plazo.

El saldo se queda en 28 céntimos o se va a negativo

Si redondeas la cuota a dos decimales para escribirla a mano, el cálculo arrastra un error que se acumula mes a mes. Con 10.000 al 6% a 60 meses, redondear a 193,33 puede dejar el último saldo en 0,28 o algo parecido.

La solución más limpia es no redondear nunca la cuota calculada: deja que Excel use los decimales que tiene por dentro. Si aun así quieres mostrar la cuota con dos decimales, aplica solo el formato de celda (como en el apartado de redondeo), no redondees el valor. Otra opción es ajustar la última fila con la fórmula =E66 en la columna Capital, de modo que el último pago absorba el redondeo y el saldo cierre a cero.

La tabla da #¡VALOR! al cambiar los datos

Este error aparece cuando una de las celdas de entrada no se puede interpretar como número. Las causas más frecuentes son escribir la tasa como 6 en vez de 0,06 o 6%, dejar B3 vacía, o mezclar texto con números. Para diagnosticarlo, haz clic en cada celda de B1:B3 y mira la barra de fórmulas: lo que ves ahí es lo que Excel va a usar. Corrige el valor y la tabla vuelve a calcularse.

Lo que esta calculadora no hace (y cómo lo notas)

Esta calculadora vale para préstamos con cuota fija y tipo de interés constante durante toda la vida del préstamo, que es lo que usa el sistema francés. No incluye comisiones de apertura, seguros vinculados, gastos de estudio ni cambios de tipo. Si tu préstamo tiene un periodo inicial con tipo más bajo, una comisión del 1% al principio o cuotas que suben cada año, los números que salen aquí no coincidirán con los del banco.

Tampoco sirve para préstamos a tipo variable referenciados a un índice (Euríbor, etc.), porque la cuota se recalcula con cada revisión. Para esos casos, la tabla se complica: en la columna del tipo tendrías que poner el valor de cada periodo y la cuota se recalcularía al cambiar.

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

• Tienes en la hoja Préstamo los cuatro datos de entrada: capital, tasa anual, plazo y cuota.
• Calculas la cuota con la fórmula escrita a mano o con PAGO (PMT), y verificas que dan lo mismo.
• La tabla mes a mes reparte cada cuota en interés y capital, con el saldo bajando hasta cero en la fila 66.
• Los totales pagados y de intereses están a la vista, junto al porcentaje de intereses sobre el capital.
• El rango es una tabla de Excel, así que añadir filas nuevas o aplicar formato condicional se hace en un par de clics.

Siguiente paso: añade una columna Amortización anticipada a la derecha de la tabla y un control de número para introducir el importe extra cada mes. Verás cómo bajan los intereses totales y en cuántos meses se acorta el préstamo.

Dudas al usar la calculadora de tabla de amortización en Excel