Crear un calendario de amortización de préstamos en Excel puede ser una habilidad valiosa que le permitirá visualizar y gestionar los pagos de sus préstamos de forma eficaz. Un cronograma de amortización es una tabla que detalla cada pago periódico de un préstamo de amortización (generalmente una hipoteca o un préstamo para automóvil). Desglosa cada pago en componentes de interés y principal, mostrando el saldo restante después de cada pago. Profundicemos en una guía paso a paso para crear dicho cronograma en Excel.

¿Qué es un programa de amortización?

Crear un cronograma de amortización en Excel

Crear un cronograma de amortización para un número variable de períodos

Crea un cronograma de amortización con pagos extras

Cree un calendario de amortización (con pagos adicionales) utilizando una plantilla de Excel

¿Qué es un programa de amortización?

Un cronograma de amortización es una tabla detallada que se utiliza en los cálculos de préstamos y que muestra el proceso de liquidación de un préstamo a lo largo del tiempo. Los programas de amortización se utilizan comúnmente para préstamos a tasa fija, como hipotecas, préstamos para automóviles y préstamos personales, donde el monto del pago permanece constante durante el plazo del préstamo, pero la proporción del pago de intereses frente al principal cambia con el tiempo.

De hecho, para crear un calendario de amortización de préstamos en Excel, las funciones integradas PMT, PPMT e IPMT son cruciales. Entendamos qué hace cada función:

• Función PMT: Esta función se utiliza para calcular el pago total por período de un préstamo en base a pagos constantes y una tasa de interés constante.
• Función IPMT: Esta función calcula la parte de interés de un pago para un período determinado.
• Función PPMT: Esta función se utiliza para calcular la parte principal de un pago para un período específico.

Al utilizar estas funciones en Excel, puede crear un cronograma de amortización detallado que muestre los componentes de interés y principal de cada pago, junto con el saldo restante del préstamo después de cada pago.

Crear un cronograma de amortización en Excel

En esta sección, presentaremos dos métodos distintos para crear un cronograma de amortización en Excel. Estos métodos se adaptan a diferentes preferencias de usuario y niveles de habilidad, lo que garantiza que cualquier persona, independientemente de su dominio de Excel, pueda elaborar con éxito un calendario de amortización detallado y preciso para su préstamo.

Las fórmulas ofrecen una comprensión más profunda de los cálculos subyacentes y brindan flexibilidad para personalizar el cronograma según los requisitos específicos. Este enfoque es ideal para quienes desean tener una experiencia práctica y una visión clara de cómo se desglosa cada pago en componentes de capital e intereses. Ahora, analicemos paso a paso el proceso de creación de un cronograma de amortización en Excel:

⭐️ Paso 1: Configura la información del préstamo y la tabla de amortización

1. Ingrese la información relativa del préstamo, como la tasa de interés anual, el plazo del préstamo en años, la cantidad de pagos por año y el monto del préstamo en las celdas como se muestra en la siguiente captura de pantalla:
   
2. Luego, cree una tabla de amortización en Excel con las etiquetas especificadas, como Período, Pago, Interés, Principal, Saldo restante en las celdas A7:E7.
3. En la columna Período, ingrese los números del período. Para este ejemplo, el número total de pagos es 24 meses (2 años), por lo que ingresará los números del 1 al 24 en la columna Período. Ver captura de pantalla:
   
4. Una vez que haya configurado la tabla con las etiquetas y los números de período, puede proceder a ingresar fórmulas y valores para las columnas Pago, Interés, Principal y Saldo según los detalles específicos de su préstamo.

⭐️ Paso 2: Calcule el monto total del pago utilizando la función PAGO

La sintaxis del PMT es:

Ingrese la siguiente fórmula en la celda B7 y luego arrastre el controlador de relleno hacia abajo para completar esta fórmula en otras celdas y verá un monto de pago constante para todos los períodos. Ver captura de pantalla:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Paso 3: Calcule el interés utilizando la función IPMT

En este paso, calculará el interés para cada período de pago utilizando la función IPMT de Excel.

Ingrese la siguiente fórmula en la celda C7 y luego arrastre el controlador de relleno hacia abajo en la columna para completar esta fórmula y obtener el interés de cada período.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Paso 4: Calcule el capital usando la función PPMT

Después de calcular el interés para cada período, el siguiente paso para crear un calendario de amortización es calcular la parte principal de cada pago. Esto se hace mediante la función PPMT, que está diseñada para determinar la parte principal de un pago durante un período específico, en función de pagos constantes y una tasa de interés constante.

La sintaxis del IPMT es:

