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

Un simulador de cuadro de amortización es una hoja de Excel que, a partir del importe del préstamo, el tipo de interés anual y el plazo en meses, te calcula la cuota mensual y desglosa mes a mes qué parte paga intereses y qué parte devuelve capital. En este tutorial vas a montar uno desde cero: empiezas con la calculadora de la cuota y terminas con un cuadro completo que puedes reutilizar cambiando los datos. Funciona en cualquier versión de Excel, desde 2007 hasta Microsoft 365, y también en Excel para la web y para Mac, porque solo necesitas la función PAGO (PMT) y aritmética básica.

Qué es un cuadro de amortización y para qué te sirve en Excel

Cuando pides un préstamo, cada mes pagas la misma cantidad (la cuota), pero esa cuota se reparte entre dos cosas: los intereses que el banco te cobra por el dinero que aún te debe, y la parte que devuelve el capital pedido. Al principio pagas más intereses y menos capital; al final, casi todo es capital.

Para verlo claro, imagina un préstamo de 10.000 a un tipo de interés anual del 5% a devolver en 24 meses. La cuota mensual sale de unos 438,71 y, mes a mes, la parte de intereses baja mientras sube la de capital. El cuadro de amortización es la tabla que refleja ese reparto mes a mes y el capital que te queda por devolver después de cada pago.

En Excel esto se monta con pocas piezas: el importe, el tipo anual, el plazo y la cuota calculada con la función PAGO (PMT). A partir de ahí, una columna por mes y unas pocas fórmulas te dan el resto.

Las piezas del préstamo que vas a escribir

Antes de tocar fórmulas, conviene tener claras las seis celdas que controlan todo el simulador.

• Importe del préstamo (B2): el dinero que te conceden, en este tutorial 10.000.
• Tipo de interés anual (B3): el porcentaje que cobra el banco al año, por ejemplo 5%. Lo escribes como 5, no como 0,05, y lo divides entre 12 cuando calcules la cuota.
• Plazo en meses (B4): el número total de cuotas, aquí 24.
• Cuota mensual (B5): lo que se paga cada mes; se calcula con PAGO.
• Capital pendiente inicial (B6): al principio es igual al importe (10.000); en cada mes baja.
• Intereses del mes en curso (B7): lo que se paga de intereses en la fila que estés mirando, calculado sobre el capital pendiente.

Más abajo, a partir de la fila 12, escribirás una fila por cada mes con el número de mes, el capital amortizado, los intereses y el capital pendiente después de pagar.

Cómo se calcula la cuota mensual de un préstamo

La cuota se obtiene con la función PAGO (en inglés, PMT), que es la fórmula financiera básica de Excel para préstamos con cuotas iguales. Su sintaxis es:

=PAGO(tasa; nper; va)

Donde:

• tasa es el tipo de interés por período. Si el tipo anual es del 5% y pagas cada mes, escribes 5%/12 o B3/12.
• nper es el número total de períodos (cuotas). Aquí, 24.
• va es el valor actual, es decir, el capital que te prestan. Se escribe positivo, aunque PAGO lo devuelve en negativo porque representa dinero que sale de tu bolsillo.

Con los datos del ejemplo (10.000 al 5% anual a 24 meses), la fórmula es =PAGO(5%/12;24;10000) y el resultado es -438,71. La cuota real que pagas es 438,71; el signo negativo solo indica que es un pago.

La hoja lista para empezar

Abre un libro nuevo y crea la zona de datos en la esquina superior izquierda. Deja la celda A1 vacía y rellena los encabezados y valores a mano.

1. En la celda A2 escribe Importe, en B2 escribe 10000.
2. En A3 escribe Tipo anual (%), en B3 escribe 5.
3. En A4 escribe Plazo (meses), en B4 escribe 24.
4. En A5 escribe Cuota mensual, en B5 déjala vacía de momento.
5. En A6 escribe Capital pendiente inicial, en B6 déjala vacía.
6. En A7 escribe Intereses del mes, en B7 déjala vacía.
7. En A11 escribe Mes, en B11 Capital amortizado, en C11 Intereses, en D11 Capital pendiente.

Calcular la cuota con la función PAGO

Con los datos ya escritos, llega el momento de la primera fórmula. Selecciona B5 y escribe:

=PAGO(B3/12;B4;B2)

Pulsa Entrar. En B5 aparece -438,71. Para que se vea en positivo, escribe delante un signo menos:

