Amortización de un préstamo en Excel: guía para empezar

La amortización de un préstamo en Excel permite calcular la cuota periódica y ver cómo cada pago se divide entre intereses y capital pagado. En este tutorial construirás un libro de amortización para un préstamo de 150.000 euros al 12 % anual durante 20 años, con pagos mensuales.

La función PAGO (PMT) está disponible en Excel 2007 y versiones posteriores. Al terminar, tendrás una hoja «Loan» con un plan de pagos, un saldo pendiente y un escenario para comparar plazos.

Qué es la amortización de un préstamo y qué información necesitas

La amortización reparte cada pago del préstamo entre dos partes: intereses, que remuneran la deuda, y capital, que reduce lo que debes. En una cuota fija, el pago total permanece igual; al principio se paga más en intereses y, conforme baja el saldo, se recupera más capital.

Para construir la hoja Loan usa estos datos:

PAGO calcula el pago periódico de una anualidad, pero no incorpora por sí sola una comisión de apertura. Si el préstamo tiene una, puedes registrarla aparte y sumarla al coste total. También conviene confirmar si el tipo pactado es fijo o variable: este modelo supone un tipo fijo.

Cómo calcula Excel la cuota con PAGO

PAGO (PMT) devuelve el pago periódico de una anualidad. Su sintaxis es:

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

La fórmula del ejemplo es:

=PAGO(3%/12;20*12;-150000)

El resultado es 831,90 euros mensuales. La tasa y el número de pagos deben estar expresados en la misma unidad: aquí se usa una tasa mensual y un plazo de 240 meses. Al introducir el capital con signo negativo, Excel muestra la cuota como un importe positivo. La documentación oficial de PAGO explica el comportamiento de cada argumento.

La cuota mensual que se actualiza al cambiar los datos

En la hoja «Resumen» del libro Loan, coloca los datos iniciales y una fórmula que dependa de ellos. Así podrás probar otras condiciones sin rehacer el cálculo.

1. Cambia el nombre de la hoja a Resumen.
2. Escribe Capital en A1 y 150000 en B1.
3. Escribe Tipo anual en A2 y 12 % en B2.
4. Escribe Años en A3 y 20 en B3.
5. Escribe Cuota mensual en A5.
6. En B5, escribe =PAGO(B2/12;B3*12;-B1).

El capital recuperado y los intereses de cada período

Ahora añade una hoja que muestre el primer año. Cada pago se dividirá en intereses y capital, y el saldo permitirá comprobar cuánto queda pendiente.

1. Crea la hoja Amortización.
2. En A1, escribe Período; en B1, Pago; en C1, Intereses; en D1, Capital; y en E1, Saldo.
3. En A2, escribe 1 y en A3, 2.
4. En B2, escribe =PAGO(Resumen!$B$2/12;Resumen!$B$3*12;-Resumen!$B$1).
5. En C2, escribe =Resumen!$B$1*Resumen!$B$2/12.
6. En D2, escribe =B2-C2.
7. En E2, escribe =Resumen!$B$1-D2.
8. Copia B2:E2 hasta la fila 13 con el controlador de relleno.

El signo negativo en el capital indica que reduce la deuda. En el cálculo real puedes escribir =B2-C2 para el capital y =Resumen!$B$1+D2 para el saldo, de modo que ambos aparezcan positivos. La plantilla de programa de amortización de Microsoft ofrece un punto de partida para esta distribución de pagos.

El saldo pendiente después de cada pago

Organiza la hoja Loan con los datos arriba y un control al final. Esta estructura separa las entradas, la cuota y la tabla de amortización.

1. Renombra la hoja Amortización como Loan.
2. En G1, escribe Capital y en H1, 150000.
3. En G2, escribe Tipo anual y en H2, 12 %.
4. En G3, escribe Meses y en H3, 240.
5. En G4, escribe Cuota y en H4, =PAGO(H2/12;H3;-H1).
6. En A2, escribe 1; en B2, =H4.
7. En C2, escribe =H1*H2/12.
8. En D2, escribe =ABS(B2-C2).
9. En E2, escribe =H1-D2.
10. En F2, escribe =E2.
11. Copia A2:F2 hasta el período 240 y usa referencias absolutas para H2, H3 y H1.

