Tabla de amortización francesa y alemana en Excel: cómo hacerla

Una tabla de amortización es el calendario mes a mes de un préstamo: en cada fila ves cuánto pagas de cuota, cuánto es de interés y cuánto es de capital, y cuánto te queda por devolver. En este tutorial montarás las dos tablas que más usan los bancos, la francesa y la alemana, con un mismo préstamo de ejemplo, y verás en qué se diferencian. Al terminar sabrás calcular la cuota con la función PAGO, repartir el pago entre interés y capital y comparar el total de intereses de cada sistema. Funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016.

Qué es una tabla de amortización y por qué se hace en Excel

Una tabla de amortización es el cronograma que desglosa, período a período, cómo se devuelve un préstamo. Para cada mes muestra la cuota total que se paga, la parte que corresponde a intereses (calculada sobre lo que aún se debe), la parte que reduce el capital y el saldo pendiente después de pagar.

El ejemplo que usaremos es un préstamo de 12.000 a 12 meses con una Tasa Efectiva Anual (TEA) del 10%. La hoja, llamada "Préstamo", guardará los datos del crédito arriba y, debajo, las 12 filas del cronograma con las columnas Cuota, Interés, Amortización de capital y Saldo. La idea es que con cuatro números (capital, tasa, plazo y fecha del primer pago) y unas pocas fórmulas, Excel haga el resto. Al arrastrar con el controlador de relleno, cada fila arrastra sus propias referencias y el saldo del último mes queda en 0,00.

Los dos sistemas que usan los bancos: francés y alemán

Con el mismo capital, el mismo plazo y la misma tasa, hay más de una forma de repartir cada cuota entre interés y capital. Los dos sistemas más usados son el francés y el alemán, y la diferencia está en qué se mantiene constante de un mes a otro.

Sistema francés: cuota fija, más capital con el paso de los meses

En el sistema francés la cuota es la misma los 12 meses. Lo que cambia es su composición: en la primera cuota, gran parte son intereses (porque el saldo aún es alto) y solo una pequeña parte reduce capital; en las últimas ocurre al revés, casi todo es capital y el interés es mínimo. Por eso el saldo pendiente baja lento al principio y rápido al final.

Sistema alemán: capital fijo, cuota que baja cada mes

En el sistema alemán, también llamado "cuotas decrecientes", se amortiza la misma cantidad de capital cada mes (capital dividido entre el número de meses). Como los intereses se calculan siempre sobre el saldo vivo, y ese saldo baja, cada mes se paga menos interés y, en consecuencia, la cuota total es cada vez más baja. La primera cuota es la más alta del cronograma.

Los datos que necesita la hoja para calcular la tabla

Antes de tocar fórmulas, prepara la parte de arriba de la hoja con los datos del préstamo y los encabezados del cronograma. La hoja se llamará "Préstamo" y quedará así:

Para escribir los datos, haz lo siguiente:

1. En B2 teclea 12000 y, con Ctrl+1, aplícale formato moneda con dos decimales (12.000,00).
2. En B3 escribe 10% para la TEA; en B4, 12 para el plazo.
3. En B5 dejarás la fórmula de la tasa mensual (siguiente sección).
4. En B6 anota la fecha del primer pago con formato de fecha corta.
5. En la fila 8, escribe los encabezados del cronograma: A8 Mes, B8 Cuota, C8 Interés, D8 Amortización, E8 Saldo.

TEA, TEP y la conversión a una tasa mensual real

La TEA (Tasa Efectiva Anual) es lo que el banco publica, pero la cuota se calcula con la tasa del período, que aquí es mensual. Para pasar de anual a mensual se usa la fórmula de tasa equivalente:

=(1+B3)^(1/12)-1

Escribe esa fórmula en B5 con formato porcentaje con dos decimales. Con la TEA del 10%, la tasa mensual que sale es 0,80%, y es la que se multiplica por el saldo cada mes para obtener el interés. Si el banco te da una TEP (Tasa Efectiva Periódica) ya mensual, simplemente cópiala en B5 y te saltas esta conversión.

Construir la tabla francesa con PAGO y resto de fórmulas

Con la tasa mensual ya calculada, la cuota constante del sistema francés sale directamente de la función PAGO (PMT). El cronograma de la hoja "Préstamo" quedará así:

