Tabla de amortización
Cálculo del monto de pago
Una vez que tenemos las variables previamente mencionadas podremos calcular el monto de cada uno de los pagos mensuales utilizando la función PAGO de Excel. Esta función tiene tres argumentos obligatorios y que son precisamente nuestras variables: Tasa de interés para cada período, número total de pagos, y monto del crédito.
Suponiendo que vamos a solicitar un crédito por un monto de $150,000 y que tenemos una tasa de interés anual del 12% y queremos realizar 24 pagos mensuales. La fórmula que debemos utilizar para calcular el pago mensual será similar a la siguiente:
=PAGO(1%,24,-150000)
La institución financiera nos proporcionó el dato de 12% de interés anual, pero para la función PAGO necesita utilizar la tasa de interés para cada período, que en este caso es mensual, así que debo hacer la división entre 12 para obtener el resultado de 1% de interés mensual. El segundo argumento de la función es el número de mensualidades en las que pagaremos el rédito y finalmente el monto del crédito. Observa el cálculo del pago y la fórmula implementada al leer los valores de los argumentos de las celdas en la columna B:
Para nuestro ejemplo ha quedado un pago de $7,061.02 que tendremos que hacer durante 24 meses para saldar nuestra deuda.
Creación de la tabla de amortización
La tabla de amortización en Excel será el desglose de cada uno de los pagos mensuales para conocer el monto exacto destinado tanto al pago de intereses como al pago del capital de nuestra deuda. El cálculo de pago de intereses lo haremos con la función PAGOINT de Excel. Esta función utilizará los mismos argumentos que la función PAGO pero agregará un cuarto argumento para indicar el número de período para el cual deseamos calcular el monto del interés a pagar.
Utilizando nuestro ejemplo de préstamo, calcularemos el interés a pagar en el primer período utilizando una fórmula como la siguiente:
=PAGOINT(1%,1,24,-150000)
Compara esta fórmula con la función PAGO de la sección anterior y verás que la única diferencia es que el segundo argumento indica el período que deseamos calcular, que en este caso es el primer período. Para obtener el interés a pagar en cada uno de los 24 pagos podemos implementar una tabla como la siguiente:
Observa que la fórmula de la celda E2 hace referencia a las variables de la columna B y las he colocado como referencias absolutas porque deseo que dichas referencias permanezcan fijas al momento de copiar la fórmula hacia abajo. El segundo argumento de la función PAGOINT hace referencia a la columna D que es precisamente donde se encuentra el número de pago correspondiente.
Por el contrario, para obtener el monto que se abona mes a mes a nuestra deuda, debemos utilizar la función PAGOPRIN de Excel. La sintaxis de esta función será prácticamente idéntica a la de la función PAGOINT. Considera la siguiente fórmula que nos ayuda a obtener el pago a capital para el primer período:
=PAGOPRIN(1%,1,24,-150000)
De esta manera calcularemos el monto de nuestro pago mensual que estará destinado al pago de capital de nuestra deuda. De igual manera, el segundo argumento de la función indica el número de período para el cual estamos haciendo el cálculo. Observa el resultado al incluir esta fórmula en nuestra tabla utilizando las variables previamente definidas:
Si revisas con detenimiento verás que la suma del pago de interés y pago a capital para todos los períodos nos da el total obtenido con la función PAGO. De esta manera podemos deducir que estas tres funciones son complementarias: La suma del resultado de las funciones PAGOINT y PAGOPRIN siempre será igual al resultado de la función PAGO.
Para finalizar nuestra tabla de amortización podemos agregar algunas columnas adicionales, por ejemplo el saldo en cada uno de los períodos:
El saldo es el monto del crédito menos la suma de todos los pagos a capital realizados hasta el momento. El saldo se va reduciendo con cada pago aunque no es una reducción constante ya que al inicio pagamos más interés que al final pero en el último pago llegamos a liquidar el total del monto del crédito.
Como tal vez ya lo imaginas, si queremos cambiar nuestra tabla de amortización para tener 36 pagos mensuales será necesario agregar manualmente los nuevos registros y copiar las fórmulas hacia abajo. Es por eso que una mejor solución para crear una tabla de amortización en Excel es utilizar una macro para generar automáticamente la tabla.
