KutoolsforOffice — Una solución, cinco potentes herramientas.Lograr más con menos esfuerzo.Venta de marzo: 20 % de descuento

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

AutorXiaoyang Fecha de modificación

Crear un calendario de amortización de préstamos en Excel puede ser una habilidad muy valiosa, ya que te permite visualizar y gestionar con eficacia tus pagos. Un calendario de amortización es una tabla que detalla cada pago periódico de un préstamo amortizable —normalmente una hipoteca o un préstamo para automóvil— desglosando cada cuota en sus componentes de interés y capital, y mostrando el saldo pendiente tras cada pago. A continuación, te presentamos una guía paso a paso para crear este tipo de calendario en Excel.

Crear un calendario de amortización de préstamos

¿Qué es un cuadro de amortización?

Crear un cuadro de amortización en Excel

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

Crear un cuadro de amortización con pagos adicionales

Crear un cuadro de amortización (con pagos adicionales) mediante una plantilla de Excel

¿Qué es un cuadro de amortización?

Un calendario de amortización es una tabla detallada utilizada en los cálculos de préstamos que muestra cómo se amortiza un préstamo a lo largo del tiempo. Los calendarios de amortización se utilizan comúnmente para préstamos a tipo fijo, como hipotecas, préstamos para automóviles y préstamos personales, en los que el importe del pago permanece constante durante todo el plazo del préstamo, aunque la proporción destinada a intereses frente al capital varía con el tiempo.

Para crear un calendario de amortización de préstamos en Excel, las funciones integradas PAGO, PAGOPRIN y PAGOINT son esenciales. Veamos qué hace cada una:

  • Función PAGO: Esta función calcula el pago periódico de un préstamo con pagos constantes y una tasa de interés fija.
  • Función PAGOINT: Calcula la parte proporcional de interés de un pago correspondiente a un período específico.
  • Función PAGOPRIN: Esta función calcula la parte del capital correspondiente a un pago en un período específico.

Al utilizar estas funciones en Excel, podrá crear un calendario de amortización detallado que muestre los componentes de interés y capital de cada pago, así como el saldo pendiente del préstamo tras cada uno de ellos.

Crear un cuadro de amortización en Excel

En esta sección presentamos dos métodos distintos para crear un calendario de amortización en Excel, adaptados a diferentes preferencias y niveles de competencia. Así, cualquier usuario —independientemente de su dominio de Excel— podrá 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 brindan la flexibilidad necesaria para adaptar el calendario a requisitos específicos. Este enfoque es ideal para quienes buscan una experiencia práctica y una visión clara de cómo se desglosa cada pago entre capital e intereses. A continuación, te guiaremos paso a paso en la creación de un calendario de amortización en Excel:

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

  1. Introduzca 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 importe del préstamo en las celdas, tal como se muestra en la siguiente captura de pantalla:
    Introduzca la información relativa al préstamo
  2. A continuación, cree en Excel una tabla de amortización con las etiquetas indicadas: Período, Pago, Interés, Capital y Saldo pendiente, en las celdas A7:E7.
  3. En la columna Período, introduzca los números de período. En este ejemplo, el número total de pagos abarca 24 meses (2 años), así que ingrese los números del 1 al 24 en la columna Período. Consulte la captura de pantalla:
    introduzca los números de período
  4. Una vez que haya configurado la tabla con las etiquetas y los números de período, podrá introducir fórmulas y valores en las columnas Pago, Interés, Capital y Saldo según las características específicas de su préstamo.

⭐️ Paso 2: Calcule el importe total del pago mediante la función PAGO

La sintaxis de la función PAGO es:

= -PAGO()tasa de interés por período,número total de pagos, importe del préstamo)
  • tipo de interés por período: Si el tipo de interés de su préstamo es anual, divídalo entre el número de pagos al año. Por ejemplo, si el tipo anual es del 5 % y los pagos son mensuales, el tipo por período será del 5 %/12. En este ejemplo, el tipo se mostrará como B1/B3.
  • número total de pagos: Multiplique el plazo del préstamo en años por el número de pagos anuales. En este ejemplo, se muestra como B2*B3.
  • Importe del préstamo: Este es el capital que ha solicitado. En este ejemplo, corresponde a la celda B4.
  • signo negativo (-): La función PMT devuelve un número negativo porque representa un pago saliente. Puedes añadir un signo negativo delante de la función PMT para mostrar el pago como un número positivo.