Para llenar la columna A con los números de mes, escribe 1 en A9 y 2 en A10, selecciona las dos celdas y arrastra el controlador de relleno hasta A20; Excel continuará la serie hasta 12.

La cuota constante con la función PAGO (PMT)

La cuota constante se calcula en B9 con la función PAGO (PMT), que devuelve la cuota con signo negativo porque representa una salida de dinero. Para que la cuota salga en positivo, se pone un menos delante:

=-PAGO($B$5;$B$4;$B$2)

Los tres argumentos son la tasa del período ($B$5), el número total de períodos ($B$4) y el capital del préstamo ($B$2). Los signos $ fijan las celdas para que, al arrastrar la fórmula, no se desplacen. Una vez escrita en B9, cópiala con Ctrl+C y pega en B10:B20 con Ctrl+V; la cuota es la misma en todas las filas.

El interés de cada mes y la amortización de capital

En C9 se calcula el interés del mes, que es el saldo anterior multiplicado por la tasa mensual. Como en la primera fila no hay saldo anterior, se usa directamente el capital del préstamo:

=$B$2*$B$5

A partir de la fila 10, el interés pasa a ser =E9*$B$5. En D9 se calcula la amortización de capital como la cuota menos el interés del mes:

=B9-C9

Por último, en E9 se anota el saldo nuevo: el capital inicial menos lo amortizado en este mes:

=$B$2-D9

Desde la fila 10, el saldo se calcula como =E9-D10, es decir, saldo anterior menos amortización de la fila actual. Arrastra todas estas fórmulas con el controlador de relleno hasta la fila 20.

Comprobar que la tabla cierra en cero

La forma más rápida de saber que la tabla está bien es mirar dos cifras: la suma de la columna de amortización (D) y el saldo del último mes (E20). Si D9:D20 suma 12.000,00, todo el capital se ha devuelto; si E20 es 0,00 (o un residuo de céntimos por redondeo), la tabla cierra correctamente. Si quieres forzar el cierre exacto, ajusta la cuota de la última fila como =E19+$B$5*E19 y pon la amortización final como =B20-C20 redondeado.

Construir la tabla alemana con la cuota que baja cada mes

Ahora repite el cronograma, pero en el sistema alemán, donde la amortización de capital es la misma cada mes. Puedes hacerlo a la derecha de la francesa, en las columnas G a K, para verlas juntas:

El capital fijo por período

En la columna de amortización (J) se calcula el capital fijo de cada mes, que es el préstamo total dividido entre el plazo:

=$B$2/$B$4

Con el capital de 12.000 y plazo 12, cada mes se amortizan 1.000,00. Al arrastrar la fórmula con el controlador de relleno, la cifra se mantiene igual porque divide entre una celda fija ($B$4) y el numerador también ($B$2).

La cuota decreciente y el interés sobre lo que aún debes

En la columna de interés (I), el primer mes se calcula sobre el capital completo y, a partir del segundo, sobre el saldo anterior:

=$B$2*$B$5

En I10 cambia a =K9*$B$5. La cuota de la columna H es la suma de las dos: amortización fija más interés del mes. En H9:

=I9+J9

Y el saldo nuevo en K9 es el capital inicial menos la amortización del mes:

=$B$2-J9

Desde la fila 10, el saldo es =K9-J10 (saldo anterior menos amortización fija). Arrastra todas las fórmulas hasta la fila 20.

Comprobar que la alemana termina antes en intereses

Suma la columna I con =SUMA(I9:I20) y compara con la suma de la columna C de la francesa. Para los datos del ejemplo, los intereses de la francesa suman 633,52 y los de la alemana 626,30: la alemana paga menos intereses en total porque amortiza más capital en las primeras cuotas, cuando los intereses son más caros.

Comparar las dos tablas en la misma hoja

Con las dos tablas ya armadas, añade debajo un cuadro resumen que las compare fila por fila y en totales. En la hoja "Préstamo", debajo de la fila 20, escribe los encabezados de la fila 22:

Las fórmulas del resumen son: =SUMA(B9:B20) para el total de cuotas en B23, =SUMA(C9:C20) para intereses en B24, =SUMA(D9:D20) para capital en B25, y para la columna del alemán cambia los rangos a H, I y J. Los totales de capital son idénticos (12.000,00) porque todo préstamo se devuelve entero; la diferencia está en los intereses, que dependen de cuánta deuda queda viva en cada momento.