=-PAGO(B3/12;B4;B2)

Ahora B5 muestra 438,71. La cuota sale negativa porque PAGO interpreta que el préstamo es un ingreso y cada cuota, una salida de dinero. Al añadir el menos delante, conviertes el resultado en lo que realmente pagas cada mes.

Construir el cuadro mes a mes: capital amortizado y pendiente

Ahora viene la parte que da sentido al simulador: una fila por cada cuota con el reparto entre capital e intereses. Empieza en la fila 12, justo debajo de los encabezados que escribiste en la fila 11.

El capital amortizado de cada mes

En A12 escribe 1 (el primer mes). En B12 escribe:

=PAGO(B3/12;B4;B2)

Esta fórmula da la parte de la cuota que es capital en el primer mes. La idea de PAGO con un solo 1 y -B3 al final es pedirle solo el primer pago; cuando arrastres hacia abajo, la fórmula se adaptará. Sin embargo, una forma más estable de calcular el capital amortizado cada mes es restar los intereses del mes a la cuota fija. Por eso, en B12 escribe directamente:

=$B$5-C12

De momento, como C12 está vacío, Excel lo trata como 0 y B12 muestra 438,71, que es la cuota entera. Cuando rellenes C12 con los intereses, B12 pasará a mostrar solo la parte de capital.

Pulsa Entrar, selecciona B12 y arrastra el controlador de relleno (el cuadradito de la esquina inferior derecha) hasta la fila 35 (24 meses más una fila de margen). Al arrastrar, las referencias a $B$5 se quedan fijas.

Los intereses de cada mes

Los intereses del mes se calculan sobre el capital pendiente del mes anterior. La primera fila (fila 12) calcula los intereses sobre 10.000. En C12 escribe:

=D11*$B$3/12

Aquí D11 contiene el texto "Capital pendiente", pero al estar vacío y usarse en una multiplicación, Excel lo cuenta como 0. Para que la primera fila calcule bien los intereses sobre el importe inicial, escribe en C12 la fórmula que apunta al capital inicial:

=$B$2*$B$3/12

Resultado: 41,67. A partir de la fila 13, los intereses bajan porque el pendiente ya es menor. En C13 escribe:

=D12*$B$3/12

Y arrastra el controlador de relleno desde C13 hasta C35. Cada mes, los intereses se reducen porque el capital pendiente de la fila anterior ha bajado.

El capital pendiente después de cada cuota

En D12 escribe el capital pendiente inicial, que es el importe del préstamo:

=$B$2

En D13, el pendiente después de pagar la primera cuota es el inicial menos el capital amortizado en B12:

=D12-B12

Selecciona D13 y arrastra el controlador de relleno hasta D35. La columna D va bajando hasta llegar a 0 en la última fila del plazo.

Ahora vuelve a la columna B: en B13, la fórmula =$B$5-C13 ya muestra la parte de capital real del segundo mes. Ajusta la fórmula de B12 para que también reste los intereses: en B12 escribe =$B$5-C12 y arrastra hasta B35.

Comprobar que el cuadro cierra en cero

Un cuadro de amortización bien hecho cierra en dos sitios: la suma del capital amortizado debe coincidir con el importe del préstamo, y el capital pendiente de la última fila debe ser 0 o casi 0.

1. Ve a una celda vacía, por ejemplo F12, y escribe =SUMA(B12:B35). El resultado debe ser 10000, el mismo importe de B2.
2. Mira la última fila del cuadro. En el ejemplo, D35 vale 0,33. Esa pequeña diferencia aparece por el redondeo a dos decimales de cada cuota y se corrige en la sección de errores.
3. Para ver el total de intereses pagados, escribe en G12 =SUMA(C12:C35). Con este préstamo sale 528,93, que es lo que cuesta de más devolver el dinero en 24 meses.

Convertir los datos en una entrada con sus celdas nombradas

Hasta ahora las fórmulas usan referencias como B2, B3 o B4. Si las nombras, las fórmulas se leen mucho mejor y tú te evitas errores al copiarlas. Para crear nombres definidos, usa la cinta:

1. Selecciona B2 y abre el cuadro Asignar nombre. Escribe Importe y acepta.
2. Selecciona B3 y crea el nombre tasaAnual.
3. Selecciona B4 y crea el nombre plazo.
4. Selecciona B5 y crea el nombre cuota.

