Tabla de amortización de un préstamo hipotecario en Excel: cómo hacerla

Una tabla de amortización es el cuadro que te dice, mes a mes, cuánto pagas de cuota, qué parte son intereses y qué parte recuenta capital. Con Excel abierto la construyes tú mismo y la puedes adaptar a las que la vida te depare: amortizar antes de tiempo, cambiar el plazo, comparar ofertas. Al terminar este tutorial sabrás calcularla con PAGO, montar la hoja por completo, añadir pagos extra, detectar los errores típicos y obtener un resumen con una tabla dinámica. Funciona en todas las versiones de Excel desde 2007.

Qué es una tabla de amortización y por qué te conviene hacerla tú

Una tabla de amortización reparte cada cuota entre lo que pagas de capital (la deuda que devuelves) y lo que pagas de interés (el precio del dinero prestado). Al principio pagas más intereses porque la deuda es grande; al final, casi todo lo que pagas recuenta capital.

Imagina una hipoteca de 150.000 EUR al 3% anual a 20 años. La cuota fija sale alrededor de 831,90 EUR al mes. En la primera cuota, unos 375 EUR son intereses y unos 457 EUR recuentan capital; en la última, los intereses son casi cero y todo recuenta deuda. Verás cómo cambia la composición mes a mes y cuánto ahorras si haces un pago extra.

Hacerla tú, y no fiarte del PDF del banco, te deja comparar dos hipotecas cambiando solo dos celdas, probar con distintos plazos o ver el efecto de una amortización anticipada. Es el control que necesitas para decidir.

Las cuatro cifras que mueven toda la tabla

Cuatro números, y solo cuatro, determinan todo el cuadro:

• Capital prestado: lo que te han dejado.
• Tipo de interés anual: el TIN, en porcentaje.
• Plazo: en años, pero la fórmula trabaja en meses.
• Cuota fija: lo que pagas cada mes, calculada con PAGO.

Las dos primeras cifras necesitan pasarse a la misma unidad (mensual) antes de tocar una fórmula: el 3% anual se divide entre 12 para sacar la tasa mensual (0,25%); un plazo de 20 años se multiplica por 12 para sacar 240 meses. Mezclar unidades es el error más común, y por eso la tabla no cuadra.

Las otras dos cifras, cuota y plazo, están ligadas: a más meses, menos cuota pero más intereses totales; a menos meses, más cuota pero menos intereses. Tocar una cambia la otra.

Calcular la cuota mensual con la función PAGO

PAGO (en inglés PMT) devuelve la cuota periódica de un préstamo.

Sintaxis:

=PAGO(tasa; nper; va; [va; tipo])

Para 150.000 EUR al 3% anual a 20 años:

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

Pasos:

1. En la celda B1 escribe 150000.
2. En B2 escribe 3%.
3. En B3 escribe 20.
4. En B4 escribe la fórmula =PAGO(B2/12;B3*12;-B1).
5. Pulsa Entrar y verás la cuota: 831,90 EUR.

El capital va en negativo para que la cuota salga en positivo; es el criterio de flujos de caja.

Montar la hoja de amortización paso a paso

Coloca las cuatro cifras de entrada arriba del todo y deja una fila vacía antes de empezar el mes 1.

La fila del mes 1 lleva cinco columnas: Mes, Saldo inicial, Cuota, Interés, Capital amortizado y Saldo final.

Pasos:

1. Escribe los encabezados en A1:H1.
2. En A2 escribe 1.
3. En B2 escribe =B1 (la celda B1 del capital).
4. En C2 escribe =$B$4 para fijar la cuota.
5. Deja D2, E2 y F2 para los siguientes apartados.

La fórmula del interés de cada mes

El interés del mes se calcula sobre el saldo que aún debes.

=B2*($B$2/12)

Pasos:

1. En D2 escribe =B2*($B$2/12).
2. Arrastra el controlador de relleno hasta la fila 241.

La referencia a B2 (el interés anual) lleva dólares para que no se mueva al copiar.

El capital amortizado cada mes

Capital amortizado = cuota − interés del mes.

=C2-D2

Pasos:

1. En E2 escribe =C2-D2.
2. Arrastra hasta la fila 241.

El saldo después de pagar

Saldo final = saldo inicial − capital amortizado.

=B2-E2

Pasos:

1. En F2 escribe =B2-E2.
2. Para que la siguiente fila use el saldo final como inicial, en B3 escribe =F2.
3. Arrastra B3:F3 hasta la fila 241.

Comprobar que la tabla está bien hecha

Pasos:

1. En una celda aparte, suma la columna de capital amortizado con =SUMA(E2:E241). Tiene que darte 150.000,00.
2. Revisa que la cuota de la columna C es idéntica en todas las filas.
3. Comprueba que los intereses totales son mayores que el capital al principio y casi cero al final.
4. Si el saldo final no termina exactamente en cero, ajusta la última fila como se explica más adelante.