Para comprobar el final sin observar las 240 filas, añade en G6 Saldo final esperado y en H6 =0, y en G7 Control del saldo con esta fórmula:

=ABS(INDICE(E:E;241))

El control debe quedar cerca de cero. Si no lo hace, revisa que H3 sea 240, que la cuota proceda de H4 y que la última fila sea el período 240.

Comparar plazos y tipos de interés sin rehacer la hoja

Crea una hoja «Escenarios» y compara tres alternativas con la misma lógica del libro Loan: 120, 240 y 360 meses.

1. Crea la hoja Escenarios.
2. En A1, escribe Plazo; en B1, Capital; en C1, Tipo anual; en D1, Cuota; en E1, Total pagado; en F1, Intereses; en G1, Saldo final.
3. En A2, escribe 120; en A3, 240; en A4, 360.
4. Copia 150000 en B2:B4 y 12 % en C2:C4.
5. En D2, escribe =PAGO(C2/12;A2;-B2) y copia hasta D4.
6. En E2, escribe =D2*A2 y copia hasta E4.
7. En F2, escribe =E2-B2 y copia hasta F4.
8. En G2, escribe =ABS(INDICE(Loan!$E:$E;A2+1)); para un plazo distinto, utiliza el saldo de la fila final de su tabla.
9. Duplica la hoja Loan con escenarios de 120, 240 y 360 meses y cambia H3 en cada duplicado.

Los totalesshown están redondeados a dos decimales. A mayor plazo, la cuota baja, pero suele aumentar el coste total de intereses. La plantilla oficial de calculadoras de préstamos de Microsoft también permite modificar valores y comparar escenarios.

Cuándo aparecen diferencias entre Excel y el banco

Excel aplica una fórmula matemática; el banco puede redondear cada pago a céntimos, cobrar una comisión o utilizar una fecha de vencimiento concreta. Si la frecuencia de los vencimientos no coincide, la comparación también cambia.

Un cálculo con precisión completa conserva el capital pendiente. Al redondear la cuota y los intereses a dos decimales, el saldo final puede quedar en céntimos, pero una diferencia mayor apunta a otro problema.

=REDONDEAR(PAGO(3%/12;20*12;-150000);2)

El resultado sigue siendo 831,90. Ahora, si el banco carga 900 euros de comisión de apertura, el coste total será:

=831,90*240+900

El resultado es 200.556 euros. Esta fórmula es una comprobación puntual; para un calendario real conviene conservar la cuota con más decimales y redondear solo para presentarlo.

Revisa estos puntos antes de atribuir la diferencia al banco:

Qué puede calcular Excel con las funciones de amortización

PAGO organiza la cuota de un préstamo. Para construir el resto de la hoja se necesitan fórmulas de saldo, intereses y capital. No conviene confundir este uso con las funciones de amortización de activos.

La documentación oficial de PAGO.PRINC.ENTRE permite consultar el capital pagado entre dos períodos. En cambio, la ayuda de DDB y la ayuda de DVS se refieren a depreciación de activos.

En este libro, las fórmulas de la tabla hacen el trabajo: interés del período = saldo anterior × tasa; capital pagado = cuota − interés; saldo final = saldo anterior − capital pagado. Para un préstamo con amortizaciones anticipadas, cambios de tipo o carencias, este modelo debe ampliarse con datos y calendario específicos.

Lo que ya sabes hacer con la amortización de un préstamo

• Calcular la cuota de un préstamo con PAGO.
• Vincular capital, tipo anual, plazo y cuota a celdas editables.
• Separar cada pago en intereses y capital pagado.
• Controlar el saldo pendiente después de cada período.
• Comparar plazos de 120, 240 y 360 meses.
• Identificar diferencias de redondeo, comisiones y periodicidad.
• Distinguir la amortización de préstamos de la depreciación de activos.
• Guardar el libro Loan como plantilla reutilizable.

Dudas al amortizar un préstamo en Excel