4 Fórmulas de Excel para crear una tabla de amortización

Algunas veces queremos comprar algo por medio de un préstamo y normalmente nos dan los datos del plazo, el monto a financiar y la cuota. Pero no nos detenemos a pensar en los intereses que finalmente pagamos. Este artículo describe cómo hacer utilizar 4 fórmulas de Excel para crear una tabla de amortización.

Antes de empezar

Para crear una tabla de amortización ocupamos contar con información básica:

Monto del préstamo
Tasa de interés anual
Plazo del préstamo (normalmente en años)
Cantidad de pagos al año

¿Qué vamos a obtener?

El monto de la cuota para cada pago
El desglose de ese pago en cuánto de ese pago corresponde a los intereses y cuánto va disminuir la deuda (amortización).
Cantidad de intereses acumulados pagados por periodo.

Ingresando los datos iniciales

Para realizar el ejercicio, vamos a suponer que el préstamo es por $200,000 (celda C2) a una tasa de interés anual del 6.5 % (celda C3), a un plazo de 5 años (celda C5) y con pagos mensuales (celda C6).

Esto nos dice que se harán 60 pagos (12 pagos por año, por 5 años 12×5 = 60) y que la tasa de interés por periodo de pago es 0.54% (celda C4, 6.5% / 12 = 0.54 %).

Por una cuestión de orden, se resaltan con fondo gris las celdas en que se digita información y se dejan tal cual las celdas calculadas con fórmula.

Definiendo los parámetros para las funciones

Normalmente, cuando abrimos una hoja de cálculo y revisamos las fórmulas utilizadas, vemos las referencias a las celdas utilizando F2 para comprender su funcionamiento. Sin embargo, una forma de facilitar su comprensión es definir nombres (significativos) para las celdas y así utilizar esos nombres en los parámetros de las funciones, facilitando su comprensión.

Hay varias formas de asignar un nombre a una celda de Excel.

De esta forma se definen los siguientes nombres:

C2 = Principal
C4 = TasaInteres
C7 = Periodos

Columnas de la tabla de amortización

La tabla de amortización contendrá el detalle de cada uno de los 60 pagos que se harán al préstamo, desglosando los intereres y el pago al principal realizado en cada pago. Para esto se tendrán las siguientes columnas:

Periodo: es un número del 0 al 60 para indicar cada pago a realizar. El periodo 0, solo refleja el saldo del principal.
Cuota: monto total a pagar mensualmente. Siempre es el mismo valor.
Interés: monto de la cuota correspondiente a los intereses de cada periodo.
Amortización: monto de la cuota que reduce el monto de la deuda.
Saldo: valor de la deuda en cada periodo.

Incluyendo las fórmulas

Para completar la tabla de amortización se utilizan 3 fórmulas financieras:

PMT (en inglés) / PAGO (en español): monto a pagar en cada periodo
IPMT (en inglés) / PAGOINT (en español): monto del pago correspondiente a los intereses.
PPMT (en inglés) / PAGOPRIN (en español): monto del pago que reduce el monto de la deuda.

Fórmula de la cuota

En este caso se utiliza la fórmula PMT, porque mi Excel está en inglés, si estuviera en español, la fórmula sería PAGO. Ambas funciones son equivalentes y tienen los mismos parámetros.

Interés (rate): interés del periodo y que ya estaba definido con un nombre que hace referencia a la celda C4.
Núm_Per (Nper): cantidad de pagos (periodos) del préstamo.
Va (Pv): valor actual o el monto total del préstamo.
Vf (Fv): valor futuro o monto del préstamo luego del último pago. Si se omite, el valor predeterminado es 0.
Tipo (Type): el valor predeterminado es 0 que indica que el pago se realiza al final del periodo y 1 cuando es al inicio.

Fórmulas del interés y la amortización

Las fórmulas para el cálculo de los intereses (IPMT / PAGOINT) y de la amortización (PPMT / PAGOPRIN) tienen los mismos parámetros. A diferencia de la fórmula (PMT / PAGO) tiene un parámetro adicional para indicar el periodo que se está calculando:

Interés (rate): interés del periodo y que ya estaba definido con un nombre que hace referencia a la celda C4.
Período (Per): es el periodo al que se le va a calcular el interés. En este caso hace referencia a la celda A11, pues la columna A tiene el número de cada uno de los períodos del préstamo.
Núm_Per (Nper): cantidad de pagos (periodos) del préstamo.
Va (Pv): valor actual o el monto total del préstamo.
Vf (Fv): valor futuro o monto del préstamo luego del último pago. Si se omite, el valor predeterminado es 0.
Tipo (Type): el valor predeterminado es 0 que indica que el pago se realiza al final del periodo y 1 cuando es al inicio.

De forma similar en la celda D11 se incluye la fórmula PPMT / PAGOPRIN con los mismos parámetros establecidos en la función IPMT / PAGOINT.

Fórmula para el saldo

Para finalizar con las fórmulas, en la columna del saldo, se incluye la fórmula =E10-D11. Que lo que hace es restarle al saldo del préstamo del período anterior, el monto amortizado en el período.

Una vez ingresadas todas las fórmulas, se copian para todos los periodos y tenemos completa la tabla de amortización.

Fórmula del interés acumulado a un período

Algunas veces se desea saber ¿cuánto es el total acumulado de intereses pagados en determinado momento del préstamo?. Para esto existe la función CUMIPMT / PAGO.INT.ENTRE, que calcula la cantidad de intereses pagados entre dos períodos. Esta fórmula tiene los siguientes parámetros:

Interés (rate): interés del periodo y que ya estaba definido con un nombre que hace referencia a la celda C4.
Núm_Per (Nper): cantidad de pagos (periodos) del préstamo.
Va (Pv): valor actual o el monto total del préstamo.
Per_inicial (Start_period): primer período del cálculo.
Per_final (End_period): último período del cálculo.
Tipo (Type): el valor predeterminado es 0 que indica que el pago se realiza al final del periodo y 1 cuando es al inicio.

Para este caso, vemos como la sumatoria de todos los intereses pagados durante el préstamo, suman lo mismo que el resultado de la fórmula CUMIPMT / PAGO.INT.ENTRE entre los períodos 1 al 60.

Referencias

Microsoft. (26 de marzo de 2022). Funciones financieras (referencia). https://support.microsoft.com/es-es/office/funciones-financieras-referencia-5658d81e-6035-4f24-89c1-fbf124c2b1d8