Incluir pagos extra para ahorrar intereses

Una columna más para un pago extra opcional cambia las reglas del juego. Cualquier cantidad que sumes al capital de un mes reduce la deuda y, con ella, los intereses futuros.

Pasos:

1. Añade la columna G con el encabezado Pago extra.
2. Cambia la fórmula del saldo final para restar también el extra.
3. El saldo inicial del mes siguiente pasa a ser el saldo final ya rebajado.

Cuota fija con capital extra al principio

El pago extra se suma al capital amortizado del mes y baja el saldo.

=B2-E2-G2

Pasos:

1. Cambia la fórmula de F2 a =B2-E2-G2.
2. Escribe 50 en G2 y arrastra la columna G hacia abajo si el extra se mantiene todos los meses.

Adelantar cuotas al final del año

Una fórmula condicional aplica el extra solo en los meses múltiplos de 12.

=SI(RESIDUO(A3;12)=0;G3;0)

Pasos:

1. Añade la columna H (Extra fin de año) con la fórmula =SI(RESIDUO(A3;12)=0;G3;0).
2. En F2 cambia el cálculo a =B2-E2-G2-H2.
3. Mira cómo bajan los meses pendientes: en la columna de saldo final verás que cada 12 meses el descenso es mayor.

Por qué la tabla falla o no cuadra

Las cinco causas reales con la hoja delante:

• Unidades mezcladas: pusiste el interés como 3 en vez de 3%, o los años en vez de los meses.
• Signo de PAGO: el capital iba en positivo y la cuota sale negativa.
• Decimales: redondeos al céntimo que dejan un céntimo suelto en la última fila.
• Último mes con saldo raro: no ajustaste la última cuota al saldo pendiente.
• Referencias que se mueven: la celda del interés anual se desplazó al arrastrar.

La cuota sale negativa o el doble

PAGO devuelve un número negativo porque el capital se pone en negativo (criterio de flujo de caja). Si pusiste el capital en positivo, la cuota sale negativa.

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

devuelve -831,90. Para verlo en positivo tienes dos opciones:

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

o, mejor, dejar el capital en negativo desde el principio:

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

La segunda es la forma limpia.

El último mes no termina en cero

Los redondeos al céntimo dejan un saldo residual en la última fila. Lo arreglas forzando que el capital amortizado del último mes sea el saldo pendiente.

=B241

Pasos:

1. En E241 escribe =B241 para que el capital amortizado del último mes sea justo el saldo que queda.
2. En C241 la cuota quedará distinta del resto, pero el saldo final será exactamente 0,00.

El interés cambia al copiar la fórmula

La celda del interés anual no estaba fijada con F4 / Cmd+T y al arrastrar se desplazó.

=B2*(B2/12)

Pasos:

1. Selecciona D2 y edítala para que la referencia a la celda del interés anual lleve dólares.
2. Pulsa F4 / Cmd+T sobre $B$2 para fijarla.
3. Vuelve a arrastrar hacia abajo.

Convertir el rango en una tabla de Excel que crece sola

Si más adelante quieres añadir meses (por una amortización anticipada, por ejemplo), convierte el rango en una tabla de Excel.

Pasos:

1. Selecciona A1:F241.
2. Ve a Insertar > Tablas > Tabla (o Inicio > Estilos > Dar formato como tabla).
3. Marca La tabla tiene encabezados y pulsa Aceptar.
4. Cambia el nombre de la tabla a TablaAmort en la pestaña Diseño de tabla.
5. Las fórmulas pasarán a usar referencias estructuradas: =SUMA(TablaAmort[Capital]).

Sacar un resumen con una tabla dinámica

Con los 240 meses ya calculados, una tabla dinámica te resume el cuadro en tres clics.

Pasos:

1. Haz clic dentro del rango A1:F241.
2. Pulsa Alt+F5 y elige Tabla dinámica.
3. En la lista de campos, arrastra Interés a Valores (lo pone como Suma automáticamente).
4. Repite con Capital.
5. Para la cuota media, arrastra Cuota a Valores, haz clic en su configuración y elige Promedio.
6. Cambia los datos de entrada: pulsa Alt+F5 o ve a Analizar tabla dinámica > Actualizar.

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

• Las cuatro cifras que mueven la tabla: capital, interés anual, plazo en meses y cuota fija.
• PAGO (PMT) calcula la cuota mensual con la tasa y el plazo en la misma unidad.
• La fila tipo con saldo inicial, cuota, interés, capital amortizado y saldo final, arrastrada con el controlador de relleno.
• Los pagos extra acortados al final del año con SI y RESIDUO.
• La tabla dinámica resume el cuadro en tres clics con Alt+F5.

El siguiente paso: cambia las celdas de entrada (capital, interés, plazo) y compara dos hipotecas distintas en la misma hoja, o duplica la hoja y juega con pagos extra hasta dar con la cuota que te encaja.

Dudas al montar una tabla de amortización en Excel