Mortgage Payment Table Spreadsheet – Table of amortization in Excel

Amortizing means gradually extinguishing a debt or a loan through periodic payments. The objective of an amortization table is to specify the detail of each of the payments until the total liquidation of the loan.

It is very likely that you have ever seen an amortization table, especially if you have approached a banking institution to apply for a car loan or a mortgage loan. Generally the bank advisor will ask you the amount and the desired duration of the credit and will immediately show you a table with the breakdown of the payments to be made.

The consultant does not do the calculations manually in the moment but uses a computer system developed for that purpose. We can also automate this type of tasks by creating a table of amortization in Excel and that way to know easily and quickly the amount of payments to be made and as well as the exact amounts destined to the payment of interest and the payment of capital.

Variables for calculation

In order to create the amortization table in Excel we must have at least the following information:

Amount of credit: It is indispensable to know the amount of the loan. This is the net amount granted by the financial institution when we approve a loan.
Interest rate: Not only must we cover the total amount of credit but also the interest rate charged by the financial institution as it is the way they obtain profits for the provision of such service. Generally we will find the interest rate specified on an annual basis.
Number of payments: It is necessary to establish the number of payments that we wish to make to cover our debt. It is a very common practice to establish a number of monthly payments (in annual blocks): 12, 24, 36, 48, etc.
As a general rule, the greater the number of payments to make, the lower the amount of each monthly payment, but the interest payable will be much higher. If this statement is not clear to you, it will surely be once we have created our table of amortization in Excel and we can analyze different scenarios for a credit.

### Calculation of payment amount

Once we have the variables previously mentioned we will be able to calculate the amount of each of the monthly payments using the function PAGO of Excel. This function has three mandatory arguments, which are precisely our variables: Interest rate for each period, total number of payments, and amount of credit.

Assuming that we are going to request a loan in the amount of \$ 150,000 and that we have an annual interest rate of 12% and we want to make 24 monthly payments. The formula to use to calculate the monthly payment will be similar to the following:

= PAYMENT (1%, 24, -150000)

The financial institution provided us with 12% annual interest, but for the PAYMENT function you need to use the interest rate for each period, which in this case is monthly, so I must divide between 12 to get the result of 1 % Of monthly interest. The second argument of the function is the number of monthly payments in which we will pay the revenue and finally the amount of the credit. Note the calculation of the payment and the implemented formula when reading the values ​​of the arguments of the cells in column B:

### Table of amortization in Excel

For our example there is a payment of \$ 7,061.02 that we will have to make during 24 months to pay off our debt.

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:

Pago de intereses en una tabla de amortización en Excel

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:

Pago a capital en una tabla de amortización en Excel

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:

Saldo en una tabla de amortización en Excel

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.

Macro para tabla de amortización en Excel

Lo único que necesita hacer nuestra macro es leer los valores de la columna B e insertar las fórmulas correspondientes en cada fila de acuerdo al número de pagos a realizar.

Macro para generar tabla de amortización en Excel

Con esto hemos terminado el desarrollo de una tabla de amortización en Excel que será funcional para conocer el detalle de los pagos necesarios para liquidar una deuda. Puedes descargar el libro de trabajo el cual contiene dos hojas, en la primera encontrarás la solución que tiene solo las fórmulas y en la segunda hoja la que contiene la macro.