Tabla de amortización con Excel

sábado, agosto 11, 2007

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:

35 comments:

Anónimo,  05 noviembre, 2007 12:08  

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

Jorge L. Dunkelman 05 noviembre, 2007 20:22  

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?

Anónimo,  05 noviembre, 2007 21:58  

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...

Jorge L. Dunkelman 06 noviembre, 2007 17:31  

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.

Anónimo,  06 noviembre, 2007 21:22  

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.

Juan 11 marzo, 2010 21:06  

Como puedo incluir una columna de abonos a capital

Juan 11 marzo, 2010 21:07  

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

Jorge L. Dunkelman 11 marzo, 2010 23:36  

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

Anónimo,  12 abril, 2010 04:10  

Buena herramienta, gracias por la explicacion

Charro Negro 30 setiembre, 2010 06:47  

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

Anónimo,  27 julio, 2011 19:35  

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

Anónimo,  23 abril, 2012 03:37  

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...

Jorge L. Dunkelman 24 abril, 2012 07:01  

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.

Anónimo,  17 mayo, 2012 21:13  

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!

Jorge L. Dunkelman 19 mayo, 2012 12:51  

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.

caro 23 mayo, 2012 02:58  

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

Jorge L. Dunkelman 26 mayo, 2012 11:25  

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

Anónimo,  12 junio, 2012 15:25  

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é

Jorge L. Dunkelman 12 junio, 2012 16:50  

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)

Anónimo,  12 junio, 2012 17:17  

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é

Jorge L. Dunkelman 13 junio, 2012 07:10  

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.

Anónimo,  17 junio, 2012 05:36  

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

MARIA JOSE 22 agosto, 2012 13:48  

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.

Jorge L. Dunkelman 22 agosto, 2012 14:19  

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.

MARIA JOSE 22 agosto, 2012 21:34  

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.

Jorge L. Dunkelman 23 agosto, 2012 14:56  

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).

Anónimo,  25 setiembre, 2012 22:19  

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?

Jorge L. Dunkelman 26 setiembre, 2012 06:59  

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).

Klaumark 27 setiembre, 2012 00:05  

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,

Jorge L. Dunkelman 05 octubre, 2012 11:30  

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

Anónimo,  19 agosto, 2013 20:16  

HOLA EN DONDE PUEDO DESCARGAR EL ARCHIVO DE EJEMPLO?

Anónimo,  19 agosto, 2013 20:22  

en donde puedo descargar el archivo de ejemplo?

Jorge Dunkelman 19 agosto, 2013 21:12  

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

juan carlos carvajal restrepo 28 abril, 2014 16:27  

como determino el valor de los abonos a capital

Jorge Dunkelman 28 abril, 2014 19:20  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP