Tabla de préstamo en Excel: guía para calcularla y revisarla

Una tabla de préstamo en Excel es una hoja que muestra, para cada cuota, cuánto pagas de intereses, cuánto devuelves de capital y cuánto te queda por devolver. Se construye con una sola función, PAGO (en inglés PMT), y unas fórmulas que reparten cada cuota. Al terminar, sabrás calcular la cuota mensual de cualquier préstamo, repartirla entre intereses y capital, y dejar el saldo a cero. Funciona en todas las versiones de Excel desde 2007, también en Excel para la web y para Mac.

Qué es una tabla de préstamo y qué información contiene

Una tabla de préstamo, también llamada cuadro de amortización, es una hoja con una fila por cada cuota del préstamo. Cada fila muestra cuatro datos: el número de cuota, el pago que se realiza, los intereses que se pagan en esa cuota y el capital que se devuelve. Al final, una columna con el saldo pendiente te dice cuánto queda por devolver después de pagar esa cuota.

La idea es sencilla: empiezas debiendo el capital entero, pagas una cuota cada mes, y la cuota se reparte entre lo que cobró el banco por prestarte el dinero (los intereses) y lo que reduces la deuda (el capital devuelto). Al principio pagas sobre todo intereses, porque la deuda es grande; al final pagas sobre todo capital, porque la deuda ya es pequeña.

Para verlo, en una hoja nueva escribe los datos básicos del préstamo y la cuota del primer mes:

La cuota (232,22) se divide en 116,67 de intereses y 115,55 de capital; el saldo baja de 20.000 a 19.884,45. Esa es la mecánica que vamos a programar en las siguientes secciones.

Preparar los datos del préstamo antes de calcularlo

Antes de escribir fórmulas conviene reunir los datos del préstamo en celdas con etiquetas claras. Así podrás cambiar el capital, el interés o el plazo y ver cómo se recalcula toda la tabla sin tocar nada más.

Abre una hoja nueva y rellena la zona superior siguiendo estos pasos:

1. En A1 escribe Capital; en B1 escribe 20.000.
2. En A2 escribe Interés anual; en B2 escribe 7%.
3. En A3 escribe Plazo (meses); en B3 escribe 120.
4. En A4 escribe Cuota mensual; déjala vacía de momento.
5. En A6 escribe Mes; en B6 escribe Cuota; en C6 escribe Intereses; en D6 escribe Capital; en E6 escribe Saldo.
6. Selecciona B1:B3 y pulsa Ctrl+1 para abrir Formato de celdas. Aplica formato Moneda con dos decimales a B1 y formato Porcentaje con dos decimales a B2.

Calcular la cuota mensual con PAGO

PAGO (en inglés PMT) devuelve la cuota periódica de un préstamo. Su sintaxis es =PAGO(tasa; nper; va; vf; tipo). El truco está en que tasa y nper deben estar en la MISMA unidad: si la cuota es mensual, divides el interés anual entre 12 y el plazo lo pones en meses.

Separar tasa anual y número de cuotas

Con los datos del ejemplo (20.000 euros, 7% anual, 120 meses) escribe en B4 la fórmula y púlsalo Entrar:

=PAGO(B2/12;B3;-B1;0;0)

El resultado es 232,22, la misma cuota que aparece en la mayoría de cuadros de amortización. Tres detalles que conviene tener claros:

• B2/12 pasa el 7% anual a tasa mensual (0,5833%).
• -B1 lleva el capital en negativo, así la cuota sale en positivo.
• 0;0 al final son el valor futuro y el tipo de pago al final del período, los dos con cero.

Construir la tabla de cuotas y saldos

Ahora vamos a rellenar las 120 filas de la tabla: cuota, intereses, capital y saldo. La cuota es siempre la misma (232,22), así que se copia tal cual. Los intereses, el capital y el saldo se calculan fila a fila.

1. En A7 escribe 1; en A8 escribe 2. Selecciona A7:A8 y arrastra el controlador de relleno hacia abajo hasta la fila 126 para tener los 120 meses.
2. En B7 escribe =$B$4 (la cuota con referencia absoluta, para que al copiar no se mueva). Cópiala hacia abajo hasta B126.
3. En E7 escribe =$B$1; en E8 escribe =E7-D8. Copia E8 hacia abajo hasta E126 para ir arrastrando el saldo.

De momento solo hemos puesto la cuota y un saldo inicial; los intereses y el capital los calculamos en los siguientes subapartados.

Calcular los intereses de cada período

Los intereses de cada cuota son el saldo al inicio del mes multiplicado por la tasa mensual. En la primera cuota el saldo es 20.000, así que los intereses son 20.000 por 7%/12, es decir, 116,67.

En C7 escribe:

=E7*$B$2/12

Copia C7 hacia abajo hasta C126. En la primera fila sale 116,67; en las siguientes va bajando porque el saldo es cada vez menor.

Calcular el capital devuelto y el saldo

El capital devuelto en cada cuota es lo que queda de restar los intereses a la cuota: capital = cuota − intereses. En la primera cuota es 232,22 − 116,67 = 115,55.

En D7 escribe:

=B7-C7

Cópialo hacia abajo hasta D126. Ahora la tabla se cierra sola: el saldo de la fila siguiente se calcula restando el capital devuelto al saldo anterior, y ya lo tenemos en la columna E desde el paso anterior.

Comprobar la última cuota y ajustar décimas

