Cuando pides un préstamo sabes cuánto pagas cada mes, pero no siempre queda claro cómo se distribuyen esos pagos entre capital e intereses ni cuánto ganas adelantando una cuota. Yo creo que esa falta de visibilidad es la causa de muchas decisiones equivocadas al comprar a crédito. En esta guía reescribo y simplifico el proceso para crear un calendario de amortización en Excel: desde los cálculos básicos hasta la tabla final, con fórmulas exactas y ejemplos numéricos prácticos que podrás copiar y adaptar.
Qué es un calendario de amortización y por qué usarlo
Un calendario de amortización es, en esencia, la hoja de ruta de un préstamo. Muestra, periodo a periodo, cuánto del pago mensual va a intereses y cuánto reduce el capital pendiente. Verlo por escrito cambia la perspectiva: deja de ser un número fijo en tu cuenta y se convierte en información para planificar. Yo lo uso como herramienta decisiva cuando valoro plazos, pagos anticipados o alternativas de financiación.
Más allá de la claridad, el calendario permite comparar escenarios. Por ejemplo, con el mismo pago mensual puedes ver cómo varía la duración del préstamo si subes o bajas el tipo de interés, o cuánto ahorrarías si realizas pagos extraordinarios. Esa comparativa convierte al calendario en un plan de acción, no en un simple informe.
En la práctica, un calendario te ayuda a responder preguntas concretas: ¿cuánto pagaré de intereses en total? ¿Cuándo llegaré al 50% del capital pagado? ¿Merece la pena amortizar ahora con la liquidez disponible? Si te interesa negociar condiciones en el concesionario o en el banco, presentar estos números te da ventaja, porque basas la conversación en impacto real sobre tu balance.
En esta guía mantengo los datos del ejemplo original para que puedas reproducirlos sin pérdida: compra de un vehículo con precio neto estimado de 68.529 USD, entrada de 6.853 USD, plazo de 60 meses y tipo anual del 5 %, lo que da un pago mensual de 1.164 USD según los datos iniciales. Mantengo esos valores para explicar fórmulas y transferirlas a tu propio caso.
Calcular los datos clave en Excel (fórmulas imprescindibles)
Antes de construir la tabla conviene verificar que los parámetros básicos del préstamo son coherentes. En Excel hay cuatro funciones que resuelven los casos más habituales: PMT, RATE, NPER y PV. Yo las empleo como comprobación y como punto de partida cuando alguno de los datos no está disponible en la oferta del banco.
PMT calcula el pago periódico cuando conoces el interés, el número de periodos y el capital. Si tu banco te da el pago y sospechas que hay error, PMT te dice si los números cierran. En Excel se usa así (si trabajas con meses, recuerda ajustar el tipo):
=-PMT(RATE,NPER,PV,[FV],[TYPE])Nota práctica: divide RATE entre 12 si el préstamo se amortiza mensualmente. Yo siempre pongo RATE/12 en la celda para evitar confusiones posteriores al copiar fórmulas.
Si desconoces el tipo de interés y tienes el resto de datos, RATE devuelve la tasa periódica. La función entrega el interés por periodo; multiplícalo por 12 para obtener la TAE aproximada en préstamos mensuales:
=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS])En escenarios donde sólo conoces cuánto puedes pagar cada mes y cuánto quieres pedir, NPER te indica cuántos meses necesitarás para saldar la deuda. Ajusta RATE a meses (RATE/12) si tu contrato está en términos anuales:
=NPER(RATE,-PMT,PV,[FV],[TYPE])Por último, PV calcula el importe financiable con un pago mensual determinado y un tipo conocido. Útil cuando negocias con un pago objetivo y quieres saber qué precio neto puedes afrontar:
=PV(RATE,NPER,-PMT,[FV],[TYPE])Consejo experto: mantén una hoja de control con las celdas de entrada separadas (precio, entrada, plazo, tipo anual, pago esperado). Así puedes enlazar esas celdas con las funciones sin reescribir fórmulas y probar escenarios con facilidad.
Construir la tabla de amortización en Excel: estructura y fórmulas
Una vez verificados los datos, el siguiente paso es levantar la tabla de amortización. Yo estructuro siempre las columnas de la misma forma para que sea más fácil seguirla y compartirla: Periodo, Saldo inicial, Pago mensual, Amortización de capital, Pago por intereses, Principal acumulado, Intereses acumulados, Saldo final, Tipo de interés y Pagos extraordinarios. Esa disposición recoge todo lo necesario para el análisis y permite añadir columnas auxiliares más adelante.
Para empezar, crea las celdas con los datos iniciales (Total, Entrada, Importe préstamo, Tipo anual, Plazo en meses, Pago mensual). En el ejemplo que mantengo en esta guía el Importe préstamo sería 68.529 – 6.853 = 61.676 USD, plazo 60 meses, tipo anual 5 % y pago 1.164 USD. Esos valores serán la referencia para las fórmulas que seguirán.
Completar la primera fila (mes 1) requiere fórmulas con referencias absolutas a las celdas de entrada. En mi hoja uso referencias con $ para fijar las celdas de parámetros. La primera fila suele contener estas fórmulas:
- Periodo: texto o número (Mes 1).
- Saldo inicial: = $B$3 (celda donde se calcule el importe del préstamo).
- Pago mensual: = $B$6 (celda con la fórmula PMT o el valor proporcionado por el banco).
- Interés periodo: = B9 * (I9/12) — donde B9 es el saldo inicial y I9 la celda con el tipo anual.
- Amortización de capital: = C9 – E9 (pago mensual menos intereses del periodo).
- Saldo final: = B9 – D9 (saldo inicial menos capital amortizado).
- Principal acumulado: = D9; Intereses acumulados: = E9; Tipo de interés: = B4 (copia del tipo anual de las celdas de entrada).
Insisto: utiliza el símbolo $ en las referencias a las celdas de parámetros para que, al arrastrar o copiar fórmulas, esos valores permanezcan constantes. Ese detalle elimina errores frecuentes al rellenar la tabla para 60 meses o más.
Autocompletar las filas siguientes sin equivocarte
La fila 2 (mes 2) parte del saldo final del mes anterior. La fórmula típica para el saldo inicial de la fila 2 es = H9 – I9 si H9 contiene el saldo final anterior y I9 recoge un pago extraordinario; si no vas a usar pagos extraordinarios basta con = H9. Yo prefiero incluir la lógica con la columna de pagos extraordinarios desde el principio para no tener que reestructurar la hoja más tarde.
Para el resto de columnas copia las fórmulas de la fila anterior: pago mensual, cálculo de intereses, amortización de capital y saldo final. Luego, en las columnas acumuladas suma el valor del periodo actual al acumulado anterior: por ejemplo, Principal acumulado = F9 + D10; Intereses acumulados = G9 + E10. Estas sumas te permiten ver la evolución acumulada sin cálculos externos.
Una vez que la fila 2 funcione correctamente, selecciónala (desde Saldo inicial hasta Pagos extraordinarios) y cópiala hacia abajo hasta llegar al último periodo (mes 60 en el ejemplo). Excel ajustará las referencias relativas y mantendrá las absolutas, completando la amortización. Mi recomendación es hacer una comprobación rápida en el último saldo: cuando el saldo final pase por debajo de cero, el préstamo está pagado y es posible que necesites ajustar la última cuota para evitar un pequeño sobrante por redondeos.
Cómo incluir pagos extraordinarios y experimentar con escenarios
Un valor diferencial de la tabla es poder simular pagos extraordinarios (lump sum). Yo acostumbro a reservar una columna para introducción de importes extraordinarios por periodo: así puedo proyectar el efecto de pagar la prima de la nómina o la devolución de impuestos en meses concretos sin alterar permanentemente las fórmulas base.
Introduce un importe puntual en la columna de Pagos extraordinarios en el periodo deseado. La fórmula del saldo final en ese periodo debe restar además ese importe: Saldo final = Saldo inicial – Amortización – PagoExtra. Si no hay pago extraordinario, la celda queda en blanco o en cero y la tabla continúa igual. Este método permite ver rápida y visualmente la reducción de intereses totales y la anticipación del vencimiento del préstamo.
También es útil variar el número de periodos iniciales y observar el resultado. Por ejemplo, si cambias 60 por 48 en la celda Plazo, la tabla y los pagos se recalculan (si el pago mensual se mantiene con PMT actualizará la cuota). Este tipo de pruebas te permiten decidir si conviene aumentar el importe mensual para acortar el plazo o mantener la cuota y alargar la vida del préstamo.
Consejo práctico: guarda varias hojas en el mismo libro —una con el escenario base, otra con pagos extraordinarios y una tercera con un plazo distinto—. Así comparas sin perder la versión original. Cambios menores en el tipo de interés o en la entrada pueden alterar sustancialmente los intereses pagados en 5 años, y tener las versiones lado a lado facilita la decisión.
Checklist práctico y errores comunes al crear la tabla
Antes de dar por finalizada tu tabla repasa estos puntos; son errores frecuentes que veo en hojas compartidas y que distorsionan el resultado:
- ¿TYPE y FV? Normalmente no son necesarios: deja esos argumentos en blanco salvo que sepas exactamente su efecto.
- ¿RATE en anual o mensual? Confirma que la celda de tipo esté en porcentaje anual y que en las fórmulas uses RATE/12 cuando calcules pagos mensuales.
- ¿referencias absolutas ($)? Verifica que las celdas con parámetros no cambian al copiar fórmulas.
- ¿redondeos? Comprueba si quieres redondear pagos a céntimos; los redondeos acumulativos pueden dejar un saldo residual final.
- ¿pagos extraordinarios aplicados correctamente? Asegúrate de que restan al saldo en la fila correspondiente y que las filas siguientes parten del saldo resultante.
Errores comunes explicados con detalle. Primero, confundir la tasa anual con la mensual: introducir 5 % en RATE y no dividir por 12 al usar PMT te dará un pago muy superior. Segundo, olvidarse del signo en PMT: Excel devuelve habitualmente un negativo para indicar salida de caja; el uso de -PMT evita confusiones en hoja de cálculos donde esperas ver números positivos para pagos. Tercero, no fijar las celdas de parámetros; al arrastrar la fórmula, la referencia cambiante rompe todo el calendario.
Mi recomendación de experto: añade una comprobación final sumando todos los pagos mensuales y comparando ese total con la suma de principal más intereses acumulados. Ambas cifras deben coincidir salvo por pequeños redondeos. Esa es la mejor validación antes de compartir la hoja o tomar decisiones financieras.
Comparativa: Excel frente a otras alternativas y pros/contras
No es obligatorio usar Excel; puedes hacerlo en Google Sheets o en una calculadora financiera. Aún así, elegir la herramienta correcta depende de prioridades: control, colaboración y compatibilidad. A continuación explico diferencias prácticas que yo aplico con mis clientes.
| Característica | Excel (escritorio) | Google Sheets |
|---|---|---|
| Compatibilidad con funciones financieras | Completa y estable; funciones PMT/NPER/RATE/PV igual que Sheets | Igual funcionalidad; ligeras diferencias de comportamiento en algunos casos complejos |
| Autofill y rendimiento | Mejor rendimiento en hojas grandes y mayor control de formatos | Bueno para colaboración en tiempo real; puede ralentizar con hojas muy extensas |
| Colaboración | Buena con OneDrive; menos fluida en tiempo real comparado con Sheets | Excelente colaboración y comentarios en directo |
| Portabilidad | Formato XLSX estándar; recomendado para presentaciones formales | Accesible desde cualquier navegador, fácil de compartir con permisos |
Pros y contras resumidos: Excel es mi opción cuando necesito precisión, rendimiento y control de formatos; además facilita macros y plantillas avanzadas. Google Sheets es preferible si voy a trabajar con otras personas simultáneamente y no quiero problemas de versiones. En cualquier caso, las fórmulas financieras básicas son transferibles entre ambas plataformas con mínimas adaptaciones.
Conclusiones prácticas
Crear un calendario de amortización en Excel no es complicado, pero sí exige orden: separar las celdas de entrada, usar referencias absolutas, comprobar signos y dividir la tasa anual por 12 para cálculos mensuales. Con esos principios cuesta menos de una hora replicar la tabla completa para un préstamo de 60 meses como el ejemplo que he mantenido en esta guía.
Si sólo vas a usar la hoja para decidir hoy, crea dos versiones: una con el escenario base y otra con pagos extraordinarios. Si la vas a compartir con pareja o asesor, deja notas junto a las celdas de entrada explicando qué representa cada valor para evitar malentendidos. Esa práctica reduce discusiones y acelera la negociación con la entidad financiera.
Por último, verifica los totales y compara la suma de pagos con capital más intereses acumulados. Esa comprobación rápida es la garantía de que la tabla refleja la realidad financiera y no un error de referencias o de redondeo.
Preguntas frecuentes
¿Puedo usar las mismas fórmulas si mi préstamo es con pago anual o trimestral?
Sí, las funciones son las mismas, pero debes adaptar la conversión del tipo y del número de periodos. Por ejemplo, si los pagos son anuales no divides el tipo entre 12 ni multiplicas el resultado por 12 al usar RATE; trabajas con la tasa anual tal cual y el número de periodos en años.
Si el pago es trimestral divide el tipo anual entre 4 y multiplica el número de años por 4; la consistencia entre unidad de periodo y tasa es imprescindible para que las fórmulas funcionen correctamente.
Mi consejo: coloca en la hoja una pequeña nota que indique la unidad de periodo (mensual, trimestral, anual) y utiliza ese mismo criterio en todas las referencias para evitar errores.
¿Qué hago si mi préstamo es de tipo variable?
La tabla que explico aquí funciona para préstamos a tipo fijo. Con un tipo variable necesitas introducir cambios en la columna de Tipo de interés por periodo y recalcular los intereses de cada fila en función del tipo vigente en ese mes. En la práctica, eso significa actualizar la columna de tipo y volver a copiar las fórmulas hacia abajo para reflejar el nuevo escenario.
Para mayor control, mantiene una fila de cambios de tipo con la fecha de efecto y crea una fórmula que consulte ese valor en la columna de tipo por periodo. Esa implementación es más técnica, pero mantiene la trazabilidad de cada ajuste.
Si la variación es frecuente, recomiendo guardar versiones mensuales de la hoja para auditar la evolución sin perder la hoja original.
¿Debo redondear los pagos a céntimos en la tabla?
Depende del propósito. Para decisiones rápidas, redondear a céntimos facilita la lectura. Pero ten en cuenta que el redondeo acumulado puede generar un saldo residual en la última fila. Si vas a usar la hoja para conciliaciones contables o presentaciones formales, conserva los decimales internos y sólo muestra el redondeo en un formato de visualización.
Una práctica segura es mantener dos columnas: una con valores exactos para cálculos y otra con la versión redondeada destinada a impresión o presentación.
Así evitas discrepancias y explicas al receptor por qué puede existir una pequeña diferencia en la última cuota.
¿Cómo verifico que no hay errores al final de la tabla?
Haz estas comprobaciones: suma todos los pagos mensuales y compárala con la suma del principal amortizado más los intereses acumulados; el resultado debe coincidir salvo diferencias mínimas por redondeo. También verifica que el saldo final del último periodo sea cero o negativo cercano a cero; un saldo positivo indica que faltan pagos.
Si detectas diferencias importantes, revisa las referencias absolutas y la conversión del tipo. Normalmente los errores vienen de ahí. Mantener las celdas de parámetros aisladas y bien identificadas facilita la revisión.
Como última medida, añade una fila de verificación al final que calcule la diferencia entre ambas sumas; un cero o una cifra muy pequeña te confirmará la coherencia de la tabla.
