Cómo Crear una Tabla de Amortización de Préstamos en Excel

¿Alguna vez has querido entender cuánto de tu pago mensual se va a interés, cuánto realmente reduce tu deuda y cómo cambia tu saldo mes con mes?

Porque una cosa es pagar… y otra muy distinta es entender qué está pasando con ese dinero.

En este tutorial te enseño a crear en Excel una tabla de amortización de préstamos para que puedas visualizar fácilmente:

• ✅ El pago mensual.
• ✅ Cuánto se va a interés.
• ✅ Cuánto se abona a capital.
• ✅ Cómo disminuye el balance en cada período.

Vamos a trabajar con este escenario: tienes 2 años para pagar un préstamo de $80,000 con un interés anual del 5.52%.

Paso 1: Calcula el pago mensual con la función PAGO

Usa =PAGO(tasa; nper; va):

• Tasa: el interés anual entre 12, para obtener la tasa mensual (5.52%/12).
• Nper: el número total de pagos, es decir, los años multiplicados por 12 (2*12 = 24).
• Va: el valor actual de la deuda (80000).

El resultado aparece en negativo porque es dinero que sale de tu bolsillo — antepón un signo negativo a la función (=-PAGO(...)) para verlo en positivo.

Paso 2: Genera la lista de meses con la función SECUENCIA

En vez de escribir del 1 al 24 a mano, usa =SECUENCIA(años*12). Por defecto, SECUENCIA empieza en 1 y avanza de uno en uno, así que obtienes automáticamente la lista completa de periodos. Si más adelante cambias la cantidad de años, esta lista se expande o se reduce sola.

Paso 3: Calcula el interés de cada mes con PAGOINT

PAGOINT(tasa; periodo; nper; va) calcula cuánto de tu pago se va a interés en un periodo específico. En vez de pedirlo mes por mes, señala la celda donde empieza tu rango dinámico de SECUENCIA y agrégale el símbolo # (por ejemplo A2#) en el argumento de periodo — Excel devuelve automáticamente el interés de los 24 meses de una sola vez. Igual que con PAGO, antepón el signo negativo para verlo en positivo.

Paso 4: Calcula el capital abonado cada mes

El capital es simplemente el pago mensual (fijo, con referencia absoluta usando F4) menos el interés de ese periodo. Como el interés ya es un rango dinámico, restarlo directamente también genera un rango dinámico con el capital abonado en cada uno de los 24 meses, sin necesidad de arrastrar la fórmula.

Paso 5: Calcula el balance restante

El balance de cada mes es el balance del mes anterior menos el capital abonado en ese periodo. Esta parte sí requiere arrastrar la fórmula hacia abajo (doble clic en el controlador de relleno), porque cada balance depende directamente del balance de la fila de arriba — no se puede resolver con un solo rango dinámico.

Paso 6: Evita errores al extender la tabla más allá del plazo

Si arrastras la fórmula del balance más allá de los meses que realmente necesitas (por ejemplo, hasta la fila 100, pensando en préstamos a plazos más largos), vas a ver una fila de ceros de más — porque la resta sigue calculando aunque ya no haya datos. Para evitarlo, envuelve la fórmula en:

=SI(ESNUMERO(celda_izquierda); balance_anterior - capital; "")

Así, si la celda de la izquierda no es un número, la fórmula devuelve un campo vacío en vez de un cero — y la tabla se ajusta sola si cambias el plazo del préstamo, sin dejar filas sobrantes.

Así se ve la tabla completa

Con el préstamo de $80,000 a 2 años (24 meses) y 5.52% de interés anual, así queda la tabla de amortización. Cada fórmula está lista para copiar y pegar directamente en Excel — respetan la misma posición de fila que ves en la columna gris de la izquierda.