Cuando solicitamos un préstamo o una hipoteca, lo más normal es que tengamos que devolverla en pagos periódicos o a plazos en lo que se conoce como «amortizar» y para llevar un buen control al respecto, podemos recurrir a nuestros ordenadores y a pequeños conocimientos de ofimática que cualquiera puede controlar. En este caso y para hacer una tabla de amortización para un préstamo, nada como utilizar Excel así que os explicamos cómo hacerla y además os damos algunos ejemplos.
Hacer una Tabla de amortización en Excel de un préstamo
Las tablas de amortización son más comunes de lo que podemos llegar a pensar. De hecho, no es algo que solo usemos a modo particular o a nivel usuario cuando queremos calcular las cantidades a pagar cada mes a partir de un préstamo solicitado. También los bancos las usan y de hecho es posible que te hayan enseñado alguna cuando precisamente fuiste a pedir tu préstamo.
Para hacerla deberemos hacer uso de las fórmulas disponibles en Excel, de modo que podamos calcular la cantidad a pagar cada mes, en función de los meses en los que queramos amortizar ese préstamo, tanto en lo que respecta al pago de los intereses, como al del capital en sí.
Qué se necesita para hacer una tabla de amortización en Excel
La creación de cualquier tabla de amortización que queramos hacer en excel requiere que aportemos una determinada información y que consiste en:
- Cantidad del préstamo: Es decir, la cantidad neta otorgada que nos da el banco cuando nos concede el crédito.
- Tasa de interés: Que se corresponde con la ganancia que se lleva el banco por esa concesión del préstamo. Suele establecerse con un porcentaje que además se revisa anualmente.
- Número de pagos: Que tiene que ver con los pagos que debemos hacer en función de los meses. Generalmente se establecen al menos seis meses, y así: 12, 24, 36 meses, etc… Cuanto más meses introduzcamos en nuestra paga más se reducirá la cantidad a pagar aunque aumentarán los intereses.
Cómo hacer el cálculo del monto de pago
Una vez ya tenemos todas las variables señaladas, podemos entonces calcular cuánto tenemos que pagar cada mes. Para ello, lo que tienes que hacer es usar la función «pago» de Excel. que cuenta además con las variables señaladas, es decir, la tasa de interés, el total de pagos y la cantidad a pagar.
Podemos ver un ejemplo claro en la imagen que te dejamos a continuación. Imagina que vas a pedir un préstamo de 150,000 € con una tasa de interés anual del 12% a pagar en 24 meses. La fórmula que debes introducir en excel será esta:
=PAGO(1%,24,-150000)
Aunque el interés sea del 12%, no olvides nunca que para hacer el cálculo del pago, la fórmula tiene que tener la información por un mes de modo que si tenemos en cuenta que el año tiene 12 meses, tendremos que poner ese 1%. De este modo, la fórmula te queda así:
Como vemos en la imagen, nos quedará un pago de 7,061.02 € que tendremos que pagar cada mes.
Crear la tabla de amortización
Lo explicado es solo un ejemplo para que sepáis cuál es la fórmula y cómo se escribe en el documento excel para ver la cantidad total que tenemos que pagar pero no es en sí la tabla de amortización. Esta se corresponderá con todo el desglose de pagos de intereses, y luego de mensualidades en función del préstamo solicitado.
Para calcular el pago de intereses tenemos que usar otra función de excel que es «Pagoint» . En esta debemos introducir los mismos datos que antes pero añadir una cuarta variable que se corresponde con el número de período para el que deseamos hacer el cálculo del interés que tenemos que devolver a la entidad bancaria.
De este modo, la fórmula a usar tiene que ser ahora:
=PAGOINT(1%,1,24,-150000)
La función es como la otra pero añadimos el período que queremos calcular. De este modo, si quieres saber el interés que has de pagar en las 24 mensualidades puedes ver este otro ejemplo:
Si te fijas, la celda E2, se refiere a las variables que tenemos en la columna B que como hemos hecho nosotros, las puedes poner como referencias absolutas para que se mantengan fijas a medida que vamos moviéndonos por la columna. En cuanto a la columna D es la que refleja el cada número de pago correspondiente hasta llegar a los 24.
Para acabar, en el caso de que queramos obtener la cantidad que vamos a abonar cada mes a la deuda contraída tenemos también la función de Excel, «Pagoprin» que se refleja con esta fórmula.
=PAGOPRIN(1%,1,24,-150000)
Gracias a esta otra función podemos ver reflejado el monto que pagaremos de capital del préstamo que hayamos solicitado. Puedes ver como el segundo dato de la fórmula hace referencia al número de período para el que vamos a estar haciendo el cálculo. Te dejamos otra imagen a modo de ejemplo para que veas o te hagas una idea de cómo se introduce y de cómo funciona esta otra fórmula en las tablas de amortización para excel:
Hasta aquí toda la explicación de cómo puedes hacer fácilmente tu tabla de amortización en excel para cualquier préstamo o crédito que solicites a tu banco o a una entidad financiera. Pero antes de acabar te diremos que puedes añadir además una nueva columna en la que poner el saldo que nos va quedando. En dicha columna que puede ser la G, debes poner una simple fórmula de resta para la columna anterior. Puedes verla en este último ejemplo:
