Cómo crear su propia calculadora de amortización de préstamos |

La mayoría de las personas piensan que hay algún tipo de magia vudú que se usa para crear un préstamo plan de amortización . Pero me complace decirte que no hay, e incluso mejor, incluso un principiante puede crear uno fácilmente usando Microsoft Excel.

Una tabla de amortización de préstamos le proporciona mucha información buena: la cantidad total pagadera al prestamista, la porción que es el pago de intereses, la porción que es el pago principal y el saldo pendiente del préstamo.

Cuando se amortiza un préstamo, simplemente significa que el pago de intereses y el pago del principal se combinan en un pago periódico y los montos de pago son iguales para cada período de pago. Pero una de las características más interesantes de un préstamo amortizado es que con cada pago cambia la cantidad de intereses y el principal que se paga.

Excel es una herramienta extremadamente útil para calcular su propia tabla de amortización. Incluso puede experimentar con las diferentes entradas para ver cómo las diferentes tasas de interés, los plazos de amortización y los montos principales del préstamo afectan los pagos del préstamo que realizará.

Veamos un ejemplo para ilustrar el proceso.

Supongamos que acaba de comprar un automóvil con un préstamo de $ 20,000 que debe ser reembolsado en 4 años. La tasa de interés anual del préstamo es del 3%. Dado que el préstamo se amortiza, los pagos mensuales serán todos del mismo importe.

El primer paso es calcular el monto del pago mensual. Esto se puede hacer utilizando la función PMT (que significa "pago") en Excel.

Comience por crear una nueva hoja de cálculo e ingrese los siguientes datos necesarios para el cálculo del monto de pago.

La columna A contendrá las siguientes etiquetas para nuestros datos:

A1 = el principal del préstamo original (monto del préstamo)

A2 = el plazo del préstamo en años

A3 = la tasa de interés anual

A4 = la cantidad de pagos por año

A5 = el monto del pago (lo que estamos calculando)

La columna B lo hará contienen los valores correspondientes para cada etiqueta en la columna A:

B1 = $ 20,000

B2 = 4

B3 = 3%

B4 = 12

B5 = el valor que estamos buscando

La función PMT de Excel se puede encontrar en el menú de funciones financieras. Primero seleccione la celda B5, luego haga clic en el menú de funciones financieras debajo de la pestaña de fórmulas y seleccione la función PMT de la lista. Aparecerá un cuadro en el que debe ingresar los datos de la hoja de cálculo tal como está en esta imagen:

Aquí hay algunas cosas importantes que debe tener en cuenta. Primero, debemos hacer dos ajustes importantes, porque estamos calculando pagos mensuales. Necesitamos dividir nuestra tasa anual por 12 para obtener la tasa de interés mensual (Tasa), y tenemos que multiplicar 4 por 12 para obtener la cantidad total de meses en nuestro período de amortización (Nper).

Segundo, el valor presente (PV) se ingresa como un valor negativo porque ese monto se debe al prestamista.

Una vez que se completen sus casillas, presione "OK".

Su hoja de cálculo ahora debe verse así:

Ahora comenzaremos a construir la tabla de amortización.

Salte una fila debajo de la hoja de cálculo hecha en la parte 1 y, comenzando en la fila 7, haga lo siguiente etiquetas que comienzan en la columna A y terminan en la columna E:

A7 = Mes

B7 = Pago

C7 = Interés

D7 = Principal

E7 = Balance

Estos son los cinco encabezados para la tabla de amortización.

Ahora es el momento de comenzar a completar la tabla. El primer paso es ingresar el número de meses en la vida del préstamo. Debajo del encabezado "mes", escriba "0" en la celda A8 (el momento en que se emitió el préstamo). Complete las celdas siguientes en orden secuencial hasta el número 48 (que lo colocará en la celda A56). Un truco: una vez que haya ingresado 0, asegúrese de que la celda esté seleccionada (A8), luego arrastre la esquina inferior derecha de la celda hacia abajo a la línea 56. Luego seleccione "llenar serie" de la pequeña casilla que aparece a la derecha de la última célula Esto debería rellenar automáticamente la serie numérica durante los 48 meses.

