Cómo hacer un cuadro de amortización en Excel con fórmulas

Una calculadora de cuadro de amortización es una hoja que, a partir del importe que pides, el tipo de interés y el plazo, te desglosa mes a mes cuánto pagas de cuota, cuánto son intereses y cuánto capital devuelves. En este tutorial la montas en una hoja de Excel con las funciones PAGO (PMT) y PAGOINT (IPMT), de modo que basta cambiar tres celdas para recalcular todo el cuadro. Funciona en Excel 2007 y posteriores, aunque PAGOINT y PAGOPRIN (PPMT) están disponibles en Microsoft 365, 2024 y 2021; en 2019 y 2016 la sección de errores explica cómo sustituirlas.

Qué es un cuadro de amortización y por qué hacerlo en Excel

Un cuadro de amortización es la tabla que entrega el banco cuando pides un préstamo y donde se ve, período a período, cómo se reparte cada cuota entre intereses y capital. La idea es simple: si pides 10.000 a un 6 % anual a devolver en 12 meses, cada mes pagas una cuota fija, pero la parte que es interés va bajando (porque el capital pendiente baja) y la parte que amortiza capital va subiendo; al final del mes 12 el capital pendiente es exactamente 0.

En Excel esto se traduce en cinco columnas: número de mes, cuota total, intereses del mes, capital amortizado en el mes y capital que queda por devolver. Si pones los datos del préstamo en tres celdas (capital, tasa anual y plazo) y formulas en las columnas, el cuadro se recalcula solo cuando cambias una de esas tres celdas. Eso convierte la hoja en una calculadora que puedes reutilizar: cambias el capital y vuelves a tener el cuadro del préstamo nuevo.

La diferencia con una calculadora online es que aquí ves cada fórmula, entiendes de dónde sale cada cifra y puedes añadir cosas (pagos extra, cambios de tipo, cuotas anuales) sin empezar de cero. Por eso merece la pena montarla una vez y guardarla como plantilla.

Las funciones de amortización contable que ya trae Excel

Excel trae un grupo de funciones pensadas para la amortización contable de activos (maquinaria, vehículos, equipos informáticos), no para préstamos. Las más conocidas son SLN, DB, DDB, DVS, AMORTIZ.LIN y AMORTIZ.PROGRE. Todas calculan cuánto se deprecia un bien en cada ejercicio fiscal; ninguna te da la cuota mensual de un préstamo ni el capital pendiente mes a mes.

Si tu búsqueda es «calculadora cuadro amortización» referido a un préstamo, ninguna de estas seis funciones te sirve tal cual: trabajan sobre el valor residual del activo, no sobre el capital pendiente de un préstamo. Para el préstamo necesitas las funciones de la sección siguiente: PAGO, PAGOINT y PAGOPRIN.

La fórmula de la cuota mensual con PAGO y PAGOINT

La cuota fija de un préstamo se calcula con PAGO (PMT, de Payment). Su sintaxis es:

=PAGO(tasa; nper; va)

tasa es el tipo de interés POR período (si el préstamo es anual y pagas cada mes, divides entre 12); nper es el número total de períodos; va es el valor actual, es decir, el capital que te prestan. Para 10.000 al 6 % anual a 12 meses:

=PAGO(6%/12; 12; 10000)

El resultado es -860,66. Sale negativo porque Excel interpreta el capital como dinero que sale de tu bolsillo (un pago). Si prefieres verlo en positivo, pon un signo menos delante del va: =PAGO(6%/12; 12; -10000) y devolverá 860,66.

Los intereses de cada período se calculan con PAGOINT (IPMT):

=PAGOINT(tasa; período; nper; va)

El cuarto argumento, período, indica en qué mes estás calculando los intereses. Para el mes 1 con los mismos datos:

=PAGOINT(6%/12; 1; 12; 10000)

Da -50,00 (los intereses del primer mes son el 0,5 % de 10.000). Para el mes 12 da -2,50, porque el capital pendiente ya es mucho menor.

La fórmula de la parte de capital que amortizas cada mes

La parte de capital que amortizas cada mes es lo que sobra de la cuota después de pagar los intereses: cuota menos intereses. La fórmula directa es:

=PAGO - PAGOINT

Equivale a usar PAGOPRIN (PPMT), que tiene la misma sintaxis que PAGOINT pero devuelve la cuota de capital en vez de la de intereses:

