sábado, agosto 11, 2007

Tabla de amortización con Excel

Varias veces en el pasado he recibido consultas sobre cómo construir una tabla de amortización de préstamos con Excel.

Aclaremos que hay varios sistemas de amortización, y aquí mostraré como construir la tabla de amortización con cuota fija. A quien quiera ver otras posibilidades le recomiendo la página Excel Avanzadode Adolfo Aparicio. Allí podrán descargar un archivo Excel (Prestamos.xls) con muchas variantes de tablas de amortización.

En esta nota veremos como construir un cuadro de amortización de prestamos que sea flexible en cuanto al tipo de plazo (mensual, trimestral, anual, etc.) y la cantidad de plazos. Nuestro modelo se divide en dos partes: el cuadro de datos





y la tabla de amortización



El cuadro de datos contiene los parámetros necesarios para el cálculo: monto/importe del préstamo, tasa de interés anual, período del pago en meses (1: mensual, 3: trimestral, 12: anual) y número de períodos. Así por ejemplo, en un préstamo a tres años con pagos trimestrales el período de pago será 3 y el número de períodos 12.

Las fórmulas en la tabla de amortización son las siguientes:

Pago (columna B): =PAGO($C$4*($C$5/12);$C$6;-$C$3).
La expresión ($C$4*($C$5/12) nos permite expresar la tasa de interés anual en términos del plazo de pago del préstamo.

Cuota del interés (columna C): =PAGOINT($C$4*($C$5/12);A11;$C$6;-$C$3)

Cuota del capital (columna D): =PAGOPRIN($C$4*($C$5/12);A11;$C$6;-$C$3)

Saldo (columna E): =E10-D11, es decir, resta el pago a cuenta del capital del saldo anterior.

Capital amortizado: =D11+F10, el complementario de la la columna anterior.

La tabla contiene 120 filas con fórmulas, es decir sirve para préstamos de hasta 10 años. Para hacer invisibles las filas que están fuera del rango del préstamo, usamos Formato Condicional a lo largo de todas las columnas del cuadro de amortización:



En la fórmula del formato condicional, =$A12>$C$6, hay que prestar atención a la dirección semi-absoluta del primer miembro.

Technorati Tags:

37 comentarios:

  1. Hola, mi nombre es David. En el caso que quisiera hacer desaparecer los errores #NUM! de las columnas C, D, E y F, podría hacerlo modificando la fórmula del formato condicional?
    Gracias

    ResponderBorrar
  2. Hola David

    no estoy seguro de entender tu consulta ya que en la nota explico que hacemos desaparecer las filas con el resultado #NUM usando formato condicional. Has descargado el archivo con el ejemplo?

    ResponderBorrar
  3. Correcto. Las filas desaparecen pero lo que quiero decir, es si hay alguna manera para hacer desaparecer el marcado del error #NUM!, por motivos de estética en la hoja. Supongo que desmarcando el tipo de error en Herramientas, Comprobaciones de errores...pero no resultaría incoherente.
    Gracias...

    ResponderBorrar
  4. David,
    sigo sin entender tu consulta. Dado que el contenido de las celdas se vuelve "transparente", no se ve el #NUM. La única forma de ver el contenido de las celdas "transparentes" es cambiar las definiciones de la fuente.
    De todas maneras, con una macro podemos ocultar realmente las filas (lo que manualmente harías con el menú Formato-Filas-Ocultar.

    ResponderBorrar
  5. Sí, tienes razón Jorge. No se ve el #NUM!, se ve un marcado en la celda, es este signo ^ . El cual desaparece si yo lo desabilito en Herramientas, comprobación de errores...
    Gracias de todos modos.

    ResponderBorrar
  6. Como puedo incluir una columna de abonos a capital

    ResponderBorrar
  7. Como puedo agregar una columna para abonos al captal, me interesa la formula.

    ResponderBorrar
  8. Si te refieres a los pago de capital, estos aparecen en la columna D

    ResponderBorrar
  9. Buena herramienta, gracias por la explicacion

    ResponderBorrar
  10. Grandiosa ayuda. Me has hecho la noche menos larga!!!! GRACIAS!!!!

    ResponderBorrar
  11. Muchas gracias.... es de gran ayuda un saludo.

    ResponderBorrar
  12. En el caso de ue mi tabla de amortización sea mensual a tres años el número de periodos son 36, sin embargo si cambio a trimestral el número de periodos serían 12, pero cuando cambian los datos como ya tenia prediseñado lo de los 36 periodos al momento de convertirse en 12 lo demas aparece como cero o como falso, mi pregnta es como eliminar esos datos, ya que mi profesor me dijo que en la fecha pusiera la uncion logica =SI(B12,FECHA(AÑO(A11),MES(A11)+(12/m),DIA(A11))) pero que tambien debo de añadir una mas para eliminar esos datos, espero haberme explicado y que alguien me ayude ya que no encuentro la respuesta...

    ResponderBorrar
  13. El modelo expuesto en la nota se adapta al número de períodos ocultando las otras líneas. Creo que no has aplicado el formato condicional que explico en la última parte de la nota.

    ResponderBorrar
  14. Hola! tu me puedes explicar como usase el formato condicional para hacer desaparecer las filas esque ya intente pero no me se desaparecen las filas. Gracias!

    ResponderBorrar
  15. Usando la fórmula que aparece en la última imagen de la nota. Apretamos el botón formato y definimos el color de la fuenta de manera que coincida con el color del fondo de la celda cuando la condición se cumple.

    ResponderBorrar
  16. Hola, un favor, en el último cálculo de interés me aparece elNUM...mi tabla es a 5 periodos, por lo que cambie el 12 por el 5 en la fórmula...pero al momento de hacer la fórmula condicional, no se si debe ser $A12 o qué...me ayudas?
    gracias

    ResponderBorrar
  17. El #NUM aparece si no aplicas el formato condicional correctamente. Te sugiero que descargues el archivo del ejemplo.

    ResponderBorrar
  18. Hola,

    Tengo un préstamo con las siguientes condiciones:
    Capital= 57000 € al 7,25% de interés nominal anual durante 9 meses. Los intereses junto con el capital de devolverán al vencimiento.

    El banco me informa que los intereses ascienden a 3.133,81 €, pero mis cálculos en Excel me arrojan una cifra de 4.132,50 € ¿Qué estoy haciendo mal?

    Gracias y felicitaciones por el blog.

    José

    ResponderBorrar
  19. El banco está aplicando interés compuesto. Para hacer el cálculo puedes hacer esto: en la celda
    A1 = 57000
    A2 = 7.25%
    A3 = A2/12 (la tasa mensual)
    A4 = 9 (el plazo del préstamo)

    En la celda A5 (o cualquier otra) pones esta fórmula

    =A1*((1+A3)^A4-1)

    ResponderBorrar
  20. Hola Jorge,

    Existe una función en Excel para realizar este cálculo, que me indicas:

    "...En la celda A5 (o cualquier otra) pones esta fórmula =A1*((1+A3)^A4-1)..."

    Gracias,

    José

    ResponderBorrar
  21. Me parece que no. Las funciones financieras en Excel se refieren a préstamos con pagos periódicos de capital e interés, a diferencia del tuyo donde existe un único pago.

    ResponderBorrar
  22. Hola Jorge,
    Gacias por tu modelo, muy útil y fácil de aplicar.
    Saludos
    Edgardo

    ResponderBorrar
  23. Hola, He hecho un cuadro como indicais arriba para un prestamo entre particulares, tengo varias dudas. Una de ellas es las retenciones, no tendrían que ir especificadas? y necesitaria saber cual es el interes legal del dinero.

    ResponderBorrar
  24. No necesariamente entre particulares. Además hay que tener en cuenta que se trata de un modelo general que, naturalmente, no puede tomar en cuenta las regulaciones de cada país en particular.

    ResponderBorrar
  25. Gracias por contestarme, te expecifico un poco mas, es un prestamo de particular a autonomo para empresa en España, necesito saber como calcular la retencion para que todo este bien ante Hacienda, la retencion segun ellos es del 21%, como lo puedo incluir dentro de tu formula.

    ResponderBorrar
  26. Maria Jose, dado que no vivo ni trabajo en España no conozco los detalles de las retenciones.
    Te sugiero que me envíes una explicación breve por mail privado (la dirección aparece en el enlace Ayuda, en la parte superior de la plantilla).

    ResponderBorrar
  27. bUENAS MUCHAS HOLA MI NOMBRE ES CLAUDIA, GRACIAS POR TU MODELO, QUE LO ESTOY APLICANDO PERO PREGUNTO PORQUE LOS VALORES DE CAPITAL E INTERESES MES SALEN EN NEGATIVOS, ESTOY HACIENDO EL EJEMPLO QUE DEJAS ARRIBA PERO ME SALEN EN NEGATIVOS ESOS NUMERPO PORQUE? QUE DEBO HACER?

    ResponderBorrar
  28. Claudia, si te fijas con atención verás que el argumento que representa el monto del capital (la celda C3 en el ejemplo) se ingresa precedida con el signo menos.
    Excel interpreta los pagos de un préstamos como un flujo de caja negativo y por este motivo el resultado es ngeatiivo. Al ingresar el monto del préstamo como número negativo el resultado se convierte en positivo.
    Algo más: no escribas los comentarios en mayúsuculas (es como si estuvieras gritando).

    ResponderBorrar
  29. Sr. Jorge por favor ayuda, no puedo hacer desaparecer con el formato condicional las filas cuando estan por fuera del rango del credito... he aplicado y aplicado a lo de su ejemplo y nada... todo parece estar en orden... me puede regalar un correo electronico para poder enviarselo por favor... pues siguen apareciendo las filas con los demas numeros... :( no doy ...
    gracias,

    ResponderBorrar
  30. Klaumark,
    el correo aparece en el enlace Ayuda, en la parte superior de la plantilla.

    ResponderBorrar
  31. HOLA EN DONDE PUEDO DESCARGAR EL ARCHIVO DE EJEMPLO?

    ResponderBorrar
  32. en donde puedo descargar el archivo de ejemplo?

    ResponderBorrar
  33. Haciendo clic en el enlace donde dice "construer un cuadro de amortizacion de prestamos"

    ResponderBorrar
  34. Está explicado en la nota (función PAGOPRIN)

    ResponderBorrar
  35. Para ayudar un poco con el error de #NUM. He fijado las diferentes celdas diferente a como está en el ejemplo dado por el autor, solo tienen que revisar donde ustedes colocaron estas celdas y hacer los cambios pertinentes, si no les funcionan las "comas" cambien por "punto y coma":

    Celda Duracion "B6"

    Columna Pagos "G"

    En la primera casilla (celda) de pagos (o la No. 1), he colocado la siguiente formula:
    =SI(B6<1,"",1)

    En la siguientes casillas (celdas) de la columna de pago:
    =SI(G5="","",SI(G5>=$B$6,"",G5+1))

    Esto hará que incremente el número hasta igualarse a la cantidad de pagos previamente establecidos, y ademas, si no tenemos número positivo allí, la tabla estará en blanco.

    En las demas formulas solo añadan al principio esto:

    =SI(G5="","",

    Y cierren con ")"

    Se verá asi:

    Pago
    =SI(G5="","",PAGO($B$4*($B$5/12),$B$6,-$B$3))

    Interes:
    =SI(G5="","",PAGOINT($B$4*($B$5/12),G5,$B$6,-$B$3))

    Cuota Capital:
    =SI(G5="","",PAGOPRIN($B$4*($B$5/12),G5,$B$6,-$B$3))

    Saldo:
    =SI(G5="","",K4-J5)

    ResponderBorrar
  36. MUCHAS GRACIAS, COMO HAGO LA TABLA PARA 180 PERÍODOS GRACIAS

    ResponderBorrar
  37. Sencillamente, agregando las filas necesarias con sus correspondientes fórmulas.
    Te sugiero que veas este post donde muestro como hacerlo usando Power Query.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.