Tabla de amortización en Excel

Amortizar significa extinguir gradualmente una deuda o un préstamo a través de pagos periódicos. El objetivo de una tabla de amortización es especificar el detalle de cada uno de los pagos hasta la liquidación total del préstamo.

Es muy probable que alguna vez hayas visto una tabla de amortización, especialmente si te has acercado a una institución bancaria para solicitar un crédito de auto o un crédito hipotecario. Generalmente el asesor del banco te preguntará el monto y la duración deseada del crédito y de inmediato te mostrará una tabla con el desglose de los pagos a realizar.

El asesor no hace los cálculos manualmente en el instante sino que utiliza un sistema computacional desarrollado para ese fin. Nosotros también podemos automatizar este tipo de tareas al crear una tabla de amortización en Excel y de esa manera conocer fácil y rápidamente la cantidad de pagos a realizar y así como los montos exactos destinados al pago de intereses y al pago de capital.

Variables para el cálculo

Para poder crear la tabla de amortización en Excel debemos tener al menos la siguiente información:

  • Monto del crédito: Es indispensable conocer el monto del préstamo. Esta es la cantidad neta otorgada por la institución financiera al aprobarnos un crédito.
  • Tasa de interés: No solo debemos cubrir el monto total del crédito sino también la tasa de interés cobrada por la institución financiera ya que es la manera como obtienen ganancias por la prestación de dicho servicio. Generalmente encontraremos especificada la tasa de interés de forma anual.
  • Número de pagos: Es necesario establecer el número de pagos que deseamos realizar para cubrir nuestra deuda. Es una práctica muy común establecer una cantidad de pagos mensuales (en bloques anuales): 12, 24, 36, 48, etc.

Como regla general, entre mayor sea el número de pagos a realizar, menor será el monto de cada uno de los pagos mensuales, pero el interés a pagar será mucho mayor. Si esta aseveración no te queda muy clara, seguramente lo estará una vez que hayamos creado nuestra tabla de amortización en Excel y podamos analizar diversos escenarios para un crédito.

Cálculo del monto de pago

Una vez que tenemos las variables previamente mencionadas podremos calcular el monto de cada uno de los pagos mensuales utilizando la función PAGO de Excel. Esta función tiene tres argumentos obligatorios y que son precisamente nuestras variables: Tasa de interés para cada período, número total de pagos, y monto del crédito.

Suponiendo que vamos a solicitar un crédito por un monto de $150,000 y que tenemos una tasa de interés anual del 12% y queremos realizar 24 pagos mensuales. La fórmula que debemos utilizar para calcular el pago mensual será similar a la siguiente:

=PAGO(1%,24,-150000)

La institución financiera nos proporcionó el dato de 12% de interés anual, pero para la función PAGO necesita utilizar la tasa de interés para cada período, que en este caso es mensual, así que debo hacer la división entre 12 para obtener el resultado de 1% de interés mensual. El segundo argumento de la función es el número de mensualidades en las que pagaremos el rédito y finalmente el monto del crédito. Observa el cálculo del pago y la fórmula implementada al leer los valores de los argumentos de las celdas en la columna B:

Tabla de amortización en Excel

Para nuestro ejemplo ha quedado un pago de $7,061.02 que tendremos que hacer durante 24 meses para saldar nuestra deuda.

Creación de la tabla de amortización

La tabla de amortización en Excel será el desglose de cada uno de los pagos mensuales para conocer el monto exacto destinado tanto al pago de intereses como al pago del capital de nuestra deuda. El cálculo de pago de intereses lo haremos con la función PAGOINT de Excel. Esta función utilizará los mismos argumentos que la función PAGO pero agregará un cuarto argumento para indicar el número de período para el cual deseamos calcular el monto del interés a pagar.

Utilizando nuestro ejemplo de préstamo, calcularemos el interés a pagar en el primer período utilizando una fórmula como la siguiente:

=PAGOINT(1%,1,24,-150000)

Compara esta fórmula con la función PAGO de la sección anterior y verás que la única diferencia es que el segundo argumento indica el período que deseamos calcular, que en este caso es el primer período. Para obtener el interés a pagar en cada uno de los 24 pagos podemos implementar una tabla como la siguiente:

Pago de intereses en una tabla de amortización en Excel

Observa que la fórmula de la celda E2 hace referencia a las variables de la columna B y las he colocado como referencias absolutas porque deseo que dichas referencias permanezcan fijas al momento de copiar la fórmula hacia abajo. El segundo argumento de la función PAGOINT hace referencia a la columna D que es precisamente donde se encuentra el número de pago correspondiente.

