¿Sabía que puede usar Excel para calcular los reembolsos de su préstamo? Este artículo lo guiará a través de todos los pasos necesarios para hacerlo. (Consulte también: Calculadoras de hipotecas: cómo funcionan .)

Con Excel, puede obtener una mejor comprensión de su hipoteca en tres sencillos pasos. El primer paso es determinar el pago mensual. El segundo es descubrir la tasa de interés, y el tercero es encontrar el cronograma del préstamo. Para hacerlo, puede crear una tabla en Excel que le dirá: las tasas de interés; el cálculo del préstamo por la duración; descomposición de un préstamo, así como la amortización y el cálculo de la renta mensual.

Cálculo de préstamo para alquiler mensual

Primero, veamos cómo implementar el cálculo de un pago mensual para una hipoteca. En otras palabras, utilizando la tasa de interés anual, el capital y la duración, podemos determinar el monto que se reembolsará mensualmente.

La fórmula, como se muestra en la captura de pantalla anterior, está escrita de la siguiente manera:

= - PMT (rate; length; present_value; [future_value]; [type])

El signo menos delante de PMT es necesario, ya que la fórmula devuelve un número negativo. Los primeros tres argumentos son la tasa del préstamo, la duración del préstamo (número de períodos) y el capital prestado. Los últimos dos argumentos son opcionales, el valor residual predeterminado es 0, pagadero por adelantado (por 1) o al final (por 0), también es opcional.

La fórmula de Excel utilizada para calcular el pago mensual del préstamo es:

= - PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Explicación: Para la tasa utilizamos el período de tasa, que es la tasa mensual, luego calculamos el número de períodos (meses aquí 120 por 10 años multiplicados por 12 meses) y finalmente indicamos el capital prestado. Nuestro pago mensual será de $ 1, 161. 88 durante 10 años.

Cálculo de hipotecas para tasas de interés

Hemos visto cómo establecer el cálculo de un pago mensual para una hipoteca. Pero es posible que deseemos establecer un pago mensual máximo que podamos pagar que también muestre la cantidad de años durante los cuales tendríamos que reembolsarlo. Por ese motivo, nos gustaría conocer la tasa de interés anual correspondiente.

Calcular la tasa de interés de un préstamo

Como se muestra en la captura de pantalla anterior, primero calculamos la tasa de período (mensualmente en nuestro caso) y luego la tasa anual. La fórmula utilizada será RATE, como se muestra en la captura de pantalla anterior, está escrita de la siguiente manera:

= RATE (Nper; pmt; valor_presente; [valor_del_verano]; [tipo])

Los primeros tres argumentos son la longitud de el préstamo (número de períodos) y el pago mensual para reembolsar el capital prestado. Los últimos tres argumentos son opcionales y el valor residual predeterminado es 0, el término argumento para administrar el vencimiento por adelantado (para 1) o al final (para 0) también es opcional, y finalmente el argumento de estimación es opcional, pero puede dar una estimación inicial de la tasa.

La fórmula de Excel utilizada para calcular la tasa de préstamo es:

= TASA (12 * B4; -B2; B3) = TASA (12 * 13; -960; 120000)

Nota: los datos correspondientes en el pago mensual debe tener un signo negativo. Es por eso que un signo menos antes de la fórmula. Nuestro período de tarifa es 0. 294%.

Usamos la fórmula = (1 + B5) es 12-1 ^ = (1 + 0. 294%) ^ 12-1 para obtener la tasa anual de nuestro préstamo en 3. 58 % En otras palabras, para tomar prestados 120,000 $ durante 13 años para pagar mensualmente 960 $ debemos negociar un préstamo a una tasa máxima anual de 3. 58%.

Cálculo de hipotecas para la duración de un préstamo

Ahora veremos cómo obtener la duración de un préstamo cuando conozca la tasa anual, el capital prestado y el pago mensual que se reembolsará. En otras palabras, ¿cuánto tiempo tendremos que pagar una hipoteca de $ 120,000 con una tasa de 3. 10% y un pago mensual de $ 1, 100?

Número de reembolsos de un préstamo

La fórmula que usaremos es NPER, como se muestra en la captura de pantalla anterior, y está escrita de la siguiente manera:

= NPER (rate; pmt; present_value; [future_value]; [tipo])

Los tres primeros argumentos son la tasa anual del préstamo, el pago mensual necesario para pagar el préstamo y el capital prestado. Los dos últimos argumentos son opcionales, el valor residual predeterminado es 0, el término argumento pagadero por adelantado (para 1) o al final (para 0) también es opcional.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Nota: los datos correspondientes en el pago mensual deben tener un signo negativo. Es por eso que tenemos un signo menos antes de la fórmula. La duración del reembolso es de 127,97 períodos (meses en nuestro caso).