Por el redondeo a dos decimales, al llegar a la cuota 120 el saldo no baja a 0 exacto, sino que se queda con unas pocas céntimos. Es normal y se arregla en una sola fila.

Ve a la última fila con datos (la 126) y observa el saldo: si es un valor pequeño como 0,57, no hace falta cambiar nada. Si prefieres dejarlo a cero, haz lo siguiente:

1. En la fila 126 (mes 120), en la columna B sustituye la fórmula =$B$4 por =E126+C126 (saldo pendiente más intereses de ese mes).
2. En la columna D, la fórmula =B126-C126 te dará el capital necesario para liquidar.
3. Copia la fila 126 encima de sí misma para fijar el saldo a 0,00.

Para validar la tabla entera, suma la columna D (capital devuelto) y comprueba que da 20.000,00. Puedes usar =SUMA(D7:D126) en una celda auxiliar.

Analizar cómo cambia la tabla durante el préstamo

Una vez tienes la tabla, compararla en distintos puntos es la mejor forma de entender cómo funciona un préstamo. La cuota se queda fija, pero su composición cambia mucho: en la primera cuota pagas 116,67 de intereses y solo 115,55 de capital (pagas más en intereses que en capital); en la cuota 60, los intereses son 69,36 y el capital 162,86; en la última, los intereses son apenas 1,34.

Para ver esto en la propia hoja, en una zona vacía como G6:I9, escribe esta pequeña comparativa:

Lo que demuestra esta comparativa es que el peso de los intereses depende mucho del plazo: cuanto más largo es el préstamo, más cuota se va en intereses. Si subes el plazo a 240 meses con el mismo capital e interés, la cuota baja pero los intereses totales se disparan. Si lo reduces a 60 meses, la cuota sube pero los intereses totales caen a la mitad.

Para profundizar, la función PAGO.PRINC.ENTRE (en inglés CUMPRINC) devuelve el capital total pagado entre dos períodos, útil cuando quieres saber cuánto llevas devuelto a fecha de hoy.

Detectar errores en la tabla de préstamo

Una tabla de amortización se rompe por cuatro causas muy concretas. Reconocerlas a tiempo ahorra mucho trabajo.

Por qué PAGO devuelve un resultado inesperado

Si PAGO da un número enorme o negativo, casi siempre es porque la tasa y el plazo no están en la misma unidad. Los casos típicos:

• Pones el 7% anual tal cual en PAGO, sin dividir entre 12: la cuota sale 1.750.
• Pones el plazo en años (10) en vez de en meses (120): la cuota sale 23.221,84.
• Escribes el capital en positivo en vez de con el negativo dentro de la fórmula, y la cuota sale en negativo: -232,22.

La solución es revisar la fórmula y volver a escribirla como =PAGO(B2/12;B3;-B1;0;0) para que la tasa sea mensual y el plazo en meses.

Por qué el saldo final no llega a cero

El caso más común: el saldo se queda en 0,57 o 1,23 en la última fila. La causa es el redondeo de la cuota a dos decimales en cada fila, que acumula céntimos de diferencia. El arreglo es el que vimos en la sección anterior: en la última fila, la cuota debe ser el saldo pendiente más los intereses del mes, en lugar de la cuota fija.

Si aun así el saldo queda muy lejos de cero, lo más probable es que la tabla se haya ordenado o que las referencias se hayan desplazado al copiar. Selecciona A7:E126, ve a Datos > Ordenar y comprueba que la columna A (Mes) está en orden ascendente; si no lo está, ordénala. Luego revisa que B7 sigue siendo =$B$4 (referencia absoluta al capital).

Llevar la tabla de préstamo a una hoja reutilizable

Si vas a usar esta tabla con varios préstamos, conviene dejarla bien blindada para no romper las fórmulas al cambiar datos.

1. Define nombres para las celdas de entrada: selecciona B1, ve a Fórmulas > Nombres definidos > Definir nombre y llámalo Capital. Haz lo mismo con B2 (llámalo InteresAnual) y con B3 (PlazoMeses).
2. Sustituye en las fórmulas B1, B2 y B3 por esos nombres: =PAGO(InteresAnual/12;PlazoMeses;-Capital;0;0).
3. Protege las celdas con fórmulas: selecciona el rango B7:E126, ve a Inicio > Celdas > Formato > Proteger celdas y marca Bloqueada. Luego ve a Revisar > Proteger hoja para que solo se puedan cambiar los datos de entrada.

Si el inventario de préstamos crece hasta necesitar algo más serio, con cientos de contratos y varios usuarios, el siguiente paso natural es pasar la información a Access, la base de datos de Microsoft para Windows.

Lo que ya sabes hacer con una tabla de préstamo

• Preparar los datos del préstamo en celdas separadas con sus etiquetas.
• Calcular la cuota mensual con =PAGO(tasa;nper;va;vf;tipo).
• Repartir cada cuota entre intereses y capital con fórmulas que usan el saldo y la tasa.
• Comprobar que el saldo final llega a cero ajustando la última cuota.
• Detectar los errores típicos: tasa y plazo en unidades distintas, capital con signo cambiado, redondeo acumulado y referencias desplazadas.

El siguiente paso natural es añadir una columna de amortización anticipada para ver cómo bajan los intereses si haces un pago extra cada año; la fórmula del capital devuelto (=B-C) se mantiene, solo cambia el saldo inicial.

Dudas al montar una tabla de préstamo en Excel