Introduzca la siguiente fórmula en la celda B7 y, a continuación, arrastre el controlador de relleno hacia abajo para aplicarla al resto de las celdas; obtendrá un importe de pago constante en todos los períodos. Vea la captura de pantalla:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)
Nota: Aquí, utilice referencias absolutasen la fórmula para que, al copiarla en las celdas inferiores, permanezca igual sin cambios.

Calcular el importe total del pago mediante la función PAGO

⭐️ Paso 3: Calcule los intereses mediante la función PAGOINT

En este paso, calculará los intereses de cada período de pago mediante la función PAGOINT de Excel.

= -INT.PAGO.PER()tasa de interés por período,período específico,número total de pagos, importe del préstamo)
  • tipo de interés por período: Si el tipo de interés de su préstamo es anual, divídalo entre el número de pagos al año. Por ejemplo, si el tipo anual es del 5 % y los pagos son mensuales, el tipo por período será del 5 %/12. En este ejemplo, el tipo se mostrará como B1/B3.
  • período específico: El período concreto para el que desea calcular los intereses. Normalmente empieza en 1 en la primera fila de su tabla y aumenta progresivamente en cada fila siguiente. En este ejemplo, el período comienza en la celda A7.
  • número total de pagos: Multiplique el plazo del préstamo en años por el número de pagos anuales. En este ejemplo, se muestra como B2*B3.
  • Importe del préstamo: Este es el capital que ha solicitado. En este ejemplo, corresponde a la celda B4.
  • signo negativo (-): La función PMT devuelve un número negativo porque representa un pago saliente. Añada un signo negativo delante de la función PMT para mostrar el pago como un número positivo.

Introduzca la siguiente fórmula en la celda C7 y, a continuación, arrastre el controlador de relleno hacia abajo por la columna para aplicarla a las celdas siguientes y obtener los intereses correspondientes a cada período.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)
Nota: En la fórmula anterior, A7 se establece como una referencia relativa, lo que garantiza que se adapte dinámicamente a la fila específica a la que se extienda la fórmula.

Calcular los intereses mediante la función PAGOINT

⭐️ Paso 4: Calcule el capital mediante la función PAGOPRIN

Tras calcular los intereses de cada período, el siguiente paso para crear un calendario de amortización consiste en determinar la parte del capital correspondiente a cada pago. Para ello, se utiliza la función PAGOPRIN, diseñada específicamente para calcular la porción de capital de un pago en un período determinado, considerando pagos constantes y una tasa de interés fija.

La sintaxis de la función PAGOINT es:

= -PAGO.PRINC.PER()tasa de interés por período,período específico,número total de pagos, importe del préstamo)

La sintaxis y los parámetros de la fórmula PAGOPRIN son idénticos a los de la fórmula PAGOINT descrita anteriormente.

Introduzca la siguiente fórmula en la celda D7 y, a continuación, arrastre el controlador de relleno hacia abajo por la columna para completar el capital correspondiente a cada período. Consulte la captura de pantalla:

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

Calcular el capital mediante la función PAGOPRIN

⭐️ Paso 5: Calcule el saldo pendiente

Después de calcular tanto los intereses como el capital de cada pago, el siguiente paso en su calendario de amortización es calcular el saldo pendiente del préstamo tras cada pago. Esta es una parte fundamental del calendario, ya que muestra cómo disminuye el saldo del préstamo con el tiempo.

  1. En la primera celda de la columna de saldo – E7, introduzca la siguiente fórmula, que significa que el saldo pendiente será el importe original del préstamo menos la parte del capital del primer pago:
    =B4-D7
    Calcular el saldo pendiente
  2. Para el segundo período y todos los posteriores, calcula el saldo pendiente restando el pago de capital del período actual al saldo del período anterior. Aplica la siguiente fórmula en la celda E8:
    =E7-D8
    Nota: La referencia a la celda del saldo debe ser relativa, para que se actualice automáticamente al arrastrar la fórmula hacia abajo.
    Calcular el saldo pendiente
  3. A continuación, arrastre el controlador de relleno hacia abajo por la columna. Como podrá observar, cada celda ajustará automáticamente el cálculo del saldo pendiente en función de los pagos de capital actualizados.
    arrastre el controlador de relleno hacia abajo por la columna

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

