Amortizar significa extinguir gradualmente una deuda o un préstamo a través de pagos periódicos. La amortización se refiere al proceso en el que comenzamos a saldar la deuda de un préstamo hasta que es totalmente cancelada. Este concepto es fundamental para entender cómo funcionan las hipotecas, los préstamos para automóviles y otros tipos de financiamiento.
El uso de una tabla de amortización nos permitirá tener una visión completa sobre cualquier crédito. Es muy probable que alguna vez hayas visto una tabla de amortización, especialmente si te has acercado a una institución bancaria para solicitar un crédito de auto o un crédito hipotecario. Una tabla de amortización te permite controlar pagos, intereses y saldo pendiente con claridad. Es una de las plantillas más útiles para finanzas personales y préstamos.
Crear una tabla de amortización en Excel es un proceso meticuloso pero muy útil para entender y gestionar préstamos. El asesor en una institución bancaria no hace los cálculos manualmente, sino que utiliza un sistema computacional desarrollado para ese fin.
Variables Esenciales para el Cálculo de la Amortización
Son 3 variables cuyos valores necesitamos manejar para el correcto cálculo de los pagos que haremos. Estas son:
- Monto del crédito (Va): Es indispensable conocer el monto del préstamo. El valor actual o el valor total que tiene actualmente una serie de pagos futuros. También se conoce como valor bursátil.
- Tasa de interés: No solo debemos cubrir el monto total del crédito sino también la tasa de interés cobrada por la institución financiera, ya que es la manera como obtienen ganancias por la prestación de dicho servicio. La tasa de interés representa la ganancia que obtiene la entidad financiera tras otorgar el préstamo. Este es el tipo de interés del préstamo.
- Número de pagos (Núm_per): Es necesario establecer el número de pagos que deseamos realizar para cubrir nuestra deuda. Es el número total de pagos que se desea realizar para cubrir la deuda. Como regla general, entre mayor sea el número de pagos a realizar, menor será el monto de cada uno de los pagos mensuales, pero el interés a pagar será mucho mayor.
Además de estas variables principales, para ciertas funciones de Excel también se pueden considerar:
Lea también: Aguinaldo: Cálculo de Impuestos
- Vf: Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si omite el argumento vf, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).
- Tipo: Opcional. Es el número 0 o 1; indica cuándo vencen los pagos ("0" para una tasa vencida o "1" para una tasa anticipada).
Funciones Clave de Excel para la Amortización
Las variables que mencionamos antes son necesarias para llevar a cabo los cálculos con las funciones: PAGO, PAGOINT y PAGOPRIN.
Función PAGO: Cálculo de la Cuota Mensual o Anual
¿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. Una vez que tenemos las variables previamente mencionadas podremos calcular el monto de cada uno de los pagos mensuales utilizando la función PAGO de Excel. A través de la función PAGO obtendremos el monto que debemos pagar mensualmente para cubrir nuestro préstamo. Para hallar la cuota mensual que estará presente durante toda la obligación, se utiliza la función “PAGO” de Excel que explicamos anteriormente.
¿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. 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.
Por ejemplo, suponiendo que vamos a solicitar un crédito por un monto de $150,000 con una tasa de interés anual del 12% y queremos realizar 24 pagos mensuales. La institución financiera nos proporcionó el dato de 12% de interés anual, pero para la función PAGO necesita utilizar la tasa de interés para cada período, que en este caso es mensual, así que debemos hacer la división entre 12 para obtener el resultado de 1% de interés mensual. El segundo argumento de la función es el número de mensualidades en las que pagaremos el rédito y finalmente el monto del crédito.
Función PAGOINT: Cálculo del Interés del Período
Con el uso de la función PAGOINT podemos conocer el monto correspondiente al interés mensual de cada cuota que pagamos. Emplearemos la función PAGOINT de Excel para determinar el pago de intereses. El cálculo de pago de intereses lo haremos con la función PAGOINT de Excel.
Lea también: Cómo crear una tabla contable
La fórmula en la celda utiliza las variables de los datos del préstamo, y es importante fijarlas con referencias absolutas para que no cambien al copiar la fórmula hacia abajo. Compara esta fórmula con la función PAGO de la sección anterior y verás que la única diferencia es que el segundo argumento indica el período que deseamos calcular, que en este caso es el período actual de la tabla.
Función PAGOPRIN: Cálculo del Pago de Capital
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. 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. 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. 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.
Construyendo la Tabla de Amortización en Excel
La tabla de amortización en Excel será el desglose de cada uno de los pagos mensuales para conocer el monto exacto destinado tanto al pago de intereses como al pago del capital de nuestra deuda. El primer caso que planteamos resulta también un ejemplo de tabla de amortización en Excel con cuota fija.
PASO 1: Configuración Inicial
Para crear la tabla, primero debemos definir las variables principales del préstamo: Monto del crédito, Tasa de interés y Número de pagos. Estas se ingresan en celdas designadas para fácil referencia.
Lea también: Pagos Provisionales ISR Artículo 96
PASO 2: Crear la Estructura de la Hoja de Cálculo
1. Encabezados de las columnas:
Genera encabezados para columnas como "Período", "Pago Mensual", "Interés del Período", "Pago a Capital" y "Saldo Pendiente".
2. Ingresar Datos del Préstamo:
Ingresa los valores de las variables del préstamo en las celdas correspondientes.
PASO 3: Cálculo del Pago Mensual
Utiliza la función PAGO de Excel para obtener el monto de la cuota fija mensual que se aplicará durante toda la duración del préstamo.
PASO 4: Desglose del Pago Mensual
Ahora pasamos a crear el desglose de los pagos. Para ello, generamos filas para cada período (por ejemplo, 24 períodos) y habilitamos las columnas necesarias.
1. Interés del Período:
En la columna "Interés del Período", aplica la función PAGOINT para cada período. Recuerda fijar las referencias de las variables del préstamo con referencias absolutas ($).
2. Pago del Capital:
En la columna "Pago a Capital", utiliza la función PAGOPRIN para cada período, también fijando las referencias absolutas a las variables del préstamo.
3. Saldo Pendiente:
El saldo es el monto del crédito menos la suma de todos los pagos a capital realizados hasta el momento. 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. Revisa que el saldo pendiente en el último período sea cercano a cero.
Automatización con Macros en Excel
Como tal vez ya lo imaginas, si queremos cambiar nuestra tabla de amortización para tener 36 pagos mensuales será necesario agregar manualmente los nuevos registros y copiar las fórmulas hacia abajo. Aquí es donde una macro de Excel se vuelve increíblemente útil.
Lo único que necesita hacer nuestra macro es leer los valores de las variables de entrada e insertar las fórmulas correspondientes en cada fila de acuerdo al número de pagos a realizar. Las líneas de código de VBA (Visual Basic for Applications) serán las encargadas de insertar las fórmulas que harán los cálculos. Es importante recordar que en VBA debemos utilizar el nombre de las funciones de Excel en inglés o de lo contrario obtendremos un error #¿NOMBRE? en nuestra hoja de Excel.
Con esto hemos terminado el desarrollo de una tabla de amortización en Excel que será funcional para conocer el detalle de los pagos necesarios para liquidar una deuda. Recordemos que con la función LAMBDA podemos crear nuestras propias funciones personalizadas sin necesidad de usar macros, ofreciendo otra vía para la automatización.
Ampliando la Funcionalidad y Consideraciones Avanzadas
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. 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.
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. Por último tenemos que reemplazar las fórmulas de pago de interés y pago de capital por sus cálculos conceptuales.
Prácticamente todos los modelos financieros que crees requerirán esta habilidad o alguna variación de lo que haces al construir una tabla de amortización, especialmente en el sector inmobiliario. Para comprender mejor cómo funciona el reembolso de tu hipoteca, lo ideal es recurrir a una tabla de amortización en Excel, que debe figurar en tu contrato. Los bancos proporcionan este cuadro junto con el contrato de préstamo. No obstante, si eliges contratar un seguro de vida hipotecario externo (no con el banco), éste no se incluye directamente en la tabla. El coste del seguro varía según el perfil del solicitante (edad, salud, profesión, si es fumador, etc.). Si eliges una póliza externa al banco, el TAE del seguro se convierte en un criterio de comparación clave. Antes de contratar una hipoteca, es recomendable elaborar tu propia tabla de amortización en Excel.
Si estás interesado en profundizar en el análisis financiero de préstamos inmobiliarios, te invitamos a explorar nuestros recursos adicionales. Como recurso adicional para modelar la deuda, recomendamos utilizar nuestro Creador de Tablas de Amortización Avanzadas GPT. Esta herramienta ofrece cronogramas personalizados para varias estructuras de préstamos, entradas flexibles de diferentes términos de préstamos, tasas de interés y frecuencias de pago, y un cronograma que puede actualizar automáticamente en función de los cambios en los supuestos del préstamo o los términos de refinanciamiento.
En este simulador podrás comparar varios créditos bancarios con condiciones diferentes de periodicidad y tasa de interés para decidir cuál es más conveniente financieramente. Con tu Suscripción Actualícese accede a este liquidador que contiene 2 tablas de amortización de un contrato de leasing con y sin opción de compra. Hallarás el valor de la cuota fija, partiendo del costo del contrato o valor razonable del bien, la tasa de interés y el plazo pactado.
Tutoriales y Recursos Educativos
En esta publicación, se proporcionan tutoriales en video, con las hojas de trabajo de Excel combinadas en un libro de trabajo de Excel. El primer tutorial es un vídeo “Mírame construir” sobre cómo crear una tabla de amortización totalmente dinámica y más compleja, con muchas de las características que se pueden encontrar en un modelo institucional.
¿Qué enseña el segundo video tutorial (de 90 segundos)? El segundo video es un resumen rápido que muestra cómo construir una tabla de amortización simple en Excel en menos de 90 segundos. ¿Qué diferencia hay entre los dos tutoriales? El primer tutorial es más avanzado y dinámico, adecuado para modelado institucional. ¿Puedo descargar los archivos de Excel utilizados en los videos? Sí. Los archivos de Excel se ofrecen en modalidad “Pague lo que pueda”, sin mínimo ni máximo.
Si deseas simplificar aún más el proceso de cálculo de amortización, te invitamos a descargar una plantilla de amortización en Excel. Si te interesa aprender más sobre Excel y sus aplicaciones financieras, no dudes en explorar otros artículos y recursos disponibles. Si estás viendo estos tutoriales, definitivamente encontrarás un gran valor al convertirte en miembro de El Acelerador, donde se abordan técnicas en profundidad en el programa de Modelado Financiero Inmobiliario.