Tabla de amortización alemana en Excel: cómo hacerla con fórmulas

Una tabla de amortización alemana reparte el préstamo en partes iguales de capital y va cobrando los intereses sobre lo que queda por devolver. Cada mes pagas un poco menos de cuota porque la deuda baja, pero la porción de capital que amortizas es siempre la misma. En este tutorial la vas a montar en Excel sobre un libro nuevo, con fórmulas que se arrastran y que sirven para cualquier préstamo: capital en una celda, plazo en otra, tasa anual en una tercera, y debajo las 24 cuotas con su capital, su interés, su cuota total y el saldo que va bajando. Al terminar sabrás hacer una tabla alemana limpia, compararla con la francesa sobre los mismos datos y arreglar los tres errores típicos (el saldo que no cuadra, la cuota disparada y el residuo de la última fila). Funciona en Excel 2007 en adelante, también en Excel para Microsoft 365, para la web y para Mac.

Qué es el sistema alemán y para qué te sirve en Excel

El sistema alemán de amortización, también llamado método de cuotas decrecientes, tiene una idea muy sencilla: cada mes devuelves la misma cantidad de capital y los intereses se calculan sobre lo que todavía debes. Como la deuda va bajando, el interés de cada mes es menor que el del anterior, y la cuota total (capital + interés) también baja.

Para verlo en Excel, imagina un préstamo de 10.000 a devolver en 5 años (60 meses) al 5% anual. Cada mes amortizas 10.000 / 60 = 166,67 de capital. El primer mes, los intereses son 10.000 · 0,05 / 12 = 41,67, así que pagas 416,67 en total. El segundo mes amortizas otros 166,67 de capital, pero los intereses se calculan sobre 9.833,33, que es lo que queda por pagar, y la cuota baja un poco.

La tabla que vas a montar tiene cinco columnas: Mes, Capital amortizado, Interés del mes, Cuota total y Saldo pendiente. Con ella puedes comparar cuánto pagas de intereses frente al sistema francés (cuota fija), ver cuándo termina de cerrarse el préstamo y entender por qué las primeras cuotas son las más altas.

En este tutorial usaremos 24 meses en vez de 60 para que las cuentas se vean de un vistazo, pero la estructura es la misma para cualquier plazo.

Las tres fórmulas que necesitas y dónde ponerlas

La tabla alemana se sostiene sobre tres fórmulas, una por columna. Si las entiendes, puedes cambiar el capital, el plazo o la tasa sin tocar nada más.

Los argumentos se separan con punto y coma porque el Excel en español suele usar la coma como decimal. En Excel configurado con punto decimal el separador es la coma.

La cuota de capital que se repite

La cantidad de capital que amortizas cada mes es siempre la misma: el capital del préstamo dividido entre el número de cuotas. Si el capital está en B1 y el plazo en B2, la fórmula es:

=$B$1/B2

El dólar en B1 ($B$1) fija la referencia al capital: al copiar la fórmula hacia abajo, siempre apunta a la celda del capital. La celda B2 (sin $) es el plazo en meses, que también está fija porque solo hay un valor, pero se queda relativa por si más adelante quieres una variante con plazos por fila.

El resultado de la primera cuota es 10.000 / 24 = 416,67. Como es una división fija, todas las cuotas de capital valen exactamente lo mismo.

El interés sobre el saldo vivo

El interés de cada mes se calcula multiplicando el saldo pendiente del mes anterior por la tasa mensual. Si el saldo del mes anterior está en la columna C (en C2 para la primera cuota) y la tasa anual está en B3, la fórmula es:

=C2*$B$3/12

$B$3/12 convierte la tasa anual (5%) en tasa mensual. En la primera cuota, con saldo 10.000, el interés es 10.000 · 0,05 / 12 = 41,67. En la segunda, con saldo 9.583,33, el interés baja a 39,93. La cifra va cayendo mes a mes.

La cuota total del mes y el nuevo saldo

La cuota total es la suma de capital más interés, y el saldo nuevo es el saldo anterior menos el capital amortizado. En la misma fila, las dos fórmulas son:

=B2+C2
=B1-B2

La cuota total es lo que pagas ese mes al banco. El saldo va bajando en la misma cantidad que el capital amortizado, porque la parte de capital reduce directamente lo que debes.

Monta la tabla con 24 cuotas en una hoja nueva

Abre un libro en blanco y guárdalo con el nombre "Préstamo alemán". Dentro crea una hoja llamada "Alemán" y deja los datos del préstamo arriba: en B1 el capital (10.000), en B2 el plazo en meses (24) y en B3 la tasa anual (0,05). La celda activa al empezar a trabajar será B6, donde escribiremos las fórmulas.

Pinta la cabecera y da formato Moneda

1. Ve a A5 y escribe "Mes", en B5 "Capital", en C5 "Interés", en D5 "Cuota" y en E5 "Saldo".
2. Selecciona B5:E5 y pon el texto en negrita con Ctrl+N.
3. Selecciona B6:E29 (las celdas donde irán los importes) y aplica formato Moneda con Ctrl+Mayús+$. En Mac, Ctrl+Mayús+4. En el cuadro de formato, deja dos decimales.
4. Ajusta el ancho de las columnas A a E arrastrando el borde derecho de cada cabecera hasta que se vean los títulos enteros.

En pantalla verás la cabecera en negrita sobre fondo blanco y, debajo, una zona reservada con formato moneda lista para escribir las fórmulas.

Escribe las fórmulas en la fila 6 y comprueba los totales

