x

Cómo hacer una tabla de amortizaciones en Excel

Escrito por Nelly Acosta última actualización: December 29, 2018

Amortizar una deuda, es reducir gradualmente su saldo o cuantía mediante pagos periódicos que normalmente contienen parte del capital que se amortiza e intereses.

Dependiendo del método o “sistema de amortización” que se emplee, los pagos o cuotas consecutivas que debes pagar, pueden ser iguales o diferentes, en este último caso generalmente tendrán un valor decreciente.

Hay tres sistemas de amortización de deudas populares, estos son el sistema francés, sistema alemán y el método americano de amortización única.

El sistema francés de amortización de préstamos es el más utilizado por la banca, se caracteriza porque los pagos o cuotas periódicas son constantes durante toda la vigencia de la deuda, las cuotas iguales y periódicas se distribuyen tanto en capital como en intereses, la porción de capital crece gradualmente y los intereses decrecen.

El sistema de amortización alemán es conocido como amortización constante, se caracteriza porque la cuota o pago periódico difiere una de otra, siendo más altas al comienzo de los pagos y van decreciendo paulatinamente, mientras que el capital amortizado es siempre el mismo, los intereses decrecen con la cuota.

Por último el método americano de amortización no es un sistema como tal, es un convenio de pago en el que solo se erogan intereses periódicamente durante la vigencia de la obligación y al final del plazo se devuelve el capital en un pago único. Es el mecanismo usado por las corporaciones y entidades que hacen emisiones de bonos.

Construir una tabla de amortización es una tarea relativamente simple si tienes claro los sistemas y términos antes indicados y otros como la tasa de interés anual o APR cobrada por el acreedor, el capital del préstamo y el plazo en meses o años.

Dado que de los tres sistemas antes mencionados el más usado es el método francés, expondremos cómo hacer una tabla de amortización en Microsoft Excel usando el sistema francés.

No dejes de leer: ¿Cómo calcular el cronograma de pago de un préstamo?

Cómo hacer una tabla de amortizaciones utilizando Microsoft Excel

Una tabla de amortización te permite ver la información relacionada con un préstamo durante la vigencia del mismo. Detalles que incluyen los intereses, pagos o cuota periódica, capital y saldo remanente.

Utilizar Microsoft Excel puede hacer que construir una tabla de amortizaciones sea muy sencillo. Las indicaciones que te ofrecemos deben servir con cualquier versión de Excel.

Excel.tips.net nos aclara que el uso de Excel nos limita a configurar un simple cronograma de amortización que no tendrá en cuenta refinanciamientos, pagos anticipados o adicionales, cuotas exoneradas, e incluso tasas de interés variables. En tales casos debemos ser capaces de modificar nuestras plantillas para ajustarlas a esas condiciones especiales.

Abre una nueva hoja de cálculo. Haz clic en Archivo y después en Nuevo. Coloca encabezados para 5 columnas. Comenzando desde la izquierda, coloca cada uno de los siguientes encabezados en las celdas: Período, Saldo, Capital, Intereses, Cuota.

Coloca la cantidad del préstamo (valor actual de la deuda) en la celda debajo del encabezado de Saldo. Puedes utilizar USD 299.000 como ejemplo.

Coloca el puntero debajo del encabezado de Mes o período. Escribe el número 1. En la celda de abajo, escribe el número 2. Para realizar una amortización a 12 meses, necesitarás doce filas de información adicionales a la fila de encabezado.

En vez de escribir cada uno de los meses posteriores (por ejemplo, 3, 4, 5, etcétera), selecciona las celdas del par de dígitos escritos. Con el cursor, selecciona y resalta las celdas que contienen el número 1 y 2. Ahora mueve el cursor a la esquina inferior derecha de las celdas resaltadas.

Verás que tu cursor se convierte en un pequeño signo de más. Manteniendo presionado el botón principal del mouse, arrastra la celda hacia abajo hasta que veas aparecer el número 12. Se trata de la aplicación de “autocompletar” de Excel que te ayudará mucho, sobre todo cuando se trate de tablas con hasta 120 períodos de amortización o más.

Calcularemos ahora el monto de la cuota o pago. Ubica el cursor en la casilla debajo del encabezado pago. Haz clic en la pestaña “Fórmulas” y luego en el botón “f__x". Esto hará que aparezca un cuadro de diálogo con las fórmulas de Excel.

