Cómo crear un plan de amortización en Excel: Explicación de los pasos

Un plan de amortización proporciona una hoja de ruta financiera clara, mostrando cómo se divide cada pago del préstamo entre intereses y capital hasta que tu deuda esté totalmente saldada.

Con las funciones integradas de Excel, puedes crear planes de amortización detallados para visualizar el ciclo de vida de tu préstamo, tomar decisiones financieras con conocimiento de causa e incluso explorar opciones de amortización anticipada.

En este artículo, aprenderás a construir un plan de amortización completo en Excel desde cero. Cubriremos funciones esenciales como PMT(), IPMT() y PPMT(), te guiaremos por el proceso paso a paso y exploraremos escenarios prácticos para mejorar tu conjunto de herramientas de planificación financiera.

¿Qué es exactamente un plan de amortización?

Un plan de amortización es una tabla completa de pagos periódicos del préstamo, que muestra la cantidad de capital e intereses que comprende cada pago hasta que el préstamo se liquida al final de su plazo.

Cuando pides un préstamo, varios elementos clave determinan cómo funcionan tus pagos:

• Director: La cantidad original prestada que debes devolver
• Interés: El coste de pedir prestado el dinero, calculado como porcentaje del principal
• Periodo depago: Con qué frecuencia realizas los pagos (mensual, quincenal, etc.)
• Plazodel préstamo: El plazo total en el que devolverás el préstamo

En la mayoría de los préstamos estándar -como hipotecas, préstamos para automóviles y préstamos personales- las cuotas se mantienen constantes durante todo el plazo. Sin embargo, lo que cambia con cada pago es la proporción que se destina a intereses frente al principal. En las primeras etapas, un porcentaje mayor de cada pago cubre los intereses. A medida que avanzas en el plazo del préstamo, una mayor parte de cada pago reduce el saldo principal.

El objetivo principal de crear un plan de amortización es proporcionar transparencia a tu préstamo. Te permite ver exactamente cuántos intereses pagarás a lo largo del tiempo, con qué rapidez estás acumulando capital y cómo podrían afectar los pagos adicionales al plazo de tu préstamo. Para las empresas, estos anexos también sirven para la planificación contable y fiscal.

Las funciones y fórmulas de Excel que debes conocer

Excel no tiene incorporada ninguna función de "amortización". En lugar de eso, normalmente elaboras un plan de amortización con funciones financieras específicas que trabajan juntas para calcular distintos aspectos de los pagos de tu préstamo. Éstas son las funciones clave que necesitarás:

Función PMT()

La función PMT() calcula el pago de un préstamo basándose en pagos constantes y un tipo de interés constante:

=PMT(rate, nper, pv, [fv], [type])

• rate: El tipo de interés por periodo (tipo anual dividido por la frecuencia de pago)

• nper: El número total de periodos de pago

• pv: El valor actual (importe del préstamo)

• [fv]: Valor futuro opcional (normalmente 0 para préstamos pagados en su totalidad)

• [type]: Opcional; 0 para pagos al final del período, 1 para pagos al principio del período

Para calcular la cuota mensual de una hipoteca de 250.000 $ al 4,5% de interés anual durante 30 años: 

=PMT(4.5%/12, 30*12, 250000)

El resultado es -1.266,71 $, que representa la salida de efectivo para el prestatario.

Para una explicación detallada de la función PMT(), incluyendo errores comunes y ejemplos de uso avanzado, puedes consultar nuestra completa guía PMT() de Excel.

Función IPMT()

La función IPMT() calcula la parte de intereses de un pago concreto:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: El periodo de pago específico que estás calculando

El parámetro per es esencial, ya que determina el importe de los intereses del periodo de pago que estás calculando: a diferencia de la función PMT(), que da el mismo importe de pago para todos los periodos, los resultados de IPMT() varían significativamente dependiendo del periodo que especifiques, ya que las porciones de interés disminuyen a lo largo del plazo del préstamo.

Esto muestra exactamente qué parte de un pago determinado se destina a intereses.

Función PPMT()

La función PPMT() calcula la parte de capital de un pago concreto:

=PPMT(rate, per, nper, pv, [fv], [type])

Al igual que en IPMT(), el importe del principal varía según el periodo, de modo que los primeros pagos tienen porciones de principal más pequeñas y los últimos más grandes. Muestra en qué medida un pago determinado reduce el saldo de tu préstamo.

Fórmulas personalizadas

Para cada periodo de pago, querrás hacer un seguimiento:

Saldo restante: El importe pendiente del préstamo después de cada pago

Remaining balance = Previous balance - Current principal payment

Capital acumulado: Total principal pagado hasta la fecha

Cumulative principal = Previous cumulative principal + current principal payment

Interés acumulado: Intereses totales pagados hasta la fecha

Formula: Previous cumulative interest + current interest payment

Función redondear()

Incluso los pequeños errores de redondeo pueden agravarse con el tiempo en un plan de amortización:

=ROUND(value, 2)

Esto garantiza que todos los valores monetarios se redondeen al céntimo más próximo.

Referencias absolutas vs. relativas

Cuando construyas tu tabla de amortización, necesitarás

• Utiliza referencias absolutas ($) para valores fijos como el tipo de interés y el importe del préstamo

• Utiliza referencias relativas para valores que cambian cada periodo

La fórmula siguiente, por ejemplo, utiliza referencias absolutas para el tipo de interés, el plazo del préstamo y el importe, pero una referencia relativa para el periodo de pago:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Guía paso a paso para crear el Plan de Amortización

Ahora que entiendes las funciones clave, vamos a construir un plan de amortización desde cero. Este recorrido creará una tabla de pagos de préstamo completa que realiza un seguimiento de cada pago a lo largo de la vida de tu préstamo.

1. Introducir datos del préstamo

Introduce los datos de tu préstamo en las casillas correspondientes, como se muestra en el ejemplo siguiente:

• Importe del préstamo: 250.000 $ (celda B1)
• Tipo de interés anual: 4,5% (celda B2)
• Plazo del préstamo: 30 años (celda B3)
• Frecuencia de pago: Mensualmente (celda B4)

Configurar los parámetros de entrada del préstamo. Imagen del autor. 

2. Cálculo de la cuota mensual

En la celda B5, calcula la mensualidad utilizando la función PMT: =PMT(B2/12, B3*12, B1)

Esta fórmula devuelve -1.266,71 $, que representa tu pago mensual. El signo negativo indica que se trata de un pago (salida de efectivo).

Configurar los encabezados de columna y las fórmulas. Imagen del autor.

3. Crear la primera fila del programa

A continuación, podemos crear las siguientes cabeceras y trabajar con la primera fila de datos (filas 7 y 8):  

• Periodo: Introduce 1 en la celda A8

• Importe del pago: =$B$5 en la celda B8 (haciendo referencia al pago mensual calculado)

• Pago de intereses: =IPMT($B$2/12, A8, $B$3*12, $B$1) en la celda C8

• Pago principal: =PPMT($B$2/12, A8, $B$3*12, $B$1) en la celda D8

• Saldo restante: =$B$1+D8 en la celda E8 (préstamo original más pago del principal, lo que funciona porque D8 devuelve un valor negativo, restando efectivamente el importe del principal del préstamo)

• Principal acumulado: =D8 en la celda F8 (igual que el pago del principal de la primera fila)

• Interés acumulado: =C8 en la celda G8 (igual que el pago de intereses de la primera fila)

Muestra el desglose del primer pago. Imagen del autor.

4. Completar el programa

Para la segunda fila (fila 9):

• Período: =A8+1 en la celda A9 (incrementando en 1)

• Importe del pago: Copia la fórmula de B8 a B9

• Pago de intereses: =IPMT($B$2/12, A9, $B$3*12, $B$1) en la celda C9

• Pago principal: =PPMT($B$2/12, A9, $B$3*12, $B$1) en la celda D9

• Saldo restante: =E8+D9 en la celda E9 (saldo anterior más capital actual)

• Principal acumulado: =F8+D9 en la celda F9 (total anterior más principal actual)

• Interés acumulado: =G8+C9 en la celda G9 (total anterior más intereses actuales)

Ten en cuenta que tanto en la función IPMT() como en PPMT(), A9 se utiliza como referencia relativa (sin signos de dólar), lo que significa que cuando copies la fórmula hacia abajo, se actualizará automáticamente en A10, A11, etc., calculando cada vez el periodo correcto.

Configurar fórmulas que se puedan copiar . Imagen del autor.

Los primeros pagos del plan de amortización. Imagen del autor.

Selecciona todas las celdas de la fila 9 y arrástralas hasta la fila 367 (para un préstamo mensual a 30 años con 360 cuotas).

A medida que avanzas en el plazo del préstamo, una mayor parte de cada pago se destina al principal en lugar de a los intereses. Imagen del autor.

5. Verificación del pago final