=PAGOPRIN(tasa; período; nper; va)

Ambas te dan el mismo número; la diferencia es de claridad: si en una columna quieres ver los intereses del mes, usa PAGOINT; si quieres ver el capital que amortizas, usa PAGOPRIN o resta PAGOINT a PAGO. Las dos se aplican fila a fila cambiando el argumento período con el número de mes correspondiente.

Montar el cuadro en una hoja: las cinco columnas que necesitas

La hoja se llama «Préstamo» y solo necesita cuatro datos de entrada y una tabla de 12 filas. Los datos de entrada van arriba:

• B1 – Capital inicial: 10.000
• B2 – Tasa anual: 6 %
• B3 – Plazo en meses: 12

La tabla empieza en A5 con los títulos y baja hasta la fila 17 (12 meses + fila de títulos).

1. Escribe los encabezados en A5:E5:

– A5: Mes – B5: Cuota – C5: Intereses – D5: Capital amortizado – E5: Capital pendiente

1. En A6 escribe 1 y arrastra el controlador de relleno hasta A17 para que aparezcan los números del 1 al 12.
2. Deja B6:E6 vacías por ahora: las fórmulas se montan en los pasos siguientes.

La fila de títulos y el formato de moneda y porcentajes

Ahora se da formato a las celdas para que se lean bien:

1. Selecciona B6:D17 y pulsa Ctrl+1 para abrir Formato de celdas.
2. En la categoría Moneda, elige 2 decimales y símbolo de euro (o el de tu país). Acepta.
3. Selecciona E6:E17, vuelve a Ctrl+1 y aplica también Moneda con 2 decimales.
4. Selecciona B2 y aplica formato Porcentaje con 2 decimales (queda 6,00 %).

La cuota que se repite igual cada fila con F4 y PAGO

La cuota es la misma en los 12 meses, así que la fórmula va en B6 y se arrastra hasta B17:

1. Selecciona B6 y escribe:

=PAGO($B$2/12;$B$3;$B$1)

1. Pulsa Entrar y arrastra el controlador de relleno desde B6 hasta B17.

Los signos $ se ponen pulsando F4 después de escribir cada referencia (o una vez con el cursor sobre la referencia completa). Sirven para que al arrastrar la fórmula hacia abajo las tres celdas de entrada no se muevan: si arrastraras sin $, en B7 saldría =PAGO(B3/12;B4;B2) y los datos apuntarían a celdas vacías.

Los intereses de cada mes con PAGOINT

Los intereses cambian cada mes porque dependen del capital pendiente. En C6 escribe:

=PAGOINT($B$2/12;A6;$B$3;$B$1)

La diferencia clave con la fórmula anterior está en el segundo argumento: aquí va A6 (sin dólares), que es el número de mes. Al arrastrar hacia abajo, A6 pasa a A7, A8, etc., así cada fila calcula los intereses de su mes. El resto de referencias lleva $ para no moverse.

El capital que amortizas cada mes y el capital pendiente

El capital amortizado de cada mes es la cuota menos los intereses. En D6:

=B6-C6

El capital pendiente de la primera fila es el capital inicial menos lo amortizado en el mes 1. En E6:

=$B$1-D6

Para las filas siguientes el pendiente se arrastra hacia abajo desde la fila anterior menos lo amortizado en la fila actual. En E7:

=E6-D7

Arrastra D6:D6 y E7:E7 hasta la fila 17.

Comprobar que la última cuota cierra el préstamo a cero

Mira la celda E17: debería ser 0,00 si todo está bien. En este ejemplo queda 26,98 porque los redondeos a dos decimales de las cuotas mensuales no permiten cerrar el préstamo exactamente. Esto le pasa a casi todos los cuadros hechos a mano.

Para que cuadre, ajusta la última cuota: pon en B17 directamente la cantidad que falta para terminar de pagar (=E16 con signo contrario), y deja las celdas de intereses y capital de la fila 12 calculadas a partir de esa cuota. La forma rápida es:

1. En C17 pon =E16*$B$2/12 (intereses del último mes sobre el capital que quedaba en E16).
2. En B17 pon =E16+C17 (lo que falta pagar más los intereses).
3. En D17 pon =B17-C17 y comprueba que E17 queda 0,00.

Cuotas anuales, pagos extra y otros cambios sobre el mismo cuadro

