Cómo crear un programa de amortización con Excel

 

Las deudas ayudan a que las cosas sucedan: un automóvil nuevo, una educación universitaria o incluso una casa nueva. Sin embargo, debe devolverlo. Puede solicitar un estado de cuenta del préstamo, pero no siempre es fácil de entender. Sin embargo, si puede ver a dónde va su dinero, es más fácil ver la luz al final del préstamo.

Aquí es donde entra en juego el cronograma de amortización. Visualizará mejor el préstamo si comprende cómo funciona su deuda y cómo la afectan sus pagos. Incluso puede ver cómo un pequeño pago adicional puede afectarlo significativamente. Así es como puedes hacer uno en Microsoft Excel.

¿Qué es un programa de amortización?

Según Investopedia , "un calendario de amortización de un préstamo es una tabla completa de los pagos periódicos del préstamo, que muestra la cantidad de capital y la cantidad de interés que comprende cada pago hasta que el préstamo se liquida al final de su plazo".

Entonces, cuando cree un cronograma, verá cuánto paga cada mes y cómo se divide entre el pago de intereses y el monto principal. En función de sus pagos mensuales, también verá cuánto tiempo se tarda en pagar el préstamo. Y, si paga extra, también verá cómo afecta el cronograma general de pagos.

El cronograma de amortización lo ayuda a ver cuánto tiempo lleva pagar su deuda y cómo la afecta cada dólar que invierte para pagarla. También puedes usarlo para planificar compras importantes, así verás cuánto estás pagando cada mes y cuánto ahorrarás si pagas antes.

Cómo calcular los detalles de su amortización en Excel

Para este ejemplo, supongamos que planea comprar un Mustang Mach 1 Premium 2022. Su precio neto estimado es de $68.529, y su pago mensual es de $1.164. Este cálculo se basa en un pago inicial de $6,853 por un plazo de 60 meses y una tasa de porcentaje anual del 5%.

Ingrese los datos en Excel para confirmar si son correctos. Puede determinar sus pagos mensuales, la tasa de interés anual, los meses para pagar el préstamo o el monto total del préstamo utilizando las siguientes fórmulas, respectivamente: PMT, RATE, NPER y NPV. También puede usar estas fórmulas si el banco o el distribuidor no incluyeron un punto de datos (como la tasa de interés).

Entonces, si observa los datos anteriores, PMT = 1,164, RATE = 5%, NPER = 60 y PV = 61,676 (precio neto estimado menos el pago inicial). Pero si tiene datos incompletos, así es como puede calcularlos.

Pagos mensuales

Para calcular los pagos mensuales, use la función PAGO:

=-PMT(RATE,NPER,PV,[FV],[TYPE])

Solo recuerde dividir la TASA por 12, ya que estamos calculando los pagos mensuales.

Tasa de interés

Si necesita determinar la tasa de interés, use la función TASA:

=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS])

Esta fórmula te dará la tarifa mensual, así que no olvides multiplicar el resultado final por 12.

Periodo de pago

Cuando no está seguro de cuántos meses va a pagar un préstamo, pero sabe cuánto está pidiendo prestado y cuánto puede pagar cada mes, puede usar la función NPER:

=NPER(RATE,-PMT,PV,[FV],[TYPE])

Nuevamente, divida la TASA por 12 ya que está calculando la cantidad de meses que pagará un préstamo.

Monto total del préstamo

Finalmente, si desea saber cuánto puede pedir prestado dada una tasa de interés específica, la cantidad que puede pagar mensualmente y la cantidad de meses que está dispuesto a pagar el préstamo, use la función PV:

