Aplicación en Excel para el cálculo de préstamos (todo en uno)

En esta ocasión, no vamos a hacer ninguna nueva aplicación, ni ninguna utilidad que no hayamos visto antes en el blog. Lo que voy a presentaros es simplemente un libro de Excel, donde tendremos integradas las siguientes aplicaciones:

- Cálculo de préstamos e hipotecas mediante el método francés (el común y usual en este tipo de operaciones): cuotas constantes, donde el capital que se amortiza va creciendo en cada cuota, y los intereses van decreciendo en cada cuota.

- Cálculo de préstamos según el método americano: cuotas constantes excepto la última que es donde se amortiza el principal del préstamo. Las cuotas constantes sólo llevan cargo de intereses.

- Cálculo de préstamos con amortización de capital constante: cuotas variables y decrecientes, con amortización de capital constante en cada cuota, e intereses decrecientes en cada cuota.

- Calcular una TAE (tasa anual equivalente, o tasa anual efectiva) y un interés nominal.

Todo esto ya lo hemos visto en el blog, en diferentes artículos, así que no volveremos a explicarlo. Simplemente he juntado todo en la misma aplicación, para tenerlo bien ordenadito, y no tener que abrir cuatro ficheros para calcular esas cuatro cosas diferentes.

De momento es la primera versión, con lo que no descarto ampliarla en un futuro.

Aquí os dejo un pantallazo de lo que se os cargará al abrir el fichero Excel. Recordad habilitar las macros, para que la aplicación sea totalmente funcional:

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo.

33 Utilidades para Microsoft Excel

Hoy os presento un manual en pdf, de lo que creo que podrían ser, las 33 mejores utilidades para Microsoft Excel que he publicado en el blog. Quizás algunos de vosotros no estéis de acuerdo, y penséis que hay otros artículos en el blog que deberían incluirse en el manual. Muy probablemente tengáis razón, pero he escogido esas 33 utilidades, después de hacer muchos descartes.

Este manual con 33 utilidades para Microsoft Excel, no pretende ser un manual de cabecera, pero si un manual de consulta, especialmente ideado para aquellos lectores que quieran aprender las posibilidades de las macros en Excel. No obstante, este manual no solo incluye macros, sino que también podréis encontrar en él, funciones propias de Excel, como la TIR, y el VAN, por poner solo dos ejemplos.

El manual, como todo lo que encontrarás en este humilde blog de Excel, es gratuito y de libre distribución, por lo que puedes imprimirlo, enviárselo a tus amigos, compartirlo, y en definitiva, hacer lo que quieras con el :-)

En muchos de los artículos, podréis comprobar que al final de los mismos, hay un enlace para descargar un fichero con todo lo explicado, para que el usuario no tenga que partir de cero escribiendo el código fuente en Excel. Asimismo, se incluye un enlace a la entrada original de este blog, por si en algún momento el lector quiere acercarse hasta aquí, para ver si he realizado algún cambio o modificación en algún artículo del blog, como ha ocurrido recientemente por ejemplo, en el que explico como obtener datos de una página web.

Estas son las utilidades que he incluido en el pdf:

1. Obtener el nombre del archivo.
2. Obtener el nombre de la hoja.
3. Obtener la ruta, el nombre del fichero, y la hoja.
4. Mi primer macro en Excel.
5. Mi primer UserForm.
6. Introducir datos utilizando un formulario.
7. Modificar datos utilizando un formulario.
8. Mi primer ComboBox.
9. Sacándoles provecho a los ComboBox.
10. Macro al abrir o cerrar un libro.
11. Desproteger una hoja de cálculo.
12. Crear carpetas (o directorios), desde Excel.
13. Poner la hora en una celda.
14. Crear hojas con un clic.
15. Buscar hojas ocultas.
16. Mostrar y ocultar hojas, utilizando macros.
17. Leer una base de datos Access.
18. Simultanear filas de colores.
19. Validación con datos en otra hoja.
20. Validación de listas dependientes.
21. Control horario: horas normales y horas extras.
22. Números aleatorios no repetidos.
23. Préstamos y cálculo de hipotecas.
24. Préstamos según el método americano.
25. Préstamos con amortización de capital constante.
26. Calcular la TAE.
27. Calcular la TIR y el VAN.
28. Evolución de un capital a interés simple e interés compuesto.
29. Calcular la letra del NIF/DNI.
30. Controlar vencimientos de facturas y recibos.
31. Calcular vencimientos.
32. Obtener datos de una página web.
33. Calendarios para imprimir.

Aquí os dejo una imagen de una vista a 4 páginas, para que os hagáis una idea de lo que podéis encontrar en el pdf que podéis descargar más abajo:


Ya no os hago esperar más. Aquí tenéis el manual con las 33 utilidades para Microsoft Excel (cliquead en la imagen para descargar el manual en pdf):

Si te ha gustado este manual en pdf, te agradecería que dejases un comentario.

Controlar vencimientos de facturas y recibos, con descuento comercial

No hace mucho tiempo, vimos una aplicación en Excel, con la que podíamos controlar vencimientos de facturas y recibos. Esta aplicación estaba pensada para controlar nuestra cartera de recibos y/o facturas pendientes de cobro, y para alertarnos llegado su vencimiento.

En el artículo de hoy, hemos añadido una funcionalidad adicional a esa aplicación en Excel, que no es otra que la del control del riesgo bancario por descuento de efectos. No entraré a detallar el código fuente, pues la mayoría de los usuarios harán caso omiso del mismo, ya que lo que buscan es una aplicación que les permita gestionar de una forma medianamente decente su cartera de recibos y de efectos descontados.

Para el que no sepa que es el descuento comercial, simplemente daré unas pinceladas, para comentar que se trata de una fórmula de financiación, por la cual el acreedor de una deuda, puede anticipar el cobro de la misma, normalmente a través de una entidad financiera, y a cambio de un tipo de interés que suele estar en función del plazo de vencimiento de la deuda. Lo normal es que las empresas soliciten a estas entidades financieras, la apertura de una línea de descuento por un determinado importe, de acuerdo a sus necesidades previamente establecidas. Las entidades financieras, una vez analizada la documentación que les solicitarán a estas empresas, determinarán si es factible la concesión de esa línea de descuento, y el importe de la misma. Las condiciones de esta línea de descuento, se deberían negociar de forma individualizada con la entidad financiera.

La aplicación que os presento hoy, y que no es más que una revisión de la que vimos en su día (la he llamado versión 2.0, por darle un toque algo más formal), consta en líneas generales de las siguientes mejoras:

Añade al libro una hoja donde se incluye una relación de las entidades financieras, así como el importe de la línea de descuento de cada una de ellas. Respecto al funcionamiento de la aplicación, en lo que respeta a este límite de descuento, comentar que lo normal es que las empresas no puedan exceder de este límite, pero como no siempre es así y cada empresa es un mundo, ya que a veces las entidades te permiten exceder ese límite, esta aplicación en Excel simplemente informa del importe del riesgo que tenemos en ese momento, del límite de la línea de descuento, y de si estamos excedidos o no, y por qué importe. Será el usuario quien decida a partir de esos datos, si la entidad financiera en cuestión, nos va a aceptar o no algún efecto comercial adicional para su descuento.


En la hoja de facturas, se ha añadido una columna, para informar a través de un desplegable, de la entidad financiera en la que vamos a negociar los efectos. En el encabezado de la columna, aparece el texto "Efecto descontado en", tal y como podéis comprobar en la siguiente imagen (si no lo veis bien, cliquead en la imagen para ampliarla):


Una vez hemos seleccionado una entidad financiera por donde descontar el efecto, veremos un MsgBox, de la siguiente forma:


El MsgBox nos informará de lo siguiente:

- Entidad financiera.
- Riesgo concedido.
- Cantidad descontada (y pendiente de vencimiento).
- Exceso/defecto sobre el límite de riesgo de la póliza de descuento.

Lo podemos comprobar en el siguiente MsgBox:


Finalmente, y una vez hayamos informado de todo lo necesario para el control de nuestras facturas, pulsaremos el botón Previsión de cobros, que nos llevará a la hoja donde tenemos la previsión de cobros, mes a mes. En esta hoja, se ha añadido una tabla en la que se incluye el riesgo por descuento que tenemos en cada entidad, y el mes de vencimiento de ese riesgo. En la tabla superior, como hasta ahora, tenemos la previsión de cobros, que incluye un cambio respecto a la versión anterior de esta misma aplicación. En esa tabla de cobros, evidentemente no aparecerán todos aquellos efectos que hayan sido negociados y por tanto descontados, pues ya habrán sido cobrados (lo cual no quiere decir que el deudor haya pagado).