Usaremos la fórmula = B5 / 12 = 127. 97/12 por el número de años para completar el reembolso del préstamo. En otras palabras, para tomar prestados $ 120,000, con una tasa anual de 3. 10% y pagar $ 1, 100 mensuales, debemos pagar los vencimientos por 128 meses o 10 años y 8 meses.

Descomposición del préstamo

El pago de un préstamo consiste en dos cosas, el principal y el interés. El interés se calcula para cada período, por ejemplo, los reembolsos mensuales a lo largo de 10 años, nos darán 120 períodos.

La captura de pantalla anterior muestra el desglose de un préstamo (un período total igual a 120), utilizando las fórmulas PPMT e IPMT. Los argumentos de las dos fórmulas son los mismos y se desglosan de la siguiente manera:

= - PPMT (tasa; num_período; longitud; principal; [residual]; [terme])

= - INTPER (tasa; num_period; longitud; principal; [residual]; [terme])

Los argumentos son los mismos que para la fórmula PMT vista en la primera parte, excepto num_period, que se agrega para mostrar el período para romper el préstamo, dando el principal y el interés por ello. Tomemos un ejemplo:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) -1; 1; 10 * 12; 120000)

= - INTPER ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10) %) ^ (1/12) -1; 1; 10 * 12; 120000)

El resultado es el que se muestra en la captura de pantalla "Descomposición del préstamo", durante el período analizado que es "1", por lo que el primer período , o el primer mes.Para este, pagamos $ 1161. 88, desglosado en $ 856, 20 principal y $ 305. 68 interés.

Cálculo de préstamos de Excel

Ahora también es posible calcular el capital y el reembolso de intereses por varios períodos, como los primeros 12 meses o los primeros 15 meses.

= - CUMPRINC (rate; length; principal; start_date; end_date; type)

= - CUMIPMT (rate; length; principal; start_date; end_date; type)

Encontramos los argumentos, rate, length, principal y plazo (que son obligatorios) que ya vimos en la primera parte con la fórmula PMT. Pero aquí, también necesitamos los argumentos start_date y end_date. El primero indica el comienzo del período a analizar y el segundo el final. Tomemos un ejemplo:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

= - CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3 ; 1; 12; 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

El resultado es el uno que se muestra en la captura de pantalla "Cumul 1st year", por lo que los períodos analizados van del 1 al 12, del primer período (primer mes) al duodécimo (12º mes). Más de un año, pagaríamos $ 10 419, 55 Principal y $ 3 522. 99 Intereses.

Amortización del préstamo

Las fórmulas anteriores nos permiten crear nuestro programa período por período, cuánto pagaremos mensualmente en capital e intereses, y cuánto queda por pagar.

Crear un cronograma de préstamos en Excel

Para crear un cronograma de préstamos, usaremos diferentes fórmulas discutidas anteriormente y las ampliaremos sobre el número de períodos.

En la primera columna del período, simplemente ingrese "1" como el primer período, luego arrastre la celda hacia abajo. En nuestro caso, necesitamos 120 períodos desde el pago de un préstamo a 10 años multiplicado por 12 meses = 120.

La segunda columna es el monto mensual que debemos pagar cada mes, que es constante durante todo el cronograma del préstamo. Para calcularlo, REPLACEe la siguiente fórmula en la celda de nuestro primer período:

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12 ) -1; 10 * 12; 120000)

La tercera columna es el principal que se reembolsará mensualmente. Por ejemplo, para el 40 ° período, reembolsaremos $ 945. 51 en capital sobre nuestro monto mensual total de $ 1, 161. 88. Para calcular el monto del capital canjeado estamos usando la siguiente fórmula:

= - PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

La cuarta columna es el interés, para lo cual calculamos el capital reembolsado en nuestra cantidad mensual para descubrir cómo se debe pagar mucho interés, usando la fórmula:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) ; 1; 10 * 12; 120000)

La quinta columna contiene la cantidad restante a pagar. Por ejemplo, después del pago número 40 tendremos que pagar $ 83, 994. 69 en $ 120, 000. La fórmula es la siguiente:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

La fórmula usa una combinación de principal en un período posterior con la celda que contiene el capital prestado. Este período comienza a cambiar cuando copiamos y arrastramos la celda hacia abajo.La captura de pantalla siguiente muestra que al final de 120 períodos nuestro préstamo se amortiza.