Tabla de Amortización

Una tabla de amortización sirve para planificar el pago de un crédito, y tomar decisiones en el flujo de caja de un negocio, basándose en dicha planificación.

¿Qué encontrarás aquí?

Fórmula para calcular la amortización

La amortización es un concepto clave en todo proceso contable, y permite llevar un control riguroso de las obligaciones contraídas, también conocidas como pasivos. La fórmula general para el cálculo de amortizaciones es:

Donde:

r: Cuota a cancelar mensualmente
A: Monto total de préstamo
i: Tasa de interés mensual
n: Número de cuotas mensuales

Esta expresión calcula el monto total de cada cuota. Sin embargo, para discriminar la prima de los intereses hay que realizar un par de pasos adicionales.

Primero, calcular el interés a pagar en el periodo dado. Dicho interés puede calcularse mediante la fórmula:

j = k * i

Donde:

j: Monto del interés a pagar en el periodo especifico
k: Remanente de la deuda
i: Tasa de interés

Luego, se calcula la prima restando al monto de la cuota, el monto del interés a pagar en ese periodo. Se resumiría en la siguiente expresión:

p = r – j

Siendo que:

p: Prima a cancelar
r: Monto total de la cuota
j: Monto de los intereses a pagar en ese periodo

Ya con esta base teórica, simple pero esencial, vamos a ver cómo se puede implementar esta necesaria herramienta usando una hoja de cálculo de Excel. Para visualizar el proceso de forma más interactiva, puedes descargar nuestro ejemplo y experimentar con él.

Calcular una tabla de amortización en Excel (fórmula tradicional)

Partiremos del siguiente formato básico, que no es otra cosa sino una tabla de amortización con sus constantes (monto préstamo, tasa de interés, número de cuotas), un campo para el monto de la cuota (que se calculará con la fórmula) y una tabla de 5 columnas: Fecha, Periodo, Prima, Interés, Saldo.

Ahora sigamos paso a paso el procedimiento para aplicar la fórmula general en Excel:

Calcular la cuota mensual usando la fórmula general. Si traducimos esta fórmula al formato de Excel, tendríamos:

A * i / (1 – 1 / (1 + i)^n)

Calcular el interés del periodo, multiplicando el saldo anterior por la tasa de interés. Esto debes hacerlo en la columna “Interes” de la tabla de amortización. La referencia a la tasa de interés debe ser absoluta.

Calcular la prima del periodo, restando el interés del periodo al monto de la cuota. Igualmente, el monto de la cuota debe ser una referencia absoluta.

Calcular el saldo actual, restándole la prima al saldo anterior.

Selecciona Prima, Interés y Saldo del primer periodo y arrastra hacia abajo para copiar las fórmulas en el resto de las filas.

Como pudiste ver, se trata de un proceso sencillo pero un poco laborioso. Afortunadamente, Excel siempre tiene una respuesta para (casi) todo.

Calcular una tabla de amortización en Excel (fórmula Excel)

Para evitar lo tedioso que resulta copiar la fórmula general de la amortización, Excel implementa esta, en la función PAGO. Esta función financiera recibe los tres argumentos regulares (tasa, nro. de cuotas, valor del préstamo) más dos opcionales (valor esperado, tipo de pago). La sintaxis de esta función financiera es:

=PAGO(tasa; nper; va; [vf]; [tipo])

Siendo sus argumentos:

tasa: Es la tasa de interés MENSUAL estipulada en las condiciones del préstamo. En caso de ser anual, tienes que dividirla entre 12.
nper: Es el número de periodos para la cancelación del préstamo.
va: Es el valor inicial, o monto total de préstamo. Si no está expresado como un decimal negativo, tendrás que convertirlo colocando un guión (-) antes de la referencia.
vf: Es un valor opcional, que por defecto se establece como cero (0). Es el monto con el que debe finalizar la tabla de amortización. Puede ser cualquier decimal mayor que 0 pero menor que el monto total del préstamo.
tipo: define si los intereses se cancelan previo a cada periodo o al vencimiento del periodo. Puede tener valor 1 o 0, respectivamente.

Al aplicarla sobre una celda, lo haremos de la siguiente forma:

Pero eso no es todo. También dispones de dos fórmulas complementarias que te permiten calcular tanto la prima como los intereses del periodo en curso. Las funciones PAGOPRIN y PAGOINT, te ahorra tener que calcular estos dos valores. Como podrás imaginar al sumar los resultados de ambos en cada periodo, obtienes el monto de las cuotas, pues:

cuota = prima + interés del saldo restante

Como estas dos funciones son dependientes del periodo, además de los argumentos propios de la función PAGO, tenemos un nuevo argumento: periodo. El periodo es una de las divisiones del tiempo total de pago, y comienza a contar a partir del primer pago. Por lo general, se coloca un periodo 0 al comenzar una tabla de amortización, para reservar el espacio del saldo inicial.

El saldo actual si debe calcularse de manera normal, sustrayendo el valor de la prima al saldo anterior.

Ya sabes cómo montar una sencilla pero efectiva tabla de amortización en Excel. Con este conocimiento ya no tienes excusas para no hacer seguimiento a tus obligaciones y pagar todos tus préstamos y créditos en el tiempo previsto. Además, es una buena idea usar esta herramienta para evaluar si es o no factible un crédito antes de comprometerte a pagarlo.