Cómo crear un cronograma de amortización de hipoteca en Excel
Un cronograma de amortización muestra exactamente a dónde va cada euro o dólar del pago de su hipoteca — cuánto cubre intereses, cuánto amortiza principal y cuánto adeuda tras cada mes. Construirlo usted mismo en Excel es la mejor manera de entender su hipoteca, comparar ofertas de distintos bancos y ver lo que realmente ahorraría refinanciarla. Esta guía recorre todo el proceso, fórmula a fórmula.
¿Qué es un cronograma de amortización?
Un cronograma de amortización es una tabla mensual de su préstamo. Cada fila muestra el número de pago, el importe del pago, la parte de intereses, la parte de principal y el saldo restante. En los primeros años, la mayor parte de su pago son intereses; con el tiempo, el saldo se desplaza hacia el principal. Ver esa curva desplegada es lo que hace evidente el coste real de una hipoteca — y el valor de las amortizaciones anticipadas.
Spitzer vs. principal constante: conozca su método de pago
La mayoría de las hipotecas utilizan el método Spitzer (anualidad): un pago mensual fijo, con la mezcla de intereses/principal cambiando cada mes. La alternativa es el principal constante: paga la misma cantidad de principal cada mes más intereses sobre el saldo restante, por lo que los pagos comienzan más altos y disminuyen con el tiempo. El principal constante cuesta menos en intereses totales pero exige un pago mayor al principio. Sus fórmulas de Excel difieren entre ambos, así que decida cuál está modelando antes de empezar.
Lo que necesita antes de empezar
Tres números definen el cronograma básico: el principal del préstamo, la tasa de interés anual y el plazo en meses (años × 12). Si su hipoteca se divide en varias pistas — por ejemplo, una pista de tasa fija, una vinculada al prime y una vinculada al IPC — trate cada pista como su propio préstamo con su propio cronograma y luego súmelos. Obtenga los números de la carta de oferta de su banco; cada pista se lista por separado.
Paso 1 — Configure un área de entradas
Coloque los parámetros del préstamo en celdas dedicadas en lugar de escribirlos dentro de las fórmulas: B1: principal (por ejemplo, 900.000) B2: tasa de interés anual (por ejemplo, 3,5%) B3: plazo en meses (por ejemplo, 240) Cada fórmula del cronograma debe hacer referencia a estas celdas. De ese modo, cambiar una entrada recalcula al instante todo el cronograma — que es precisamente lo que hace útil el modelo para comparar ofertas.
Paso 2 — Calcule el pago mensual con PAGO
Para un préstamo Spitzer, el pago mensual fijo es: =PAGO(B2/12; B3; -B1) PAGO toma la tasa mensual (tasa anual dividida por 12), el número de pagos y el principal (negativo, porque es dinero que recibió). Para 900.000 al 3,5% durante 20 años esto devuelve unos 5.219,64 al mes. Este número permanece constante durante toda la vida de un préstamo Spitzer a tasa fija.
Paso 3 — Divida cada pago con PAGOINT y PAGOPRIN
Construya la tabla con una fila por mes. Si la columna A contiene el número de pago (1, 2, 3…): Parte de intereses: =PAGOINT($B$2/12; A7; $B$3; -$B$1) Parte de principal: =PAGOPRIN($B$2/12; A7; $B$3; -$B$1) Ambas siempre suman el importe de PAGO. Observe los signos $: las entradas son referencias absolutas para que pueda arrastrar las fórmulas por toda la tabla, mientras que el número de periodo permanece relativo.
Paso 4 — Realice el seguimiento del saldo restante
Añada una columna de saldo. La primera fila parte del principal menos el primer pago de principal; cada fila siguiente resta la parte de principal de ese mes del saldo anterior. La fila final debe llegar exactamente a cero — si no es así, una referencia en una de sus fórmulas ha fallado. Sumar la columna de intereses le da el coste total de intereses del préstamo, generalmente una cifra reveladora.
Pistas múltiples y préstamos vinculados al IPC
Las hipotecas reales suelen ser una mezcla: parte fija, parte vinculada al prime, parte vinculada al IPC. Modele cada pista en su propia hoja y sume los pagos mensuales en una vista combinada. Las pistas vinculadas al IPC añaden un matiz — el propio saldo restante se indexa cada mes, por lo que hay que multiplicar el saldo por la inflación mensual esperada antes de calcular intereses. Las pistas de tasa variable (por ejemplo, con reajuste cada 5 años) necesitan que la tasa se recalcule en cada punto de reajuste. Aquí es donde una hoja hecha a mano empieza a ser realmente difícil de mantener correcta.
Comparación de opciones de refinanciación
Para evaluar la refinanciación, construya un segundo cronograma para el nuevo préstamo (saldo restante, nueva tasa, nuevo plazo) y compare dos cosas: el cambio en el pago mensual y el cambio en el interés total restante. No olvide las comisiones de amortización anticipada. Un pago mensual más bajo que alarga el plazo puede costar mucho más en intereses totales — el cronograma hace visible ese trade-off en lugar de ocultarlo.
Errores comunes
Los errores clásicos: usar la tasa anual donde corresponde la mensual (siempre divida por 12); confundir los números de pagos cuando el plazo está en años; olvidar el signo menos en el principal en PAGO/PAGOINT/PAGOPRIN; codificar números fijos en las fórmulas en lugar de referenciar las entradas; e ignorar la indexación en pistas vinculadas al IPC, lo que subestima el coste real. Compruebe siempre que el saldo final sea cero y que interés + principal sea igual al pago en cada fila.
¿Prefiere que lo hagan por usted?
El Constructor de hipoteca en Excel genera todo este libro por usted — múltiples pistas de interés, indexación por IPC, cronogramas de amortización completos con fórmulas en vivo y una comparación de refinanciación — listo para descargar y editar.
Construir mi hipoteca en Excel — $20¿Estás evaluando la compra o inversión de un inmueble?
Combina esta tabla de amortización con nuestra guía de estudio de viabilidad para ver el VAN, la TIR y el payback junto con el costo del préstamo.
Guía de estudio de viabilidadPreguntas frecuentes
¿Cómo calculo un cronograma de amortización de hipoteca en Excel?
Use PMT para obtener el pago mensual fijo, después IPMT y PPMT para dividir cada pago entre intereses y principal, y añada una columna de saldo acumulado que reste la parte de principal cada mes. Los pasos anteriores recorren cada fórmula.
¿Qué fórmula de Excel calcula el pago mensual de la hipoteca?
=PMT(tasa/12, meses_plazo, -principal). El primer argumento es la tasa anual dividida entre 12 (tasa mensual), el segundo es el número de pagos mensuales (años × 12) y el tercero es el importe del préstamo en negativo para que el resultado salga positivo.
¿Hay una plantilla gratuita de calculadora de hipoteca en Excel para descargar?
Puede crear la suya en unos minutos con los pasos de esta guía: todas las fórmulas son de Excel estándar. Y si prefiere saltarse el montaje, nuestro generador de Excel de hipoteca crea un archivo listo y totalmente enlazado con fórmulas para sus cifras exactas. Probar el generador de Excel de hipoteca
¿Cómo comparo en Excel tramos de hipoteca fijos, indexados al IPC y fijos no indexados (קל"צ)?
Cree un cronograma de amortización por tramo en su propia hoja y añada una hoja resumen que sume los pagos, los intereses totales y el coste final de cada tramo en paralelo. Nuestro generador produce la comparación de varios tramos automáticamente.
¿Cuál es la diferencia entre el método Spitzer y la amortización de capital constante?
Spitzer mantiene igual el pago mensual total, por lo que la parte de intereses baja y la de principal sube con el tiempo. Con capital constante se amortiza un principal fijo cada mes, así que el pago empieza más alto y va bajando — y se pagan menos intereses en total.
¿Puedo modelar en Excel una hipoteca de solo intereses o de tipo variable?
Sí. Para periodos de solo intereses, fije el pago como saldo × tasa mensual y deje el saldo sin cambios. Para tipo variable, ponga la tasa de cada periodo en su propia columna y recalcule PMT sobre el saldo y el plazo restantes cada vez que cambie la tasa.
¿Qué precisión tiene un cronograma hecho con IA frente a hacerlo manualmente?
Usa exactamente las mismas fórmulas estándar PMT, IPMT y PPMT que escribiría usted, enlazadas en vivo a las celdas de entrada. Nada está fijado a mano, así que puede auditar cada celda, cambiar un dato y ver recalcularse todo el cronograma.