Una vez montado el cuadro, cambiar el tipo de préstamo es cuestión de tocar las tres celdas de entrada. Para pasar de 12 cuotas mensuales a 4 cuotas trimestrales con el mismo préstamo de 10.000 al 6 % anual:

1. En B3 escribe 4 (plazo en trimestres).
2. La fórmula de la cuota pasa a ser =PAGO($B$2/4;$B$3;$B$1) (tasa anual dividida entre 4, plazo 4).
3. La fórmula de intereses pasa a ser =PAGOINT($B$2/4;A6;$B$3;$B$1) y la de capital =PAGOPRIN($B$2/4;A6;$B$3;$B$1).
4. Cambia el rango de la tabla a 4 filas (B6:B9, C6:C9, etc.) o limita la altura al arrastrar.

La cuota resultante es 2.589,25 (intereses totales 354,99), claramente mayor que la mensual porque pagas más intereses al haber menos períodos.

Para añadir un pago extra, la forma más simple es restarlo del capital pendiente del mes en que lo haces. En la fila del mes 6, por ejemplo, en E11 sustituye la fórmula por =E10-D11-500 para simular una amortización anticipada de 500. Verás cómo el pendiente baja más deprisa y los intereses de los meses siguientes también.

Por qué el cuadro no cuadra o da números raros

Cuando el cuadro no cuadra, casi siempre es por una de estas cuatro causas. Cada una se ve de una manera distinta en la hoja.

La tasa anual sin dividir entre 12 dispara la cuota

Si olvidas dividir la tasa entre 12 y dejas =PAGO($B$2;$B$3;$B$1), la cuota sale mucho más alta: para 10.000 al 6 % a 12 meses con la fórmula mal hecha da -1.061,68 en vez de -860,66, casi un 23 % más. Se detecta comparando con una calculadora online: si tu cuota no coincide, mira primero la fórmula de la cuota.

La referencia del capital inicial se mueve al arrastrar

Si escribes =PAGO(B2/12;B3;B1) sin dólares y arrastras hacia abajo, en la fila 7 sale =PAGO(B3/12;B4;B2), que multiplica por celdas vacías. El resultado es #¡VALOR! o un número absurdo. La pista es que la cuota va cambiando fila a fila.

Arreglo: selecciona la fórmula de B6, pulsa F4 una vez sobre cada referencia para añadir los $, y vuelve a arrastrar.

El capital pendiente no termina en cero

Por el redondeo a dos decimales, el pendiente final casi nunca es 0,00 exactamente: en el ejemplo queda 26,98. La causa es que cada cuota se redondea al céntimo, y la suma de esos céntimos no coincide con el capital exacto. Arreglo: usa REDONDEAR (ROUND) alrededor de las fórmulas de capital pendiente, o ajusta la última cuota como se vio en el paso anterior.

=REDONDEAR(E6-D7;2)

#¡NOMBRE? al escribir PAGO en un Excel antiguo

PAGO existe desde Excel 2003, así que #¡NOMBRE? en esa función suele ser un error de escritura. Pero PAGOINT y PAGOPRIN aparecen con esos nombres en Microsoft 365, 2024 y 2021; en Excel 2019 y 2016 también están (se incluyeron en 2016). Si tu Excel es 2013 o anterior, toca calcular los intereses a mano con la fórmula:

=capital_pendiente * tasa_mensual

Es decir, en C6 sería =E5*$B$2/12 (los intereses del mes son el capital pendiente al inicio del mes por la tasa mensual). La cuota total se obtiene con PAGO igual que antes, y el capital amortizado se resta.

Lo que ya sabes hacer con tu calculadora de amortización

• Las cinco columnas del cuadro: mes, cuota, intereses, capital amortizado y capital pendiente.
• Fijar referencias con F4 para que las celdas de entrada no se muevan al arrastrar.
• Usar PAGO (PMT) para la cuota, PAGOINT (IPMT) para los intereses y PAGOPRIN (PPMT) para el capital de cada mes.
• Ajustar la última cuota para que el capital pendiente cierre en 0,00.
• Saber que PAGOINT y PAGOPRIN requieren Excel 2016 o posterior; en versiones más antiguas se calculan los intereses a mano.

El siguiente paso es proteger la hoja y guardarla como plantilla .xltx para reutilizarla con cada préstamo nuevo sin tener que volver a escribir las fórmulas.

Dudas al montar la calculadora de amortización