¿Cómo calcular el pago de una hipoteca en Excel? Guía sencilla y ejemplos

En este artículo, le explicaremos cómo calcular el pago de una hipoteca en Excel. Básicamente, le proporcionaremos instrucciones tanto con la fórmula PMT como con una plantilla lista para usar.

Tabla de contenido

1. Variables clave de los préstamos hipotecarios
2. ¿Cómo calcular la cuota hipotecaria en Excel con funciones?
3. Calculadora de hipotecas lista para usar
4. Puntos clave

Una hipoteca es esencialmente un préstamo utilizado específicamente para la compra de bienes inmuebles. Cuando usted contrata una hipoteca, un prestamista (como un banco) le proporciona una determinada cantidad de dinero para comprar una vivienda. A cambio, usted se compromete a devolver este préstamo a lo largo de un periodo determinado, que suele oscilar entre 10 y 30 años.

1. ¿Cuáles son las variables clave de los préstamos hipotecarios?

Antes de empezar a ver cómo calcular las mensualidades, es importante comprender los componentes clave de un contrato de crédito hipotecario:

• Importe del préstamo: Es la cantidad de dinero que le prestan para comprar su casa.
• Tipo de interés: El prestamista le cobra un interés, que es un porcentaje del importe del préstamo. Puede ser un tipo fijo o variable.
• Plazo del préstamo: Es el número de cuotas que pagará a través de su plan hipotecario.
• Cuota mensual: Es la cuota mensual que pagará. Tiene dos componentes: 1. Pago de intereses, 2. Pago del principal.
• Calendario de amortización: Es el plazo en el que reembolsará el préstamo.

Por lo tanto, comprender estos elementos básicos de una hipoteca es crucial antes de sumergirse en los detalles específicos del cálculo de las cuotas hipotecarias en Excel.

2. ¿Cómo calcular el pago de una hipoteca en Excel con funciones?

Ahora, crearemos un calendario de hipoteca desde cero. Utilizaremos la función PMT de Excel para calcular las cuotas de nuestro crédito hipotecario.

Paso 1: Preparar las variables

En primer lugar, debe establecer los supuestos de su préstamo.

Aquí introduciremos el importe del préstamo, el tipo de interés y las condiciones del préstamo.

Paso 2: Crear el esquema de la hipoteca

En segundo lugar, crearemos una tabla para nuestro calendario de hipotecas con los encabezados Periodo, Pago mensual, Pago de intereses, Pago del capital y Saldo restante.

Haremos todos los cálculos del calendario en esta tabla.

Paso 3: Calcular el pago de la hipoteca en Excel

A continuación, calcularemos los pagos mensuales y la división del capital y los intereses utilizando las funciones de Excel.

• ¿Qué es la fórmula del pago de la hipoteca en Excel?

La principal fórmula de pago de hipotecas en Excel es la función PMT.

En realidad, Excel tiene un montón de funciones incorporadas para calcular los pagos de amortización de los préstamos. También utilizaremos principalmente estas funciones para calcular los pagos de nuestra hipoteca:

• Función PMT: Calcula el pago mensual total.
• Función IPMT: Calcula la parte de intereses de cada pago.
• Función PPMT: Calcula la parte de capital de cada pago.

• Calcule los pagos mensuales:

Utilizaremos la función PMT para calcular nuestro pago mensual total:

• Tasa: El tipo de interés para cada periodo. Si tiene un tipo anual y realiza pagos mensuales, divida este tipo por 12.
• Nper: Número total de pagos del préstamo.
• Pv: Valor actual o importe total del préstamo

Supongamos que tomamos prestados 500.000 $ con un tipo de interés anual del 8% y que pagaremos esta hipoteca en 10 años con pagos mensuales.

He aquí el cálculo de nuestro pago mensual:

Aquí hemos utilizado la función PMT con la Tasa Anual/12,el Plazo del Préstamo en meses y el Plazo del Préstamo para calcular nuestros pagos mensuales.

Así, veremos el resultado en la celda seleccionada. Además, es importante tener en cuenta que la función PMT devuelve un número negativo, que representa un pago saliente.

• Calcular los pagos de intereses y principal

