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

Calcular la cuota de una hipoteca en Excel es más fácil de lo que parece: la función PAGO (en inglés PMT) hace el trabajo sucio. Al terminar este tutorial tendrás una hoja que te dice, mes a mes, cuánto pagas de cuota, cuánto son intereses y cuánto capital devuelves, partiendo solo del importe, el tipo de interés y el plazo. Sirve para cualquier hipoteca a tipo fijo y, cambiando el tipo de la celda correspondiente, también ver cómo varía la cuota de una hipoteca variable.

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

Una tabla de amortización es el calendario completo de tu hipoteca: una fila por cada cuota, con la fecha (o el número de mes), la cuota que pagas, qué parte de esa cuota son intereses y qué parte devuelve capital, y cuánto te queda por devolver al banco. Empieza con el importe que te prestaron y termina con un capital pendiente de cero en la última fila.

Imagina una hipoteca de 150.000 al 2% anual a 30 años: la cuota mensual sale a unos 554,60. En la primera cuota pagas unos 250 de intereses y solo unos 304 de capital; en la última, al revés, casi todo es capital. Esa es la idea clave: al principio pagas más intereses, al final devuelves más capital. La tabla te lo muestra de un vistazo y te permite saber, en cualquier mes, cuánto te queda por pagar.

Las tres cifras que mueven todo: capital, tipo y plazo

Con tres datos puedes reconstruir cualquier hipoteca: el capital prestado, el tipo de interés anual y el plazo en años. Todo lo demás (cuota, intereses de cada mes, capital amortizado, capital pendiente) sale de combinar esos tres números.

El capital es lo que el banco te da (100.000, 150.000, lo que sea). El tipo es el interés anual que te cobra, expresado en porcentaje (un 2% se escribe 0,02 al meterlo en fórmulas). El plazo son los años durante los que devuelves el dinero. Multiplicando el plazo por 12 tienes el número total de cuotas, y dividiendo el tipo anual entre 12 tienes el interés que se aplica cada mes. Tres cifras, dos operaciones, y ya tienes todo lo que necesita Excel.

La función PAGO y cómo se calcula la cuota mensual

La función PAGO (en inglés PMT) calcula la cuota fija de un préstamo. Su sintaxis es =PAGO(tasa; nper; va; [vf]; [tipo]):

=PAGO(B2/12; B3*12; -B1)

Tres detalles importantes:

• Tasa y plazo van en la misma unidad (mensual). Si el tipo está en B2 en porcentaje anual, divides entre 12; si el plazo en años está en B3, multiplicas por 12 para sacar meses.
• El capital va en negativo (-B1). Es la forma que tiene Excel de distinguir el dinero que recibes del que pagas; al ponerlo negativo, la cuota sale en positivo.
• Los argumentos se separan con punto y coma. En Excel configurado con punto decimal (México, Estados Unidos) el separador es la coma.

Con los 150.000, el 2% y 30 años, la fórmula devuelve -554,60; el signo negativo es solo la convención de Excel, la cuota que pagas son 554,60 al mes.

Monta tu hoja de ejemplo con los datos del préstamo

Abre un libro nuevo y escribe los datos del préstamo en la hoja, en una zona reservada arriba a la izquierda. Así dejas la parte de abajo libre para la tabla mes a mes.

1. Selecciona la celda B1 y escribe 150000 (el capital).
2. En B2 escribe 2% (el tipo de interés anual).
3. En B3 escribe 30 (el plazo en años).
4. En B4 escribe la fórmula de la cuota y pulsa Entrar.

=PAGO(B2/12; B3*12; -B1)

Construye la tabla mes a mes: número, cuota, interés y capital

Ahora baja a la fila 6 y monta los encabezados, uno por columna. La tabla empezará en la fila 7.

1. Ve a A6 y escribe Mes; en B6, Cuota; en C6, Interés; en D6, Capital amortizado; en E6, Capital pendiente.
2. En A7 escribe 1; en A8 escribe 2. Selecciona A7:A8 y arrastra el controlador de relleno hasta la fila A366 (la fila 7 más los 360 meses de 30 años).
3. En B7 escribe =B$4 para fijar la cuota. Copia B7 hasta B366.
4. En E7 escribe =B$1 (el capital inicial pendiente).
5. En C7 calcula el interés del primer mes: =E7*B$2/12.
6. En D7 calcula el capital que amortizas: =B7-C7.
7. En E8 el nuevo pendiente: =E7-D7.
8. Copia C7:E8 hacia abajo hasta la fila 366.

Copia las fórmulas hasta el final con el controlador de relleno

El truco para no escribir 360 fórmulas a mano está en la esquina inferior derecha de la celda seleccionada, el controlador de relleno. Tras copiar las fórmulas de las filas 7 y 8, seleccionas A8:E8, te sitúas sobre el cuadradito verde de la esquina, el cursor se convierte en una cruz negra, y arrastras hacia abajo hasta la fila 366. Excel repite las fórmulas ajustando las referencias fila a fila.

