Cómo hacer una tabla de amortización en Excel: simulador paso a paso

Vas a montar una tabla de amortización en Excel que reparte cada cuota de un préstamo entre capital e intereses, mes a mes, hasta dejar el saldo a cero. Al terminar tendrás un libro con los datos del préstamo arriba, la tabla debajo y las fórmulas listas para cambiar el importe, el plazo o el tipo de interés y ver cómo se rehace todo solo. Funciona en Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013, 2010 y 2007, y también en Excel para la web, para Mac y para móviles, porque las funciones que usamos (PAGO, PAGO.PRINC.ENTRE, PAGO.INT.ENTRE) existen en todas esas versiones.

Qué es una tabla de amortización y para qué te sirve

Una tabla de amortización es el cuadro que desglosa cada cuota de un préstamo en dos partes: lo que pagas de intereses y lo que destinas a devolver el capital prestado. Al final del plazo, la suma de las partes de capital iguala lo que te dejaron, y la suma de las partes de intereses es lo que has pagado de más por usar ese dinero.

Imagina un préstamo de 15.000 EUR al 6 % anual a devolver en 5 años con cuotas mensuales. La cuota será siempre la misma, pero la parte de intereses va bajando con el tiempo porque se calcula sobre el capital que aún te queda por devolver. La tabla te enseña mes a mes cuánto pagas de intereses, cuánto reduces de capital y qué saldo te queda. Te sirve para comparar ofertas de bancos, decidir si reduces plazo o cuota, y ver de un vistazo cuánto acabarás pagando.

Las tres funciones de Excel que calculan la cuota

Excel trae tres funciones financieras que nacieron con la versión 2003 y siguen en todas las posteriores, incluida la versión web y la de Mac. Se complementan entre sí: una te da la cuota entera, otra la parte que amortiza capital y otra la parte que son intereses. Sus argumentos son los mismos: la tasa por período, el número total de períodos, el capital prestado (va), el valor residual que dejas (vf), y un 0 o un 1 si la cuota se paga al final o al principio del mes (tipo).

PAGO: la cuota fija de cada mes

PAGO (en inglés PMT) devuelve la cuota periódica de un préstamo amortizado por el método francés, el que usan los bancos: misma cantidad cada mes. Cuando el capital «va» es positivo, el resultado sale negativo porque, desde el punto de vista del préstamo, el dinero sale.

=PAGO(tasa;nper;va;[vf];[tipo])

PAGO.PRINC.ENTRE: la parte de la cuota que amortiza capital

PAGO.PRINC.ENTRE (en inglés PPMT) usa los mismos argumentos que PAGO y añade el período concreto que te interesa. Si sumas su resultado en todos los períodos, el total es el capital que pediste.

=PAGO.PRINC.ENTRE(tasa;período;nper;va;[vf];[tipo])

PAGO.INT.ENTRE: la parte de la cuota que son intereses

PAGO.INT.ENTRE (en inglés IPMT) tiene la misma sintaxis que PAGO.PRINC.ENTRE, solo cambia el nombre. En cada fila, la suma de las dos piezas (capital + intereses) es exactamente la cuota que da PAGO.

=PAGO.INT.ENTRE(tasa;período;nper;va;[vf];[tipo])

Monta la hoja con los datos del préstamo

Abre un libro nuevo y crea una hoja llamada «Préstamo». En la parte de arriba vas a poner las variables del préstamo: capital, tipo de interés anual, años y pagos por año. Esas celdas son las que luego usará cada fórmula de la tabla, así que conviene dejarlas en un sitio fijo.

1. En B1 escribe «Préstamo» en negrita.
2. En A2 «Capital», en B2 15000.
3. En A3 «Interés anual», en B3 6% (Excel lo reconoce como 0,06).
4. En A4 «Años», en B4 5.
5. En A5 «Pagos por año», en B5 12.
6. En A6 «Pagos totales», en B6 =B4*B5. Tiene que dar 60.
7. En A7 «Cuota mensual», en B7 =PAGO(B3/B5;B6;B2). Tiene que dar -289,99.

Crea la tabla mes a mes con la columna de saldo

Debajo de los datos empieza la tabla con una fila por mes. Necesitas cinco columnas: el número de mes, la cuota entera, la parte de capital, la parte de intereses y el capital que aún te queda por devolver.

1. En A10 escribe «Mes», en B10 «Cuota», en C10 «Capital», en D10 «Intereses» y en E10 «Capital pendiente», todo en negrita.
2. En A11 escribe 1 y, con A11 seleccionada, arrastra el controlador de relleno (el cuadradito de la esquina inferior derecha) hasta A70. Te aparecerán los números del 1 al 60.

La cuota fija copiada hacia abajo

En la columna B vas a poner la cuota de B7, pero fijando la referencia con $ para que al arrastrar hacia abajo siempre apunte a la misma celda. La tecla F4 (en Mac, Cmd+T) convierte una referencia en absoluta al editar la fórmula.

1. En B11 escribe =$B$7 y arrastra hacia abajo hasta B70.

=$B$7

Los intereses de cada mes con PAGO.INT.ENTRE

La fórmula de intereses usa la tasa por período (B3/B5), el número de mes (A11), los pagos totales (B6) y el capital (B2). Como tiene que valer para los 60 meses, las cuatro referencias a los datos del préstamo se quedan fijas con $ y solo A11 es relativa.

1. En D11 escribe =PAGO.INT.ENTRE($B$3/$B$5;A11;$B$6;$B$2) y arrastra hasta D70. D11 tiene que dar 75,00.

=PAGO.INT.ENTRE($B$3/$B$5;A11;$B$6;$B$2)

