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.