Crear un calendario de amortización de préstamos en Excel – Un tutorial paso a paso

Crear un calendario de amortización de préstamos en Excel puede ser una habilidad valiosa, permitiéndote visualizar y gestionar tus pagos de préstamos de manera efectiva. Un calendario de amortización es una tabla que detalla cada pago periódico sobre un préstamo amortizable (normalmente una hipoteca o un préstamo automotriz). Desglosa cada pago en componentes de interés y principal, mostrando el saldo restante después de cada pago. Vamos a profundizar en una guía paso a paso para crear dicho calendario en Excel.

¿Qué es un calendario de amortización?

Crear un calendario de amortización en Excel

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

Crear un calendario de amortización con pagos adicionales

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

¿Qué es un calendario de amortización?

Un calendario de amortización es una tabla detallada utilizada en los cálculos de préstamos que muestra el proceso de pago de un préstamo a lo largo del tiempo. Los calendarios de amortización se utilizan comúnmente para préstamos a tasa fija como hipotecas, préstamos automotrices y préstamos personales, donde el monto del pago permanece constante durante el plazo del préstamo, pero la proporción del pago destinado al interés versus el principal cambia con el tiempo.

Para crear un calendario de amortización de préstamos en Excel, las funciones incorporadas 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 basado en 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 del principal de un pago para un período específico.

Al utilizar estas funciones en Excel, puedes crear un calendario 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 calendario de amortización en Excel

En esta sección, presentaremos dos métodos distintos para crear un calendario de amortización en Excel. Estos métodos se adaptan a diferentes preferencias y niveles de habilidad de los usuarios, asegurando que cualquiera, independientemente de su competencia con Excel, pueda construir 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 proporcionan flexibilidad para personalizar el calendario según requisitos específicos. Este enfoque es ideal para aquellos que desean tener una experiencia práctica y una visión clara de cómo se desglosa cada pago en componentes de principal e interés. Ahora, desglosaremos paso a paso el proceso de creación de un calendario de amortización en Excel:

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

1. Ingresa la información relativa al préstamo, como la tasa de interés anual, el plazo del préstamo en años, el número 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, crea 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, ingresa los números de período. Para este ejemplo, el número total de pagos es de 24 meses (2 años), por lo que ingresarás los números del 1 al 24 en la columna Período. Ver captura de pantalla:
   
4. Una vez que hayas configurado la tabla con las etiquetas y los números de período, puedes proceder a ingresar fórmulas y valores para las columnas Pago, Interés, Principal y Saldo según las especificaciones de tu préstamo.

⭐️ Paso 2: Calcular el monto total del pago usando la función PMT

La sintaxis de la función PMT es:

Ingresa la siguiente fórmula en la celda B7, y luego, arrastra el controlador de relleno hacia abajo para llenar esta fórmula en otras celdas, y verás 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: Calcular el interés usando la función IPMT

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

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

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

⭐️ Paso 4: Calcular el principal usando la función PPMT

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

La sintaxis de la función IPMT es:

La sintaxis y los parámetros para la fórmula PPMT son idénticos a los utilizados en la fórmula IPMT discutida previamente.

Ingresa la siguiente fórmula en la celda D7, y luego, arrastra el controlador de relleno hacia abajo en la columna para llenar el principal para cada período. Ver captura de pantalla:

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

⭐️ Paso 5: Calcular el saldo restante

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

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

   =B4-D7

   
2. Para el segundo y todos los períodos subsiguientes, calcula el saldo restante restando el pago principal del período actual del saldo del período anterior. Aplica la siguiente fórmula en la celda E8:

   =E7-D8

    Nota: La referencia a la celda de saldo debe ser relativa, para que se actualice a medida que arrastres la fórmula hacia abajo.
   
3. Y luego arrastra el controlador de relleno hacia abajo en la columna. Como puedes ver, cada celda se ajustará automáticamente para calcular el saldo restante basado en los pagos principales actualizados.
   

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

Después de configurar tu calendario de amortización detallado, crear un resumen del préstamo puede proporcionar una visión rápida de los aspectos clave de tu préstamo. Este resumen normalmente incluirá el costo total del préstamo, 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 simple pero completo. Ver captura de pantalla:

Descubre la Magia de Excel con Kutools AI

• Ejecución Inteligente: Realiza operaciones en celdas, analiza datos y crea gráficos, todo impulsado por comandos simples.
• Fórmulas Personalizadas: Genera fórmulas adaptadas para optimizar tus flujos de trabajo.
• Codificación VBA: Escribe e implementa código VBA sin esfuerzo.
• Interpretación de Fórmulas: Comprende fórmulas complejas con facilidad.
• Traducción de Texto: Supera las barreras del idioma dentro de tus hojas de cálculo.

Potencia tus capacidades de Excel con herramientas impulsadas por IA. ¡Descarga Ahora y experimenta una eficiencia como nunca antes!

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

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

Pero, si estás buscando crear un calendario de amortización flexible que pueda usarse repetidamente para préstamos con períodos variables, permitiéndote modificar el número de pagos según sea necesario para diferentes escenarios de préstamos, necesitarás seguir un método más detallado.

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