En la aplicación anterior que no controlaba el riesgo por descuento de efectos, y cuyo enlace incluí al principio de este artículo, solo existía una tabla en la hoja de "Previsión de cobros", pero en esta nueva versión hay dos, una tabla para los cobros pendientes, y otra para los efectos descontados (y por tanto cobrados).


Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011). Espero vuestros comentarios, para saber si os ha sido útil o no :-)

Controlar vencimientos de facturas y recibos

Si tienes cosas que hacer, mejor deja la lectura de este artículo para cuando dispongas de tiempo, porque una vez redactado todo, acabo de darme cuenta que ocupa la friolera de 16 páginas en DIN-A4. En cualquier caso, si prefieres descargarte la aplicación ahora, y leerte el resto más tarde, puedes hacerlo desde aquí: descargar la aplicación para el control de vencimientos de facturas y recibos. Eso sí, al menos léete los párrafos iniciales para saber de qué va esta aplicación en Excel, y los párrafos finales de este artículo, para saber cual es el procedimiento que debes seguir como usuario de la aplicación, para su correcto funcionamiento, y saber como debes introducir los datos, para no obtener errores inesperados.

Son muchas las pequeñas empresas, ya sean talleres, asociaciones, fundaciones, cooperativas, microempresas, e inclusos profesionales o empresarios individuales, que por su volumen de facturación y por su carga de trabajo administrativo, no requieren del uso de complejos programas para llevar el control de sus facturas, y saber cuando tienen que presentar los recibos al cobro, o cuando tienen que llamar al cliente para reclamar el pago de las facturas.

Normalmente estas aplicaciones de control de recibos, forman parte de los propios programas de contabilidad, pero como muchas de esas empresas, probablemente tengan externalizada su contabilidad, delegando este trabajo en una gestoría o asesoría fiscal y contable, he pensado que podría ser de utilidad a más de uno, esta sencilla pero útil aplicación.

Como siempre, se trata de una aplicación en Excel para el control de vencimientos de facturas y recibos, y es de libre distribución, como todo lo que puedes encontrar en este blog de Excel. Es una aplicación que no está protegida, por lo que podéis copiarla, enviársela a un amigo, o simplemente cambiar lo que os apetezca para adaptarla a vuestras necesidades. Respecto a esto último, solo quiero comentar que la aplicación va a servirle al 99,99% de los usuarios (por no decir al 100%), pues no está diseñada para un sector de actividad en concreto, de tal forma que servirá tanto para una empresa industrial como de servicios, ejerciten la actividad que ejerciten.

Voy a explicar un poco por encima, cómo debe utilizarse esta aplicación, para un correcto funcionamiento. No es nada complicado su uso, pues incluso los más vagos pueden saltarse este pequeño tutorial, que no va a tener problemas para hacerse con él, en menos de un minuto :-)

La aplicación consta de 5 hojas. Estas hojas no están ocultas, pero lo que sí que hemos hecho es ocultar las etiquetas de las hojas para que el usuario interactúe con los botones, y no a través de clics en las pestañas. Las hojas en cuestión, son las siguientes:

- Una hoja para el menú principal.

- Otra hoja para dar de alta, modificar, y ver los datos de nuestros clientes.

- Otra hoja para dar de alta, modificar, y ver las condiciones de pago (condiciones de cobro de nuestras facturas).

- Otra hoja para dar de alta, modificar, y ver las facturas que hemos emitido. Las facturas se tienen que generar con otra aplicación informática (programa de facturación), pues esta aplicación en Excel no es un programa de facturación, sino de control de vencimientos de facturas.

- Otra hoja para obtener una previsión de cobros, en función de los datos de las facturas que hemos introducido. Esta previsión de cobros, nos muestra los cobros previstos mes a mes, así como un total general por cliente.

Vamos a explicar para qué sirve cada hoja, y que es lo que encontraremos en cada una de ellas:

Menú principal:

Pocas cosas podemos decir sobre la funcionalidad de esta hoja, porque es más que evidente. Un pantallazo nos sacará de dudas:



El macro que tenemos en esta hoja (la hoja1), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de clientes:

Desde esta hoja, daremos de alta nuestros clientes, con todos los datos fiscales, y de contacto, así como su forma de pago, seleccionándola del desplegable que se genera automáticamente al dar de alta el nombre del cliente. También se generará una validación de datos automática, por lo que si el cliente tiene fecha de pago fija, solo podremos introducir un número entre el 1 y el 31.

Este sería un pantallazo con un ejemplo donde salen nuestros clientes (clic sobre la imagen, para ampliarla):



En esta hoja tenemos dos macros. Uno que se ejecuta al activarse la hoja (la hoja2), y nos sirve para proteger la hoja:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Este otro macro se ejecutará cuando cambiemos un dato de la hoja en cuestión. Si el cambio afecta a la columna A, entonces ocurrirá lo que comentaba anterior mente, es decir, se generará un desplegable de forma automática, y se generará también una validación de datos:

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'ocultamos el procedimiento Application.ScreenUpdating = False 'fichamos la celda donde estamos celda = ActiveCell.Address 'si introducimos datos en la columna A, 'entonces añadimos la validación de datos en 'las dos columnas finales If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'añadimos la lista de validación de las formas de pago Cells(Target.Row, 11).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=FPA" End With 'añadimos la lista de validación del día de pago fijo Cells(ActiveCell.Row, 12).Select With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, Operator:=xlBetween, _ Formula1:="1", Formula2:="31" .ErrorMessage = "El día de pago debe estar entre el 1 y el 31." End With End If 'volvemos donde estábamos Range(celda).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'protegemos la hoja ActiveSheet.Protect End Sub

Alta y modificación de formas de pago:

Esta hoja nos sirve para dar de alta las diferentes formas de pago, que luego serán las que se muestren en el desplegable al dar de alta los clientes. Un ejemplo de ello es el pantallazo que os presento a continuación (clic sobre la imagen, para ampliarla):



El macro que tenemos en esta hoja con las formas de pago (la hoja3), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Facturas emitidas:

Aquí es donde iremos introduciendo las facturas de nuestra empresa. Solo tendremos que seleccionar el cliente desde el desplegable que nos aparecerá en la columna A. Este desplegable se genera automáticamente al colocarnos en una celda de esa columna, y toma los datos de la hoja de clientes (evidentemente de los clientes que hayamos introducido). Si generamos una nueva factura, antes deberemos dar de alta al cliente, y su forma de pago si es que no la tenemos ya en nuestra hoja de forma de pago.

El aspecto que presenta esta hoja de facturas es similar a la de este ejemplo (clic sobre la imagen, para ampliarla):



En el caso de haber facturas vencidas, la fila nos saldrá de color azul celeste, para tenerlas más a la vista. Solo nos quedará llamar a los clientes que no han pagado todavía, para recordarles que su factura ha vencido, y no hemos recibido el cobro. Una vez las hayamos cobrado, eliminaremos las facturas, situándonos en cualquier celda de la fila, y pulsando el botón "Eliminar fila".

Lo primero que tenemos que hacer para dar de alta un cliente, es seleccionarlo del desplegable, tal y como se muestra en este ejemplo (clic sobre la imagen, para ampliarla):



Una vez seleccionado el cliente, automáticamente nos aparecerán a la derecha una serie de datos, algunos de ellos son propuestas que evidentemente podemos modificar, como el número de factura (nos genera el siguiente nº de factura presuponiendo que el de la fila anterior es el último número de factura), y la fecha de la factura (presuponiendo que es la misma que la fecha de la fila anterior). El vencimiento evidentemente no lo debemos introducir, pues lo obtenemos automáticamente a partir de la fecha de emisión de la factura, y de las condiciones de pago (forma de pago).

Con todo ello, y siguiendo con nuestro ejemplo, al seleccionar en el desplegable, nos aparecerá algo como lo de este ejemplo (clic sobre la imagen, para ampliarla):



Los macros que nos encontraremos en esta hoja (la hoja4), son los siguientes. Uno de ellos, para proteger la hoja, en el momento de activarse:

Private Sub Worksheet_Activate() 'Si hay errores, que continúe On Error Resume Next 'protegemos la hoja ActiveSheet.Protect End Sub

Otro macro que se ejecuta al seleccionar una celda de la columna A, y que nos sirve para generar el desplegable con los clientes a seleccionar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si seleccionamos una celda de la columna A, 'entonces añadimos la validación de datos en 'la celda en cuestión If Not Application.Intersect(Target, Range("A:A")) _ Is Nothing And ActiveCell.Row >= 5 Then 'añadimos la lista de validación de los clientes Cells(Target.Row, 1).Select With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=CLI" End With End If 'protegemos la hoja ActiveSheet.Protect End Sub