Escribe “PMT” o “Pago” en la caja de búsqueda y haz clic en el botón de Aceptar. Esto hará que aparezca el cuadro de la función Pago de Excel. Encontrarás una explicación de cada campo haciendo clic en cada uno de ellos. El campo Tasa describe la tasa de interés. Para este ejemplo, usaremos una tasa del 8,7% anual; escribe “8,7%/12”. Dividimos por doce para indicar que los pagos o cuotas los haremos con una frecuencia mensual. Si habláramos de frecuencia trimestral dividiríamos entre 4 en vez que 12.

En el cuadro “Nper” escribe “12”, corresponde al número de pagos mensuales que llevaremos a cabo. En “Va” (Valor presente), puedes escribir “299.000” o hacer clic en la celda donde escribiste esa cantidad en la hoja de cálculo. Al terminar, haz clic en el botón de Aceptar.

Si lo hiciste de manera correcta debe aparecer la cantidad “(US$26.106,41)” como resultado, ya sea entre paréntesis o acompañado con un signo “-” indicando que se trata de una erogación. El símbolo monetario cambiará dependiendo del que tengas preestablecido y puede ser configurado. Como hablamos del sistema francés esa cantidad será constante para los 12 meses del ejercicio.

Ahora calcularemos la porción de intereses de cada cuota. Haz clic en el botón de funciones nuevamente y en esta ocasión selecciona “PAGOINT” y das clic en aceptar. En el cuadro de dialogo siguiente ingresa: Tasa: “8,7%/12”. En Per o Período ingresa “1” o refiérete a la celda donde colocaste el número 1 correspondiente al primer mes, por ejemplo “A2”. En Nper nuevamente ingresa “12”. En Va o valor presente, puedes escribir los “299.000” o seleccionar la celda donde se encuentra ese número. Haz clic en el botón de Aceptar, cuando hayas finalizado. Deberías ver que aparece el resultado (USD 2.167,75).

Calcula la porción de la cuota mensual destinada a amortizar el capital del préstamo. Esto se puede hacer para el primer pago mediante la fórmula de Excel “PAGOPRIN”. Ingresa los mismos datos en la Tasa, Per, Nper, y Va que utilizaste en las otras funciones. Estos son: 8,7%/12, A2, 12, USD 299.000. Haz clic en el botón Aceptar, cuando hayas terminado se debería mostrar un número (USD 23.938,66).

Calculamos ahora el saldo. Haz clic en la siguiente celda vacía de la columna de Saldo (B3). Deberás rentar la amortización realizada del saldo original del crédito o capital. Si la amortización hecha a capital se muestra como un número negativo, debemos sumar en lugar de restar.

Escribe el signo de igual en la celda. Ahora haz clic en la celda del saldo ubicada a arriba (con el valor 299.000). Ingresa el signo de más y haz clic en la celda que contiene la amortización hecha a capital (USD 23.938,66 que aparece entre paréntesis o con signo negativo). Presiona Enter, Excel hará los cálculos por ti. Deberías ver la cifra USD 275.061,34

Ahora puedes llenar de manera automática las celdas restantes para las columnas Saldo, Capital, Intereses, Cuota usando la función de "selección y arrastre".

Al terminar el “autocompletar”, deberías ver un saldo de cero en el mes 12 en la columna de Saldo y los siguientes números para el Capital, Interés y Pago, de manera respectiva: USD 25.918,50; USD 187,91; USD 26.106,41.

Si todo quedó claro ya podrás llevar a cabo cualquier cuadro de amortización en Excel empleando el sistema Francés. Si deseas plantillas en Excel con calidad profesional, para este y otros tipos de ejercicios similares, puedes ir al sitio web oficial de Microsoft Office y da un vistazo a los formatos disponibles.

Te puede interesar: ¿Cómo calcular el interés en un préstamo personal?

Este artículo fue escrito, editado y revisado exhaustivamente por el equipo de Cuida Tu Dinero con la finalidad de asegurar que los lectores reciban la mejor y más detallada información posible. Para enviarnos tus inquietudes, ideas o simplemente saber más acerca de Cuida Tu Dinero, escríbenos aquí.