A continuación, se pueden completar todos los pagos porque el valor de cada pago mensual ya se ha determinado. A partir del mes 1 (no hay pago en el mes 0 porque los pagos se realizan al final de cada mes) complete el valor de $ 442.69 por cada mes 1-48. Después de esto, los montos del interés y del capital pueden calcularse utilizando sus respectivas funciones de Excel.

Calcular el interés y los componentes principales de los pagos utilizando la función Excel es simple y se puede usar para completar toda la tabla de amortización. Para calcular el pago de intereses, seleccione la celda C9 y luego haga clic en el menú de funciones financieras en la pestaña de fórmulas. Seleccione la función IPMT (para "pago de intereses") de la lista. Aparecerá un cuadro en el que debe ingresar los datos de la hoja de cálculo tal como está en esta imagen:

Nota: los signos "$" entre las letras y números de la celda son necesarios para cuando copiamos las fórmulas hasta las celdas restantes más tarde. El "$" bloquea la celda referida, por lo que las fórmulas permanecen intactas cuando las copiamos a las celdas restantes. Puede bloquear las celdas (y ver el "$") presionando F4.

A continuación, podemos calcular la cantidad de principal en el pago. De forma similar al cálculo de IPMT, seleccione la celda D9 y luego haga clic en el menú de funciones financieras debajo de la pestaña de fórmulas. Seleccione la función PPMT (para "pago principal") de la lista. Aparecerá un recuadro donde debe ingresar los datos de la hoja de cálculo tal como está en esta imagen:

Nota: el principal podría se calcula utilizando esta función o simplemente restando el monto del interés del monto total del pago. De la misma manera, el interés podría calcularse si calculáramos el principal primero usando la función PPMT y luego restaría ese valor del pago total. Sin embargo, es bueno para saber cómo usar ambas fórmulas, que también permite que el resto de la tabla se complete de manera más eficiente, como verá a continuación.

La columna final (Columna E) es la columna de saldo restante. Esto se puede calcular fácilmente para cada período ahora que hemos determinado los montos de interés y principal. Para el mes 0, aún no se han realizado pagos, por lo que el saldo es de $ 20,000. Sin embargo, queremos ordenarle a Excel que calcule automáticamente el saldo en cada período, así que en lugar de Manu al ingresar "$ 20,000", ingrese "= B1", refiriéndose a la tabla anterior para el cálculo del pago. Para el mes 1, dado que el saldo se calcula restando el saldo del mes anterior al pago del principal del mes actual, ingrese "= E8-D9" en la celda E9. El saldo se calculará automáticamente. Su tabla de amortización ahora está completa para el mes 1 y debería verse exactamente así:

Todos los datos de los meses restantes se pueden completar simplemente seleccionando las celdas B9-E9, copiando la selección, y luego pegando la selección en celdas B10-E10. Como hemos ingresado las fórmulas para cada celda y bloqueado nuestras celdas que se usarán en cada cálculo, los valores de interés, principal y saldo se calculan automáticamente. Al ver que funciona para el mes 2, se puede completar toda la tabla simplemente seleccionando las columnas B-E hasta el mes 48 y pegando la información ya copiada. Excel ha calculado todos los valores de la tabla de amortización utilizando las fórmulas que ingresamos. La tabla completa debe tener el siguiente aspecto:

Primer año de préstamo:

Último año de préstamo:

Nota: estas tablas solo contienen el primer y el último año del préstamo. tabla de amortización Los años 2 y 3 estarían entre estas dos tablas formando una tabla de amortización continua para los 48 meses.

Si está interesado en calcular los pagos de la hipoteca para una hipoteca de tasa fija, puede hacer una tabla de amortización usando estos pasos, o puede usar nuestra calculadora financiera para hacerlo por usted. Nuestra calculadora de hipotecas le mostrará su pago mensual y un plan de amortización completo.