Cuadro de amortización en Excel: sistema francés, hipoteca variable y amortización anticipada (con ejemplo)
Cómo hacer un cuadro de amortización en Excel con el sistema francés: cuota con PAGO, intereses y capital de cada mes, capital pendiente, revisión de tipo en hipotecas variables y comparación entre amortizar anticipadamente reduciendo plazo o cuota, con un ejemplo de 150.000 €.
Respuesta rápida: un cuadro de amortización muestra, cuota a cuota, qué parte de lo que pagas son intereses, qué parte es capital y cuánto capital queda pendiente. En España, la mayoría de préstamos e hipotecas usan el sistema francés: cuota constante mientras no cambie el tipo. En Excel la cuota mensual se calcula con:
=PAGO(TIN/12;Meses;-Capital)
Y cada fila del cuadro aplica: intereses = capital pendiente × TIN/12; amortización = cuota − intereses; nuevo pendiente = pendiente − amortización. Para una hipoteca de 150.000 € a un TIN del 3 % y 25 años, la cuota es de 711,32 € y los intereses totales suman 63.395,09 €. Las cifras son un ejemplo, no una oferta.
Paso 1: datos del préstamo
| Celda | Nombre | Ejemplo |
|---|---|---|
| B2 | Capital | 150.000 € |
| B3 | TIN | 3,00 % |
| B4 | Meses | 300 (25 años) |
| B5 | PrimeraCuota | Fecha del primer recibo |
| B6 | Cuota | =PAGO(TIN/12;Meses;-Capital) |
Usa el TIN (tipo de interés nominal) para calcular la cuota. La TAE sirve para comparar ofertas porque incluye comisiones y la frecuencia de pago, pero no es el tipo con el que se calculan los intereses de cada recibo.
Paso 2: las filas del cuadro
| Columna | Fórmula (fila 10) |
|---|---|
| A Nº cuota | 1, y debajo =SI(G10>0,005;A10+1;"") |
| B Fecha | =PrimeraCuota, y debajo =SI(A11="";"";FECHA.MES(B10;1)) |
| C Capital pendiente inicial | =Capital, y debajo =G10 |
| D Intereses | =REDONDEAR(C10*TIN/12;2) |
| E Cuota | =MIN(Cuota;C10+D10) |
| F Amortización de capital | =E10-D10 |
| G Capital pendiente final | =C10-F10 |
Arrastra hasta la fila 309 (300 cuotas) o más si vas a simular amortizaciones anticipadas; envuelve las fórmulas con SI($A11="";"";…) para que el cuadro termine solo cuando el pendiente llegue a cero.
Ejemplo: hipoteca de 150.000 € al 3 % a 25 años
| Cuota | Pendiente inicial | Intereses | Capital amortizado | Pendiente final |
|---|---|---|---|---|
| 1 | 150.000,00 | 375,00 | 336,32 | 149.663,68 |
| 2 | 149.663,68 | 374,16 | 337,16 | 149.326,52 |
| … | … | … | … | … |
| 60 | — | — | — | 128.258,21 |
Tras 5 años (60 cuotas) habrás pagado 42.679,20 € en cuotas, de los cuales 20.937,23 € son intereses, y todavía deberás 128.258,21 €. Es normal: en el sistema francés los primeros años se pagan sobre todo intereses.
Fórmulas directas útiles:
- Intereses de la cuota 12:
=PAGOINT(TIN/12;12;Meses;-Capital) - Capital amortizado en la cuota 12:
=PAGOPRIN(TIN/12;12;Meses;-Capital) - Intereses totales:
=Cuota*Meses-Capital(si no hay cambios de tipo ni amortizaciones anticipadas)
Paso 3: hipotecas variables y revisiones de tipo
En una hipoteca variable, el tipo se revisa periódicamente (normalmente cada 6 o 12 meses) como índice de referencia, habitualmente el Euríbor, más un diferencial. En cada revisión se recalcula la cuota sobre el capital pendiente y el plazo restante:
=PAGO(NuevoTipo/12;MesesRestantes;-CapitalPendiente)
Añade al cuadro una columna “Tipo aplicado” que cambie en los meses de revisión, y calcula los intereses con ese tipo. Ejemplo con valores supuestos: el primer año al 2,5 % (cuota 672,93 €); al revisar, el tipo pasa al 3,5 % y, con 145.624,99 € pendientes y 288 meses por delante, la nueva cuota es de 748,10 €, 75 € más al mes. Simular varios escenarios de tipo te ayuda a comprobar si podrías asumir subidas.
Paso 4: amortización anticipada, ¿reducir plazo o cuota?
Supón que al cumplir 5 años entregas 10.000 € extra. El pendiente baja de 128.258,21 € a 118.258,21 € y tienes dos opciones:
| Opción | Qué cambia | Ahorro de intereses |
|---|---|---|
| Reducir plazo | Mantienes 711,32 € y terminas 24 meses antes (216 cuotas restantes en lugar de 240) | ≈ 7.670,63 € |
| Reducir cuota | Mantienes el plazo y la cuota baja a 655,86 € | ≈ 3.310,34 € |
Reducir plazo ahorra más intereses; reducir cuota da más margen mensual. En Excel:
- Nuevo plazo:
=NPER(TIN/12;-Cuota;NuevoPendiente) - Nueva cuota:
=PAGO(TIN/12;MesesRestantes;-NuevoPendiente)
Antes de amortizar, revisa en tu escritura o contrato la comisión por amortización anticipada; la normativa de crédito inmobiliario la limita, pero el importe concreto depende del tipo de préstamo y del momento. Si además tienes otras deudas más caras, quizá convenga empezar por ellas: lo explica el plan para pagar deudas en Excel.
Otros usos del cuadro
- Préstamo personal o de coche: mismo modelo con menos cuotas.
- Carencia: durante los primeros meses solo se pagan intereses (cuota = intereses, amortización 0); después, PAGO sobre el plazo restante.
- Gráfico: un gráfico de áreas apiladas con Intereses y Amortización muestra cómo cambia el reparto de la cuota con los años.
- Presupuesto: la cuota debe encajar en tu presupuesto mensual con margen para imprevistos.
Errores frecuentes
- Usar la TAE en lugar del TIN para calcular la cuota.
- Olvidar dividir el tipo entre 12 o poner años en vez de meses.
- Signos: sin el signo menos delante del capital, PAGO devuelve una cuota negativa.
- No recalcular tras una revisión en hipotecas variables.
- Comparar solo la cuota: un plazo más largo baja la cuota pero dispara los intereses totales.
Preguntas frecuentes
¿Por qué el cuadro de mi banco no coincide al céntimo?
Los bancos pueden calcular intereses por días, aplicar el redondeo de otra forma o tener fechas de cargo distintas. Diferencias pequeñas son normales; si son grandes, comprueba el TIN, el plazo y las fechas.
¿Qué es el sistema alemán?
Es un sistema con amortización de capital constante: la cuota empieza más alta y va bajando. Se calcula con amortización = Capital/Meses e intereses sobre el pendiente; es menos habitual en hipotecas en España.
¿Funciona en Google Sheets?
Sí. PAGO, PAGOINT, PAGOPRIN, NPER y FECHA.MES existen en Google Sheets en español.
Más guías en Deudas y ahorro.