Tras configurar su calendario de amortización detallado, elaborar un resumen del préstamo le ofrece una visión rápida de los aspectos clave: el coste total del préstamo y el importe total de los intereses pagados.

● Para calcular el total de pagos:

=SUM(B7:B30)

● Para calcular el total de intereses:

=SUM(C7:C30)

Crear un resumen del préstamo

⭐️ Resultado:

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

se ha creado un calendario de amortización de préstamo sencillo

una captura de pantalla de kutools for excel ia

Descubra la magia de Excel con KUTOOLS AI

  • Ejecución inteligente: Realice operaciones en celdas, analice datos y cree gráficos con comandos sencillos.
  • fórmulas personalizadas: Cree fórmulas a medida para optimizar sus flujos de trabajo.
  • Programación en VBA: Escriba e implemente código VBA sin esfuerzo.
  • Interpretación de fórmulas: Comprenda con facilidad fórmulas complejas.
  • Traducción de texto: Rompa las barreras del idioma directamente en sus hojas de cálculo.
Potencie sus capacidades en Excel con herramientas impulsadas por inteligencia artificial.Descargar ahora¡y experimente una eficiencia sin precedentes!

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

En el ejemplo anterior, creamos un calendario de pagos de préstamo para un número fijo de cuotas. Este enfoque es ideal para gestionar un préstamo o hipoteca concretos cuyos términos permanecen inalterados.

Sin embargo, si desea crear un calendario de amortización flexible que pueda reutilizarse fácilmente para préstamos con distintos plazos y le permita ajustar el número de pagos según las necesidades de diversos escenarios de préstamo, deberá seguir un método más detallado.

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

  1. Introduzca 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 importe del préstamo en las celdas, tal como se muestra en la siguiente captura de pantalla:
    Introduzca la información relativa al préstamo
  2. A continuación, cree en Excel una tabla de amortización con las etiquetas indicadas: Período, Pago, Interés, Capital y Saldo pendiente, ubicadas en las celdas A7:E7.
  3. En la columna Período, introduzca el número máximo de pagos que podría considerar para cualquier préstamo; por ejemplo, rellene con números del 1 al 360. Esto cubre un préstamo estándar a 30 años si realiza pagos mensuales.
    introduzca el número máximo de pagos

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

Introduzca las siguientes fórmulas en las celdas correspondientes y, a continuación, arrastre el controlador de relleno para extenderlas hasta el número máximo de períodos de pago que haya establecido.

● Fórmula del pago:

Normalmente, se utiliza la función PAGO para calcular el pago. Para incluir una instrucción SI, la sintaxis de la fórmula es:

=SI()período actual<=número total de períodos, -PAGO()tasa de interés por período,número total de períodos, importe del préstamo), «» )

Por tanto, la fórmula es la siguiente:

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

● Fórmula del interés:

La fórmula sintáctica es:

=SI()período actual<=número total de períodos, -INT.PAGO.PER()tasa de interés por período,período actual,número total de períodos, importe del préstamo), «» )

Por tanto, la fórmula es la siguiente:

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

● Fórmula del capital:

La fórmula sintáctica es:

=SI()período actual<=número total de períodos, -PAGO.PRINC.PER()tasa de interés por período,período actual,número total de períodos, importe del préstamo), «» )

Por tanto, la fórmula es la siguiente:

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

Modifique las fórmulas de pago, interés y capital con la función SI

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

Para el saldo restante, normalmente se resta el capital del saldo anterior. Modifíquelo con una instrucción SI de la siguiente manera:

● Primera celda de saldo: (E7)

=B4-D7

● Segunda celda de saldo: (E8)

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

Ajustar el saldo pendiente

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

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

● Para calcular el total de pagos:

=SUM(B7:B366)

● Para calcular el total de intereses:

=SUM(C7:C366)

Crear un resumen del préstamo

⭐️ Resultado:

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

Crear un cuadro de amortización con pagos adicionales

