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: MS Excel
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?
ResponderBorrarGracias
Hola David
ResponderBorrarno 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?
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.
ResponderBorrarGracias...
David,
ResponderBorrarsigo 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.
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...
ResponderBorrarGracias de todos modos.
Como puedo incluir una columna de abonos a capital
ResponderBorrarComo puedo agregar una columna para abonos al captal, me interesa la formula.
ResponderBorrarSi te refieres a los pago de capital, estos aparecen en la columna D
ResponderBorrarBuena herramienta, gracias por la explicacion
ResponderBorrarGrandiosa ayuda. Me has hecho la noche menos larga!!!! GRACIAS!!!!
ResponderBorrarMuchas gracias.... es de gran ayuda un saludo.
ResponderBorrarEn 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...
ResponderBorrarEl 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.
ResponderBorrarHola! 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!
ResponderBorrarUsando 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.
ResponderBorrarHola, 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?
ResponderBorrargracias
El #NUM aparece si no aplicas el formato condicional correctamente. Te sugiero que descargues el archivo del ejemplo.
ResponderBorrarHola,
ResponderBorrarTengo 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é
El banco está aplicando interés compuesto. Para hacer el cálculo puedes hacer esto: en la celda
ResponderBorrarA1 = 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)
Hola Jorge,
ResponderBorrarExiste 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é
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.
ResponderBorrarHola Jorge,
ResponderBorrarGacias por tu modelo, muy útil y fácil de aplicar.
Saludos
Edgardo
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.
ResponderBorrarNo 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.
ResponderBorrarGracias 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.
ResponderBorrarMaria Jose, dado que no vivo ni trabajo en España no conozco los detalles de las retenciones.
ResponderBorrarTe 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).
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?
ResponderBorrarClaudia, 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.
ResponderBorrarExcel 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).
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 ...
ResponderBorrargracias,
Klaumark,
ResponderBorrarel correo aparece en el enlace Ayuda, en la parte superior de la plantilla.
HOLA EN DONDE PUEDO DESCARGAR EL ARCHIVO DE EJEMPLO?
ResponderBorraren donde puedo descargar el archivo de ejemplo?
ResponderBorrarHaciendo clic en el enlace donde dice "construer un cuadro de amortizacion de prestamos"
ResponderBorrarEstá explicado en la nota (función PAGOPRIN)
ResponderBorrarPara 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":
ResponderBorrarCelda 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)
MUCHAS GRACIAS, COMO HAGO LA TABLA PARA 180 PERÍODOS GRACIAS
ResponderBorrarSencillamente, agregando las filas necesarias con sus correspondientes fórmulas.
ResponderBorrarTe sugiero que veas este post donde muestro como hacerlo usando Power Query.