¿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