Un cuadro de préstamo es una tabla que desglosa cada pago en la parte que reduce la deuda y la parte que corresponde a intereses. Sirve para saber cuánto se paga en total y how queda la deuda después de cada cuota. En este tutorial construirás uno para un préstamo personal de 10.000 USD a 60 meses, con la función PAGO y fórmulas enlazadas. El método funciona en Excel 2007 y posteriores, Excel para Mac, web y móviles.
Qué es un cuadro de préstamo y para qué sirve
Un cuadro de préstamo o tabla de amortización muestra, período por período, la cuota que pagas, cuánto de ella son intereses, cuánto se abona al capital y cuánto saldo queda pendiente.
En nuestro caso, el libro Excel se llama Préstamo personal. La hoja Datos guardará las condiciones del préstamo; la hoja Amortización contendrá el cuadro completo.
Partimos de 10.000 USD, una tasa anual del 6 % y 60 cuotas mensuales. El cuadro permitirá comprobar que:
- Cada cuota tiene la misma cantidad: 193,33 USD.
- Al principio se paga más en intereses.
- El saldo disminuye hasta llegar a cero.
- El total abonado será 11.599,80 USD.
- Los intereses sumarán 1.599,80 USD.
Cómo se calcula la cuota con PAGO
PAGO (en inglés PMT) devuelve el pago periódico de una anualidad. Su sintaxis es:
=PAGO(tasa; nper; va; vf; tipo)| Argumento | Significado | En el ejemplo |
|---|---|---|
| tasa | Interés por período | 6%/12 |
| nper | Número total de pagos | 60 |
| va | Capital recibido hoy | -10000 |
| vf | Saldo pendiente al final | 0 |
| tipo | 0 al final; 1 al inicio | 0 |
El capital va en negativo porque represents dinero recibido. Así Excel devuelve la cuota como una cantidad positiva.
La regla más importante: la tasa y el número de pagos deben usar la misma unidad. Una tasa mensual necesita un número de pagos expressed en meses; una tasa anual, un número de años o de pagos anuales.
El interés periódico: convertir el anual en mensual
La tasa anual del 6 % se convierte en tasa mensual dividiendo entre los 12 pagos del año:
=6%/12El resultado es 0,5 %. En un préstamo con pagos anuales se usaría 6 % directamente, porque entonces cada período equivale a un año.
Si divides la tasa anual entre 12, pero conservas 60 pagos, estás mezclando unidades: la cuota saldrá demasiado alta.
El valor actual y el valor final
va es el valor actual: el capital que recibes hoy, 10.000 USD. Se escribe como -10000.
vf es el valor final: el saldo que quedaría después del último pago. En un préstamo totalmente amortizado es 0. Si no lo escribes, Excel usa 0 como valor predeterminado.
Cuotas al inicio o al final del período
El último argumento, tipo, distingue cuándo se paga:
0: al final de cada período. Es el caso habitual de las cuotas mensuales.1: al inicio de cada período, como un pago anticipado mensual.
Para el préstamo del tutorial usaremos 0. Una cuota pagada al inicio reduce algo más el capital durante el primer período, por lo que el resultado no es idéntico.
Calcular la cuota de un préstamo paso a paso
Primero creamos el libro Préstamo personal y una hoja llamada Datos.
- Abre Excel y guarda el archivo como
Prestamo personal.xlsx. - Cambia el nombre de la hoja inicial a Datos.
- Escribe los encabezados y los datos que aparecen abajo.
- Selecciona B5 y escribe la fórmula de la cuota.
- Presiona Entrar.
| A | B | |
|---|---|---|
| 1 | Concepto | Valor |
| 2 | Importe del préstamo | 10000 |
| 3 | Tasa anual | 6% |
| 4 | Plazo en años | 5 |
| 5 | Cuota mensual | 193,33 |
| 6 | Pagos por año | 12 |
| 7 | Número de pagos | 60 |
=PAGO(6%/12;5*12;-10000;0;0)El resultado es 193,33 USD.
Construir las tablas de datos del préstamo
La hoja Datos debe quedar con las variables realimentadas. B7 calcula los pagos totales a partir del plazo y la frecuencia:
=B4*B6Ahora las fórmulas del cuadro usarán estas celdas, no cifras escritas a mano. Así podrás cambiar el importe, la tasa o el plazo y ver cómo se actualiza todo.
| Celda | Dato | Fórmula o valor |
|---|---|---|
| B2 | Importe | 10000 |
| B3 | Tasa anual | 6% |
| B4 | Plazo | 5 |
| B5 | Cuota | PAGO |
| B6 | Pagos por año | 12 |
| B7 | Número de pagos | =B4*B6 |
Comprobar que cuota por número de pagos da el total
Añade dos comprobaciones en la hoja Datos:
| A | B | |
|---|---|---|
| 1 | Concepto | Valor |
| 2 | Importe del préstamo | 10000 |
| 3 | Tasa anual | 6% |
| 4 | Plazo en años | 5 |
| 5 | Cuota mensual | 193,33 |
| 6 | Pagos por año | 12 |
| 7 | Número de pagos | 60 |
| 8 | ||
| 9 | Total abonado | 11599,80 |
| 10 | Intereses totales | 1599,80 |
=B5*B7Y para los intereses:
=B9-B2Construir el cuadro de amortización completo
Añade una hoja llamada Amortización. Sus columnas serán: período, cuota, interés, capital abonado y saldo.
- Crea los encabezados en A1:E1.
- En A2 escribe 1 y en A3 escribe 2.
- Selecciona A2:A3 y arrastra el controlador de relleno hasta A61.
- En B2,B3 y las filas siguientes, enlaza la cuota de la hoja Datos.
- En C2 calcula el interés sobre el saldo inicial.
- En D2 calcula el capital abonado.
- En E2 calcula el saldo posterior.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Período | Cuota | Interés | Capital | Saldo |
| 2 | 1 | 193,33 | 50,00 | 143,33 | 10000,00 |
| 3 | 2 | 193,33 | 49,28 | 144,05 | 9856,67 |
| 4 | 3 | 193,33 | 48,56 | 144,77 | 9712,62 |
| 5 | 4 | 193,33 | 47,84 | 145,49 | 9567,85 |
| 6 | 5 | 193,33 | 47,11 | 146,22 | 9422,36 |
=Datos!$B$5=E2*Datos!$B$3/Datos!$B$6=B2-C2=E2-D2Datos!$B$5 es absoluta porque la cuota vive en otra hoja y no debe moverse al copiar la fórmula. El signo $ se añade con F4; en Mac, se usa Cmd+T.El interés del primer período y el saldo inicial
La primera cuota se calcula sobre 10.000 USD. El interés es 0,5 % de ese saldo:
=10000*6%/12Resultado: 50,00 USD de interés. La parte que reduce la deuda es:
=193,33-50,00Resultado: 143,33 USD de capital.
El saldo posterior queda en 10.000 – 143,33 = 9.856,67 USD. En la hoja, E2 contiene el saldo inicial 10000 y E2 obtiene 10000,00 al restar el capital.
El capital que se devuelve en cada cuota
A partir de la segunda fila, el interés se calcula con el saldo de la fila anterior:
=E2*Datos!$B$3/Datos!$B$6En E3 queda el saldo después de la segunda cuota. La fórmula de E3 es:
=E2-D3Selecciona C2:E2 y arrastra el controlador de relleno hasta la fila 61. Cada fila debe tomar el saldo de la anterior.
Para comprobar el cierre, en una celda libre escribe:
=E61El resultado debe ser 0,00 o un importe tan cercano a cero que la moneda lo muestre como 0,00.
Ver cómo el interés baja y el capital sube
Los primeros períodos muestran el patrón:
| Período | Cuota | Interés | Capital | Saldo |
|---|---|---|---|---|
| 1 | 193,33 | 50,00 | 143,33 | 10.000,00 |
| 2 | 193,33 | 49,28 | 144,05 | 9.856,67 |
| 3 | 193,33 | 48,56 | 144,77 | 9.712,62 |
| 4 | 193,33 | 47,84 | 145,49 | 9.567,85 |
| 5 | 193,33 | 47,11 | 146,22 | 9.422,36 |
Al bajar el saldo, el interés disminuye y la parte destinada a capital aumenta. Ese cambio es la amortización del préstamo.
Un segundo caso: cuota conocida y capital por calcular
Ahora cambiamos la pregunta: en lugar de conocer el importe y calcular la cuota, conocemos la cuota y queremos saber cuánto se puede pedir.
Crea una hoja Escenario B con estos datos:
| A | B | |
|---|---|---|
| 1 | Concepto | Valor |
| 2 | Cuota mensual | 500 |
| 3 | Tasa anual | 9% |
| 4 | Plazo en años | 2 |
| 5 | Capital solicitado | 10944,50 |
| 6 | Número de pagos | 24 |
=-PAGO(9%/12;2*12;500;0;0)PAGO devuelve el capital con signo negativo cuando la cuota entra como valor positivo. El signo menos delante de la función lo convierte en 10.944,50 USD.
La tasa mensual es 0,75 % y el número de pagos, 24.
Comprobar el total abonado en 24 cuotas
En B7 escribe:
=B2*B6El resultado es 12.000,00 USD. En B8, calcula los intereses:
=B7-B5El resultado es 1.055,50 USD.
Por qué las celdas no se actualizan o muestran errores
Un cuadro de préstamo puede fallar por referencias incorrectas, unidades mezcladas o fórmulas que no llegaron a todas las filas. La mayoría de los problemas se detectan mirando las dos primeras filas y la última.
La cuota sale #¡VALOR! o un número descabellado
La causa más frecuente es usar la tasa anual con un número de pagos mensuales: 60 no son 60 años. También puede aparecer un error si falta una coma o un punto y coma entre argumentos.
Comprueba que la fórmula sea:
=PAGO(Datos!$B$3/Datos!$B$6;Datos!$B$7;-Datos!$B$2;0;0)Si el capital se escribe positivo, la cuota aparecerá negativa. Para obtenerla positiva, usa -10000.
El saldo no llega a cero al final
Revisa que la fórmula del saldo sea =E2-D3 en la fila 3 y que cada fila use la anterior. Si arrastraste la fórmula desde una fila intermedia, puede estar.tomlando el mismo saldo repetido.
Comprueba también que B2:B61 enlazan la misma celda de cuota. Si una fila quedó con un valor escrito a mano, el saldo no cerrará.
Puedes forzar un recálculo con F9. Si usas cálculos manuales, activa el cálculo automático desde Fórmulas > Opciones para el cálculo.
La tabla se desordena al copiar las fórmulas
Las referencias de la hoja Datos deben ser absolutas. Selecciona la celda que contiene Datos!B5 y pulsa F4 para convertirla en Datos!$B$5; en Mac, Cmd+T.
Después selecciona las fórmulas de las filas 2 y 3 y arrastra juntas hasta la fila 61. Verifica que la fila 3 use E2 y no E3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Período | Cuota | Interés | Capital | Saldo |
| 2 | 1 | 193,33 | 50,00 | 143,33 | 10000,00 |
| 3 | 2 | 193,33 | 49,28 | 144,05 | 9856,67 |
| 4 | 3 | 193,33 | 48,56 | 144,77 | 9712,62 |
Qué funciones de Excel qué needed para la amortización
PAGO (en inglés PMT) es la función principal. PAGO.PRINC.ENTRE (en inglés CUMPRINC) calcula el capital acumulado pagado entre dos períodos, pero no sustituye el desglose fila a fila.
| Función | Uso en el cuadro |
|---|---|
| PAGO (PMT) | Calcula la cuota periódica |
| PAGO.PRINC.ENTRE (CUMPRINC) | Capital acumulado entre dos períodos |
| SUMA | Suma intereses o importes |
| ABS | Compara saldos sin distinguir el signo |
Alternativas para recalcular sin tocar cada fórmula
En hojas pequeñas, puedes calcular el interés manualmente en cada fila. Para banyak datos, convertir el rango en una tabla de Excel permite copiar fórmulas con referencias estructuradas ajusta coherentes.
BUSCARX (en inglés XLOOKUP), disponible en Excel 2021, 2024 y Microsoft 365, no interviene en el cálculo de la cuota. Sí puede ayudar a buscar datos auxiliares, como la tasa aplicada a cada cliente dentro de otra hoja.
Lo que ya sabes hacer con un cuadro de préstamo
- Calculaste una cuota mensual de 193,33 USD con PAGO para un préstamo de 10.000 USD.
- Convertiste la tasa anual en una tasa periódica y mantuviste las mismas unidades.
- Construiste un cuadro con período, cuota, interés, capital y saldo.
- Comprobaste que el saldo final llega a cero y que los intereses suman 1.599,80 USD.
- Calculaste el capital solicitado cuando la cuota ya era conocida: 10.944,50 USD.
- Detectaste y corregiste referencias movibles, unidades mezcladas y filas incompletas.
El siguiente paso es duplicar la hoja Datos y comparar dos ofertas de préstamo con la misma estructura, cambiando únicamente la tasa, el plazo o el importe.
Dudas al crear un cuadro de préstamo en Excel
1. ¿Cómo calculo el abono a capital de cada cuota de un préstamo?
En el cuadro de amortización, el interés de cada período se calcula sobre el saldo anterior: =SaldoAnterior*TasaPeriodica. El abono a capital es la cuota menos ese interés, con =Cuota-Interés. Así puedes ver cuánto de cada pago reduce la deuda.
2. ¿Cómo simulo un pago anticipado en mi cuadro de préstamo?
Reduce el saldo del período en el que harás el pago y arrastra hacia abajo las fórmulas de saldo. Si además quieres recalcular la cuota con el saldo menor, usa =PAGO(TasaPeriodica;PagosRestantes;-Saldo;0;0). El pago extra puede reducir también el total de intereses.
3. ¿Qué simulador de préstamos puedo construir en Excel?
Un simulador básico necesita importe, tasa anual, plazo, frecuencia de pago y cuota. Usa PAGO para obtener la cuota y una tabla con período, interés, capital y saldo para ver la evolución. Con dos juegos de datos puedes comparar tasas o plazos distintos.
4. ¿Cómo hago una calculadora de préstamos personales en Excel?
Coloca los datos del préstamo en celdas fijas y calcula la cuota con =PAGO(TasaAnual/PagosPorAño;PagosTotales;-Importe;0;0). Después reutiliza esa cuota en la tabla de amortización. Al cambiar el importe o la tasa, todo se recalcula.
5. ¿Cuánto es el interés total de un préstamo bancario?
Multiplica la cuota por el número de pagos y resta el capital recibido. En el ejemplo del artículo, 60 cuotas de 193,33 USD suman 11.599,80 USD; menos 10.000 USD, los intereses ascienden a 1.599,80 USD.
6. ¿Cómo calculo el pago mensual de un préstamo?
Divide la tasa anual entre el número de pagos por año y usa PAGO con el capital en negativo. Para 10.000 USD al 6 % anual durante 60 meses: =PAGO(6%/12;60;-10000;0;0), que devuelve 193,33 USD.
7. ¿Qué interés se aplica en los préstamos personales?
La función PAGO trabaja con una tasa periódica. En un préstamo mensual, una tasa anual del 6 % se convierte en 0,5 % mensual al dividir entre 12. Ese 0,5 % se aplica al saldo pendiente en cada período del cuadro de amortización.
8. ¿Cómo funciona una calculadora de intereses de préstamo en Excel?
La cuota se obtiene con PAGO y cada período se desglosa en interés sobre el saldo anterior y capital abonado. La suma de los intereses de la columna es el coste total del préstamo. La hoja puede comparar escenarios cambiando las celdas de entrada.
Fuentes
- MicrosoftPAGO — documentación oficialconsultado el 28/10/2026
- MicrosoftAtajos propios y reasignar atajos en Excel — documentación oficialconsultado el 28/10/2026
- MicrosoftPAGO.PRINC.ENTRE — documentación oficialconsultado el 28/10/2026
- MicrosoftPegar, Opciones de pegado y Pegado especial — documentación oficialconsultado el 28/10/2026