Hoy utilizaremos nuestra potente hoja de cálculo Excel, para montar un sistema de amortización de préstamos, siguiendo el mismo mecanismo y la metodología que ya aplicamos en su momento para el modelo de amortización de préstamos siguiendo el sistema francés, que es el modelo estándar para el cálculo de préstamos e hipotecas. En esta ocasión, lo que haremos será calcular la amortización del préstamo, pero amortizando en cada cuota la misma cantidad de capital, es decir, en esta ocasión trabajaremos los préstamos con amortización de capital constante. Como siempre, tendremos también en cuenta la posibilidad de que exista un periodo de carencia en el que no se amorticen cuotas del principal del préstamo.
Las características básicas de este tipo de préstamos, son las siguientes:
- Los intereses se devengan al vencimiento de cada cuota.
- El capital que se amortiza es constante en cada cuota, es decir, el principal del préstamo que se va pagando, es siempre del mismo importe, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.
- Los intereses van disminuyendo y son menores en cada cuota.
- Las cuotas totales que se pagan, son variables y cada vez menores, debido a que los intereses son cada vez menores.
Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo en cualquier momento, pero es recomendable al menos, una lectura rápida por encima, para hacernos una idea y saber de que estamos hablando.
Lo primero que haremos en nuestra aplicación financiera para el cálculo de préstamos con amortización de capital constante, será construir un formulario con información sobre el préstamo, tal y como aparece en la siguiente imagen:
Para no escribir líneas de código innecesarias, solo añadiré aquí el código fuente (o la parte del código fuente) que sea diferente a la aplicación que ya vimos en su momento cuando estudiamos el método francés de amortización de préstamos.
Para lanzar el formulario con la información sobre este tipo de préstamos, tal y como muestra la imagen anterior, simplemente tendremos que añadir estas líneas de código en un módulo (al formulario le hemos puesto por nombre InfoPrestamoCapitalConstante):
| Sub Prestamo_Capital_constante() 'Lanzamos el formulario con info sobre 'el préstamo con amortización de capital constante InfoPrestamoCapitalConstante.Show End Sub |
Una vez hayamos lanzado ese formulario, y pulsemos el botón “Si”, nos aparecerá este segundo formulario, para informar directamente de las características del préstamo.
Para no ser excesivamente aburrido, aquí os dejo solo la parte del código que está directamente relacionada con los préstamos con amortización de capital constante, y más concretamente la parte correspondiente a las fórmulas de la tabla resultante:
| 'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))" 'seguimos poniendo el capital amortizado 'en la primera cuota Range("F18").Select ActiveCell.Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))" 'seguimos poniendo los intereses pagados Range("G18").Select ActiveCell.Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _ "RC[-2]*R8C6/R10C6,0))" 'seguimos poniendo el capital amortizado acumulado Range("H18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo los intereses acumulados Range("I18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo la cuota total Range("J18").Select ActiveCell.Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)" 'seguimos poniendo el resto de datos, es decir 'el capital vivo antes del pago de cada cuota, 'el capital amortizado, los intereses, el capital 'amortizado acumulado, los intereses acumulados, 'y el importe de las cuotas Range("E18").Select For i = 1 To CuotasTotales - 1 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _ "RC[-2]*R8C6/R10C6,0))" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next |
Lo que obtendremos será una tabla como la que podemos ver en este ejemplo (aquí solo sale una parte de la tabla). En ella podréis ver que en cada cuota, se amortiza siempre la misma cantidad de capital (del principal del préstamo):
Llegados a este punto, solo me queda por comunicaros a todos los lectores del blog, que desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.
25 comentarios:
Eres un crack del excel tio!!!
¿No tendrás nada de finanzas cuantitativas o de riesgos?
Un saludo y sigue así, me encanta este blog!
Gracias por tu comentario, pero soy un usuario de excel que simplemente quiere compartir algunas cosillas ;-)
Aunque tengo un montón de cosas a medio hacer: lo empiezo, paro, abro otro proyecto, vuelvo al primero, me canso, abro uno nuevo, vuelto al tercero, y así, hasta entrar en un bucle casi infinito, lamentablemente no tengo nada de lo que pides.
De nada, es que me ha encantado el blog.
¿Tienes algún manual del lenguaje que utiliza excel para programar las macros?
Me gustaría empezar a hacer cosillas aunque no creo que llegue a hacer cosas tan complicadas.
Un saludo.
Pues se utiliza VBA (Visual Basic para Aplicaciones), que es una versión recortada de Visual Basic (recortada, porque no es tan potente).
Si googleas encontrarás cientos de manuales, pero creo que el mejor, es el de la propia ayuda de Excel, me refiero a la ayuda desde el modo VBA (pulsando Alt + F11, para entrar en modo programación).
Muchas gracias, empezaré a probar pequeñas cosas a ver que tal.
Un saludo!
Hola Javier, necesito un poco de tu ayuda:
Respecto a esta entrada: http://hojas-de-calculo-en-excel.blogspot.com/2008/08/validacin-con-datos-en-otra-hoja.html
Necesito hacer lo mismo pero sin que en la lista se repitan los valores.
Sabes como puedo hacer eso, por mucho que lo intento no consigo nada ni se donde puedo hacerlo.
Espero tu respuesta.
Saludos, Manuel.
Imagina que tienes una hoja con una columna llena de datos,a lgunos repetidos, y que quieres montar una lista de validación con los datos únicos (no repetidos).
Para ello, podems hacer varias cosas. Yo crearía una columna adyacente a los datos repetidos, pero 2 columnas más allá. Es decir, si los datos los tienes en A1 hasta por ejemplo A500, entonces crearíamos una columna desde C1 hasta donde fuera, con los datos no repetidos. A esa columna C, con datos, el pondríamos un nombre (yo en el ejemplo qu te adjunto, le he puesto por nombre "misdatos").
Aquí tienes un macro, para que lo incluyas en un módulo, y pruebes lo que te comento:
Sub valores_unicos()
'seleccionamos la primera celda
Range("A1").Select
'buscamos los elementos, bajando fila a fila
Do While Not IsEmpty(ActiveCell)
'si el dato no está repetido, lo añadimos
'a la variable "datos", separando los
'elementos por #
If InStr(datos, ActiveCell) = 0 Then
'añadimos el dato
datos = datos & "#" & ActiveCell
End If
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
'eliminamos el primer #
datos = Right(datos, Len(datos) - 1)
'separamos cada elemento por un guión
dato_individual = Split(datos, "#")
'Nos situamos 2 columnas a la derecha de A1
'es decir, en C1, pero antes eliminamos lo que haya
Columns("C:C").Select
Selection.EntireColumn.Delete
Range("C1").Select
'recorremos todos los elementos
For i = 0 To UBound(dato_individual)
'Añadimos los elementos
ActiveCell = dato_individual(i)
ActiveCell.Offset(1, 0).Select
Next
'subimos 1 fila
ActiveCell.Offset(-1, 0).Select
'pasamos la dirección de la celda a una variable
celda = ActiveCell.Address
'seleccionamos todo el rango de datos
ActiveWorkbook.Names.Add Name:="misdatos", RefersTo:="=Hoja1!$C$1:" & celda
End Sub
Muchisimas gracias, lo voy a probar y te cuento.
Funciona de lujo, solo que me gustaría poner un botón en otra hoja y que sacara los datos de otra hoja y los introdujera en ella misma. Investigaré a ver si soy capaz.
Supongo que lo único es cambiar esto:
Range("A1").Select
Diciéndole que vaya a una hoja determinada, ¿no?
Muchas gracias.
Así es. Antes de esa línea, pon esto:
Hoja2.select
o bien:
Sheets("Nombre de la hoja 2").select
La diferencia entre ambas opciones, es que la primera se refiere al nombre interno de la hoja (el que se ve desde el modo VBA), y la segunda opción es el nombre de la pestaña (el que ven todos los usuarios).
Aquí tienes más info para asociar un macro a un botón, como también querías.
Muchas gracias, ya lo habia hecho y lo del boton creo que también, lo que pasa es que me pinta los datos en la hoja de datos y quiero que me los pinte en la que tengo el botón, es decir, desde la que ejecuto.
Poniendo
Sheets("Nombre de la hoja 2").select
me coge los datos de la hoja correcta pero sigue pintandomelos en la misma aunque cambie la hoja abajo del todo.
Muchas gracias de nuevo.
Pues antes de esta línea:
'Nos situamos 2 columnas a la derecha de A1
Pon esto:
Sheets("Nombre de la hoja 1").select
Ojo, que te empezará a escribir en la columna C de la Hoja1
Voy a probarlo.
Ya te contaré como sigue mi aventura.
Muchas gracias.
Funciona muy bien, pero me gustaria asignar un nombre a la columna entera, como si pinchara en la letra y al lado de las formulas pusiera un nombre. ¿Cómo puedo hacer eso en el código?
Necesito que tengan ese nombre para hacer un combo de validación en otra hoja.
Perdona las molestias, pero ni idea de como se hace.
Gracias de antemano.
Un saludo.
Perdona de nuevo.
Además me he dado cuenta de que el botón se mueve hacia la izquierda cada vez que ejecuto la macro.
Se borran las 4 columnas?
Muchas gracias
Así:
ActiveWorkbook.Names.Add Name:="pepito", RefersTo:="=Hoja1!C:C"
El resto, ya te dejo que lo investigues, porque sino esto se convierte en un chat :-)
PD: pepito es el nombre del rango (en este caso, la columna C).
Muchisimas gracias ya te diré como me ha ido.
Un saludo.
Hola,
ya he conseguido lo del nombre de la columna y lo mismo para 4 columnas en lugar de 1.
Voy a ver si sigo, porque ahora según esas columnas creo validaciones para tener desplegables y tengo que hacer para que respecto a la primera tabla de datos cuente unas opciones que hay por fila.
Muchas gracias por todo, a ver si soy capaz yo solo.
Un saludo.
Hola, ya he conseguido más o menos todo el programa que quería.
Me has sido de mucha ayuda.
Una vez lo tenga terminado te lo mando y lo ves.
Muchísimas gracias.
Saludos, Manuel.
Sin esfuerzo no hay recompensa ;-)
Hola Javier Marco.
Sus articulos sobre macros en Excel son muy buenos, pregunto ¿ha escrito un libro sobre el tema?
Estoy interesado en estudiar esta tematica con el lenguaje Visual, me indica por favor como conseguir el material que Ud ha publicado.
Muchas gracias
Hola Luis Fernando. No, no llego a tanto, solo soy un simple mortal...
Lo poco que sé, lo he aprendido invirtiendo horas, utilizando la ayuda de VBA, y con algún que otro manual de Visual Basic sencillito, sobretodo para el tema de las funciones.
mi estimado javier marco. me impresiona tu habilidad con el excel es fabulosa me gustaria saber si me puedes ayudar con un trabajo.
mi interes esta en crear una lista desplegable ocmo la que sale en el mismo excel al momento de colocar una formula si tu pones =y colocas las inicales de la formula se te desplega un seria de posibilidades para elegir. esa forma de lista es la que me interesa por favor la necesito para hacer una lista de productos
Muy buen aporte, sin embargo no sé si podría modificar el código para los casos de créditos o préstamos con interés anticipado. Muchas gracias.
Quizás un día lo haga, pero la pereza me invade :-)
El método alemán con intereses anticipados o prepagables es poco utilizado en créditos o préstamos (es más común en otras operaciones, como el arrendamiento financiero o leasing).
Publicar un comentario