Por el contrario, para obtener el monto que se abona mes a mes a nuestra deuda, debemos utilizar la función PAGOPRIN de Excel. La sintaxis de esta función será prácticamente idéntica a la de la función PAGOINT. Considera la siguiente fórmula que nos ayuda a obtener el pago a capital para el primer período:

=PAGOPRIN(1%,1,24,-150000)

De esta manera calcularemos el monto de nuestro pago mensual que estará destinado al pago de capital de nuestra deuda. De igual manera, el segundo argumento de la función indica el número de período para el cual estamos haciendo el cálculo. Observa el resultado al incluir esta fórmula en nuestra tabla utilizando las variables previamente definidas:

Pago a capital en una tabla de amortización en Excel

Si revisas con detenimiento verás que la suma del pago de interés y pago a capital para todos los períodos nos da el total obtenido con la función PAGO. De esta manera podemos deducir que estas tres funciones son complementarias: La suma del resultado de las funciones PAGOINT y PAGOPRIN siempre será igual al resultado de la función PAGO.

Para finalizar nuestra tabla de amortización podemos agregar algunas columnas adicionales, por ejemplo el saldo en cada uno de los períodos:

Saldo en una tabla de amortización en Excel

El saldo es el monto del crédito menos la suma de todos los pagos a capital realizados hasta el momento. El saldo se va reduciendo con cada pago aunque no es una reducción constante ya que al inicio pagamos más interés que al final pero en el último pago llegamos a liquidar el total del monto del crédito.

Como tal vez ya lo imaginas, si queremos cambiar nuestra tabla de amortización para tener 36 pagos mensuales será necesario agregar manualmente los nuevos registros y copiar las fórmulas hacia abajo. Es por eso que una mejor solución para crear una tabla de amortización en Excel es utilizar una macro para generar automáticamente la tabla.

Macro para tabla de amortización en Excel

Lo único que necesita hacer nuestra macro es leer los valores de la columna B e insertar las fórmulas correspondientes en cada fila de acuerdo al número de pagos a realizar. Para ello agregaré un botón de comando ActiveX en la hoja y colocaré el siguiente código en el evento Click del botón:

Private Sub CommandButton1_Click()

'Limpiar el contenido de las celdas antes de iniciar
Range("D2:G1200").ClearContents

'Obtener el número de pagos de la celda B3
num_pagos = Range("B3").Value

'Para cada fila de pago insertar las fórmulas correspondientes
For i = 1 To num_pagos
    Cells(i + 1, 4).Value = i
    Cells(i + 1, 5).Formula = "=IPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1)"
    Cells(i + 1, 6).Formula = "=PPMT($B$2/12,D" & i + 1 & ",$B$3,-$B$1)"
    Cells(i + 1, 7).Formula = "=$B$1-SUM($F$2:F" & i + 1 & ")"
Next i

End Sub

Las líneas 12, 13 y 14 son las encargadas de insertar las fórmulas que harán los cálculos y en VBA debemos utilizar el nombre de las funciones en inglés o de lo contrario obtendremos un error #¿NOMBRE? en nuestra hoja de Excel. Al momento de pulsar el botón se insertarán las fórmulas en las celdas correspondientes.

Macro para generar tabla de amortización en Excel

Con esto hemos terminado el desarrollo de una tabla de amortización en Excel que será funcional para conocer el detalle de los pagos necesarios para liquidar una deuda. Puedes descargar el libro de trabajo el cual contiene dos hojas, en la primera encontrarás la solución que tiene solo las fórmulas y en la segunda hoja la que contiene la macro.