Ahora reescribe la fórmula de B5 con los nombres:

=-PAGO(tasaAnual/12;plazo;Importe)

El resultado no cambia: 438,71. La fórmula se entiende de un vistazo: paga, con la tasa anual dividida entre 12, durante el plazo, el importe.

Simular qué pasa si cambia el tipo o el plazo

La gracia de tener los datos en celdas separadas es que puedes probar combinaciones sin tocar las fórmulas. Por ejemplo, qué pasa si el plazo sube de 24 a 36 meses.

1. Cambia B4 de 24 a 36. La cuota en B5 baja de 438,71 a 299,71.
2. Fíjate en la columna D del cuadro: la última fila ya no es la 35, sino la 47. Tienes que arrastrar las fórmulas de B12, C12 y D12 hacia abajo hasta la fila 47 para ver el nuevo cuadro completo.
3. La suma de intereses, en G12 (si la fórmula se ha arrastrado también hasta C47), sube de 528,93 a 789,37. A más meses, menos cuota pero más intereses totales.

Haz la prueba inversa: vuelve a poner 24 en B4 y cambia B3 a 7. La cuota sube a 435,32 y los intereses totales del cuadro suben a 447,69.

Por qué el cuadro no cuadra al cambiar los datos

Tres son los fallos que más se repiten al montar un simulador de cuadro de amortización en Excel. Los tres producen resultados numéricamente mal, pero no errores visibles, así que pasan desapercibidos si no se comprueba el cierre.

El signo negativo en PAGO y en el pendiente

PAGO siempre devuelve un número negativo, porque interpreta que el préstamo es dinero que entra y cada cuota, dinero que sale. Si no tienes en cuenta el signo, la cuota aparece con un menos delante y el capital pendiente sube en vez de bajar.

En el ejemplo, la fórmula =PAGO(tasaAnual/12;plazo;Importe) da -438,71. Para mostrarla en positivo, se usa =-PAGO(...) o =PAGO(...;-Importe). En el capital pendiente, la fórmula de D13 es =D12-B12. Si B12 es negativo, el pendiente sube en vez de bajar, y el cuadro nunca cierra.

El tipo anual sin dividir entre 12

Este es el fallo más común. La fórmula =PAGO(tasaAnual/12;plazo;Importe) necesita la tasa mensual. Si escribes =PAGO(tasaAnual;plazo;Importe), Excel interpreta que el tipo del 5% se aplica cada mes y la cuota sale disparada: 525,15 en vez de 438,71. La diferencia parece pequeña en porcentaje, pero en 24 meses son más de 2.000 de más.

Para detectarlo, pon las dos fórmulas en la misma hoja, una al lado de la otra, y compara. La correcta siempre es la que usa la tasa dividida entre 12.

El último pendiente no es exactamente 0

Aunque las fórmulas sean correctas, al redondear cada cuota a dos decimales queda un pequeño resto. En el ejemplo, D35 vale 0,33. Hay dos formas de cerrarlo bien:

• En la última fila, sustituye la fórmula de B35 por =D34 para que el último capital amortizado absorba el resto. Los intereses de esa última fila se calculan entonces como =D34*tasaAnual/12.
• O bien, redondea la cuota con =REDONDEAR(-PAGO(tasaAnual/12;plazo;Importe);2) en B5. Así todas las cuotas son idénticas y el último pendiente queda exactamente en 0.

=REDONDEAR(-PAGO(tasaAnual/12;plazo;Importe);2)

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

• Calcular la cuota mensual de un préstamo con la función PAGO a partir del importe, el tipo anual y el plazo.
• Montar el cuadro mes a mes con cuatro columnas: mes, capital amortizado, intereses y capital pendiente.
• Comprobar que el cuadro cierra en cero sumando el capital amortizado y revisando el último pendiente.
• Cambiar las variables (importe, tipo o plazo) y ver cómo se recalcula todo el cuadro.
• Detectar los tres errores típicos: signo negativo, tipo anual sin dividir entre 12 y redondeo del último pendiente.

El siguiente paso es añadir un gráfico de líneas con dos series: el capital pendiente (que baja) y los intereses acumulados (que suben). Lo tienes en la propia hoja: las columnas C y D del rango A12:D35 son la base del gráfico, en Insertar > Gráficos > Gráfico de líneas.

Dudas sobre el simulador de cuadro de amortización en Excel

Dudas sobre el simulador de cuadro de amortización en Excel