Si te quedas corto y la tabla termina antes de llegar a pendiente cero, es que has arrastrado hasta una fila anterior a la 366. Si te pasas, las filas de más muestran #¡NUM! o números negativos en el pendiente; bórralas. La fila final exacta depende del plazo: para 30 años son 360 cuotas, para 20 años 240, para 15 años 180.

Por qué la cuota puede variar en hipotecas variables

En una hipoteca variable, el tipo de la celda B2 no es fijo: cambia cada cierto tiempo según un índice (el más habitual en España es el Euríbor) más un diferencial que fija el banco. La tabla que has montado sirve igualmente: cuando toque revisar el tipo, cambias el valor de B2 por el nuevo y todas las cuotas pendientes se recalculan solas.

Para verlo, prueba a poner en B2 un 3% o un 4%, en vez de 2%: la cuota de la columna B sube en todas las filas a la vez, y los intereses de cada mes se redistribuyen. La estructura de la tabla no cambia; lo que cambia es la cifra. Una forma más avanzada de modelar las revisiones reales es tener una columna con el tipo de cada período y que la cuota se calcule fila a fila con ese tipo, pero eso queda fuera de este tutorial.

Lo que la tabla NO incluye: comisión de apertura, seguros y gastos

La tabla de amortización solo refleja el préstamo puro: lo que el banco te presta, los intereses que cobras y cómo se devuelve el capital. Hay varios costes habituales en una hipoteca que NO aparecen y que conviene sumar aparte si quieres el coste real:

• Comisión de apertura: un porcentaje sobre el capital que el banco cobra al formalizar la hipoteca.
• Seguros: el seguro de hogar suele ser obligatorio para el banco, y el de vida es habitual en hipotecas_variables; se pagan aparte, al margen de la cuota.
• Gastos de notaría, registro y tasación: van por cuenta del prestatario en la mayoría de los casos.
• Si es una hipoteca a tipo variable, la cláusula suelo o los derivados de tipos.

Para ver el coste total, basta con añadir una fila al final con la suma de la columna B (todas las cuotas) más esos gastos. Lo que sale es lo que pagas en total; compáralo con el capital para ver cuánto te ha costado la hipoteca en intereses y comisiones.

Errores típicos al montar la tabla en Excel

#¡NOMBRE? al escribir PAGO en un Excel antiguo

Si al escribir =PAGO(...) ves #¡NOMBRE?, el problema casi nunca es la versión (PAGO existe desde Excel 97/2003 y sigue en todas las posteriores), sino el separador o un nombre mal escrito. Revisa que estés usando punto y coma para separar los argumentos, o coma si tu Excel está configurado con punto decimal. Otro motivo habitual es escribir =PAGO. con un punto detrás, o confundirlo con =PAGOS. La función exacta es PAGO, sin más.

El capital pendiente nunca llega a 0 o se va a negativo

Si al final de la tabla el capital pendiente no termina en cero, lo más probable es que el plazo no coincida: arrastraste hasta una fila anterior (te faltan meses) o hasta una posterior (te pasaste y aparecen negativos). Comprueba que llegas exactamente a la fila 7 + plazo en meses (para 30 años, fila 366). Si arrastraste bien y aun así queda un céntimo, el problema es el redondeo de la cuota: envuelve B4 con =REDONDEAR(PAGO(B2/12;B3*12;-B1);2) para que la cuota salga con dos decimales exactos.

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

#¡DIV/0! cuando una celda está vacía

Si borras el tipo de B2 o el plazo de B3, todas las fórmulas de la columna C devuelven #¡DIV/0! al dividir entre 12 o entre meses. Es la pista más clara de que falta un dato: restaura el valor y la tabla vuelve a calcular sola. Si quieres evitarlo al compartir el archivo, recurre a =SI.ERROR(B7*B$2/12;0) en la columna C para que las celdas vacías muestren 0 en vez del error.

Versiones de Excel y la función PAGO

PAGO es una función clásica de Excel: existe desde Excel 97/2003 y sigue presente en 2007, 2010, 2013, 2016, 2019, 2021, 2024 y Microsoft 365, además de Excel para la web, Excel para Mac y Excel para móviles. El único requisito real es respetar el separador regional: punto y coma donde el decimal es la coma, coma donde el decimal es el punto.

Si tu Excel es muy antiguo (2003 o anterior) y aún lo mantienes, la función también está; lo que puede faltar son las tablas y los formatos como moneda con dos decimales. En cualquier versión actual de Microsoft 365 o Excel 2024, todo lo que ves en este tutorial funciona igual, incluida la fórmula, el controlador de relleno y el formato de las celdas.

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

• Calcular la cuota mensual de cualquier hipoteca fija con la función PAGO, metiendo solo capital, tipo anual y plazo en años.
• Montar una tabla mes a mes con cinco columnas (mes, cuota, interés, capital amortizado, capital pendiente) que termina exactamente en cero.
• Entender qué gastos se quedan fuera (comisión de apertura, seguros, notaría) para sumarlos aparte y conocer el coste total.

Como siguiente paso, prueba a duplicar la hoja y cambiar el tipo y el plazo en la copia: compara las cuotas y los intereses totales de las dos hipotecas para decidir cuál te conviene antes de firmar.

Datos comprobados en octubre de 2026.

Dudas al montar la tabla de amortización en Excel