Índice
DESCARGA LA PLANTILLA Y APRENDE A CREAR Y PERSONALIZAR TU PROPIA TABLA DE AMORTIZACIÓN EN EXCEL PARA DESGLOSAR PAGOS, CALCULAR INTERESES Y MONITOREAR SALDOS
Amortizar implica repartir el costo de un préstamo en pagos regulares durante un período de tiempo determinado. Este concepto es fundamental para entender cómo funcionan las hipotecas, los préstamos para automóviles y otros tipos de financiamiento. En este artículo, te guiaremos paso a paso a crear una tabla de amortización en Excel, utilizando fórmulas y funciones que facilitarán estos cálculos, más una plantilla lista para descargar.
PASO 1: CONFIGURACIÓN INICIAL
Antes de empezar con los cálculos, necesitamos establecer algunas variables clave:
- Monto del préstamo (P): Es fundamental tener en cuenta el importe del préstamo, que representa la suma total proporcionada por la entidad financiera al concedernos un crédito.
- Tasa de interés anual (r): Generalmente encontraremos especificada la tasa de interés de forma anual.
- Número total de pagos (n): el número de pagos que se desea realizar para cubrir la deuda. Es una práctica muy común establecer una cantidad de pagos mensuales, en bloques anuales: 12, 24, 36, 48, etc.
- Frecuencia del pago: anual, semestral, trimestral, mensual, etc.
Para este ejemplo, supongamos:
- Monto del préstamo: $10,000
- Tasa de interés anual: 5,50%
- Plazo del préstamo: 2 años
- Frecuencia de pagos: Mensual
PASO 2: CREAR LA ESTRUCTURA DE LA HOJA DE CÁLCULO
1. ENCABEZADOS DE LAS COLUMNAS:
-
- Cuota
- Pago de Interés
- Pago de Capital
- Saldo pendiente
2. INGRESAR DATOS DEL PRÉSTAMO
-
- Monto del préstamo: en la celda B3
- Tasa de interés anual: en la celda B4
- Plazo del préstamo (años): en la celda B5
PASO 3: CÁLCULO DEL PAGO MENSUAL
Una vez que tenemos las variables mencionadas, podemos calcular el monto de cada pago mensual utilizando la función PAGO de Excel.
Esta función requiere tres argumentos obligatorios: la tasa de interés para cada período, el número total de pagos y el monto del crédito.
=PAGO(tasa, núm_per, va, [vf], [tipo])
donde:
- tasa: tasa de interés por período. Si la tasa anual es del 5,50%, la tasa mensual será 5,50%/12.
- núm_per: número total de pagos. Para un préstamo de 2 años con pagos mensuales, será 2*12.
- va: valor actual del préstamo.
En nuestro ejemplo:
=PAGO(5,50%/12;24;-10000)
Escribe esta fórmula en la celda B6 para obtener el pago mensual.
Mira cómo se realiza el cálculo del pago y la fórmula utilizada al tomar los valores de los argumentos de las celdas en la columna B:
PASO 4: DESGLOSE DEL PAGO MENSUAL
La tabla de amortización en Excel mostrará detalladamente todos los pagos mensuales, brindándonos la información precisa sobre la cantidad destinada al pago de intereses y al abono al capital de nuestro préstamo.
1. INTERÉS DEL PERÍODO
Emplearemos la función PAGOINT de Excel para determinar el pago de intereses. Esta función utiliza los mismos argumentos que la función PAGO, pero incluye un cuarto argumento para especificar el número del período en el que queremos calcular el interés a pagar.
=PAGOINT(5,50%/12;1;24;-10000)
Compara esta fórmula con la función PAGO de la sección anterior y notarás que la única diferencia es que el segundo argumento indica el período que deseamos calcular, que en este caso es el primer período.
Para obtener el interés a pagar en cada uno de los 24 pagos, podemos implementar una tabla como la siguiente:
La fórmula en la celda E2 utiliza las variables de la columna B, y las hemos fijado con referencias absolutas para que no cambien al copiar la fórmula hacia abajo. El segundo argumento de la función PAGOINT se refiere a la columna D, que es donde está ubicado el número del período correspondiente.
2. PAGO DEL CAPITAL
Para calcular el monto abonado al capital de la deuda cada mes, usamos la función PAGOPRIN de Excel. La sintaxis de esta función es casi idéntica a la de la función PAGOINT. Aquí tienes un ejemplo de la fórmula para obtener el pago al capital en el primer período:
=PAGOPRIN(5,50%/12;1;24;-10000)
Así, podremos calcular la parte del pago mensual destinada al capital de nuestra deuda. De manera similar, el segundo argumento de la función específica el número de período para el que estamos haciendo el cálculo. A continuación, se muestra el resultado al incluir esta fórmula en nuestra tabla utilizando las variables definidas previamente:
Si observas cuidadosamente, notarás que la suma del pago de intereses y del pago al capital en todos los períodos coincide con el total calculado mediante la función PAGO. Esto demuestra que las tres funciones son complementarias: la suma de los resultados de las funciones PAGOINT y PAGOPRIN siempre igualará al resultado de la función PAGO.
3. SALDO PENDIENTE
El saldo representa la cantidad del préstamo menos el total de todos los pagos realizados hacia el capital hasta el momento. Si bien el saldo disminuye con cada pago, esta disminución no sigue un patrón constante, dado que inicialmente abonamos más intereses que al final del período. Sin embargo, en el último pago, liquidamos el monto total del préstamo.
Esta tabla ilustra cómo realizar los cálculos de los pagos mensuales, los intereses y el capital de cada período, además del saldo restante después de cada pago.
PASO 6: VALIDACIÓN Y AJUSTE
Revisa que el saldo pendiente en el último período sea cercano a cero. Debido a redondeos, puede haber una pequeña discrepancia que puedes ajustar manualmente.
PLANTILLA DESCARGABLE
Si deseas simplificar aún más el proceso de cálculo de amortización, te invito a descargar nuestra plantilla de amortización en Excel. Esta plantilla lista para usar te ayudará a realizar los cálculos de forma rápida y precisa, permitiéndote visualizar fácilmente los pagos mensuales, los intereses y el saldo restante.
Descarga la plantilla haciendo clic [aquí] ¡Esperamos que te sea útil!
CONCLUSIÓN
Crear una tabla de amortización en Excel es un proceso meticuloso pero muy útil para entender y gestionar préstamos. Al dominar estas fórmulas y funciones, puedes personalizar esta plantilla para adaptarla a diferentes escenarios financieros.
Si te interesa aprender más sobre Excel y sus aplicaciones financieras, no dudes en explorar otros artículos y recursos que tenemos disponibles. ¡Buena suerte con tus cálculos!
» Si te gustan nuestros blogs, compártelo con tus amigos. Puedes seguirnos en Facebook e Instagram.
» Déjanos tu comentario al final de esta página.
» Explora más sobre nosotros directo aquí: Curso de Excel Online