¿Cómo hacer una tabla de amortización en Excel?
La amortización se refiere al proceso en el que comenzamos a saldar la deuda de un préstamo hasta que es totalmente cancelada. En ese sentido, una tabla de amortización en Excel nos permitirá conocer cómo se distribuyen los pagos que hacemos o cuántas cuotas restan.
¿Qué es una tabla de amortización?
Una tabla de amortización es un cuadro que muestra un historial en donde podemos ver en detalle los pagos realizados, los faltantes y cómo se distribuyen para liquidar un crédito.
¿Para qué sirven las tablas de amortización?
El uso de una tabla de amortización nos permitirá tener una visión completa sobre cualquier crédito. Así, con la información que proporciona podemos estar preparados para afrontar los costos de solicitar un préstamo y mantener un control adecuado de todo el proceso.
¿Qué elementos debe contener una tabla de amortización?
Al momento de calcular una tabla de amortización en Excel debemos considerar los siguientes conceptos:
- Periodo: tiempos en que deben realizarse los pagos de las cuotas.
- Intereses: contraprestación que recibe el prestamista en cada cuota.
- Amortización o pago de capital: es el monto neto que se orienta a disminuir el capital de la deuda en cada cuota. No contempla los intereses.
- Cuota a pagar: es el monto total que se debe abonar en cada periodo.
- Saldo: se trata del monto que falta por pagar y que se reduce tras cada cuota, hasta que el préstamo sea liquidado.
¿Cómo crear una tabla de amortización en Excel?
Crear una tabla de amortización implica conocer algunas variables para calcular el pago a través de 3 funciones de Excel.
Variables para calcular el pago
Son 3 variables cuyos valores necesitamos manejar para el correcto cálculo de los pagos que haremos. Las detallamos a continuación.
Valor del crédito
Se refiere al monto neto del crédito que recibimos por parte de la entidad financiera o prestamista.
Tasa de interés
La tasa de interés representa la ganancia que obtiene la entidad financiera tras otorgar el préstamo. Cuando pagamos, cubrimos la cantidad que recibimos y además un monto adicional por los servicios que brinda la institución.
Cantidad de pagos
Esta variable hace referencia al número de cuotas que pagaremos para cubrir los intereses y el capital a fin de liquidar el préstamo.
Funciones en Excel para calcular el pago
Las variables que mencionamos antes son necesarias para llevar a cabo los cálculos con las funciones: PAGO, PAGOINT y PAGOPRIN. Cada fórmula en Excel para la tabla de amortización arrojará un monto relacionado con los intereses, pago de capital y pagos mensuales.
Función PAGO
A través de la función PAGO obtendremos el monto que debemos pagar mensualmente para cubrir nuestro préstamo. Su sintaxis es:
=PAGO(tasa de interés mensual, cantidad de pagos, valor del crédito)
=PAGO(1%,12,100000)
Función PAGOINT
Con el uso de la función PAGOINT podemos conocer el monto correspondiente al interés mensual de cada cuota que pagamos. Su sintaxis es:
=PAGOINT(tasa de interés, número de periodo, cantidad de pagos, valor del crédito)
=PAGOINT(1%,2,12,100000)
Función PAGOPRIN
La función PAGOPRIN nos permite determinar el monto correspondiente al abono de capital en cada una de las cuotas que pagamos. Aunque ocupa las mismas variables que la función PAGOINT, la operación arroja el monto que se agrega al capital. Su sintaxis es la siguiente:
=PAGOPRIN((tasa de interés, número de periodo, cantidad de pagos, valor del crédito)
=PAGOINT(1%,2,12,100000)
Ejemplos de tabla de amortización en Excel
Las modalidades de préstamo pueden variar de acuerdo a la forma en que se llevan a cabo los pagos o si el interés es fijo o variable. En ese sentido, vamos a presentar algunos ejemplos de tablas de amortización para diferentes casos.
Tabla de amortización en Excel de un crédito bancario
Para este ejemplo de tabla de amortización de crédito bancario, solicitamos un préstamo de $100,000.00 a una tasa de interés anual de 12%, para ser pagado en 24 cuotas.
Ocuparemos la función PAGO para calcular los montos del pago anual y mensual.
El pago anual se calcula seleccionando las variables Interés, cantidad de pagos y valor del crédito. Por su parte, el monto mensual se obtiene con la misma función, pero dividiendo el interés entre 12. Nos quedaría así:
Pago Anual:
Pago Mensual:
El uso de la función ABS permite obtener el resultado en números positivos.
Ahora pasamos a crear la tabla para el desglose de los pagos. Para ello, generamos 4 nuevas filas y habilitamos tantas columnas como pagos deban realizarse, en este caso 24. Las nuevas filas serán:
- N° de periodo: número de mes correspondiente a una cuota.
- Pago mensual: cantidad obtenida a través de la función PAGO.
- Pago de interés: cantidad obtenida con la función PAGOINT.
- Pago de Capital: monto que se obtiene con la función PAGOPRIN.
- Saldo: progreso del pago del crédito que se obtiene al restar el saldo del periodo anterior y el abono de capital actual.
Tabla de amortización en Excel con pagos anticipados
Cuando recibimos un préstamo, puede darse la situación en donde queremos anticiparnos al pago de algún periodo.
Continuando con el ejemplo anterior, podemos adaptar nuestra tabla de amortización de crédito bancario a fin de que soporte el cálculo con pagos anticipados. Para ello debemos hacer una serie de cambios:
- Agregar una nueva fila antes del período uno, para crear el período cero.
- Insertar el monto de la celda valor de crédito en la primera celda correspondiente a Saldo.
- Crear una nueva columna identificada como pagos anticipados.
El penúltimo cambio lo aplicaremos editando la fórmula de pago mensual y nos quedaría de esta forma
.
En el segundo bloque de la fórmula estamos restando el número de periodo a la cantidad de pagos y en el tercero, apuntamos al saldo del periodo cero. Al final, sumamos la celda de pagos anticipados.
Por último tenemos que reemplazar las fórmulas de pago de interés y pago de capital por sus cálculos conceptuales. Nos quedaría así:
Pago de interés: tasa de interés mensual multiplicado por el saldo del periodo anterior.
Pago de capital: se restan los pagos de intereses al monto de pago mensual.
Arrastra las fórmulas hacia abajo para rellenar el resto de las celdas y luego podrás ingresar tus pagos anticipados.
Tabla de amortización en Excel con cuota fija
El primer caso que planteamos resulta también un ejemplo de tabla de amortización en Excel con cuota fija. Esto se debe a que el pago mensual representa un monto invariable, por lo que las funciones PAGO, PAGOINT y PAGOPRIN funcionan muy bien para estos cálculos.
Preguntas relacionadas
¿Cómo se calcula la cuota de un crédito en una tabla de amortización en Excel?
Para calcular las cuotas podemos ocupar la función PAGO. En ese sentido, necesitaremos contar con los datos de tasa de interés mensual, la cantidad de pagos y el valor del crédito.
¿Cómo calcular la cuota anual de un préstamo en Excel?
Para obtener la cuota anual también podemos ocupar la función PAGO. La única diferencia con el proceso anterior es que en lugar de ingresar la tasa de interés mensual, debemos usar la tasa de interés anual.