91 pensamientos en “Tabla de amortización en Excel

  1. Luisa Trejo

    es un tema muy interesante, que en lo particular me ayuda muchisimo en mi trabajo, gracias

    Responder
    1. BEATRIZ MAUNA

      HOLA MUY INTERESENTE EL TEMA DE LA TABLA DE AMORTIZACION ME HA SIDO DE GRAN UTILIDAD EN MI TRABAJO

      GRACIAS

      BETTY

      Responder
  2. Gustavo

    Excel…ente aporte. Con esta pequeña macro he podido solucionar otro pequeño problema de excel. Adicionalmente me ha parecido muy bien tener la tabla de amortización para saber cuando caraj…s término de pagar mis tarjetas de crédito jaja. Saludos Moisés.

    Responder
  3. Eduardo Zeta

    Gracias por la informacion y la forma del calculo.

    Bendiciones.

    Responder
  4. Arturo Esteban Mejia Carranza

    La verdad se me hace uno de los temas que muchos debemos de considerar manejar en estas épocas que buen aporte y felicidades por este espacio que has creado

    Responder
  5. Mynor

    Buenisimo! muy útil!! Gracias!!

    Responder
  6. Gabriel

    Excelente apunte, muy bueno para implementarlo en el trabajo y en la cotidianidad!!!!

    Responder
  7. SERGIO

    Excelente mi amigo, muchas por tus aportaciones

    Responder
  8. Dean

    Wow! muy interesante! me alegra haberme suscrito a tu pagina para recibir estos tips!

    Responder
  9. jose

    es una excelente herramienta que nos sirve para saber cuanto es lo que pagariamos en nuestros creditos o para los que tienen negocio saber cuanto cobrar de acuerdo a los creditos otorgados

    muy interesante

    gracias por compartir esta informacion

    Responder
  10. jhon

    Sinceramente aprendo mucho de tus aportes sabes escribir como para aprender ,debes de ser un ing. de sistemas o algo asi gracias sigue adelante.

    Responder
  11. carmen

    GRACIAS ES MUY UTIL LA FUNCIÓN

    Responder
  12. Eduardo

    Gracias por tomarte el tiempo para mostrar tus conocimientos a los demás… Felicidades.

    Responder
  13. Jeannete

    Gracias es muy interesante ya que soy gestora de crédito PYME y me ayuda con los empresarios. Dios los bendiga.

    Responder
  14. JOSÉ YESID

    Muy interesante y útil.

    Responder
  15. José Luis

    Muy agradecido todo lo que he recibido me ha servido muchísimo. Nuevamente gracias.

    Responder
  16. Carolina

    Gracias, como siempre, por las aportaciones.

    Responder
  17. victor sagastume

    exelente trabajo, y gracias por compartir tus conocimientos.

    saludos.

    Responder
  18. auris

    Excelente, me gusta gracias.
    He aprendido bastante de esta página.
    Quiero saber cuales son las diferencias entre excel 2010 y excel 2013.
    Sé que debe estar en ese plan.

    Gracias!!

    Responder
  19. Willian

    Excelente aporte ..gracias

    Responder
  20. Xavi

    Práctica y muy útil en la vida diaria, espero pronto aportar algo para todos, gracias.

    Responder
  21. Maria de Jesus

    Que afortunada función, me moria de ganas como obtener una amortizacion, mil gracias por compartir este conocimiento me será muy útil, saludos

    Responder
  22. Junior

    Como siempre amigo, excelente aporte gracias.

    Responder
  23. ricardo

    gracias por sus articulos son pildoritas que ayudan mucho al buen entendimieto del excel
    saludos desde Colombia

    Responder
  24. Oscar Gearado

    Excelente aporte gracias Moisés

    Responder
  25. Luis Rey

    Como siempre excelente tema y muy bien explicado, me ha sido de gran ayuda, gracias por compartir tus conocimientos

    Responder
  26. Reynaldo Méndez

    Me perecio interesante, el documento de tabla de amortización, me servio mucho.
    Gracias por el contenido.

    Responder
  27. JORGE

    HOLA MOISES, EN TUS EJEMPLOS APARECE COMO 1%, MI DUDA ES QUE SINO ES 12%?
    =PAGOINT(1%,1,24,-150000)

    Responder
    1. Moisés Ortíz Autor

      Hola Jorge, el 12% es la tasa anual y ya que la función PAGOINT requiere la tasa para cada período (que en este ejemplo es mensual), se hace la división de 12% entre 12 lo cual me da el 1% para cada mes y por eso es el valor para el primer argumento.

      Responder
  28. C. Ramirez

    muy pero muy interesante, con ello se sale de muchas dudas,, adelante—

    Responder
  29. juan

    les agradezco la gentileza de colocar herramientas para mejorar la condicion de personas en su ambiente laboral
    atte.

    juan euceda

    Responder
  30. GLORIA+

    gracias Moisés por tan excelente aportes y temas he aprendido mucho

    Responder
  31. Cata Cajtunaj

    Woow, es de gran ayuda para mi….muchÍsimas gracias MOI…

    Responder
  32. ezequiel

    gracias por el aporte

    Responder
  33. jose

    Excelente!!! Gracias ………..

    Responder
  34. iSAURO

    GRACIAS MOISES. DE VERDAD ESTO ES FASCINANTE…!TENGO 66 AÑOS Y POCO DE INTENTAR ESTO DE LA COMPUTACION Y CON TUS APORTES NO SABES CUANTO ME HA AYUDADO Y SOBRE TODO APRENDER Y VIERAS COMO ME DESESTRESO REALIZANDO TUS PRACTICAS Y CUANDO OBTENGO EL RESULTADO..GUAUUUU ME EMOCIONO MUCHO.
    DIOS TE BENDIGA…….”EL DON DE DAR E HERENCIA MARAVILLOSA DE LA VIDA”

    Responder
  35. OSCAR

    Muchas gracias por el aporte mi estimada Moisés… síguele adelante¡¡!!

    Responder
  36. Mao

    Esta aplicación de la tabla de amortización es completamente nueva para mi. Todos los dias aprendo mas de Excel. Gracias por tus aportes.

    Responder
  37. JsantiagoC

    Gracias por tomarnos en cuenta. Su aportación es muy importante para quienes estamos interesados en conocer más a fondo a Excel.

    Responder
  38. HarryC

    Muy buen tema, me ha servido mucho esta pagina, los felicito excelente

    Responder
  39. Heraclio

    Muy simple, práctica y sobre todo útil… Gracias…

    Responder
  40. Andres Cg

    Exelente aporte, el portal de funciones de excel esta muy completo.
    Gracias
    Los felicito.

    Responder
  41. RUBEN DARIO JIMENEZ

    gracias por compartir tus conocimiento conmigo! que bien excel!

    Responder
  42. HENRY

    gracias interesante el tema …

    Responder
  43. Jorge Bustillos

    Excelente soy consultor en un ICAT de hidalgo y me es muy util mi buen Moises!, un abrazo!

    Responder
  44. Oscar Buitrago

    Que excelente herramienta me ayuda mucho en mis estudios. Gracias

    Responder
  45. Crista

    Muy buen material, mis dudas quedaron resueltas. Gracias

    Responder
  46. Hector De Leon

    Gracias por tan buena informacion y facil de entender estoy cursando matematica aplicada y esto me servira de mucho.
    Felicidades.

    Responder
  47. Johangps

    Muchas gracias. Me despejo varias dudas que tenia pero la pregunta es. Se puede hacer sin necesitad de macro por eje: Al ingresar el # de cuotas automáticamente lo hagan si necesitad de la macro.
    Gracias

    Responder
    1. Moisés Ortíz Autor

      Hola Johangps, es posible hacerlo sin macros solo que debes determinar un número fijo de pagos que mostrará la tabla de amortización de manera que puedas introducir las fórmulas en las celdas correspondientes. También debes recordar que en caso de tener un número menor de pagos observarás que varias celdas mostrarán un error y en ese caso puedes utilizar de la función SI.ERROR para evitar que las fórmulas desplieguen el error.

      Responder
  48. ERICK

    MUY BUENA LA EXPLICACION EN REALIDAD ES UN MUY BUEN APORTE YA Q HA MUCHOS NOS AYUDA TANTO EN EL TRABAJO COMO EN LO PERSONAL GRACIAS

    Responder
  49. glorys

    muy muy bueno me podrias informar como sigo tu pagina o como poder checar mas acerca de excel estoy muy atrasada en su uso y me interesa mucho aprender ya que es una herramienta de uso diario y x lo que vi subes hasta practicas.. gracias..

    Responder
  50. andres rios

    Necesito calcular la tabla de amortizacion a año y medio (36 meses), debo 27000 con una tasa anual del 30%,…

    Responder
  51. jenry

    gracias!!.. buena informacion y excelente ayuda

    Responder
  52. Enric

    Gracias! buena y muy practica!

    Responder
  53. Jos

    Excelente tema para armar procesos de extinción de deudas

    Responder
  54. luis antonio diaz martinez

    Muchisimas gracias, soy M:V:Z y estoy realizando mi maestria en planes de negocios.Me sirvio muchisimo sin esta tabla no se que hubiera hecho. Muchas gracias enhorabuena amigos.

    Responder
  55. educar

    Excelente explicacion!!! muy util

    Responder
  56. NAT

    MUCHAS GRACIAS, POR EL TIEMPO DEDICADO A ESTE ESPACIO, ME VA A SERVIR MUCHO EN MI TRABAJO.

    Responder
  57. Adriana Paola

    Tu tabla es muy buena sólo que al tratar de hacerla me sale error en saldo ############
    aunque copie tal cual la formula
    Que debo hacer. Gracias

    Responder
    1. Moisés Ortíz Autor

      Hola Adriana, ese no es un error de la fórmula si no que es Excel indicando que debes hacer más ancha la columna para poder desplegar todo el resultado.

      Responder
      1. Adriana Paola

        Muchas gracias, me sirvió mucho

        Responder
  58. MATEO

    gracias por tan valiosa información, me acaban de salvar la vida, estoy estudiando una maestría en administración financiera. Felicidades amigos.

    Responder
  59. nRAMIREZ

    EXCELente, me ayudo muchisimo en un contrato… mil gracias!!!! :)

    Responder
  60. Oscar

    Excelente exposición, muy clara. De gran utilidad y facil aplicación. Felicitaciones.

    Responder
  61. victor

    Muy claro el ejemplo. Me ayudaste mucho y te lo agradezco

    Responder
  62. Renán

    Muchas gracias por el aporte. Estaba buscando cómo hacer una tabla de amortización en Excel.

    Responder
  63. Josue

    Saludos Moises, trato de seguir la fórmula y el resultado que me dá es: #NAME? Qué estoy haciendo mal?

    Responder
    1. Moisés Ortíz Autor

      Hola Josue, es porque tienes Excel en inglés y por lo tanto debes utilizar los nombres de las funciones en inglés:
      PAGOINT = IPMT
      PAGOPRIN = PPMT

      Responder
  64. Jesús Hernán Páez Sánchez

    Moises le agradezco mucho todo lo que nos enseña, es de mucha ayuda en nuestro trabajo.
    Tengo un problema con la tabla de amortización, no la puedo crear en 64 bits, en cambio en 32 bits se crea normalmemnte, que hago

    Responder
    1. Moisés Ortíz Autor

      Hola Jesús, yo tengo Excel de 64 bits y esta tabla de amortización fue creada en esa versión. ¿Cuál es el problema específico que tienes?

      Responder
      1. Jesús Hernán Páez Sánchez

        Si Moisés gracias,hice la prueba,guardé la tabla de amortización en una usb y la abrí en otro computador con 64 bits y funcionó perfecto, entonces creo que es mi computador, no se de pronto falta algún drivers quíen sabe que será.

        De verdad muchas gracias

        Responder
  65. Julio Vásquez

    Excelente trabajo, te felicito y muchas gracias por compartir tus conocimientos.

    Responder
  66. ALFREDO CELY

    LA TABLA DE AMORTIZACION ME HA SIDO DE UNA AYUDA ESTUPENDA Y ESTOY MUY AGRADECIDO YA QUE LA EXPLICACION DE LA FORMULA ES MUY SENCILLA Y FACIL DE APLICAR.
    SALUDOS

    Responder
  67. Ernesto

    Muy buena, es la mejor con la que me he encontre.

    Responder
  68. JAPV

    Muy buen aporte, con esto podré calcular los pagos de mi préstamo.

    Responder
  69. justino barcelo

    muy practica la explicacion, exelente trabajo, felicidades.

    Responder
  70. jimmy arias

    muchas gracias, este tema me fue de gran ayuda

    Responder
  71. Jisas

    Excelente aporte. Exitos

    Responder
  72. leonidas

    gracias por la informacion es de mucha ayuda a la gente q no tenemos conocimientos en contabilidad

    Responder
  73. Rodolfo Mejias

    Hola, muchas gracias, una consulta, necesito calcular créditos, pero en vez de poner el interés anual, necesito hacerlo con el interés mensual, que cambios hay que realizar para conseguir eso? un abrazo

    Responder
    1. Moisés Ortíz Autor

      Hola Rodolfo, el único cambio es en el primer argumento de las funciones PAGOINT y PAGOPRIN. En el ejemplo, dicho argumento está dividido por 12 para obtener el interés mensual, en tu caso que ya tienes el interés mensual debes indicarlo directamente (sin hacer la división entre 12).

      Responder
  74. Luis Vásquez

    Saludos Moisés, Me pregunto si la expresión (IPMT) Y (PPMT) obedecen a alguna sigla o cuardan algun significado.

    Responder
    1. Moises Ortiz Autor

      Hola Luis, ambas son funciones de Excel y las siglas corresponden a su descripción en inglés:
      IPMT = Interest Payment
      PPMT = Principal Payment

      Tal como lo mencioné en el artículo, la equivalencia en español es IPMT = PAGOINT y PPMT = PAGOPRIN, solo que al programar en VBA debemos utilizar el nombre de las funciones en inglés.

      Responder
  75. henry vaca

    muchas gracias, Este tema me fue de gran utilidad para mi trabajo.
    saludos.

    Responder
  76. Oscar

    Excelente explicación sobre la amortización en Excel. Muchas Gracias

    Responder
  77. JUAN CARLOS

    FABULOSO, DE VERDAD MUY ÚTIL PARA CONOCER EN QUE SITUACIÓN ESTAS AL ADQUIRIR UN CRÉDITO

    Responder
  78. gustavo

    gracias fue de mucha ayuda, porque había perdido mi “financiera”

    Responder
  79. Danilo

    Excelente, muchas gracias por el aporte

    Responder
  80. Juan

    Muchisimas gracias por la ayuda

    Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>