El capital amortizado en cada mes con PAGO.PRINC.ENTRE

La parte de capital se calcula igual pero con PAGO.PRINC.ENTRE. La suma de toda la columna C tiene que coincidir con los 15.000 EUR prestados.

1. En C11 escribe =PAGO.PRINC.ENTRE($B$3/$B$5;A11;$B$6;$B$2) y arrastra hasta C70.

=PAGO.PRINC.ENTRE($B$3/$B$5;A11;$B$6;$B$2)

El capital pendiente que va bajando

Para el saldo pendiente se resta al capital inicial la suma del capital amortizado hasta el momento. Se usa un truco: el rango empieza en $C$11 (fijo) y termina en C11 (relativo, para que al copiar crezca fila a fila).

1. En E11 escribe =$B$2-SUMA($C$11:C11) y arrastra hasta E70. E11 vale 14785,00 y E70 tiene que valer 0.

=$B$2-SUMA($C$11:C11)

Comprueba que la tabla cierra bien

La tabla cierra bien cuando se cumplen tres sumas a la vez. Primero, la suma de la columna D (intereses) más la suma de la columna C (capital) tiene que ser igual al total pagado, que son 60 cuotas por 289,99 EUR cada una (17.399,55 EUR). Segundo, la suma de la columna D tiene que ser la diferencia entre lo pagado (17.399,55) y el capital (15.000), es decir, 2.399,55 EUR de intereses. Tercero, el capital pendiente en la última fila tiene que ser 0.

1. Selecciona D11:D70, mira en la barra de estado de Excel (abajo a la derecha) y comprueba que la Suma es 2.399,55.
2. Selecciona E70 y comprueba que vale 0.
3. Da formato a las columnas B, C, D y E con dos decimales y estilo moneda: selecciónalas, ve a Inicio > Número y elige «;,[Red]"€"» o el formato de moneda de tu país.

Qué puede salir mal al montar la tabla

Tres fallos son los más frecuentes al montar la tabla. Cada uno tiene una causa clara y una forma de arreglarlo.

La cuota sale negativa o muy rara

PAGO devuelve un número negativo cuando el capital «va» es positivo, porque Excel entiende que el dinero sale de tu bolsillo. Si quieres ver la cuota en positivo sin tocar las fórmulas, multiplica toda la tabla por -1 o, más sencillo, pon el capital como negativo en B2 y la cuota saldrá en positivo automáticamente.

El capital pendiente no acaba en cero

Si E70 no vale 0, casi siempre es porque la fórmula de la columna E tiene la referencia del final fija con $ y no se expande al copiar. La fórmula correcta usa $C$11:C11, con la primera referencia fija y la segunda libre, para que la segunda celda se vaya moviendo conforme bajas.

La última fila se descuadra por el redondeo

Excel redondea a 14 cifras significativas en los cálculos internos, pero al mostrar dos decimales puede quedar un céntimo de más o de menos en la última fila. Si tu E70 vale 0,02 en lugar de 0, hay dos arreglos: activar el cálculo iterativo en Archivo > Opciones > Fórmulas para que Excel recalcule la última cuota, o ajustar a mano la última celda de las columnas C y D para que cuadre. Con separador de coma decimal el formato «;,[Red]€» puede dar diferencias por el símbolo.

Cambia un dato y ve cómo se rehace la tabla

Toda la tabla está atada a las celdas B2, B3, B4 y B5. Cambia una y verás cómo se actualiza el resto sin tocar nada más.

1. En B4 cambia 5 por 10. La cuota en B7 baja a -152,06.
2. En B3 cambia 6% por 4%. La cuota vuelve a bajar a -135,40.
3. Mira la columna E: el capital pendiente ahora llega a 0 en la fila 120 (10 años × 12 meses), porque la tabla se ha estirado por la propia estructura de los datos.

Hazla más usable con formato y validación

Convertir el rango en una tabla de Excel le da nombre propio, lo que facilita añadir meses sin romper las fórmulas y filtrar por año, entre otros.

1. Selecciona A10:E70.
2. Abre Insertar > Tablas > Tabla y acepta con el botón Aceptar. Verás el cuadro «Crear tabla».
3. Cambia el nombre de la tabla a «Amortización» en la pestaña Diseño de tabla > Propiedades > Nombre de la tabla.

1. Selecciona las columnas B, C, D y E y aplica formato moneda con dos decimales desde Inicio > Número.

1. Para que el usuario no meta valores raros en los datos del préstamo, añade validación en B3: Inicio > Número > ese formato no, mejor Datos > Herramientas de datos > Validación de datos.

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

• Tienes una hoja «Préstamo» con los datos del préstamo arriba y la tabla mes a mes debajo, lista para cualquier escenario.
• Sabes usar PAGO para la cuota entera, PAGO.PRINC.ENTRE para la parte de capital y PAGO.INT.ENTRE para los intereses de cada mes.
• Conoces los tres errores típicos: cuota en negativo, saldo que no acaba en cero y descuadre por redondeo, y tienes el arreglo de cada uno.
• Cambiar capital, interés o plazo en las celdas de arriba rehace toda la tabla al instante, sin tocar las fórmulas de abajo.
• Ahora sabes dar formato de tabla y aplicar moneda para que la hoja sea fácil de leer y compartir.

El siguiente paso es comparar dos préstamos colocándolos uno al lado del otro en hojas distintas (por ejemplo «Préstamo A» y «Préstamo B» dentro del mismo libro) y usando BUSCARV o INDICE y COINCIDIR para traer a una tercera hoja el resumen de cada uno: cuota, total pagado y total de intereses.

Dudas al usar la tabla de amortización en Excel