=PV(RATE,NPER,-PMT,[FV],[TYPE]

TASA siempre debe dividirse por 12 cuando su NPER se basa en meses; de lo contrario, obtendrá un valor severamente desinflado.

Ahora que sabe cómo encontrar cualquier valor faltante, es hora de crear la tabla de amortización.

Creación de una tabla de amortización de préstamos con tasa de interés fija

Primero, debe agregar los detalles de su préstamo. Esto incluye el monto total, el pago inicial, el monto del préstamo, la tasa de interés, el período del préstamo y el pago mensual. Para asegurarse de tener los datos correctos, puede usar la fórmula PMT anterior, especialmente si desea experimentar con el período del préstamo o el monto del préstamo.

Alternativamente, puede usar las otras fórmulas mencionadas si desea jugar con las cifras de la amortización mensual, la tasa de interés o el monto total del préstamo.

Después de agregar sus datos, cree la tabla de amortización del préstamo. Primero, agregue las siguientes columnas debajo de sus datos iniciales: Período , Saldo inicial , Pago mensual , Pago de principal , Pago de intereses , Principal acumulativo , Interés acumulativo , Saldo final , Tasa de interés y Pagos de suma global .

La primera fila de su tabla de calendario de amortización

Una vez que haya creado las columnas, complete la primera fila (fila 9) en Período con Mes 1 . Después de hacerlo, use la funcionalidad de autocompletar de Excel para completar rápidamente la columna Período. Seleccione la celda y coloque el cursor en el pequeño cuadro verde en la esquina inferior derecha de la celda seleccionada, donde el cursor debe convertirse en una pequeña cruz negra.

Cuando el cursor se transforme, haz clic en el pequeño cuadro verde y luego arrastra el mouse hacia abajo. Aparecerá un pequeño indicador junto al mouse, indicando la cantidad de meses que Excel aplicará a la columna. Deténgase y suelte el botón del mouse cuando llegue al Mes 60 .

Después de completar la columna Período, es hora de completar el resto de la primera fila. En Saldo inicial , escriba =$B$3 para hacer referencia a la celda donde colocó el Monto del préstamo . No olvide agregar un signo de $ delante de la letra de la columna y el número de la fila para asegurarse de que no cambie al completar automáticamente el resto de la tabla.

• En Pago mensual , escriba:

=$B$6

Esto copia la tasa de interés que ingresó con sus datos iniciales. No olvide agregar el signo $, para que no cambie cuando complete automáticamente su tabla.

En Principal , use la siguiente fórmula:

=C9-E9

Esto resta el pago de intereses del pago mensual.

• En Interés , calcule la cantidad con la siguiente fórmula

=B9*(I9/12)

Esto multiplica el pago mensual con la tasa de interés mensual.

• Para Principal acumulativo , escriba:

=D9

En Interés acumulativo, escriba:

=E9

En Tasa de interés , escriba:

=B4

Esto copia la tasa de interés que ha calculado previamente. Sin embargo, si su banco utiliza tasas de interés variables, puede cambiar esto más adelante.

• En Saldo final , utilice la fórmula:

=B9-D9

Esto elimina el monto principal que pagó de su saldo inicial.

• Mantenga los Pagos de suma global en blanco a menos que planee pagar una cantidad adicional en este período.

Completar la Tabla de Amortización del Préstamo

Ahora que ha completado la primera fila, es hora de agregar el resto de la tabla a partir de la segunda fila (fila 10).

• En Saldo inicial , escriba la fórmula siguiente:

=H9-I9

• Esto copia el saldo final que pagó anteriormente, más cualquier pago anticipado que haya realizado.

• Para las columnas Pago mensual , Capital , Interés y Saldo final , copie la fórmula de la fila anterior.

• En Principal acumulativo , escriba lo siguiente:

=F9+D10

Esto suma el monto de capital total que pagó en el último período al pago de capital que realizó en este período.

• En Interés acumulativo , escriba lo siguiente:

=G9+E10

Esto suma el monto total de intereses que pagó en el último período al pago de intereses que realizó en este período.

• Mantenga los Pagos de suma global en blanco a menos que planee pagar una cantidad adicional en este período.

Una vez que haya ingresado las fórmulas anteriores, seleccione los datos de la fila 10, desde el Saldo inicial hasta la columna Pagos de suma global. Después de seleccionar las celdas, presione Ctrl + C para copiarlas.

Seleccione la celda bajo Saldo inicial del mes 3 (fila 11). Después de hacerlo, desplácese hacia abajo hasta la fila del mes 60 (fila 68), presione Shift en su teclado y luego seleccione la celda debajo de la columna Lump Sum Payments (celda I68).

Cuando complete la selección, presione Ctrl + V para insertar las fórmulas que creó; luego verá toda su tabla de amortización llena con la información necesaria.

Luego puede enviar esto a su socio si está haciendo una compra importante para ayudarlo a tomar una decisión. Incluso si usan Hojas de cálculo de Google, pueden importar el archivo de Excel a Hojas de cálculo para ver sus cálculos.

Experimente con sus datos

Puede cambiar sus datos para ver cómo afectará su calendario de pagos. Por ejemplo, si cree que puede realizar pagos adicionales esporádicos (como el pago de una bonificación anual de la empresa), agregue su monto adicional estimado en el período correcto para Pagos de suma global.

También puede cambiar la cantidad de meses que desea realizar los pagos. Por ejemplo, si desea pagar su préstamo en 48 meses en lugar de 60, simplemente cambie la cantidad de meses en sus datos iniciales y su tabla se ajustará para mostrar cuánto debe pagar cada mes.

Una vez que su saldo inicial cae por debajo de cero, ¡eso significa que su préstamo está pagado en su totalidad! Solo tenga en cuenta que esta tabla solo funciona para préstamos de tasa fija. Si su préstamo tiene una tasa variable, tendrá que utilizar un enfoque diferente.