Y este otro macro que se ejecutará cuando cambiemos un dato de la columna A, y que nos generará todos los datos que comentábamos anteriormente (número de factura, fecha de factura, vencimiento, días hasta el vencimiento, y días de exceso sobre el vencimiento):

Private Sub Worksheet_Change(ByVal Target As Range) 'Si hay errores, que continúe On Error Resume Next 'desprotegemos la hoja ActiveSheet.Unprotect 'si modificamos una celda de la columna A, añadimos 'las fórmulas en las columnas correspondientes If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then 'si no tenemos cliente o fecha de fra., borramos el vto. si 'lo hay así como los días de exceso, y los días hasta el vto. If ActiveCell = "" Then ActiveCell.Offset(0, 1) = "" ActiveCell.Offset(0, 2) = "" ActiveCell.Offset(0, 4) = "" ActiveCell.Offset(0, 5) = "" ActiveCell.Offset(0, 6) = "" End If 'si seleccionamos un cliente en el desplegable 'añadimos la fra. previsible, la fecha de fra. previsible, 'el vto., los días de exceso, y los días hasta el vto. If ActiveCell <> "" Then 'añadimos el nº de fra. previsible If ActiveCell.Offset(-1, 1) <> "" And ActiveCell.Offset(0, 1) = "" Then ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1 End If 'añadimos la fecha de fra. previsible If ActiveCell.Offset(-1, 2) <> "" And ActiveCell.Offset(0, 2) = "" Then ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) End If 'añadimos el vencimiento If ActiveCell.Offset(-1, 4) <> "" And ActiveCell.Offset(0, 4) = "" Then ActiveCell.Offset(0, 4).FormulaR1C1 = "=IF(VLOOKUP(RC[-4]," & _ "TCLI,12,0)<>"""",IF(DAY(RC[-2]+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0)" & _ ",TFPA,2,0))>VLOOKUP(RC[-4],TCLI,12,0),MIN(DATE(YEAR(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0))+1,DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP(RC[-4]," & _ "TCLI,11,0),TFPA,2,0),1)),MIN(DATE(YEAR(RC[-2]+" & _ "VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _ "+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),DAY(VLOOKUP" & _ "(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0),0))),RC[-2]+VLOOKUP(VLOOKUP" & _ "(RC[-4],TCLI,11,0),TFPA,2,0))" End If 'añadimos los días hasta el vencimiento If ActiveCell.Offset(-1, 5) <> "" And ActiveCell.Offset(0, 5) = "" Then ActiveCell.Offset(0, 5) = "=IF(TODAY()-RC[-1]<0,RC[-1]-TODAY(),0)" End If 'añadimos los días de exceso sobre el vencimiento If ActiveCell.Offset(-1, 6) <> "" And ActiveCell.Offset(0, 6) = "" Then ActiveCell.Offset(0, 6) = "=IF(TODAY()-RC[-2]>=0,TODAY()-RC[-2],0)" End If End If End If 'protegemos la hoja ActiveSheet.Protect End Sub

Previsión de cobros:

Una vez tengamos dadas de alta todas las facturas, simplemente deberemos acceder a la hoja donde se nos genera de forma automática la previsión de cobros. Para ello, pulsaremos el botón "Previsión de cobros".

Lo que obtendremos será algo similar a lo que se muestra en el siguiente ejemplo:



En esta hoja no tenemos ningún macro, pues todo el código que genera la previsión de cobros, lo tenemos en un macro dentro del Módulo1.

Vamos a entrar ahora a comentar los macros que tenemos en el Módulo1. Lo primero que hay es el macro Auto_open(), que como sabéis es el macro que se ejecuta al abrir el fichero. El código de nuestro macro Auto_open() es el siguiente (lo he modificado una vez publicado el artículo, para que se carguen automáticamente las herramientas para análisis, y no tener problemas con la función FIN.MES):

Sub Auto_open() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'activamos las herramientas para análisis, 'para no tener problemas con la función FIN.MES AddIns("Herramientas para análisis").Installed = True 'no mostramos las pestañas (las hojas) ActiveWindow.DisplayWorkbookTabs = False 'buscamos si hay alguna factura vencida, y también 'si hay facturas para vencer en los próximos 7 días Hoja4.Select Range("G5").Select 'si no hay facturas, saltamos a la línea correspondiente If ActiveCell.Offset(0, -6) = "" Then GoTo sinfacturas 'para todo el rango de datos de la columna J For i = 5 To Selection.End(xlDown).Row 'miramos si hay facturas vencidas If ActiveCell > 0 Then vencidas = vencidas + 1 'miramos si hay facturas que 'vencen en los próximos 7 días If ActiveCell.Offset(0, -1) <= 7 And _ ActiveCell.Offset(0, -1) > 0 Then proximas = proximas + 1 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'nos situamos para escribir la siguiente factura ActiveCell.Offset(0, -6).Select 'mostramos un mensaje si hay facturas vencidas If vencidas = 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " factura vencida.") _ , , "Facturas vencidas" If vencidas > 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _ "," + Chr(10) + "y hay " & vencidas & " facturas vencidas.") _ , , "Facturas vencidas" 'mostramos un mensaje si hay facturas próximas a vencer If proximas = 1 Then MsgBox ("Hay " & proximas & " factura que vence " _ + "en los próximos 7 días."), , "Facturas para vencer" If proximas > 1 Then MsgBox ("Hay " & proximas & " facturas que vencen " _ + "en los próximos 7 días."), , "Facturas para vencer" 'nos situamos en el menú principal sinfacturas: Hoja1.Select Range("E12").Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

No comentaré mucho sobre lo que hace el macro Auto_open(), porque con leer los comentarios del propio macro, lo tenemos todo chupado. Dentro de ese código, hay dos partes importantes que sí que me gustaría recalcar. Una de ellas, es que se nos mostrará un aviso informándonos de las facturas que tenemos vencidas. Este aviso se nos mostrará con tan solo abrir el fichero, siempre y cuando tengamos facturas vencidas, claro está. En el caso de haber facturas vencidas, se nos mostrará un mensaje similar a este que os muestro a continuación, donde nos avisa que hay 9 facturas vencidas:



En el caso de tener facturas que venzan en los próximos 7 días, la aplicación también nos mostrará un aviso como el que se nos muestra a continuación, donde nos avisa que tenemos 1 factura que nos vence en los próximos 7 días:



Aparte del macro Auto_open(), tenemos estos otros macros cuyo código os incluyo a continuación. Un macro para imprimir:

Sub Imprimir() 'Imprimimos la hoja ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Otro macro para guardar el fichero pero sin cerrar la aplicación, es decir, para guardar los datos, y continuar trabajando:

Sub Guardar() 'Guardamos el libro ActiveWorkbook.Save End Sub

Otro macro para ir a la hoja de clientes:

Sub Clientes() 'Vamos a la hoja2 Hoja2.Select Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja con las formas de pago:

Sub Formas_de_pago() 'Vamos a la hoja3 Hoja3.Select Range("B5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select End Sub

Otro macro para acceder a la hoja de facturas:

Sub Facturas() 'Vamos a la hoja4 Hoja4.Select Range("A5").Select 'nos situamos en la primera fila libre If ActiveCell.Offset(0, 1) <> "" Then Selection.End(xlDown).Offset(1, 0).Select End If End Sub

Otro macro para volver al menú principal:

Sub Volver_al_menu() 'volvemos al menú, pero dependiendo 'de la hoja donde estemos, ordenaremos 'también los datos If ActiveSheet.CodeName = "Hoja2" Then Ordenar_clientes If ActiveSheet.CodeName = "Hoja3" Then Ordenar_formas_de_pago 'volvemos al menú Hoja1.Select Range("E12").Select End Sub

Otro macro para ordenar alfabéticamente los clientes:

Sub Ordenar_clientes() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("A5:K5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A5") 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="CLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'ahora le ponemos un nombre a toda la tabla Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TCLI", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C12" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente los clientes.") _ , , "Clientes ordenados" End Sub

Otro macro para ordenar alfabéticamente las formas de pago:

Sub Ordenar_formas_de_pago() 'Si hay errores, que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'fichamos la celda donde estamos, para volver a ella celda_donde_estamos = ActiveCell.Address 'seleccionamos la primera fila con datos Range("B5:C5").Select 'Ordenamos las celdas hasta el final Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B5") 'seleccionamos todo el rango continuo 'desde B5 hasta abajo del todo Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C2" 'ahora le ponemos un nombre a toda la tabla Range("B5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="TFPA", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C3" 'volvemos donde estábamos 'protegemos la hoja ActiveSheet.Protect 'volvemos a la celda donde estábamos Range(celda_donde_estamos).Select 'mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje MsgBox ("Se han ordenado alfabéticamente las formas de pago.") _ , , "Formas de pago ordenadas" End Sub

Tomad un poco de aire, que aún quedan unos cuantos macros más. El siguiente que nos encontraremos es para eliminar clientes:

Sub Eliminar_cliente() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre del cliente a buscar 'y la celda donde estamos cliente = Cells(ActiveCell.Row, 1) celda = ActiveCell.Address 'antes de borrar un cliente, buscaremos que no tengamos 'facturas emitidas a nombre de ese cliente a eliminar Hoja4.Select 'buscamos el cliente, y si existe en las facturas 'creamos una variable If Not Cells.Find(cliente) Is Nothing And cliente <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja2 Hoja2.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar este cliente, debes borrar sus facturas." _ + Chr(10) + "Por favor, accede a la hoja de Faturas, para eliminarlas.") _ , , "Imposible borrar este cliente" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Los datos de este cliente, han sido eliminados.") _ , , "Cliente eliminado" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar formas de pago:

Sub Eliminar_forma_de_pago() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'si hay errores, que continúe On Error Resume Next 'fichamos el nombre de la forma de pago a buscar 'y la celda donde estamos formadepago = Cells(ActiveCell.Row, 2) celda = ActiveCell.Address 'antes de borrar una forma de pago, buscaremos 'que no tengamos facturas emitidas a clientes 'con esa forma de pago a eliminar Hoja2.Select 'buscamos la forma de pago, y si existe en 'algún cliente, creamos una variable If Not Cells.Find(formadepago) Is Nothing And formadepago <> "" Then 'si ese cliente tiene facturas, 'creamos una variable borrar_fila = "no" End If 'volvemos a la hoja3 Hoja3.Select 'y a la celda donde estábamos Range(celda).Select 'si borrar_fila = "no", mostramos un mensaje 'de que no podemos borrar la fila If borrar_fila = "no" Then MsgBox ("Antes de borrar esta forma de pago, debes borrar o cambiar la forma" _ + Chr(10) + "de pago de los clientes que utilizan esta modalidad de pago a borrar." _ + Chr(10) + Chr(10) + "Por favor, accede a la hoja de Clientes, para editarlos.") _ , , "Imposible borrar esta forma de pago" 'finalizamos el macro Exit Sub End If 'mostramos el procedimiento Application.ScreenUpdating = True 'si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("Esta forma de pago, ha sido eliminada.") _ , , "Forma de pago eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Otro macro para eliminar facturas:

Sub Eliminar_factura() 'Si estamos en la fila 5 o superior, 'eliminamos la fila If Selection.Row >= 5 Then 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos la fila donde estamos Selection.EntireRow.Delete 'mostramos un mensaje MsgBox ("La factura seleccionada, ha sido eliminada.") _ , , "Factura eliminada" 'protegemos la hoja ActiveSheet.Protect End If End Sub

Y el último macro que nos encontraremos, y también el más largo, es este, que nos sirve para generar nuestra previsión de cobros, detallando los vencimientos por meses y por clientes:

Sub Prevision_de_cobros() 'Si hay errores, que continúe On Error Resume Next 'cambiamos el texto del botón de la hoja1 y hoja4 If ActiveSheet.CodeName = "Hoja1" Or _ ActiveSheet.CodeName = "Hoja4" Then 'creamos una variable If ActiveSheet.CodeName = "Hoja1" Then hoja = "menu" If ActiveSheet.CodeName = "Hoja4" Then hoja = "facturas" 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre, y lo ponemos en rojo Selection.Characters.Text = "Procesando..." With Selection.Font .ColorIndex = 3 End With End If 'ocultamos el procedimiento Application.ScreenUpdating = False 'comprobamos que tengamos facturas If Hoja4.Range("A5") = "" Then 'si no hay facturas, mostramos un mensaje MsgBox ("Por favor, revisa todo, para poder continuar.") _ + Chr(10) + Chr(10) + "Al parecer no hay facturas, y por tanto no se" _ + Chr(10) + "puede generar la previsión de cobros." _ , , "Hay errores" 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If Hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf Hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro Exit Sub End If 'fichamos la celda donde estamos celda = ActiveCell.Address 'eliminamos todo lo que haya en la hoja5 Hoja5.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'borramos las filas (los clientes) Selection.EntireRow.Delete 'borramos ahora las fechas Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select 'borramos las filas (los clientes) Selection.EntireColumn.Delete 'seleccionamos la hoja2 Hoja2.Select 'nos situamos en la primera celda con datos Range("A5").Select 'seleccionamos todo el rango continuo Range(Selection, Selection.End(xlDown)).Select 'copiamos los datos Selection.Copy 'seleccionamos la celda A5 Range("A5").Select 'seleccionamos la hoja5 Hoja5.Select 'seleccionamos la celda A5 Range("A5").Select 'pegamos los datos Selection.PasteSpecial Paste:=xlPasteValues 'fichamos la fila máxima fila_maxima = Selection.End(xlDown).Row 'vamos a la hoja4, donde tenemos las facturas 'y seleccionamos la fecha menor y mayor Hoja4.Select 'seleccionamos todo el rango continuo 'desde A5 hasta abajo del todo Range("A5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="FRAS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1" 'seleccionamos todo el rango continuo 'desde D5 hasta abajo del todo Range("D5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="IMPORTES", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C4:R" & ActiveCell.Row & "C4" 'seleccionamos todo el rango continuo 'desde E5 hasta abajo del todo Range("E5").Select Selection.End(xlDown).Select 'le ponemos un nombre a ese rango ActiveWorkbook.Names.Add Name:="VTOS", RefersToR1C1:="='" _ & ActiveSheet.Name & "'!R5C5:R" & ActiveCell.Row & "C5" 'volvemos a E5 Range("E5").Select 'definimos dos variables (fecha mínima y máxima) minimo = ActiveCell maximo = ActiveCell Do While Not IsEmpty(ActiveCell) 'seleccionamos la fecha mínima y máxima If ActiveCell < minimo Then minimo = ActiveCell If ActiveCell > maximo Then maximo = ActiveCell 'controlamos que no falten datos: nombre del cliente, 'fecha de factura, vencimiento e importe. 'Comenzamos controlando el vencimiento If IsDate(ActiveCell) <> True Then incorrecto = True 'controlamos la fecha de la factura If IsDate(ActiveCell.Offset(0, -2)) <> True Then incorrecto = True 'controlamos el importe If Not IsNumeric(ActiveCell.Offset(0, -1)) Or _ ActiveCell.Offset(0, -1) = "" Then incorrecto = True 'controlamos que exista el cliente If ActiveCell.Offset(0, -4) = "" Then incorrecto = True 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'si hay errores, mostramos un mensaje If Err.Number <> 0 Then 'mostramos un mensaje MsgBox ("Existen errores." _ + Chr(10) + Chr(10) + "Por favor, revisa todo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'si hay errores, mostramos un mensaje If incorrecto = True Then 'mostramos un mensaje MsgBox ("Existen errores en algunas de estas columnas:" _ + Chr(10) + Chr(10) + "- Nombre de los clientes." _ + Chr(10) + "- Fecha de las facturas." _ + Chr(10) + "- Importe de las facturas." _ + Chr(10) + "- Vencimiento de las facturas." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Datos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'ahora seleccionamos el primer día del mes del mínimo y máximo minimo = "01" & "/" & Month(minimo) & "/" & Year(minimo) maximo = "01" & "/" & Month(maximo) & "/" & Year(maximo) 'recuerda que esta aplicación ha salido de 'http://hojas-de-calculo-en-excel.blogspot.com 'calculamos la diferencia en meses entre el mínimo y el máximo meses = DateDiff("m", minimo, maximo) 'si la diferencia de meses es superior a 120 'mostramos un mensaje de error If meses > 120 Then 'mostramos un mensaje MsgBox ("Hay más de 10 años de diferencia entre el " _ + Chr(10) + "primer vencimiento, y el último vencimiento." _ + Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _ , , "Vencimientos incorrectos" 'volvemos a la hoja4 Hoja4.Select 'nos situábamos en la celda donde estábamos Range(celda).Select 'vamos a la línea "boton" GoTo boton End If 'volvemos a la hoja5 Hoja5.Select 'escribimos los meses para planificar los cobros, 'primero escribiendo los meses Range("A4").Select For i = 0 To meses 'seleccionamos la columna de la derecha ActiveCell.Offset(0, 1).Select 'escribimos el mes ActiveCell = DateAdd("m", i, minimo) Next 'escribimos el total ActiveCell.Offset(0, 1) = "TOTAL" 'fichamos la columna máxima columna_maxima = Selection.End(xlToRight).Column 'copiamos el formato de la celda A4 Range("A4").Select ActiveCell.Copy 'seleccionamos todo el rango continuo por 'la derecha, desde la segunda columna Range(Selection.Offset(0, 1), Selection.End(xlToRight)).Select 'pegamos los formatos Selection.PasteSpecial Paste:=xlPasteFormats 'ponemos los formatos de fecha (mes y año) Selection.NumberFormat = "[$-340A]mmm yyyy" 'alineamos esos encabezados a la derecha Range("B4").Select 'seleccionamos todo el rango continuo por la derecha Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlRight End With 'nos situamos en la celda B5 Range("B5").Select 'escribimos la fórmula ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT((FRAS=RC1)*(MONTH(VTOS)=MONTH(R4C))*(YEAR(VTOS)=YEAR(R4C))*IMPORTES)" 'copiamos y pegamos la fórmula en toda la tabla Selection.Copy Range(Cells(5, 2), Cells(fila_maxima, columna_maxima - 1)).Select ActiveSheet.Paste 'ponemos los totales Range("A5").Select 'nos situamos en la primera fila libre Selection.End(xlDown).Offset(1, 0).Select ActiveCell = "TOTALES" 'pasamos a la siguiente columna ActiveCell.Offset(0, 1).Select 'escribimos las sumas totales de cada mes For i = 2 To columna_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(R[-" & fila_maxima - 4 & _ "]C:R[-1]C)" 'nos movemos a la derecha ActiveCell.Offset(0, 1).Select Next 'volvemos a la última columna, y 'seleccionamos toda la fila ActiveCell.Offset(0, -1).Select Range(Selection, Selection.End(xlToLeft)).Select 'ponemos la fila en negrita, con 'bordes, y con la trama de color amarillo Selection.Font.Bold = True With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With With Selection.Interior .ColorIndex = 36 End With 'nos vamos a la última columna, 'es decir, la de los totales Cells(5, columna_maxima).Select 'escribimos las sumas totales de cada cliente For i = 5 To fila_maxima 'escribimos el total ActiveCell.FormulaR1C1 = "=SUM(RC[-" & columna_maxima - 2 & _ "]:RC[-1])" 'nos movemos hacia abajo ActiveCell.Offset(1, 0).Select Next 'ponemos todas esas sumas de cada cliente, en negrita Range(Selection, Selection.End(xlUp)).Select Selection.Font.Bold = True 'le volvemos a poner el nombre correcto al botón boton: If hoja = "menu" Or hoja = "facturas" Then 'seleccionamos la hoja1 o la hoja4 If hoja = "menu" Then Hoja1.Select If hoja = "facturas" Then Hoja4.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'seleccionamos el botón2 ActiveSheet.Shapes("Botón 2").Select 'le cambiamos el nombre Selection.Characters.Text = "Previsión de cobros" If hoja = "menu" Then With Selection.Characters(Start:=1, Length:=13).Font .ColorIndex = xlAutomatic End With With Selection.Characters(Start:=14, Length:=6).Font .ColorIndex = 3 End With ElseIf hoja = "facturas" Then With Selection.Font .ColorIndex = xlAutomatic End With End If 'protegemos la hoja ActiveSheet.Protect 'finalizamos el macro, si hay errores If Err.Number <> 0 Or incorrecto = True Or meses > 120 Then Exit Sub End If 'nos situamos en la fila con el rótulo de los 'totales, de la hoja5 Hoja5.Select Range("A5").Select Selection.End(xlDown).Select 'protegemos la hoja ActiveSheet.Protect 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Finalmente comentar que el procedimiento correcto para inicializar la aplicación sería este, y necesariamente en este orden que os incluyo a continuación, pues en caso de no seguir este procedimiento, la aplicación puede responder con errores o de forma inesperada:

1. Dar de alta las formas de pago.
2. Dar de alta los clientes.
3. Dar de alta las facturas.

A partir de ese momento, y si nuestros clientes son los que son, y no generamos nuevos clientes, tan solo tendremos que preocuparnos por introducir las facturas. Si tenemos un cliente nuevo, deberemos darlo de alta previamente, y si su forma de pago es nueva, deberemos dar de alta ésta lo primero de todo, siguiendo el esquema explicado en el párrafo anterior.

Espero que os sea de utilidad esta aplicación. Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011).

Si hacéis algún cambio a la aplicación, os ruego que no me pidáis modificaciones personalizadas para vuestro caso en concreto o el de vuestra empresa, porque mi tiempo es muy limitado, y prefiero utilizarlo en resolver cuestiones de uso común. Si por el contrario, crees que sería interesante introducir alguna mejora en la aplicación, para que sea de uso público y los demás puedan aprovechar esa utilidad, entonces gustoso intentaré darle solución, si mis limitados conocimientos me lo permiten.

Si en vuestra empresa trabajáis con descuento comercial (descuento de efectos), en este otro artículo podréis descargar la aplicación para controlar vencimientos de facturas y recibos, con descuento comercial.

Por cierto, al hilo de esta aplicación para el control de vencimientos de facturas y recibos, solo quiero recordaros a los que realizáis actividades empresariales o profesionales en territorio español, que la reciente publicación de la ley de lucha contra la morosidad comercial, solo permite un máximo de 60 días de crédito para aquellas operaciones que se realicen a partir del 1 de enero de 2013. Es decir, a partir de esa fecha, no podréis darle a vuestros clientes un plazo de pago superior a los 60 días (si los clientes tienen fecha fija de pago, deberéis tener en cuenta esta circunstancia, para adaptar las condiciones de pago, y que no exceda del límite marcado en la ley). No obstante, hasta esa fecha (1 de enero de 2013) uno no puede hacer lo que desee, ya que desde la entrada en vigor de la ley (7 de julio de 2010), existe un periodo transitorio para adaptarnos a ese máximo de 60 días, y no tener que bajar de golpe de los 180, 150, 120, 90 días, o los que vuestra empresa de a sus clientes. De esta forma, todas las empresas competirán en las mismas condiciones crediticias. Algo muy importante que como novedad incorpora la ley (en sustitución de la anterior ley antimorosidad), es que no admite el acuerdo entre las partes, para alargar los plazos de crédito a los clientes, es decir, bajo ningún concepto se podrá superar el límite de los 60 días de crédito comercial, pues no se permite que pactes alargar ese límite con tus clientes.

Préstamos según el método americano

Existe un método de amortización de préstamos, que consiste en liquidar únicamente intereses en cada cuota, excepto en la cuota final, que aparte de los intereses, también amortizaremos el préstamo, y además lo haremos de golpe. Este método de amortización de préstamos, se denomina método americano, y su uso aunque no está muy extendido, siempre puede sernos útil.

Las características básicas de este tipo de préstamos, son las siguientes:

• Tienen casi todas las cuotas constantes (excepto la última).


• El capital se amortiza en la última cuota.


• Los intereses que se pagan, son constantes en cada cuota.

Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo desde el enlace que acabo de incluir, pero es recomendable una lectura rápida, al menos para saber de qué estamos hablando.

Como en el método francés (que es el sistema habitual para el cálculo de préstamos e hipotecas), expliqué detenidamente todo el código fuente, aquí solo incluiré la parte del código que varía con respecto al resto de métodos, que no es otra que las fórmulas de la tabla de amortización que se obtiene.

Los más avispados se darán cuenta que hemos eliminado todo lo relativo a la carencia en este método de amortización de préstamos, pues implícitamente la carencia ya viene incluida en la propia metodología de cálculo del sistema americano, ya que no se amortiza el principal hasta la última cuota.

Antes os mostraré unos pantallazos, tanto del formulario de información del método americano, como del propio formulario donde introduciremos los datos necesarios para realizar los cálculos:




El código fuente correspondiente a la parte más importante (las fórmulas del préstamo americano), es este:

'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=R7C[1]" 'seguimos poniendo el capital amortizado 'en la primera cuota (que es cero) Range("F18").Select ActiveCell = 0 'seguimos poniendo los intereses pagados Range("G18").Select ActiveCell.Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'seguimos poniendo el capital amortizado acumulado Range("H18").Select ActiveCell = 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 = "=RC[-4]+RC[-3]" '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 'hasta la penúltima cuota... If i < CuotasTotales - 1 Then 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=R[-1]C" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=R[-1]C" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select 'si estamos en la última cuota... Else 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=RC[-1]" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=RC[-2]" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select End If Next

Y el resultado que obtendríamos sería algo como lo que os muestro en este ejemplo:



Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. Espero que lo disfrutéis.

Préstamos con amortización de capital constante

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.

Préstamos y cálculo de hipotecas

Antes de entrar en materia, anticiparos que este artículo que estáis comenzando a leer, ocupa ni más ni menos que quince páginas en DIN A-4 (este primer párrafo lo he redactado, una vez tenía escrito todo lo demás), así que espero que tengáis paciencia, tiempo, y un poco de voluntad.

Haré una mínima introducción, para comentaros que ha pasado algo más de un mes desde la última entrada que publiqué en el blog de Excel, y ya era hora de ofreceros a todos los usuarios que seguís fielmente estos artículos, una nueva entrega. En esta ocasión, tocaremos un tema de carácter económico y financiero, que no solo va a serle útil a quien se dedique a estos temas, sino que va a serle útil a todo el mundo. ¿Quién no tiene una hipoteca hoy en día?. ¿Quien no paga un préstamo bancario?. ¿Quién no tiene una deuda porque ha comprado algo a plazos?. Casi todos nos encontramos o nos podemos encontrar en cualquier momento de nuestra vida, en una situación así, ¿verdad?. Pues para todos vosotros, está especialmente indicado este artículo.

A aquellos usuarios a los que no les interesen los macros, y quieran descargarse el libro de Excel para calcular préstamos, e hipotecas, o simplemente quieran hacer simulaciones de préstamos (esta aplicación que os presento, también es un simulador de préstamos, o lo que es lo mismo, una calculadora de préstamos avanzada), pueden saltarse todo lo que explicaré a continuación, e ir directamente al final del artículo, donde encontrarán un enlace para descargar el simulador de préstamos, es decir, el fichero de Excel, con todo lo que veremos aquí. Y a aquellos usuarios que copian y pegan los artículos de este blog, en sus webs o blogs, sin mencionar la fuente, recordarles que la fuente original es http://www.hojasdecalculoexcel.com

Antes de seguir, quiero comentaros que la metodología que se utiliza para el cálculo de préstamos, sigue el método francés. Los que no sepan que es esto del método francés, simplemente daré un par de pinceladas. El cálculo de préstamos según el método francés, se caracteriza por lo siguiente:

• Los intereses se devengan al vencimiento de cada cuota.


• El capital que se amortiza va creciendo en cada cuota, es decir, el principal del préstamo que se va pagando, es cada vez más alto, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.


• Los intereses por el contrario, van disminuyendo y son menores en cada cuota.


• Las cuotas totales que se pagan, son todas del mismo importe. El capital que se va pagando aumenta, y los intereses disminuyen, pero las cuotas son siempre iguales.

Es importante reseñar que no todas las operaciones financieras se rigen por el método francés, como por ejemplo las operaciones de arrendamiento financiero o leasing, que siguen otra metodología distinta, pero a pesar de eso, también es importante indicar que el método francés es el más extendido para el cálculo de la mayoría de operaciones de financiación.

Ahora sí, vamos a entrar en materia. Para calcular préstamos con esta aplicación en Excel, utilizaremos un formulario para la entrada de datos. Antes de eso, crearemos otro formulario donde informaremos de las características del préstamo francés.

Los dos formularios que utilizaremos serán estos:




Aquí os dejo un pantallazo, con un ejemplo de lo que obtendremos con esta aplicación en Excel.



Entrando ya en los macros, veréis que tenemos cuatro. Uno para acceder al menú principal (desde la hoja donde calcularemos el préstamo), otro macro para imprimir, otro macro para hacer una presentación preliminar (como si utilizáramos la lupa), y otro para cargar el formulario con información sobre el préstamo francés (el formulario que vemos en la primera de las imágenes anteriores).

Vamos a ver el código de los cuatro macros, y que tendremos que copiar en un módulo:

Sub menu_principal() 'Si hay errores que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos desde la fila 6 hasta el máximo 'que podemos tener, y que ocupa hasta la 'fila número 3021 Rows("6:3021").Select Selection.Delete 'ponemos el ancho estandar de 12,14 en la columna E Columns("E:E").Select Selection.ColumnWidth = 12.14 'ponemos el ancho estandar de 11 en 'las columnas desde la F a la J Columns("F:J").Select Selection.ColumnWidth = 11 'nos situamos en la celda B2 Range("B2").Select 'protegemos la hoja ActiveSheet.Protect 'vamos a la primera hoja Hoja1.Select Range("B10").Select 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Sub imprimir() 'Si hay errores que continúe On Error Resume Next 'imprimimos la hoja activa ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Sub presentacion_preliminar() 'Si hay errores que continúe On Error Resume Next 'presentación preliminar de la hoja activa ActiveWindow.SelectedSheets.PrintPreview End Sub

Sub prestamo_frances() 'Lanzamos el formulario con info sobre 'el préstamo según el método francés InfoPrestamoFrances.Show End Sub

Ahora dentro del formulario con la información sobre el cálculo de préstamos mediante el método francés, colocaremos los siguientes códigos, uno para cuando cliqueemos en el botón "Si", y otro para cuando cliqueemos en el botón "No" (así precisamente se llaman los CommandButton):

Private Sub Si_Click() 'Si hay errores, que continúe On Error Resume Next 'descargamos el formulario de memoria Unload Me 'llamamos al formulario del préstamo francés 'para rellenar los datos PrestamoFrances.Show End Sub

Private Sub No_Click() 'Si hay errores, que continúe On Error Resume Next 'descargamos el formulario de memoria Unload Me End Sub

A los TextBox y botones del segundo formulario, es decir, del formulario donde rellenaremos los datos del préstamo, les he puesto nombres bien descriptivos. En lugar de llamarlos TextBox1, TextBox2, TextBox3, etc., los he llamado Principal, InteresPrestamo, CuotasAmortizacion, etc., pues así nos será más sencillo saber de qué estamos hablando, cuando leamos el código fuente del formulario.

Este es el segundo formulario que veremos, cuando cliqueemos en el botón "Si", del formulario anterior:


Y todo que viene a continuación, esto será el código que nos encontraremos dentro del formulario (aparte de una pequeña reseña informando que el código es de libre distribución, que está prohibida su venta y su explotación con fines comerciales, y que ha sido obtenido del blog http://www.hojasdecalculoexcel.com). No hace falta que comente para que sirve cada cosa, porque está todo debidamente comentado, y los procedimientos son muy claros. Comenzaremos con el código que nos permitirá controlar los datos introducidos en el formulario:

Private Sub UserForm_Activate() 'Si hay errores, que continúe On Error Resume Next 'al activarse sl formulario, añadimos 'las opciones del desplegable relativos 'a la carencia del préstamo (SI/NO) Carencia.AddItem "SI" Carencia.AddItem "NO" 'bloqueamos por defecto, las opciones de la 'carencia (interés y cuotas), para que no se 'pueda escribir, si no se ha seleccionado en 'el desplegable de carencia (SI/NO) InteresCarencia.Enabled = False CuotasCarencia.Enabled = False End Sub

Private Sub Carencia_Change() 'Si hay errores, que continúe On Error Resume Next 'activamos o desactivamos los TextBox 'relacionados con la carencia del préstamo If Carencia.ListIndex = 0 Then 'si se elige Carencia=SI (el primer valor es cero), 'activamos los restantes TextBox InteresCarencia.Enabled = True CuotasCarencia.Enabled = True Else 'en caso contrario, si se elige Carencia=NO, 'desactivamos los restantes TextBox InteresCarencia = "" CuotasCarencia = "" InteresCarencia.Enabled = False CuotasCarencia.Enabled = False End If End Sub

Private Sub Principal_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor introducido en el principal 'del préstamo es numérico... If IsNumeric(Principal) Then 'y además de ser numérico es menor 'o igual que cero... If Principal <= 0 Then 'eliminamos el dato introducido Principal = Empty Else 'en caso contrario, que le de formato con 'separador de miles y dos decimales Principal = Format(Principal, "#,##0.00") End If 'si no es numérico... Else 'eliminamos el dato introducido Principal = Empty End If End Sub

Private Sub InteresPrestamo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor introducido en el interés 'del préstamo es numérico... If IsNumeric(InteresPrestamo) Then 'y además de ser numérico es menor o igual 'que 100, y mayor que cero... If InteresPrestamo <= 100 And InteresPrestamo > 0 Then 'que divida el valor entre 100 (para que sea %), y 'que le de formato decimal y con cuatro decimales InteresPrestamo = Format(InteresPrestamo / 100, "##0.0000%") Else 'en caso contrario, eliminamos 'el dato introducido InteresPrestamo = Empty End If 'si no es numérico... Else 'eliminamos el dato introducido InteresPrestamo = Empty End If End Sub

Private Sub CuotasAmortizacion_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor es numérico... If IsNumeric(CuotasAmortizacion) Then 'y además de ser numérico 'es menor o igual que cero... If CuotasAmortizacion <= 0 Then 'eliminamos la entrada CuotasAmortizacion = Empty Else 'en caso contrario, que le de formato con 'separador de miles, siempre y cuando 'sea menor que 1500 If CuotasAmortizacion <= 1500 Then 'si es menor o igual que 1500, le 'damos el formato con separador de miles CuotasAmortizacion = Format(CuotasAmortizacion, "#,##0") Else 'si es mayor que 1500, eliminamos 'el dato introducido CuotasAmortizacion = Empty End If End If 'si no es numérico... Else 'eliminamos el dato introducido CuotasAmortizacion = Empty End If End Sub

Private Sub CuotasAnio_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es numérico, o es menor 'o igual que cero, o mayor que 52... If Not IsNumeric(CuotasAnio) Or CuotasAnio <= 0 Or CuotasAnio > 52 Then 'eliminamos el dato introducido CuotasAnio = Empty End If End Sub

Private Sub Fecha_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es una fecha... If Not IsDate(Fecha) Then 'eliminamos el dato introducido Fecha = Empty 'si es una fecha, que le de formato de fecha Else Fecha = Format(Fecha, "dd-mm-yyyy") 'si la fecha es menor que el 01-01-1900, o mayor 'que el 31-12-3000, borramos el dato introducido '(hay que ponerlo con formato mes-día-año) If Fecha < #1/1/1900# Or Fecha > #12/31/3000# Then 'eliminamos el dato introducido Fecha = Empty End If End If End Sub

Private Sub InteresCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor es numérico... If IsNumeric(InteresCarencia) Then 'y además de ser numérico es menor o 'igual que 100, y mayor que cero... If InteresCarencia <= 100 And InteresCarencia > 0 Then 'que divida el valor entre 100, y 'que le de formato con cuatro decimales InteresCarencia = Format(InteresCarencia / 100, "##0.0000%") Else 'en caso contrario, eliminamos 'el dato introducido InteresCarencia = Empty End If 'si no es numérico Else 'eliminamos el dato introducido InteresCarencia = Empty End If End Sub

Private Sub CuotasCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es numérico, o es menor 'o igual que cero, o mayor que 1500... If Not IsNumeric(CuotasCarencia) Or CuotasCarencia <= 0 Or CuotasCarencia > 1500 Then 'eliminamos el dato introducido CuotasCarencia = Empty Else 'si es menor o igual que 1500, le damos formato CuotasCarencia = Format(CuotasCarencia, "#,##0") End If End Sub

Sub QueEsLaCarencia_Click() 'Si hay errores, que continúe On Error Resume Next 'mostramos un mensaje, informando 'de lo que es la carencia MsgBox (Chr(13) & " La carencia es el periodo de tiempo durante " _ & Chr(13) & " el cual no se amortiza nada del principal del " _ & Chr(13) & " préstamo, pero en cambio, sí que se deven- " _ & Chr(13) & " gan y amortizan intereses. " _ & Chr(13) & Chr(13)), vbOKOnly, " ¿Qué es la carencia?" End Sub

Y ahora el código que se ejecutará cuando cliqueemos en los dos botones del formulario, empezando por el código del botón que nos hará los cálculos, y cuyo código es más extenso, y a continuación con el otro botón cuyo código es muy sencillo, y que nos permite cerrar el formulario:

Private Sub Calcular_Click() 'Si hay errores, que continúe On Error Resume Next 'si hay algún campo vacío, o si se ha seleccionado SI 'en la Carencia, pero faltan el interes y/o las cuotas 'de carencia, que muestre un mensaje If Principal = Empty Or InteresPrestamo = Empty Or CuotasAmortizacion = Empty Or _ CuotasAnio = Empty Or Fecha = Empty Or Carencia.ListIndex = -1 Or _ (Carencia.ListIndex = 0 And (InteresCarencia = Empty Or CuotasCarencia = Empty)) Then 'mostramos el mensaje MsgBox (Chr(13) & " Por favor, revisa el formulario. " _ & Chr(13) & Chr(13) & " Debes completar los datos necesarios, para " _ & Chr(13) & " poder llevar a cabo el análisis del préstamo. " _ & Chr(13) & Chr(13)), vbOKOnly, " Datos incompletos" 'en caso contrario, si todos los datos están completos... Else 'informamos que estamos efectuando 'los cálculos, en el label llamado "Informacion" Informacion = "Calculando..." DoEvents 'ocultamos el proceso Application.ScreenUpdating = False 'seleccionamos la Hoja2 (hoja del préstamo francés) Hoja2.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos desde la fila 6 hasta el máximo 'que podemos tener, y que ocupa hasta la 'fila número 3021, por si acaso no hemos 'vuelto al menú principal usando los botones Rows("6:3021").Select Selection.Delete 'escribimos en las celdas, lo que nos 'interesa, en negrita, y de color granate Range("B6").Select ActiveCell = "CÁLCULO DE PRÉSTAMOS (método francés)" Selection.Font.Bold = True Selection.Font.ColorIndex = 9 'ponemos una doble línea Range("B6:F6").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With 'escribimos los títulos del cuadro 'resumen que colocaremos en la parte 'superior de la página Range("B7") = "Principal del préstamo:" Range("F7") = Principal Range("B8") = "Tipo de interés durante la amortización del préstamo:" Range("F8") = InteresPrestamo Range("B9") = "Número de cuotas de amortización:" Range("F9") = CuotasAmortizacion Range("B10") = "Número de cuotas de amortización, al año:" Range("F10") = CuotasAnio Range("B11") = "Número de años hasta la amortización del préstamo:" Range("F11") = Format(CuotasAmortizacion / CuotasAnio, "#,##0.00") Range("B12") = "Fecha del primer pago:" Range("F12") = Fecha 'ponemos la TAE de la amortización, 'alineando el dato a la derecha, pero antes 'miraremos si hay carencia o no, para elegir 'donde escribimos el dato de la TAE. If Carencia.ListIndex = 0 Then Range("J14").Select Else Range("J15").Select End If With Selection .HorizontalAlignment = xlRight End With 'ponemos la TAE de la operación TaePrestamo = (((1 + (CDec(Replace(InteresPrestamo, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100 ActiveCell = "TAE: " & Format(TaePrestamo / 100, "##0.0000%") 'seguimos escribiendo, dependiendo de si 'tenemos o no carencia en el préstamo If Carencia.ListIndex = 0 Then Range("B13") = "Tipo de interés durante la carencia:" Range("F13") = InteresCarencia Range("B14") = "Número de cuotas de carencia:" Range("F14") = CuotasCarencia Range("B15") = "Número de años de carencia:" Range("F15") = Format(CuotasCarencia / CuotasAnio, "#,##0.00") 'ponemos la TAE de la carencia TaeCarencia = (((1 + (CDec(Replace(InteresCarencia, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100 Range("J15") = "TAE carencia: " & Format(TaeCarencia / 100, "##0.0000%") With Selection .HorizontalAlignment = xlRight End With End If 'ponemos una doble línea, 'dependiendo de si hay carencia o no If Range("B13") = Empty Then 'si no hay carencia, ponemos la doble línea 'debajo de la fila 12 Range("B12:F12").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With Else 'si hay carencia, ponemos la doble línea 'debajo de la fila 15 Range("B15:F15").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With End If 'alineamos los datos numéricos a la derecha Range("F7:F15").Select With Selection .HorizontalAlignment = xlRight End With 'seguimos escribiendo los encabezados de la tabla Range("B17") = "Cuota nº" Range("C17") = "Concepto" Range("D17") = "Fecha" Range("E17") = "Capital vivo antes del pago de la cuota" Range("F17") = "Capital amortizado" Range("G17") = "Intereses a pagar" Range("H17") = "Capital amortizado acumulado" Range("I17") = "Intereses acumulados" Range("J17") = "Cuota total" 'alineamos los textos básicos a la izquierda (puesto que se 'centran por defecto) al estar toda la columna centrada Range("B6:B15").Select With Selection .HorizontalAlignment = xlGeneral End With 'alineamos los encabezados, vertical y horizontalmente, 'los ajustamos a su celda, y los ponemos en negrita Range("B17:J17").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Font.Bold = True End With 'ponemos valores y fórmulas, empezando 'por numerar las cuotas del préstamo Range("B18").Select 'si no hay carencia (si está vacía), ponemos 'que el nº de cuotas de carencia es cero If CuotasCarencia = "" Then CuotasCarencia = 0 'le quitaremos el separador de miles al nº de 'cuotas de carencia y de amortización del préstamo, 'pues en los textbox aparecen con el separador. 'Como no en todos los países se usa el punto, sino que 'se utiliza la coma, tendremos en cuenta esta circunstancia CuotasCarencia = Replace(CuotasCarencia, ",", "") CuotasCarencia = Replace(CuotasCarencia, ".", "") CuotasAmortizacion = Replace(CuotasAmortizacion, ",", "") CuotasAmortizacion = Replace(CuotasAmortizacion, ".", "") 'pasamos el nº total de cuotas de amortización 'y de carencia a una variable CuotasTotales = CInt(CuotasAmortizacion) + CInt(CuotasCarencia) 'ponemos el nº de las cuotas de amortización y de 'carencia, siempre que CuotasAmortizacion + CuotasCarencia 'sea mayor o igual que 1 If CuotasTotales >= 1 Then For i = 1 To CuotasTotales 'ponemos el nº de la cuota ActiveCell = i 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next End If 'seguimos poniendo los conceptos Range("C18").Select 'si no hay carencia... If CuotasCarencia = 0 Then 'ponemos como concepto "Amortización" 'y debajo, comillas dobles ActiveCell = "Amortización" For i = 1 To CInt(CuotasAmortizacion) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'si hay carencia... Else 'ponemos como concepto "Carencia" ActiveCell = "Carencia" 'ponemos comillas dobles, si las cuotas 'de carencia son mayores que 1 For i = 1 To CInt(CuotasCarencia) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'ponemos debajo como concepto "Amortización" ActiveCell.Offset(1, 0).Select ActiveCell = "Amortización" 'ponemos comillas dobles, si las cuotas 'de amortización son mayores que 1 For i = 1 To CInt(CuotasAmortizacion) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next End If 'recuerda que todo esto ha salido del blog 'http://www.hojasdecalculoexcel.com 'seguimos poniendo las fechas Range("D18").Select 'pasamos los primera fecha a una variable FechaDelPrimerPago = CDate(Range("F12")) Range("D18") = FechaDelPrimerPago 'si las CuotasAmortizacion + CuotasCarencia son 'mayores que 1, seguimos poniendo las fechas If CuotasTotales > 1 Then 'bajamos una fila ActiveCell.Offset(1, 0).Select For i = 1 To CInt(CuotasTotales) - 1 'miramos el nº de cuotas anuales para 'poner la fecha dependiendo de eso Select Case CuotasAnio 'cuotas semanales Case 52 'sumamos 7 días al dato de la celda anterior ActiveCell.Formula = "=R[-1]C+7" 'cuotas mensuales, bimensuales, trimestrales, 'cuatrimestrales, semestrales, o anuales Case 12, 6, 4, 3, 2, 1 'que coincida el día exacto (si es primer 'pago es el día 12, por ejemplo, que cada 'pago coincida con el día 12) ActiveCell.Formula = "=IF(DATE(YEAR(R18C),MONTH(R18C),DAY(R18C))" & _ "=DATE(YEAR(R18C),MONTH(R18C)+1,),DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2])+1,)" & _ ",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2]),MIN(DAY(R18C4),DAY(DATE(YEAR(R[-1]C)," & _ "MONTH(R[-1]C)+(12/R10C[2])+1,)))))" 'si es otro tipo de cuota Case Else ActiveCell.Formula = "=IF(R10C[2]=12,DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C6)," & _ "IF(R10C6=12,DAY(R[-1]C))),R[-1]C+INT(365/R10C[2]))" End Select 'bajamos una fila ActiveCell.Offset(1, 0).Select Next End If 'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=IF(RC[-3]<R14C6+1,R7C6,R7C6)" '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),RC[4]-RC[1],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]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-(1+(R8C6/R10C6))^-R9C6))" '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),RC[4]-RC[1],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[-6]<>0,R[-1]C+RC[-2],0)" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-" & _ "(1+(R8C6/R10C6))^-R9C6))" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'ponemos las sumas totales, lo ponemos en negrita 'y le ponemos un nombre a la celda Range("F17").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Name = "SumaDelCapitalAmortizado" ActiveCell.Font.Bold = True 'sumamos los intereses a pagar, y ponemos 'el valor de la celda en negrita ActiveCell.Offset(0, 1).Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Offset(0, 1).Font.Bold = True 'sumamos las cuotas totales, y ponemos 'el valor de la celda en negrita ActiveCell.Offset(0, 4).Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Offset(0, 4).Font.Bold = True 'ponemos las tramas alternas, es decir, celdas 'sombreadas y blancas desde B17 hasta el final Range("B17", Range("B17").End(xlDown).End(xlToRight)).Select 'borramos el formato que tengan Selection.FormatConditions.Delete 'añadimos los formatos condicionales 'a los datos de la tabla Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" 'hacemos lo mismo con los totales Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" Range("J17").End(xlDown).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" 'ponemos bordes alrededor de los conceptos Range("B17:J17").Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'seguimos poniendo bordes desde B18 hasta el final 'si solo hay 1 cuota ponemos la fila 19 con bordes If CuotasTotales = 1 Then Range("B18:J18").Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Else 'si hay más de una cuota, ponemos 'todos los datos con bordes Range("B18", Range("B18").End(xlDown).End(xlToRight)).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If 'le seguimos poniendo bordes a los totales 'del capital amortizado, e intereses a pagar Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'hacemos lo mismo para la suma 'de las cuotas totales Range("J17").End(xlDown).Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'configuramos página y ponemos la fila 17 fija, 'por si hay más de una página a imprimir, para 'que nos salgan los encabezados correctamente With ActiveSheet.PageSetup .PrintTitleRows = "$1:$17" .PrintTitleColumns = "" End With 'si la SumaDelCapitalAmortizado no cuadra exactamente hasta 'el segundo decimal, con el principal del préstamo, ponemos 'un mensaje al final de la tabla If Format(Range("SumaDelCapitalAmortizado"), "#,##0.00") <> Format(Range("E18"), "#,##0.00") Then Range("B17").End(xlDown).Offset(3, 0).Select ActiveCell = "Excel provoca un error en el cálculo, a nivel decimal, en la suma total del capital amortizado." 'lo alineamos dándole formato general With Selection .HorizontalAlignment = xlGeneral End With End If 'borramos el nombre de la suma 'total del capital amortizado ActiveWorkbook.Names("SumaDelCapitalAmortizado").Delete 'liberamos memoria Principal = Empty InteresPrestamo = Empty CuotasAmortizacion = Empty CuotasAnio = Empty Fecha = Empty Carencia = Empty InteresCarencia = Empty CuotasCarencia = Empty Informacion = Empty Unload Me 'autoajustamos desde la columna E a la J Columns("E:J").Select Selection.Columns.AutoFit 'nos situamos en la celda B2 Range("B2").Select 'protegemos la hoja ActiveSheet.Protect 'mostramos el proceso Application.ScreenUpdating = True End If End Sub

Private Sub cerrar_Click() 'Descargamos el formulario de memoria Unload Me End Sub

Si os habéis fijado bien (y no os habéis cansado leyendo tanto código fuente), he utilizado fórmulas de matemáticas financieras, omitiendo las funciones propias de Excel, como por ejemplo la función PAGO. Personalmente me gusta más utilizar las fórmulas matemáticas, que estas funciones que lo encapsulan todo, y en las que no se sabe exactamente que es lo que está haciendo la aplicación (bueno, sí se sabe, porque sabemos para que sirven esas funciones, pero el control sobre lo que estamos haciendo, no es el mismo). También os habréis fijado, que he utilizado las fórmulas como si las estuviéramos escribiendo directamente en las celdas de Excel, entrecomillándolas dentro del código, y escribiéndolas en inglés. El secreto de esto último, no es otro que crearlas utilizando la grabadora de macros, ...así no nos equivocaremos.

Otra cuestión que me gustaría remarcar, es que si en el formulario donde entraremos los datos, escogemos que las cuotas del préstamo sean mensuales, bimensuales, trimestrales, cuatrimestrales, semestrales, o anuales, los cálculos se realizarán escogiendo el mismo día de pago para todas las cuotas. Vamos a explicar esto con un ejemplo sencillo. Imaginad que escogemos amortizar el préstamo de forma mensual (12 cuotas al año). Si el primer pago lo realizamos por ejemplo el día 20-01-2010, si le sumamos 30 días, las cuotas siguientes se pagarían en estas fechas:

20/01/2010
19/02/2010
21/03/2010
20/04/2010
...

Como vemos, no coinciden todas el día 20, que es lo normal (lo que nos aplicará la entidad financiera). En el código está contemplado todo esto, y si escogemos alguno de los periodos que os he indicado antes, el día de pago siempre será fijo (en el caso del ejemplo anterior, todos los pagos se realizarían el día 20 de cada mes).

Como veis, ya son varios los artículos sobre temas financieros, los que he ido publicando en el blog. Espero que este tenga tan buena aceptación, como la ha tenido el artículo donde explicaba como calcular la tir y el van. A partir de ahora, ya no tienes excusa, para calcular las cuotas de la hipoteca, la letra del coche, o cualquier otro préstamo, porque esta aplicación se constituye como un excelente simulador de préstamos.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.