1. En A6 escribe 1. En A7 escribe 2. Selecciona A6:A7 y arrastra el controlador de relleno (el cuadrado de la esquina inferior derecha de A7) hasta A29 para completar los 24 meses.
2. En B6 escribe =$B$1/$B$2 y pulsa Entrar. Verás 416,67.
3. En E6 escribe 10000 (el capital inicial). En E7 escribe =E6-B7 y arrastra hasta E29. Verás cómo va bajando el saldo.
4. En C7 escribe =E6*$B$3/12 y arrastra hasta C29. Los intereses empiezan en 41,67 y van cayendo.
5. En D6 escribe =B6+C6 y arrastra hasta D29. La cuota total va de 458,33 abajo de la primera fila.
6. Ve a B30 y escribe =SUMA(B6:B29). La barra de estado, abajo del todo, debe mostrar Suma 10.000,00. Si lo muestra, el capital se reparte bien.

Comprueba arrastrando solo hacia abajo

Cuando arrastras la fórmula de B6 hacia abajo, la cuota de capital se queda fija en 416,67 en todas las filas. La referencia $B$1 no se mueve porque tiene el dólar delante de la B y del 1. Si hubieras escrito solo $B1, al copiar hacia abajo solo se fijaría la columna, pero como el plazo está en B2 (una sola celda), lo correcto es $B$1 para el capital y dejar B2 sin dólares porque solo se usa una vez.

Comprueba arrastrando a otra hoja

Si copias la fila 6 y la pegas en una Hoja2, las fórmulas siguen mostrando $B$1, pero ahora con el nombre de la hoja delante: =Alemán!$B$1. Eso pasa porque el $ convierte la referencia en absoluta y, al cambiar de hoja, Excel mantiene el vínculo con la celda original del capital.

Trucos de la primera práctica: viendo que la cuota baja

Una vez montada la tabla, mira la columna D (Cuota). La cuota del mes 1 es la más alta (458,33) y la del mes 24 es la más baja. En una celda vacía, por ejemplo G2, escribe =SUMA(C6:C29) para ver el total de intereses pagados en los dos años. Con estos datos sale 525,00, exactamente la mitad que en el sistema francés sobre el mismo préstamo.

Compara alemán y francés sobre los mismos datos

Ahora vamos a poner al lado, en las columnas G:K de la misma hoja, el mismo préstamo pero con el sistema francés (cuota fija), usando la función PAGO (en inglés PMT). En G5 escribe "Francés", en G6 "Mes", en H6 "Capital", en I6 "Interés", en J6 "Cuota" y en K6 "Saldo".

En J7 escribe:

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

El resultado es 439,57: esa es la cuota fija que pagarías cada mes con el sistema francés. En H7 escribe =PAGO($B$3/12;$B$2;$B$1)-I7 para repartirla entre capital e interés, y en K7 =$B$1-H7 para el saldo. Arrastra las tres fórmulas hasta la fila 30.

Comparando las dos tablas: en el alemán la cuota del mes 1 es 458,33 y baja; en el francés es 439,57 siempre igual. El saldo del alemán baja a ritmo constante (416,67 cada mes) y el del francés baja más al principio y menos al final.

Con estos datos, el sistema alemán paga menos intereses en total porque amortiza más capital al principio.

Por qué sale #¡VALOR! o un saldo que no cuadra

Tres fallos típicos al montar la tabla, y cómo arreglarlos uno a uno con la hoja abierta.

El interés se calcula sobre el saldo anterior

Si escribes en C7 la fórmula =C6*$B$3/12 en lugar de =E6*$B$3/12, el interés se calcula sobre el interés del mes anterior y los números se disparan. En la fila 7 verás 2,08 en vez de 41,67, y en la fila 8 el saldo ya no cuadra.

Arreglo: cambia C6 por E6. El interés siempre va sobre el saldo pendiente, nunca sobre el interés.

Escribes la tasa anual como 5 en lugar de 0,05

Si en B3 escribes 5 creyendo que es el 5%, la cuota de capital sale bien pero los intereses son 100 veces mayores. En C7 verás 4.166,67 en vez de 41,67, y la cuota total del mes 1 será 4.583,33.

Arreglo: cambia B3 a 0,05 (con la coma como decimal) o, si prefieres escribir 5, divide entre 100 en la fórmula: =E6*$B$3/100/12.

La última cuota no cierra a cero por los decimales

Al terminar las 24 cuotas, el saldo de E30 no queda exactamente en cero sino en algo como 0,04. Eso pasa porque los decimales de la cuota de capital (416,67 redondeado) no encajan del todo con el capital total.

Arreglo: en B30 (la cuota de capital del último mes) escribe =E29 en vez de =$B$1/$B$2. Así el último capital amortizado es justo lo que queda, y el saldo cierra en cero.

=E29

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

• Repartir un préstamo en cuotas de capital iguales con =$B$1/$B$2, fijando el capital con $.
• Calcular el interés de cada mes sobre el saldo pendiente con =E6*$B$3/12, donde $B$3/12 convierte la tasa anual en mensual.
• Obtener la cuota total como suma de las dos anteriores y el saldo nuevo como resta del capital amortizado.
• Comparar el sistema alemán con el francés usando la función PAGO (PMT) en una tabla paralela.
• Detectar y arreglar los tres errores típicos: interés sobre celda equivocada, tasa anual sin dividir entre 100 y saldo residual de la última cuota.

El siguiente paso natural es añadir a la tabla una columna con la fecha de cada pago y otra con el capital amortizado acumulado, para ver en cualquier mes cuánto llevas pagado del préstamo.

Datos comprobados en octubre de 2026.

Dudas al montar la tabla de amortización alemana en Excel