Programar reembolsos de préstamos con fórmulas de Excel
Tabla de contenido
Expandir
- Entender su hipoteca
- Calcule el pago mensual
- Calcule la tasa de interés anual
- Determinar la duración de un préstamo
- Descomposición del préstamo
- Cálculo de préstamos en Excel
- Amortización del Préstamo
- Creación de un cronograma de préstamos
El reembolso del préstamo es el acto de devolver el dinero previamente prestado de un prestamista, generalmente a través de una serie de pagos periódicos que incluyen el capital más los intereses. ¿Sabía que puede utilizar el programa de software Excel para calcular los reembolsos de su préstamo?
Este artículo es una guía paso a paso para configurar los cálculos de préstamos.
Conclusiones clave:
- Use Excel para controlar su hipoteca determinando su pago mensual, su tasa de interés y su cronograma de préstamos.
- Puede analizar más en profundidad el desglose de un préstamo con Excel y crear un calendario de pagos que se adapte a sus necesidades.
- Hay cálculos disponibles para cada paso que puede modificar 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.
Entender su hipoteca
Con Excel, puede comprender mejor su hipoteca en tres sencillos 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 construir una tabla en Excel que le dirá la tasa de interés, el cálculo del préstamo durante 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 capital y la duración, podemos determinar la cantidad a 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; pagadero 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:
= PAGO ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PAGO ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Explicación: Para la tasa, usamos la tasa mensual (período de tasa), luego calculamos el número de períodos (120 para 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 de una hipoteca. Pero es posible que deseemos establecer un pago mensual máximo que podamos pagar y que también muestre la cantidad de años durante los cuales tendríamos que reembolsar el préstamo. Por ello, nos gustaría conocer 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á TASA, como se muestra en la captura de pantalla anterior. Está escrito de la siguiente manera:
= TASA (Nper; pago; valor_presente; [valor_futuro]; [tipo])
Los primeros tres argumentos son la duración del préstamo (número de períodos), el pago mensual para reembolsar el préstamo y el capital prestado. Los últimos tres argumentos son opcionales y el valor residual predeterminado 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 activa es:
= TASA (12 * B4; -B2; B3) = TASA (12 * 13; -960; 120000)
Nota: los datos correspondientes en el pago mensual deben tener signo negativo. Es por eso que hay un signo menos antes de la fórmula. El período de la tasa es del 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 mensuales, 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 calendario de pago que minimice las tarifas que podría terminar debiendo.
Determinar la duración de un préstamo
Ahora veremos cómo determinar la duración de un préstamo cuando conoce la tasa anual, el capital prestado y el pago mensual que debe reembolsarse. En otras palabras, ¿cuánto tiempo necesitaremos para 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; pago; valor_presente; [valor_futuro]; [tipo])
Los primeros tres argumentos son la tasa anual del préstamo, el pago mensual necesario para reembolsar el préstamo y el capital prestado. Los dos últimos argumentos son opcionales, el valor residual predeterminado 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)
Signo menos antes de la fórmula
Los datos correspondientes en el pago mensual deben ir con 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 para el número de años para completar el pago del préstamo. En otras palabras, para pedir prestado $ 120,000, con una tasa anual de 3,10% y pagar $ 1,100 mensuales, debemos reembolsar vencimientos a 128 meses o 10 años y ocho meses.
Descomposición del 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 reembolsos 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 los mismos y se desglosan de la siguiente manera:
= -PPMT (tasa; núm_periodo; 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 se desglosa el préstamo dado el capital y los intereses. He aquí 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éstamos» durante el período analizado, que es «uno»; es decir, el primer período o el primer mes. Pagamos $ 1,161.88 desglosados en $ 856.20 de capital y $ 305.68 de interés.
Cálculo de préstamos en Excel
También es posible calcular el reembolso de principal e 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 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». La «fecha_inicio» indica el comienzo del período a analizar y la «fecha_finalización» indica el final del período a analizar.
He aquí un ejemplo:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
El resultado se muestra en la captura de pantalla «Cumul 1er año», por lo que los períodos analizados van del 1 al 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 cronograma período por período, saber cuánto pagaremos mensualmente en principal e intereses, y saber cuánto queda por pagar.
Creación de un cronograma de préstamos
Para crear un cronograma de préstamos, usaremos las diferentes fórmulas discutidas anteriormente y las expandiremos 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 es igual a 120.
La segunda columna es la cantidad mensual que debemos pagar cada mes, que es constante durante todo el cronograma de préstamos. Para calcular la cantidad, inserte la siguiente fórmula en la celda de nuestro primer período:
= -PMT (TP; 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 cuadragésimo período, reembolsaremos $ 945.51 de capital sobre nuestro monto total mensual de $ 1,161.88.
Para calcular el monto principal reembolsado, 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 nuestro monto mensual para descubrir cuánto interés se debe 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 40º pago, tendremos que pagar $ 83.994,69 sobre $ 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 siguiente tabla muestra que al final de 120 períodos, nuestro préstamo está reembolsado.