1. Ingresa la información relativa al préstamo, como la tasa de interés anual, el plazo del préstamo en años, el número 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, crea 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, ingresa el número más alto de pagos que podrías considerar para cualquier préstamo, por ejemplo, llena los números que van desde 1 hasta 360. Esto puede cubrir un préstamo estándar de 30 años si estás haciendo pagos mensuales.
   

⭐️ Paso 2: Modificar las fórmulas de pago, interés y principal con la función SI

Ingresa las siguientes fórmulas en las celdas correspondientes, y luego arrastra el controlador de relleno para extender estas fórmulas hacia abajo hasta el número máximo de períodos de pago que has establecido.

● Fórmula de pago:

Normalmente, usas la función PMT para calcular el pago. Para incorporar una declaración SI, la fórmula de sintaxis es:

Entonces, las fórmulas son estas:

=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, las fórmulas son estas:

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

● Fórmula de principal:

La fórmula de sintaxis es:

Entonces, las fórmulas son estas:

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

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

Para el saldo restante, normalmente restas el principal del saldo anterior. Con una declaración SI, modifícalo así:

● La primera celda de saldo:(E7)

=B4-D7

● La segunda celda de saldo:(E8)

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

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

Después de haber configurado el calendario 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, tienes un calendario de amortización completo y dinámico en Excel, completo con un resumen detallado del préstamo. Cada vez que ajustes el término del período de pago, todo el calendario de amortización se actualizará automáticamente para reflejar estos cambios. Ver la demostración a continuación:

Crear un calendario de amortización con pagos adicionales

Al realizar pagos adicionales más allá de los programados, un préstamo puede pagarse más rápidamente. Un calendario 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. Aquí te mostramos cómo configurarlo:

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

1. Ingresa la información relativa al préstamo, como la tasa de interés anual, el plazo del préstamo en años, el número 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, calcula 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 regular de pago en un préstamo asumiendo que no se realizan pagos adicionales. Aplica 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:
   • Establece 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, ingresa el número más alto de pagos que podrías considerar para cualquier préstamo. Por ejemplo, llena los números que van desde 0 hasta 360. Esto puede cubrir un préstamo estándar de 30 años si estás haciendo pagos mensuales;
   • Para el Período 0 (fila 9 en nuestro caso), recupera el valor del Saldo con esta fórmula =B4, que corresponde al monto inicial del préstamo. Todas las demás celdas en esta fila deben dejarse en blanco.
   • 

⭐️ Paso 2: Crear las fórmulas para el calendario de amortización con pagos adicionales

Ingresa las siguientes fórmulas en las celdas correspondientes una por una. Para mejorar el manejo de errores, encerramos esta y todas las fórmulas futuras dentro de la función SI.ERROR. 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:

Ingresa la siguiente fórmula en la celda B10:

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

● Calcular el pago adicional:

Ingresa la siguiente fórmula en la celda C10:

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

● Calcular el pago total:

Ingresa la siguiente fórmula en la celda D10:

=IFERROR(B10+C10, "")

● Calcular el principal:

Ingresa la siguiente fórmula en la celda E10:

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

● Calcular el interés:

Ingresa la siguiente fórmula en la celda F10:

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

● Calcular el saldo restante

Ingresa la siguiente fórmula en la celda G10:

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

Una vez que hayas completado cada una de las fórmulas, selecciona el rango de celdas B10:G10, y usa el controlador de relleno para arrastrar y extender estas fórmulas hacia abajo a través de todo el conjunto de períodos de pago. Para cualquier período no utilizado, las celdas mostrarán 0s. Ver captura de pantalla:

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

● Obtener el número de pagos programados:

=B2:B3

● Obtener el número real de pagos:

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

● Obtener el total de pagos adicionales:

=SUM(C10:C369)

● Obtener el interés total:

=SUM(F10:F369)

⭐️ Resultado:

Siguiendo estos pasos, creas un calendario de amortización dinámico en Excel que tiene en cuenta los pagos adicionales.

Crear 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 eficiente en tiempo. Excel proporciona plantillas integradas que calculan automáticamente el interés, el principal y el saldo restante de cada pago. Aquí te mostramos cómo crear un calendario de amortización utilizando una plantilla de Excel:

1. Haz clic en Archivo > Nuevo, en el cuadro de búsqueda, escribe calendario de amortización, y presiona Enter . Luego, selecciona la plantilla que mejor se adapte a tus necesidades haciendo clic en ella. Por ejemplo, aquí, seleccionaré la plantilla Simple loan calculator. Ver captura de pantalla:
   
2. Una vez que hayas seleccionado una plantilla, haz clic en el botón Crear para abrirla como un nuevo libro de trabajo.
3. Luego, ingresa los detalles de tu propio préstamo, la plantilla debería calcular automáticamente y completar el calendario basado en tus entradas.
4. Por último, guarda tu nuevo libro de trabajo del calendario de amortización.