Calculadora de hipotecas de Excel (Tabla de contenido)

  • Descripción general de la calculadora de hipotecas de Excel
  • ¿Cómo calcular los pagos mensuales de un préstamo en Excel?

Descripción general de la calculadora de hipotecas de Excel

Todos tomamos hipotecas / préstamos para nuestras necesidades. Podría ser para comprar una casa, un préstamo de automóvil, un préstamo personal, etc. Tomamos préstamos a largo plazo que se extienden hasta 5, 10 o incluso 20 años.

Tenemos que pagar estos préstamos en cuotas mensuales. Esto incluye intereses y una parte del dinero principal, durante un período de tiempo acordado. La parte del pago principal reduce lentamente el saldo del préstamo, finalmente a 0. En caso de que se realicen pagos principales adicionales, el saldo restante se reducirá más rápidamente que el período de tiempo del préstamo. El prestamista, generalmente bancos u otras instituciones financieras, toma tres elementos y lo usa en una fórmula para calcular el pago mensual. Estos tres elementos clave son:

  1. Principio (monto del préstamo)
  2. Tasa de interés
  3. Período de tiempo (Número de años o meses para los cuales ha pedido prestado el préstamo)

Estos elementos se utilizan en fórmulas para calcular los pagos mensuales para el reembolso de su préstamo. Este cálculo parece engorroso de entender para un laico.

Con la ayuda de Excel, puede crear una hoja de cálculo y calcular los pagos mensuales por sí mismo.

¿Cómo calcular los pagos mensuales de un préstamo en Excel?

Podemos calcular los pagos mensuales del préstamo / hipoteca utilizando funciones integradas como PMT y otras funciones como IPMT y PPMT .

Puede descargar esta plantilla de calculadora de hipotecas de Excel aquí - Plantilla de calculadora de hipotecas de Excel

La función PMT se utiliza para calcular los pagos mensuales realizados para el reembolso de un préstamo o hipoteca.

= PMT (tasa, nper, pv)

La función PMT requiere 3 elementos para calcular los pagos mensuales:

  • TIPO - Tasa de interés del préstamo. Si la tasa es del 4% anual, mensualmente será del 4/12, que es del 0, 33% por mes.
  • NPER: el número de períodos para el reembolso del préstamo. Ejemplo: durante 5 años tenemos 60 períodos mensuales.
  • PV - Valor actual del préstamo. Es la cantidad prestada.

Sin embargo, hay algunos otros elementos opcionales que pueden usarse para algunos cálculos específicos, si es necesario. Son:

  • FV: el valor futuro de la inversión, después de realizar todos los pagos periódicos. Suele ser 0.
  • TIPO : "0" o "1" se utilizan para determinar si el pago se realizará al principio o al final del mes.

¿Cómo usar la fórmula para obtener el monto del pago mensual?

Ahora, aprenderemos cómo usar la función PMT para calcular el pago mensual. Tomemos un ejemplo para entender cómo funciona esta función.

Ejemplo 1

Supp + ose hemos tomado un préstamo hipotecario por $ 2, 00000 por 10 años a una tasa de interés del 6%. Hagamos una tabla en Excel como a continuación.

Ahora, para calcular el pago mensual, ingresaremos todos los puntos de datos en la función de la siguiente manera:

En la celda C8, comenzaremos a escribir la fórmula presionando = y luego escribiendo PMT. Luego ingresaremos los puntos de datos según la sintaxis. Cabe señalar que, dado que nuestro préstamo se basa en pagos mensuales, tenemos que dividir la tasa de interés por 12 y multiplicar la cantidad de años por 12 (para darnos la cantidad total de pagos mensuales).

Por lo tanto, la tasa del 6% se convertirá en .5% (6% / 12) mensualmente y el período de tiempo se convertirá en 120 períodos mensuales. PV será 200000, la cantidad prestada. Fv y type son opcionales en este caso, por lo que los dejaremos. Una vez que hayamos ingresado los datos en la fórmula, presionaremos Enter. Veremos el siguiente resultado.

Para el préstamo que asciende a $ 200000, a una tasa de interés del 6% por 10 años, el pago mensual será de $ 2, 220.41

