Cómo calcular y comparar la función de pago de préstamos y su implementación en EXCEL-Excel Learning Network

Cuando comparas las opciones de pago de una hipoteca, tus opciones pueden ser abrumadoras. ...

Cuando estaba en St Ives encontré mi casa,

Luego miré siete tipos de préstamos.

Pero cada préstamo tiene siete planes.

Cada programa tiene siete semestres.

Siete por semestre;

Con estas tarifas, plazos, planes y préstamos,

Cambié de opinión y alquilé.

¿Charlie? Charlie Kidd, 1982

Una forma de reducir esta confusión es analizar sus opciones de pago en Excel. Recientemente descubrí la hoja de trabajo que creé cuando compré mi casa actual. Lo arreglé un poco y actualicé los datos. Verás una imagen a continuación.

Mi análisis inicial de préstamos

Calculadora de pagos de préstamos Inicialmente, configuré esta calculadora de préstamos para comparar los dos elementos que más me importaban: pagos mensuales y pagos de interés total.

Introduzco el monto del préstamo en la celda C3 y le asigno el nombre LoanAmt a la celda. Para hacer esto, seleccioné el rango B3:C3 y presioné Ctrl+Shift+F3 para comenzar. ¿Crear un nombre? cuadro de diálogo, ¿asegúrese solo? ¿Columna izquierda? y luego seleccionar? ¿está seguro? .

Como quería que la columna C fuera más estrecha que el ancho de la pantalla con una gran cantidad de columnas, fusioné las celdas C3 y D3. Para hacer esto, seleccioné el rango C3:D3 y presioné Ctrl+1 para comenzar. ¿Formatear celdas? Cuadro de diálogo, ¿en? ¿Alineación? pestaña, seleccionar? ¿Fusionar celdas? y luego seleccionar? ¿está seguro? .

Para encontrar el monto del primer pago, utilicé la siguiente fórmula en la celda que se muestra:

C5: =-PMT($B5/12,$A5*12, LoanAmt)

¿La sintaxis de la función PMT es la siguiente?

= PMT (tasa, nper, pv, fv, tipo)

¿Entonces en la celda C5?

Tasa es el tipo de interés mensual que se obtiene dividiendo el tipo de interés anual de la celda B5 por 12.

Nper es el número de años de la celda A5 multiplicado por 12 para obtener el periodo de amortización mensual.

Pv es el monto del préstamo.

Los otros dos parámetros son opcionales y se pueden ignorar.

Para calcular el interés total pagado, utilicé la siguiente fórmula en la celda que se muestra:

D5:= $C5*($A5*12)-Monto del préstamo

p>

Aquí, el interés total pagado es igual al pago total durante la vigencia del préstamo menos el monto del préstamo.

Luego, copié el rango C5:D5 en la celda C6.

Mi análisis de préstamos de indulgencia

Mientras buscaba más opciones hipotecarias, me encontré comparando muchos resultados diferentes. Entonces, en lugar de escribirlo, agregué cuatro tablas a la hoja de trabajo.

La forma de pago mensual se muestra en la primera tabla. Aquí ingresé varias tasas de interés en la fila 11 y varios períodos de amortización en la columna a.

Hice hincapié en que el valor medio es la elección principal y los demás valores son opciones consideradas.

Esta es la fórmula para la celda que se muestra:

B 12:=-PMT(B $11/12, $A12 * 12, LoanAmt)

Esta La fórmula utiliza la misma lógica que la fórmula PMT anterior. Copié al rango B12:F16.

Interés total pagado A continuación, quería comparar las opciones de pago observando el interés total pagado por cada opción, como se muestra a continuación.

Para hacer esto, quiero desarrollar la capacidad de probar mi hipótesis sobre cuánto durará una casa, que es diferente del período de amortización.

Por lo tanto, la celda de vida anual del préstamo M9 contiene el número supuesto de años. Pero comencé con el valor de 1999, lo que me permite ignorar el plazo del préstamo si es necesario. (Puedes ver cómo funciona esto en la siguiente fórmula.

)

Esta es la fórmula para la celda que se muestra:

I 12:=-CUMIPMT(I$11/12,$H12*12,LoanAmt,1,MIN($M $9 , $H12)*12,0)

La función CUMIPMT calcula el interés acumulado para el período especificado. Esta es su sintaxis:

= CUMIPMT(tasa, nper, pv, periodo inicial, periodo final, tipo)

Entonces en la celda I12,...

La tasa es la tasa de interés anual en la celda I11 dividida por 12.

Nper es el número de años en la celda H12 multiplicado por 12.

Pv es LoanAmt.

Start_period es 1 porque estoy mirando la hora de inicio de cada préstamo.

End_period es igual al plazo del préstamo o al plazo de amortización (el que sea menor) multiplicado por 12.

El tipo es igual a 0, Excel nos dice que esta es la configuración para el cálculo final.

Pago total de intereses en 15 años

Copié esta fórmula en el intervalo I 12: M 16 y luego cambié el período del préstamo a 15 años para realizar pruebas, como se muestra a continuación.

Como puede ver, en comparación con el gráfico anterior, la diferencia en las tasas de interés durante los períodos de amortización de 25 y 30 años no es tan significativa. Pero aun así, si quisiera liquidar el préstamo en 30 años y liquidarlo en 15 años, esta opción me costaría entre 76.169 y 115.806 dólares, o casi 40.000 dólares.

Finalmente, en mi análisis, quería ver en qué se diferenciarían mis pagos mensuales a medida que cambiaran la tasa de interés y el período de amortización. Entonces, configuré las siguientes dos tablas:

Diferencia entre el interés del préstamo y la opción de plazo

Por ejemplo, en la primera tabla, si tengo un préstamo a 15 años, si pudiera Si redujera mi tasa de interés del 3,125% al ​​3%, mis pagos se reducirían en aproximadamente $18 por mes.

En la segunda tabla, si la tasa de interés de mi préstamo es del 3%, entonces si puedo extender el plazo de mi préstamo de 15 a 30 años, mi pago se reducirá en $807.

Las fórmulas de estas tablas solo aparecen tituladas? ¿Opciones de pago mensual? Diferencias entre unidades en la tabla.

Advertencia final... no confíe únicamente en esta hoja de trabajo para seleccionar un préstamo específico. Es posible que sus cálculos y suposiciones no sean coherentes con su situación específica. Así que asegúrese de verificar los supuestos clave y las opciones de pago del libro de trabajo con su prestamista o asesor financiero.