Programar reembolsos de préstamos con fórmulas de Excel
El reembolso del préstamo es el acto de devolver dinero prestado previamente de un prestamista, generalmente a través de una serie de pagos periódicos que incluyen capital más intereses. ¿Sabía que puede usar el programa de software Excel para calcular los pagos de su préstamo?
Este artículo es una guía paso a paso para configurar los cálculos de préstamos.
Para llevar clave
- Use Excel para controlar su hipoteca mediante la determinación de su pago mensual, su tasa de interés y su calendario de préstamos.
- Puede echar un vistazo más en profundidad al desglose de un préstamo con Excel y crear un cronograma de pago que funcione para usted.
- Hay cálculos disponibles para cada paso que puede ajustar para satisfacer sus necesidades específicas.
- Desglosar y examinar su préstamo paso a paso puede hacer que el proceso de pago se sienta menos abrumador y más manejable.
Entendiendo su hipoteca
Con Excel, puede obtener una mejor comprensión de su hipoteca en tres simples pasos. El primer paso determina el pago mensual. El segundo paso calcula la tasa de interés y el tercer paso determina el cronograma del préstamo.
Puede crear una tabla en Excel que le dirá la tasa de interés, el cálculo del préstamo para la duración del préstamo, la descomposición del préstamo, la amortización y el pago mensual.
Calcule el pago mensual
Primero, aquí se explica cómo calcular el pago mensual de una hipoteca. Usando la tasa de interés anual, el principal y la duración, podemos determinar la cantidad que se pagará mensualmente.
La fórmula, como se muestra en la captura de pantalla anterior, se escribe de la siguiente manera:
= -PMT (tasa; longitud; valor presente; [valor_ futuro]; [tipo])
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 principal prestado. Los dos últimos argumentos son opcionales, el valor residual por defecto es cero; pagable por adelantado (para uno) o al final (para cero), 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 la tasa mensual (período de tasa), luego calculamos el número de períodos (120 por 10 años multiplicado por 12 meses) y, finalmente, indicamos el principal prestado. Nuestro pago mensual será de $ 1, 161.88 durante 10 años.
Calcule la tasa de interés anual
Hemos visto cómo configurar 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 pagar el préstamo. Por esa razón, nos gustaría saber la tasa de interés anual correspondiente.
Como se muestra en la captura de pantalla anterior, primero calculamos la tasa del período (mensual, en nuestro caso), y luego la tasa anual. La fórmula utilizada será RATE, como se muestra en la captura de pantalla anterior. Está escrito de la siguiente manera:
= RATE (Nper; pmt; present_value; [future_value]; [type])
Los primeros tres argumentos son la duración del préstamo (número de períodos), el pago mensual para pagar el préstamo y el principal prestado. Los últimos tres argumentos son opcionales, y el valor residual por defecto es cero; El término argumento para gestionar el vencimiento por adelantado (para uno) o al final (para cero) también es opcional. 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 interés es:
= TASA (12 * B4; -B2; B3) = TASA (12 * 13; -960; 120000)
Nota: los datos correspondientes en el pago mensual deben tener un signo negativo. Es por eso que hay un signo menos antes de la fórmula. El período de la tasa es de 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, que es 3.58%. En otras palabras, para pedir prestados $ 120, 000 durante 13 años para pagar $ 960 mensualmente, debemos negociar un préstamo a una tasa máxima anual de 3.58%.
El uso de Excel es una excelente manera de realizar un seguimiento de lo que debe y elaborar un cronograma de reembolso que minimice cualquier tarifa que pueda terminar debiendo.
Determinar la duración de un préstamo
Ahora veremos cómo determinar la duración de un préstamo cuando conozca la tasa anual, el capital prestado y el pago mensual que debe pagarse. En otras palabras, ¿cuánto tiempo tendremos que pagar una hipoteca de $ 120, 000 con una tasa del 3.10% y un pago mensual de $ 1, 100 ">
La fórmula que usaremos es NPER, como se muestra en la captura de pantalla anterior, y está escrita de la siguiente manera:
= NPER (tasa; pmt; valor_actual; [valor_deterior]; [tipo])
Los primeros tres argumentos son la tasa anual del préstamo, el pago mensual necesario para pagar el préstamo y el principal prestado. Los dos últimos argumentos son opcionales, el valor residual por defecto es cero. El término argumento pagadero por adelantado (para uno) o al final (para cero) 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 pedir prestados $ 120, 000, con una tasa anual de 3.10% y pagar $ 1, 100 mensualmente, debemos pagar los vencimientos por 128 meses o 10 años y 8 meses.
Descomponiendo el Préstamo
El pago de un préstamo se compone de capital e intereses. El interés se calcula para cada período; por ejemplo, los pagos mensuales durante 10 años nos darán 120 períodos.
La tabla 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 iguales y se desglosan de la siguiente manera:
= -PPMT (tasa; número_período; longitud; principal; [residual]; [término])
Los argumentos son los mismos que para la fórmula PMT ya vista, excepto por "num_period", que se agrega para mostrar el período durante el cual desglosar el préstamo dado el capital y los intereses. Aquí hay un ejemplo:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)
El resultado se muestra en la captura de pantalla anterior "Descomposición de préstamo" durante el período analizado, que es "uno"; es decir, el primer período o el primer mes. Pagamos $ 1, 161.88 desglosado en $ 856.20 principal y $ 305.68 intereses.
Cálculo de préstamos en Excel
También es posible calcular el reembolso del principal y los intereses para varios períodos, como los primeros 12 meses o los primeros 15 meses.
= -CUMPRINC (tasa; longitud; principal; fecha_inicio; fecha_final; tipo)
Encontramos los argumentos, tasa, longitud, principal y término (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". La "fecha de inicio" indica el comienzo del período a analizar, y la "fecha de finalización" indica el final del período a analizar.
Aquí hay un ejemplo:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
El resultado se muestra en la captura de pantalla "Cumul 1st year", por lo que los períodos analizados varían de uno a 12 del primer período (primer mes) al duodécimo (12 ° mes). Durante un año, pagaríamos $ 10, 419.55 en capital y $ 3, 522.99 en intereses.
Amortización del Préstamo
Las fórmulas anteriores nos permiten crear nuestro horario período por período, para saber cuánto pagaremos mensualmente en capital e intereses, y para saber cuánto nos queda por pagar.
Crear un cronograma de préstamos
Para crear un cronograma de préstamos, utilizaremos las diferentes fórmulas discutidas anteriormente y las ampliaremos a lo largo del número de períodos.
En la columna del primer período, ingrese "1" como el primer período y luego arrastre la celda hacia abajo. En nuestro caso, necesitamos 120 períodos ya que un pago de préstamo a 10 años multiplicado por 12 meses equivale a 120.
La segunda columna es el monto mensual que debemos pagar cada mes, que es constante durante todo el cronograma del préstamo. Para calcular la cantidad, inserte 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 pagará mensualmente. Por ejemplo, para el período 40, pagaremos $ 945.51 en capital sobre nuestra cantidad total mensual de $ 1, 161.88.
Para calcular el monto del principal canjeado, utilizamos 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 el cual usamos la fórmula para calcular el capital reembolsado en nuestra cantidad mensual para descubrir cuánto interés hay que pagar:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
La quinta columna contiene la cantidad que queda por pagar. Por ejemplo, después del pago número 40, tendremos que pagar $ 83, 994.69 por $ 120, 000.
La fórmula es la siguiente:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
La fórmula utiliza una combinación de principal en un período anterior a la celda que contiene el principal prestado. Este período comienza a cambiar cuando copiamos y arrastramos la celda hacia abajo. La tabla a continuación muestra que al final de los 120 períodos nuestro préstamo es reembolsado.