La sintaxis y los parámetros de la fórmula PPMT son idénticos a los utilizados en la fórmula IPMT analizada anteriormente.

Ingrese la siguiente fórmula en la celda D7 y luego arrastre el controlador de relleno hacia abajo en la columna para completar el capital de cada período. Ver captura de pantalla:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Paso 5: Calcula el saldo restante

Después de calcular tanto el interés como el capital de cada pago, el siguiente paso en su calendario de amortización es calcular el saldo restante del préstamo después de cada pago. Esta es una parte crucial del cronograma ya que muestra cómo el saldo del préstamo disminuye con el tiempo.

1. En la primera celda de la columna de su saldo, E7, ingrese la siguiente fórmula, lo que significa que el saldo restante será el monto del préstamo original menos la parte principal del primer pago:

   =B4-D7

   
2. Para el segundo período y todos los siguientes, calcule el saldo restante restando el pago de principal del período actual del saldo del período anterior. Aplique la siguiente fórmula en la celda E8:

   =E7-D8

    Note: La referencia a la celda de saldo debe ser relativa, por lo que se actualiza a medida que arrastra la fórmula hacia abajo.
   
3. Y luego arrastre el controlador de relleno hacia la columna. Como puede ver, cada celda se ajustará automáticamente para calcular el saldo restante en función de los pagos de principal actualizados.
   

⭐️ Paso 6: Haz un resumen del préstamo

Después de configurar su cronograma de amortización detallado, crear un resumen del préstamo puede brindarle una descripción general rápida de los aspectos clave de su préstamo. Este resumen normalmente incluirá el costo total del préstamo y el interés total pagado.

● Para calcular los pagos totales:

=SUM(B7:B30)

● Para calcular el interés total:

=SUM(C7:C30)

⭐️ Resultado:

Ahora se ha creado con éxito un calendario de amortización de préstamos sencillo pero completo. ver captura de pantalla:

Crear un cronograma de amortización para un número variable de períodos

En el ejemplo anterior, creamos un calendario de pagos de préstamos para una cantidad fija de pagos. Este enfoque es perfecto para manejar un préstamo o hipoteca específico donde los términos no cambian.

Pero, si está buscando crear un cronograma de amortización flexible que pueda usarse repetidamente para préstamos con diferentes períodos, permitiéndole modificar la cantidad de pagos según sea necesario para diferentes escenarios de préstamos, deberá seguir un método más detallado.

⭐️ Paso 1: Configura la información del préstamo y la tabla de amortización

1. Ingrese la información relativa del préstamo, como la tasa de interés anual, el plazo del préstamo en años, la cantidad de pagos por año y el monto del préstamo en las celdas como se muestra en la siguiente captura de pantalla:
   
2. Luego, cree una tabla de amortización en Excel con las etiquetas especificadas, como Período, Pago, Interés, Principal, Saldo restante en las celdas A7:E7.
3. En la columna Período, ingrese la cantidad más alta de pagos que podría considerar para cualquier préstamo; por ejemplo, complete los números que van del 1 al 360. Esto puede cubrir un préstamo estándar a 30 años si realiza pagos mensuales.
   

⭐️ Paso 2: Modificar las fórmulas de pago, intereses y principio con la función SI

Ingrese las siguientes fórmulas en las celdas correspondientes y luego arrastre el controlador de relleno para extender estas fórmulas hasta la cantidad máxima de períodos de pago que haya establecido.

● Fórmula de pago:

Normalmente, utiliza la función PAGO para calcular el pago. Para incorporar una declaración IF, la fórmula de sintaxis es:

Entonces la fórmula es esta:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Fórmula de interés:

La fórmula de sintaxis es:

Entonces la fórmula es esta:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Fórmula principal:

La fórmula de sintaxis es:

Entonces la fórmula es esta:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Paso 3: Ajusta la fórmula del saldo restante

Para el saldo restante, normalmente puedes restar el capital del saldo anterior. Con una declaración IF, modifíquela como:

● La primera celda de equilibrio:(E7)

=B4-D7

● La segunda celda de equilibrio: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Paso 4: Haz un resumen del préstamo

Una vez que haya configurado el cronograma de amortización con las fórmulas modificadas, el siguiente paso es crear un resumen del préstamo.

● Para calcular los pagos totales:

=SUM(B7:B366)

● Para calcular el interés total:

=SUM(C7:C366)

⭐️ Resultado:

Ahora tiene un cronograma de amortización completo y dinámico en Excel, completo con un resumen detallado del préstamo. Siempre que ajuste el plazo del período de pago, todo el calendario de amortización se actualizará automáticamente para reflejar estos cambios. Vea la demostración a continuación:

Crea un cronograma de amortización con pagos extras

Al realizar pagos adicionales a los programados, se puede liquidar un préstamo más rápidamente. Un cronograma de amortización que incorpora pagos adicionales, cuando se crea en Excel, demuestra cómo estos pagos adicionales pueden acelerar el pago del préstamo y disminuir el interés total pagado. Así es como puedes configurarlo:

⭐️ Paso 1: Configura la información del préstamo y la tabla de amortización

1. Ingrese la información relativa del préstamo, como la tasa de interés anual, el plazo del préstamo en años, la cantidad de pagos por año, el monto del préstamo y el pago adicional en las celdas como se muestra en la siguiente captura de pantalla:
   
2. Luego, calcule el pago programado.
   Además de las celdas de entrada, se necesita otra celda predefinida para nuestros cálculos posteriores: el monto del pago programado. Este es el monto de pago regular de un préstamo, suponiendo que no se realicen pagos adicionales. Aplique la siguiente fórmula en la celda B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Luego, crea una tabla de amortización en Excel:
   • Establezca las etiquetas especificadas, como Período, Pago programado, Pago adicional, Pago total, Interés, Principal, Saldo restante en las celdas A8:G8;
   • En la columna Período, ingrese la cantidad más alta de pagos que podría considerar para cualquier préstamo. Por ejemplo, complete los números que van del 0 al 360. Esto puede cubrir un préstamo estándar a 30 años si realiza pagos mensuales;
   • Para el Período 0 (fila 9 en nuestro caso), recupere el valor del Saldo con esta fórmula +/- 4 KHz, que corresponde al monto inicial del préstamo. Todas las demás celdas de esta fila deben dejarse en blanco.
   • 

⭐️ Paso 2: Crea las fórmulas para el cronograma de amortización con pagos extras

Ingrese las siguientes fórmulas en las celdas correspondientes una por una. Para mejorar el manejo de errores, incluimos esta y todas las fórmulas futuras dentro de la función SIERROR. Este enfoque ayuda a evitar múltiples errores potenciales que podrían surgir si alguna de las celdas de entrada se deja en blanco o contiene valores incorrectos.

● Calcular el pago programado:

Ingrese la siguiente fórmula en la celda B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Calcular el pago extra:

Ingrese la siguiente fórmula en la celda C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Calcular el pago total:

Ingrese la siguiente fórmula en la celda D10:

=IFERROR(B10+C10, "")

● Calcular el principal:

Ingrese la siguiente fórmula en la celda E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Calcular el interés:

Ingrese la siguiente fórmula en la celda F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Calcular el saldo restante

Ingrese la siguiente fórmula en la celda G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Una vez que haya completado cada una de las fórmulas, seleccione el rango de celdas B10:G10 y use el controlador de relleno para arrastrar y extender estas fórmulas a lo largo de todo el conjunto de períodos de pago. Para cualquier período no utilizado, las celdas mostrarán 0. Ver captura de pantalla:

⭐️ Paso 3: Haz un resumen del préstamo

● Obtener la cantidad programada de pagos:

=B2:B3

● Obtener el número real de pagos:

=COUNTIF(D10:D369,">"&0)

● Obtenga pagos adicionales totales:

=SUM(C10:C369)

● Obtenga interés total:

=SUM(F10:F369)

⭐️ Resultado:

Si sigue estos pasos, creará un cronograma de amortización dinámico en Excel que contabiliza los pagos adicionales.

Cree un calendario de amortización utilizando una plantilla de Excel

Crear un calendario de amortización en Excel utilizando una plantilla es un método sencillo y que ahorra tiempo. Excel proporciona plantillas integradas que calculan automáticamente el interés, el capital y el saldo restante de cada pago. A continuación se explica cómo crear un calendario de amortización utilizando una plantilla de Excel:

1. Haga Clic en Archive > Nuevo, en el cuadro de búsqueda, escriba plan de amortizacióny presione Participar llave. Luego, selecciona la plantilla que mejor se ajuste a tus necesidades haciendo clic sobre ella. Por ejemplo, aquí seleccionaré la plantilla de calculadora de préstamos simple. Ver captura de pantalla:
   
2. Una vez que haya seleccionado una plantilla, haga clic en el Crear para abrirlo como un nuevo libro.
3. Luego, ingrese los detalles de su propio préstamo, la plantilla debería calcular y completar automáticamente el cronograma en función de sus entradas.
4. Por último, guarde su nuevo libro de calendario de amortización.