Así es como calculamos los pagos mensuales utilizando la función PMT en Excel. Este pago mensual incluye una parte del monto principal y los intereses también. Bueno, si queremos saber la cantidad de principio y la cantidad de interés incluida en este pago mensual, podemos hacerlo. Para este propósito, tenemos otras dos funciones que son PPMT e IPMT .

La función PPMT se usa para calcular la parte principal del pago, mientras que la función IPMT se usa para calcular la parte de interés del pago. Ahora veremos cómo usar estas funciones para conocer la composición del pago mensual.

Tomando el ejemplo anterior, ahora encontraremos el PPMT y el IPMT. Escribiremos el número de pago en la celda B8, el pago mensual en C8, el principio en D8 y el interés en E8. En la celda B9, bajo el encabezado Pago no., Escribiremos 1 como primer pago.

Ahora, en la celda C9 calcularemos el pago mensual con la función PMT.

Para calcular la cantidad principal en el pago mensual, utilizaremos la función PPMT. Escribiremos la función en la celda D9 como se muestra a continuación.

En la función PPMT, ingresaremos los datos según la sintaxis. La tasa será del 6% / 12 para obtener una tasa de interés mensual. Luego, en " por ", escribiremos el número de pago que es 1 en este caso. Luego, tiempo (nper) 10 años * 12 para convertirlo en no. de meses y finalmente la cantidad principal (pv).

Presione Entrar para obtener el PPMT.

Por último, calcularemos la parte de interés en el pago mensual mediante la función IPMT en la celda E9.

Escribiremos = IPMT en la celda E9 e ingresaremos los datos de la misma manera que lo hicimos en la función PPMT. Presione Entrar y obtendremos el IPMT.

Esto muestra que en el pago mensual de $ 2, 220.41, $ 1, 220.41 es la parte principal y $ 1, 000 es el interés. Para obtener más claridad de todas las funciones discutidas anteriormente, aquí hay otro ejemplo.

Ejemplo # 2

Mark ha tomado un préstamo para automóvil por $ 50, 000 al 4% por 3 años. Crearemos una tabla en Excel de la siguiente manera:

Entonces tenemos dos tablas, la más pequeña mostrará el pago mensual PMT (Celda I3). La tabla más grande muestra el total de 36 pagos por el monto del préstamo que representa tanto el principio como las porciones de intereses.

En primer lugar, calcularemos PMT en la celda I3 como se muestra a continuación:

Ahora, calcularemos PPMT en la celda G10.

Presione Entrar para obtener PPMT.

Ahora calcularemos IPMT en la celda H10 como:

Entonces, ahora obtenemos $ 1309.53 como PPMT y $ 166.67 como IPMT, que se sumarán para convertirse en $ 1476.20 (pago mensual). Para mostrar todos los pagos, haremos que los valores sean dinámicos en las funciones PPMT e IPMT como se muestra a continuación.

La función IPMT se muestra a continuación.

Ahora arrastraremos PPMT (G10) e IPMT (H10) hacia abajo hasta el último pago (36º).

Podemos ver que a medida que aumenta el número de pagos, la parte del principio aumenta y el interés disminuye.

Así es como podemos crear una calculadora de hipotecas en Excel.

Cosas para recordar sobre la calculadora de hipotecas de Excel

  • El Excel muestra el pago mensual de la hipoteca como una cifra negativa. Esto se debe a que este es el dinero que se gasta. Sin embargo, si lo desea, puede hacerlo positivo también agregando: firme antes del monto del préstamo.
  • Uno de los errores comunes que a menudo cometemos al usar la función PMT es que no cerramos el paréntesis y, por lo tanto, recibimos el mensaje de error.
  • Tenga cuidado al ajustar la tasa de interés mensualmente (dividiendo entre 12) y el período de préstamo de años a no. de meses (multiplicando por 12).

Artículos recomendados

Esta es una guía de la Calculadora de hipotecas de Excel. Aquí discutimos cómo calcular los pagos mensuales de un préstamo con ejemplos y plantilla de Excel. También puede consultar nuestros otros artículos sugeridos para obtener más información:

  1. BUSCAR Fórmula en Excel
  2. Función Excel NORMSINV
  3. Cuadro de nombre y sus usos en Excel
  4. Concatenar cadenas en Excel

Categoría:

Consejos de Excel