Qué cambia entre las dos tablas, fila por fila

La francesa paga la misma cuota todo el tiempo; la alemana empieza más alta y termina más baja. En la fila 12, además, el interés de la alemana es mayor que el de la francesa: en el último mes, como ya casi no hay saldo, la francesa paga poquísimo interés, mientras que la alemana aún arrastra un saldo ligeramente superior.

Por qué tu tabla da un número raro o no cuadra

Cuando la tabla no cierra o muestra un valor extraño, el problema casi siempre está en la tasa, en los signos o en las referencias. Estos son los tropiezos más habituales con la función PAGO y con la tabla en general.

La cuota sale negativa o un número raro

Si PAGO devuelve un número negativo o un valor muy alto, lo más probable es que el capital se haya escrito con signo negativo, o que la tasa esté en formato texto y no como número. Comprueba en B2 que el capital sea 12000 (positivo) y que B3 esté con formato porcentaje. La fórmula =-PAGO($B$5;$B$4;$B$2) lleva un menos delante a propósito, porque PAGO devuelve el pago con signo negativo al representar salida de dinero.

El saldo no termina en cero

Si E20 muestra algo como 0,03 en vez de 0,00, suele ser un redondeo acumulado: cada mes redondeas la cuota a dos decimales, y al final queda un residuo. La solución es ajustar la última fila: pon en B20 =E19+$B$5*E19 (cuota exacta del último mes) y en D20 =B20-C20 para que el saldo cierre exactamente. Si el descuadre es grande (por ejemplo, 50 o 100), revisa que la tasa que usas en las fórmulas sea la mensual de B5 y no la anual de B3, y que el plazo cubra los 12 meses sin saltarte ninguno.

La tabla dice #¡NUM! al cambiar el plazo

PAGO devuelve #¡NUM! cuando la tasa o el plazo son 0, o cuando el plazo no es un número entero. Si al cambiar B4 por otro valor aparece ese error, asegúrate de que el nuevo plazo sea mayor que cero y de que la tasa mensual de B5 también lo sea. Si tu plazo se calcula con otra fórmula que temporalmente vale 0, envuélvela en =MAX(1;tu_formula) para que PAGO siempre tenga un valor válido.

Lo que ya sabes hacer con la tabla de amortización

• Tienes claros los cuatro datos que necesita la hoja: capital, TEA, plazo en meses y fecha del primer pago.
• Sabes decidir entre el sistema francés (cuota fija) y el alemán (capital fijo) según prefieras pagar menos al principio o menos en total.
• Dominas la fórmula de la francesa: =-PAGO(tasa;plazo;capital) y el cálculo mes a mes de interés, amortización y saldo.
• Montas la alemana con capital fijo (=Capital/Plazo) y cuota decreciente (amortización + interés).
• Comparas totales de las dos tablas en la misma hoja y detectas cuándo una paga menos intereses que la otra.

El siguiente paso natural es probar un abono al capital en el mes 6: réstalo del saldo y recalcula las cuotas que faltan. Verás cómo bajan los intereses totales y cuánto se acorta el plazo, que es justo la pregunta que cualquier persona se hace antes de firmar un crédito.

Dudas al usar la tabla de amortización en Excel

• ¿Excel tiene una función propia para la tabla francesa? Sí, PAGO (PMT) calcula la cuota constante; con ella y cuatro operaciones más se construye todo el cronograma.
• ¿Qué pasa si el préstamo tiene un plazo en años? Conviértelo a meses multiplicando por 12 antes de escribirlo en la celda de plazo, porque PAGO trabaja con períodos.
• ¿Por qué mi saldo no termina en cero aunque las fórmulas parecen correctas? Por el redondeo acumulado mes a mes; ajusta la cuota del último mes para que el saldo cierre exacto.
• ¿Cómo convierto la TEA en tasa mensual? Con la fórmula =(1+TEA)^(1/12)-1, escrita en una celda aparte y usada como referencia en el resto de fórmulas.
• ¿La alemana siempre paga menos intereses? Para los mismos datos del ejemplo sí, porque amortiza más capital en los primeros meses, cuando los intereses son más altos.

Dudas al usar la tabla de amortización en Excel