Cómo se calcula la cuota mensual de una hipoteca?

Si tras la lectura de las respuestas de los expertos sobre la fórmula de cálculo de un préstamo hipotecario francés y otros tipos de amortización (cuota creciente, cuota final, carencias, etc), quieres que un economista y profesor de economía financiera en la UIB te ayude a descifrar lo que pagas al banco, te invito a solicitar una sesión online de consultoría hipotecaria:

Hola Rafael,

Muchas gracias por tu pregunta, pues seguro que a mucha gente le gustaría comparar la cuota hipotecaria que pagaría según varias ofertas que haya podido encontrar. En mi respuesta voy a complementar una respuesta anterior. Voy a ayudarte a crear una herramienta Excel para que puedas utilizarla para comparar distintas hipotecas solo cambiando el capital inicial, o el tipo de interés o los años de duración. Te voy a enseñar a construir paso a paso y de forma muy intuitiva una tabla de amortización de préstamo hipotecario utilizando el sistema francés, el más común en España.

En primer lugar, destacaría 2 casillas identificativas: una para el capital inicial y otra para el número de años de la hipoteca. Seguidamente crearía la cabecera de una tabla con las siguientes columnas: Periodo, Referencia, Diferencial, Tipo de interés, Cuota, Intereses, Amortización y Capital Pendiente.

Posteriormente, en la columna periodo multiplicamos la celda del número de años por 12 (meses) para obtener el número de cuotas totales que habremos pagado al final de la vida del préstamo hipotecario. En su caso sería multiplicar 30 años por 12 meses, lo que resulta en un total de 360 cuotas mensuales. Seguidamente solo tendrás que colocarte en la celda justo debajo de ese 360, restarle 1 a la celda de arriba y arrastrar hasta que el número del periodo de 0.

En tercer lugar, deberemos construir el tipo de interés. En la columna referencia se colocará el tipo nominal fijo o bien el Euribor que resulte de la revisión. En nuestro caso, voy a simular que se hace la revisión hoy 27 de enero de 2023. Por lo tanto, tomaré el último valor del Euribor anual (supongo que es anual) publicado en el Banco de España que pertenece al mes de diciembre de 2022: 3,018%. En la columna Diferencial colocaré un 0% durante el periodo de interés fijo y 0,70% a partir de cuando se empieza a aplicar el tipo de interés variable. Por último, la columna Tipo de interés es el resultado de sumar las dos anteriores columnas.

Para el cálculo de la cuota vamos a aplicar la siguiente fórmula en Excel donde α es la cuota mensual que pagaremos; P es el capital pendiente; i% es el tipo de interés aplicable; y n es el número de cuotas pendientes.

Esta fórmula en Excel quedará de la siguiente manera:

=P*((i%/12)*((1+(i%/12))^n))/(((1+(i%/12))^n)-1)

Para calcular la cuota puedes copiar y pegar la anterior fórmula Excel en la primera celda de la columna Cuota y substituir P por la celda del capital pendiente antes del pago de la cuota (180.000€ la primera cuota); i% por la celda del tipo de interés (1,20%); y n por la celda del número de cuota anterior. Finalmente podrás arrastrar esta fórmula hasta el final de la tabla.

Casi para acabar, vamos a calcular la cantidad de intereses que corresponden a esta cuota. Lo haremos con la siguiente fórmula donde I es la cantidad en euros de los intereses pagados y las demás variables ya las conocemos:

En Excel introduciremos la siguiente fórmula en la primera celda de la columna Intereses:

=P*i%/12

Como podrás pensar, la proporción que corresponde a la amortización del préstamo es la diferencia entre la cuota que hemos calculado con la primera ecuación y la cantidad en euros pagada en concepto de intereses.

La cantidad resultante en la columna Amortización se tendrá que substraer del capital pendiente que teníamos antes del pago de la cuota para así conocer el nuevo capital pendiente.

Finalmente, solo tenemos que arrastrar todas las fórmulas hasta el final de la tabla y ya tendremos el cuadro de amortización del préstamo hipotecario utilizando el sistema francés, el más común en España. Como la hipoteca que me planteas tiene un tipo de interés variable referenciado en el Euribor “anual” (supongo que es anual), lo único que tendremos que actualizar son las celdas en blanco de la columna Referencia. Después de cada revisión del Euribor anual conoceremos el valor mensual que se aplicará a nuestro ejemplo. Una vez lo conocemos, introducimos ese valor en la columna Referencia para que la tabla nos devuelva la nueva cuota que pagaremos durante los siguientes 12 meses (en caso de que la revisión sea anual). De esto modo, iremos nutriendo la tabla con la cuota que pagaremos.

Esta misma herramienta que acabamos de crear nos aporta otra función. Con ella podemos modificar la celda del capital pendiente, de los años, del diferencial o del tipo de interés muy fácilmente para simular distintas ofertas hipotecarias que hayamos encontrado. También una estimación fácil sería rellenar la columna Referencia con la media de los valores mensual del Euribor anual durante los últimos, por ejemplo, 25 años.

Aprovecho esta pregunta, a la que llegan miles de usuarias y usuarios buscando información sobre cómo calcular la cuota de su hipoteca, para dejaros un vídeo mío sobre cómo hacer un cuadro de amortización sencillo en Excel, paso a paso, y así poder comparar lo que se pagaría por una hipoteca variable y por una a interés fijo:

Si suponemos que en la hipoteca se aplica el sistema de amortización más frecuente en España, el sistema de amortización francés o de cuota constante, la primera forma sencilla y rápida de saber la cuota mensual es usar nuestra calculadora hipotecaria o los simuladores del Banco de España.

Así utilizando el tipo fijo del 1,20% nominal anual, resultaría una cuota de 595,64 euros.

En cuanto al cálculo de la hipoteca a tipo variable, solo podemos calcular la cuota del primer periodo, fijo, ya que no sabemos cómo puede evolucionar en Euribor de los próximos años. Se pueden hacer simulaciones a futuro, pero serán solo proyecciones, no la realidad del tipo de interés que solo conoceremos a medida que vaya pasando el tiempo y se publiquen los diferentes Euribor (el más habitual es el Euribor a un año, que se publica en el BOE y puedes consultar en el Banco de España).

Si que remos conocer la matemática financiera que hay detrás de estos cálculos, saber que:

Si en un Excel replicamos P (capital pendiente en euros), i (interés nominal mensual), n (meses del plazo de la hipoteca) obtendríamos una fórmula del tipo:

=+G4/(((1-(1+G6)^(-G8))/G6))

Siendo G4 = 180.000€, G6 = 1,2%/12 = 0,10% , G8 = 360

Y también con Excel tenemos una fórmula directa:

=+PAGO(G6;G8;-G4) = 595,64