Tabla de amortización en Excel
Una tabla de amortización será de gran ayuda a la hora de solicitar una opción de crédito, ya que este documento nos mostrará el desglose de pagos que habrá que realizar para devolver un préstamo, además de indicarnos las fechas en las que habrá que realizarlos.
Muchas veces, cuando solicitamos grandes cantidades de dinero, ya sea para comprar nuestra casa, nuestro coche o para montar un negocio, nos encontramos con que los pagos que hacemos parecen no contar y nuestra deuda no disminuye, aunque no hayamos dejado de pagar.
¿Qué contiene la tabla de amortización?
- Periodo: Se refiere al momento en que se debe pagar.
- Intereses: Son los intereses pagados al prestamista por cada periodo. Se calcula multiplicando el tipo de interés acordado por el capital pendiente.
- Reembolso del principal: reembolso del préstamo sin incluir los intereses, es decir, lo que se deduce cada periodo del capital pendiente.
- Porción a pagar: es la suma de los intereses y la amortización.
- Capital restante del préstamo: el capital restante a devolver del periodo anterior y la amortización del periodo actual se restan en cada periodo.
Variables para el cálculo
Para crear la tabla de amortización en Excel necesitamos tener al menos la siguiente información:
- cantidad de crédito: Imprescindible conocer el importe del préstamo. Es el monto neto que otorga la institución financiera cuando aprueba un préstamo.
- Tasa de interés: Debemos cubrir no solo el monto total del crédito sino también la tasa de interés que cobra la entidad financiera ya que de esta forma obtienen ganancias por la prestación de ese servicio. Generalmente, obtenemos la tasa de interés especificada anualmente.
- numero de pagos: Necesitamos establecer el número de pagos que debemos hacer para cubrir nuestra deuda. Fijar el importe de las cuotas mensuales (en bloques anuales) es una práctica muy habitual: 12, 24, 36, 48, etc.
Por norma general, cuanto mayor sea el número de pagos a realizar, menor será el importe de cada una de las cuotas mensuales, pero los intereses serán mucho mayores. Si no te queda muy claro este enunciado, seguramente habrá creado nuestra tabla de amortización en Excel y podremos analizar varios escenarios de préstamo.
Cálculo del monto del pago
Una vez que tenemos las variables anteriores podemos calcular el monto de cada una de las mensualidades usando la función PAGO en Excel. Esta función tiene tres argumentos de peso, que son precisamente nuestras variables: Tasa de interés de cada período, número total de pagos y monto del crédito.
Suponiendo que estamos por solicitar un préstamo de $150.000 y tenemos una tasa de interés anual del 12% y queremos realizar 24 cuotas mensuales. La fórmula que debemos usar para calcular el pago mensual será similar a la siguiente:
=PAGO(1%,24,-150000)
La entidad financiera nos ha dado datos de interés del 12% anual, pero para la función PAGO tiene que utilizar el tipo de interés de cada periodo, que en este caso es mensual, por lo que tengo que dividir por 12 para obtener una rentabilidad del 1% mensual . interesar. El segundo argumento de la función es el número de cuotas mensuales en las que pagamos la renta y finalmente el importe del crédito. Mire el cálculo del pago y la fórmula aplicada mientras lee los valores de los argumentos de las celdas en la columna B:
Por ejemplo, hay un pago de $7.061,02 que tenemos que realizar en 24 meses para saldar nuestra deuda.
Creación de la tabla de amortización
Será un desglose de cada una de las cuotas mensuales para saber la cantidad exacta de intereses y pago de principal de nuestra deuda. Calcularemos el pago de intereses con la función PAGOINT en Excel. Esta función usará los mismos argumentos que la función PAGO pero agregará un cuarto argumento para indicar el número de períodos para los cuales estamos tratando de calcular la cantidad de interés adeudada.
Usando nuestro ejemplo de préstamo, calcularemos el interés adeudado en el primer período usando una fórmula de la siguiente manera:
=PAGOINT(1%,1,24,-150000)
Compara esta fórmula con la función PAGO del artículo anterior y verás que la única diferencia es que el segundo argumento representa el período que queremos calcular, que en este caso es el primer período. Para encontrar el interés a pagar en cada uno de los 24 pagos, podemos aplicar una tabla de la siguiente manera:
Tenga en cuenta que la fórmula en la celda E2 se refiere a las variables en la columna B y las agregué como referencias completas porque quiero que esas referencias permanezcan constantes al copiar la fórmula. El segundo argumento de la función PAGOINT se refiere a la columna D, donde se ubica precisamente el número de pago correspondiente.
Por el contrario, para obtener la cantidad pagada mes a mes por nuestras deudas, necesitamos usar la función de Excel PAGOPRIN. La proporción de esta función será casi igual a la proporción de la función PAGOINT. Considere la siguiente fórmula que nos ayuda a obtener el pago principal del primer período:
=PAGOPRIN(1%,1,24,-150000)
De esta forma calculamos el monto de nuestra cuota mensual que se utilizará para pagar el principal de nuestra deuda. De manera similar, el segundo argumento de la función representa el número de período para el que estamos realizando el cálculo. Mira el resultado e incluye esta fórmula en nuestra tabla usando las variables definidas previamente:
Si revisas detenidamente verás que la suma del pago de intereses y el pago de capital de cada periodo nos da el total recibido con la función PAGO. De esta forma podemos darnos cuenta que estas tres funciones son complementarias: La suma de los resultados de las funciones PAGOINTO y PAGOPRIN será siempre igual al resultado de la función PAGO.
Para completar nuestra tabla de amortización podemos añadir algunas columnas adicionales, como el saldo en cada uno de los periodos:
El saldo es el monto del préstamo menos la suma de todos los pagos de capital realizados hasta la fecha. El saldo va disminuyendo con cada pago, aunque no una disminución continua ya que al principio pagamos más intereses que al final, sino que en el último pago fijamos el monto total del crédito.
Como ya os podéis imaginar, si queremos cambiar nuestra tabla de amortización a 36 cuotas mensuales, será necesario añadir manualmente los nuevos registros y copiar las fórmulas. Es por eso que una mejor solución para crear una tabla de amortización en Excel es usar una macro para generar automáticamente la tabla.
Macro para tabla de amortización en Excel
Todo lo que nuestra macro necesita hacer es leer los valores en la columna B e ingresar las fórmulas correspondientes en cada fila según la cantidad de pagos a realizar. Entonces coloco un botón de comando ActiveX en la hoja y agrego el siguiente código al evento Haga clic en el botón:
Private Sub CommandButton1_Click() 'Limpiar el contenido de las celdas antes de iniciar Range("D2:G1200").ClearContents 'Obtener el número de pagos de la celda B3 num_pagos = Range("B3").Value 'Para cada fila de pago insertar las fórmulas correspondientes For i = 1 To num_pagos Cells(i + 1, 4).Value = i Cells(i + 1, 5).Formula = "=IPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1)" Cells(i + 1, 6).Formula = "=PPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1)" Cells(i + 1, 7).Formula = "=$B$1-SUM($F$2:F" & i + 1 & ")" Next i End Sub
Las lineas 12, 13 y 14 estan a cargo de las formulas que realizaran los calculos y en VBA tenemos que usar el nombre de las funciones en ingles o de lo contrario nos dara error #NOMBRE? en nuestra hoja de Excel. Cuando se presiona el botón, las fórmulas se insertarán en las celdas correspondientes.
Con esto está el desarrollo de un tabla de amortización en Excel será funcional para conocer el detalle de los pagos necesarios para resolver una deuda. Puede descargar el libro de trabajo de dos hojas, en la primera encontrará la solución que contiene solo las fórmulas y en la segunda hoja la que contiene la macro.
Deja una respuesta