Si desea ver la división de los pagos mensuales entre intereses y principal, utilizaremos la función IPMT.

Así, calcularemos los intereses de cada pago con la función IPMT de Excel:

La función IPMT tiene una sintaxis muy similar con la función PMT. Ahora también añadimos la variable per, que es el periodo para el que se quiere hallar el interés y debe estar en el intervalo de 1 a nper.

Continuemos con nuestro ejemplo:

Del mismo modo, la fórmula IPMT también da un número negativo como pago.

Después de calcular la parte del pago de intereses, es muy fácil calcular la parte del principal. Así, simplemente restaremos el pago de intereses del pago mensual total:

Como alternativa, puede calcular directamente el pago del principal con la función PPMT:

Así pues, esta función PPMT es muy similar a la IPMT en cuanto a sintaxis.

• Cálculo del saldo restante

Como no se trata de una función preestablecida de Excel, calcularemos nuestros saldos mensuales restando el principal pagado hasta la fecha del importe original del préstamo. Al utilizar el signo menos, también debemos convertir los pagos negativos del principal en positivos multiplicándolos por el coeficiente -1.

Ahora sus cálculos principales están completados.

Por último, copiaremos todas las fórmulas de la primera fila hasta el final de nuestra tabla, lo que completará nuestro calendario hipotecario:

Paso 4: Resumen de préstamos hipotecarios

Por último, resumiremos la cantidad total pagada a lo largo del periodo del préstamo al final de la tabla.

• Importe de las cuotas = El total de las cuotas mensuales.
• Interés total pagado = La suma de la columna de tipos de interés.
• Pago total = La suma de todos los pagos de intereses y principal.

Ahora tenemos una visión clara sobre nuestro crédito hipotecario. Por lo tanto, sabemos cuánto pagaremos en total, y qué parte del total se destinará a los intereses.

3. Calculadora de hipotecas lista para usar

Si no quiere dedicar su tiempo a crear un calendario hipotecario desde cero, puede utilizar nuestra plantilla para un cálculo sin complicaciones.

Básicamente, esta plantilla consta de cuatro áreas principales: 1. Supuestos, 2. Resultados, 3. Gráficos visuales, 4. Tabla de amortización.

– Esta es una captura de pantalla de la plantilla de Excel de la calculadora de hipotecas de Someka –

Es muy fácil utilizar esta plantilla:

1. Rellene sus supuestos
2. Analice el resumen y los gráficos de su préstamo
3. Imprima el calendario de su hipoteca

En la tabla de supuestos, introducirá:

• Importe del préstamo
• Tipo de interés
• Fecha del primer pago
• Frecuencia de pago
• Plazo del préstamo
• Pago global (opcional)

A continuación, el resto de la plantilla se rellenará automáticamente. No necesita realizar ningún cálculo manualmente.

4. Puntos clave: ¿Cómo calcular el pago de una hipoteca en Excel?

En este artículo hemos explicado cómo calcular el pago de una hipoteca en Excel. Hagamos un resumen de los puntos clave:

1. En primer lugar, Microsoft Excel es una potente herramienta para cálculos financieros. Además, gracias a la flexibilidad, el acceso sin conexión y el diseño fácil de usar que ofrece, mucha gente prefiere utilizar Excel para los cálculos hipotecarios.
2. En segundo lugar, puede utilizar funciones de amortización de préstamos preestablecidas en Excel para crear un plan de amortización de hipotecas desde cero. Hemos explicado todos los conocimientos de sintaxis y formato necesarios para ello.
3. Pero si prefiere una herramienta más fácil e instantánea para sus calendarios hipotecarios, entonces puede descargar la Calculadora Hipotecaria Someka, disponible en formatos Excel o Google Sheets.

Así, con esta herramienta lista, no se molestará con el formato o los cálculos:

• Listo para usar
• Diseño elegante
• Cálculo dinámico de la hipoteca
• Imprimible y editable

Espero que este artículo le resulte útil.

Lecturas recomendadas:

¿Cómo calcular el tipo de interés de un préstamo?

Fórmula VAN en Excel: ¿Cómo calcular el Valor Actual Neto en Excel?

¿Cómo crear un plan de amortización en Excel?