Al realizar pagos adicionales más allá de los programados, puedes liquidar tu préstamo más rápidamente. Un calendario de amortización en Excel que incluya estos pagos extra te muestra claramente cómo aceleran la cancelación del préstamo y reducen el interés total pagado. A continuación, te explicamos cómo configurarlo:

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

  1. Introduzca 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 importe del préstamo y el pago adicional en las celdas, tal como se muestra en la siguiente captura de pantalla:
    Introduzca la información relativa al préstamo
  2. A continuación, calcule el pago programado.
    Además de las celdas de entrada, necesitará otra celda predefinida para los cálculos posteriores: el importe del pago programado. Este corresponde al pago regular de un préstamo, asumiendo que no se realizan pagos adicionales. Aplique la siguiente fórmula en la celda B6:
    =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

    calcular el pago programado
  3. Luego, cree una tabla de amortización en Excel:
    • Establezca las etiquetas especificadas, como Período, Pago programado, Pago adicional, Pago total, Interés, Capital y Saldo pendiente en las celdas A8:G8;
    • En la columna Período, introduzca el número máximo de pagos que podría considerar para cualquier préstamo. Por ejemplo, rellene con valores comprendidos entre 0 y 360, lo que cubriría un préstamo estándar a 30 años con pagos mensuales.
    • Para el período 0 (fila 9 en nuestro caso), obtén el valor del saldo con esta fórmula: =B4, que corresponde al importe inicial del préstamo. Deja todas las demás celdas de esta fila en blanco.
    • crear una tabla de amortización

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

Introduzca las siguientes fórmulas en las celdas correspondientes, una por una. Para mejorar la gestión de errores, envuelva esta fórmula y todas las posteriores en la función SIERRORES. Este enfoque evita errores potenciales que podrían surgir si alguna celda de entrada está vacía o contiene valores incorrectos.

● Calcular el pago programado:

Introduzca la siguiente fórmula en la celda B10:

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

Calcular el pago programado

● Calcular el pago adicional:

Introduzca la siguiente fórmula en la celda C10:

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

Calcular el pago adicional

● Calcular el pago total:

Introduzca la siguiente fórmula en la celda D10:

=IFERROR(B10+C10, "")

Calcular el pago total

● Calcular el capital:

Introduzca la siguiente fórmula en la celda E10:

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

Calcular el capital

● Calcular el interés:

Introduzca la siguiente fórmula en la celda F10:

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

Calcular los intereses

● Calcular el saldo restante

Introduzca la siguiente fórmula en la celda G10:

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

Calcular el saldo pendiente

Una vez completadas todas las fórmulas, seleccione el rango de celdas B10:G10 y arrástrelo mediante el controlador de relleno para extenderlo a todos los períodos de pago. En los períodos no utilizados, las celdas mostrarán 0. Vea la captura de pantalla:
use el controlador de relleno para arrastrar y extender estas fórmulas hacia abajo

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

● Obtener el número programado de pagos:

=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)

Crear un resumen del préstamo

⭐️ Resultado:

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

Crear un calendario de amortización mediante una plantilla de Excel

Crear un calendario de amortización en Excel mediante una plantilla es una forma sencilla y eficaz de ahorrar tiempo. Excel incluye plantillas integradas que calculan automáticamente el interés, el capital y el saldo pendiente de cada pago. A continuación, te explicamos cómo crear un calendario de amortización utilizando una plantilla de Excel:

  1. Haga clic en Archivo > Nuevo, escriba calendario de amortización en el cuadro de búsqueda y pulse la tecla Intro. A continuación, seleccione la plantilla que mejor se adapte a sus necesidades haciendo clic en ella. Por ejemplo, aquí elegiremos la plantilla Calculadora sencilla de préstamos. Vea la captura de pantalla:
    Crear un calendario de amortización mediante plantilla
  2. Una vez que haya seleccionado una plantilla, haga clic en el botón Crear para abrirla como un archivo de nuevo libro de trabajo.
  3. A continuación, introduzca los datos de su préstamo; la plantilla calculará y rellenará automáticamente el calendario en función de sus entradas.
  4. Por último, guarde su nuevo libro de trabajo del calendario de amortización.