En un plan de amortización correctamente construido, el pago final debe llevar el saldo restante exactamente a cero, y el capital acumulado debe ser igual al importe original del préstamo.

El pago final hace que el saldo restante sea exactamente cero. Imagen del autor.

Como puedes ver en la última fila, el saldo restante es de 0,00 $ (resaltado), y el capital acumulado es igual a 250.000 $, que coincide con el importe de nuestro préstamo original. Esto confirma que nuestro plan de amortización es exacto.

Al verificar el saldo final, puede ser útil aplicar la función ROUND() a los cálculos clave. Las pequeñas discrepancias decimales debidas a la precisión de cálculo de Excel pueden hacer que el saldo final muestre una pequeña cantidad residual en lugar de exactamente cero. La utilización de =ROUND(value, 2) garantiza que todos los valores monetarios sean coherentes con las normas habituales de información financiera.

Cosas adicionales que podrías tener en cuenta

Un horario estándar es el punto de partida. Ten en cuenta estas cosas para que tu modelo sea más flexible:

Gestionar las variaciones de pago

Los planes de amortización adquieren aún más valor cuando se personalizan para explorar distintos escenarios de pago. Para modelar los pagos suplementarios, crea una celda de entrada adicional para los pagos suplementarios y modifica tu fórmula de pago del principal para incluir esta cantidad. Este enfoque ayuda a visualizar cómo los pagos adicionales reducen tanto el plazo del préstamo como el interés total pagado. Para préstamos con pagos globales o tipos de interés variables, considera la posibilidad de crear secciones separadas en tu calendario que reflejen los cambios de tipo en momentos concretos. Puedes utilizar el Gestor de Escenarios de Excel para comparar distintos entornos de tarifas y su impacto en tus costes totales.

Mejorar la legibilidad y el análisis

Un formato adecuado transforma tu plan de amortización de una tabla básica en una herramienta financiera perspicaz. Aplica un formato de moneda con decimales coherentes a todos los valores monetarios, y utiliza un formato condicional para resaltar los hitos clave, como cuando hayas pagado la mitad del capital o alcanzado umbrales de patrimonio específicos. También es importante entender cómo afectan a tus pagos los distintos métodos de devengo de intereses. El devengo diario (habitual en los préstamos estudiantiles) calcula los intereses basándose en un año de 365 días, mientras que muchas hipotecas utilizan un método de devengo mensual. Documentar qué método utiliza tu programa garantiza la exactitud al compararlo con los extractos reales del préstamo.

Personalización para tipos de préstamo específicos

Los distintos préstamos tienen características únicas que pueden requerir ajustes en tu plantilla básica de amortización. Para las hipotecas con cuentas de garantía bloqueada, añade columnas para hacer un seguimiento de los componentes de seguros e impuestos junto con el capital y los intereses. Para los préstamos con amortización negativa (en los que los pagos no cubren los intereses devengados), modifica tu cálculo del saldo restante para añadir los intereses impagados al capital. La opción de calendario de pagos de las funciones financieras de Excel (parámetrotype ) te permite modelar los pagos al principio o al final de los periodos, lo que resulta especialmente útil para los arrendamientos y algunos préstamos comerciales en los que las convenciones de calendario difieren de los préstamos al consumo estándar.

Conclusión

Crear un plan de amortización en Excel te proporciona una valiosa información sobre tus préstamos que va más allá de lo que proporcionan la mayoría de los prestamistas. Creando tu propio calendario, puedes ver exactamente cómo afecta cada pago a tu saldo, comprender el verdadero coste del préstamo a lo largo del tiempo y tomar decisiones informadas sobre las opciones de pago anticipado. Las habilidades desarrolladas mediante este proceso -utilizar las funciones financieras de Excel, crear fórmulas dinámicas y diseñar tablas informativas- van mucho más allá del análisis de préstamos y pueden aplicarse a muchos aspectos de la planificación financiera personal y empresarial.

Si quieres ir más allá de la amortización y construir modelos financieros más completos -incluyendo la previsión de ingresos, la evaluación de inversiones y la ejecución de escenarios hipotéticos-, consulta nuestro curso Modelización financiera en Excel. Es un gran paso para reforzar tus habilidades con las hojas de cálculo para el análisis financiero. Y recuerda, cuanto más cómodo te sientas con estos cálculos financieros, mejor posicionado estarás para tomar el control de tu futuro financiero, tanto si gestionas deudas personales como si planificas inversiones empresariales o asesoras a clientes sobre sus opciones financieras.