Hojas de cálculo en Excel - página principal

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.



29 comentarios:

Anónimo dijo...

Un simulador, interesante. ¿Se podría lograr que haga el cálculo de varios préstamos simultáneamente?


Saludos.

Javier Marco dijo...

Esta aplicación está pensada para 1 préstamo, aunque puedes hacer tantas simulaciones como desees.

Imagino que te refieres a hacer un panel, o un comparador de préstamos, y eso no está contemplado, pero bien podrías calcular un préstamo, imprimirlo, luego el otro e imprimirlo, y comparar sobre el papel ;-)

Anónimo dijo...

Hace poco tiempo que he desubierto tu blog y me parece que está muy bien, con muchos ejemplos y muy bien explicados. Felicidades y gracias.

Un saludo.

Javier Marco dijo...

Lo poco que sé de Excel, intento explicarlo como me hubiese gustado que me lo explicasen a mí :-)

Intento que todo esté medianamente mascado, y sea fácilmente digerible.

Muchas gracias por tu comentario.

Javier Marco dijo...

Acabo de incorporar un par de datos más a la apliación, y es el cálculo de la TAE (tasa anual equivalente, o tasa anual efectiva), que nos indica el coste real del préstamo (tipo de interés real). Está calculado para la amortización y para la carencia (si es que la hay).

El código fuente del artículo, ya contempa estos nuevos cambios, y el nuevo fichero para la descarga, ha sido de nuevo resubido con estas mejoras.

Saludos.

Anónimo dijo...

Me encanta Excel, y curioseando decubrí tu Blog, es muy útil e interesante. Felicitaciones por tus explicaciones.

Javier Marco dijo...

Muchas gracias por tu comentario :-)

Anónimo dijo...

Estimado Javier, creo que enel caso de la carencia no se deberia incluir un periodo adicional, y si recalcular el principal...creo q esta seria la forma correcta, pq si un prestamo es de 10 años, la cerencia debe estar denro de este periodo y no llegar al año 12

Javier Marco dijo...

¿Por qué dices que añado un periodo?. En ningñun momento estoy añadiendo periodos que el usuario no haya incluido en el UserForm.

Ejemplo:

Nº de cuotas totales de amortización: 12
Nº de cuotas anuales: 12

(por lógica será un préstamo de 1 año, pues tiene doce cuotas totales, y se pagan 12 cuotas anuales).

Seguimos...

Nº de cuotas de carencia: 2

Resultado:

Obtenemos una elegante tabla con 2 cuotas de carencia y 12 de amortización, teniendo un total de 14 cuotas.

Un saludo.

Anónimo dijo...

Hola Javier, por lo entiendo, en este caso deberian 2 periodos de de carencia y 10 (no 12) de amortizacion. Estos 10 serian recalculados con el valor futuro del capital. Vea un ej en

http://hipotecas.sagaz.es/

No estoy diciendo que tu modelo esta incorrecto y si poniendo en discusion cual seria el correcto

Gracias!

Saludos

Javier Marco dijo...

La cuestión viene determinada por un tema conceptual. Si te fijas, en el formulario de introducción de datos, hay un sitio donde pregunta literalmente esto:

"Nº de cuotas totales de amortización del préstamo". La amortización de un préstamo, lleva implícita la devolución del capital, es decir del principal del préstamo. Si no hay devolución del capital, no hay amortización del mismo. Por tanto, el concepto de "amortización" lleva aparejado el hecho de que hay que devolver parte del capital financiado.

Si hay periodos en los que se pagan intereses pero no se devuelve principal, entonces estamos hablando de un periodo de carencia, y no de un periodo de amortización del principal, por lo que este último no puede incluir la carencia como tú planteas.

Es decir, en el ejemplo que te puse de 10 cuotas de amortización del préstamo y 2 de carencia, no se deben incluir estas 2 de carencia dentro de las 10, pues la amortización del principal es algo completamente distinto a la carencia, por el tema conceptual que te planteaba.

Distinto sería si en el formulario en lugar de preguntar "Nº de cuotas totales de amortización del préstamo", preguntase "Nº de cuotas totales". En esta caso, sí que tendrías razón, pues al preguntar por el nº de cuotas ya no estaríamos distinguiendo entre amortización y carencia, ya que se englobarían todas. Pero todo estriba en el hecho de que en el fomrulario estamos preguntando el nº de cuotas en las que se devuelve el principal.

Un saludo, y gracias por tu interesante aportación.

Josue dijo...

muy buenas tardes javier como estas, primero que nada muchas gracias por todas tus ayudas han sido de gran aplicabilidad en mi diario trabajo, pero hay un tema que me esta complicando la vida,, resulta que tengo una planilla excel 2007 con cerca de 100000 filas y una 20 o 30 columnas, puede variar la cantidad de filas,,, y necesito llevar toda esta data a una base SQL Server 2005, y asi poder manejar mejor y dar visibilidad a los datos mas rapidamente, existe la forma mediante VBA de hacer esta operacion,,,,desde ya quedo atento a tus comentarios

muchas gracias denuevo y un gran abrazo desde chile

Javier Marco dijo...

Pues como no estoy muy puesto en SQL Server, te paso la información que publica Microsoft al respecto (varias formas de pasar datos desde Excel a SQL server): http://support.microsoft.com/kb/321686/es

Un saludo.

Anónimo dijo...

hola javier

tengo una hoja que me genera un cheque quiero que cada vez que ponga los datos del cheque me vaya generando una lista total con todos los nombres y valores con los que se generan los cheques en otra hoja y de forma consecutiva para poder tenerlos como una planilla

Javier Marco dijo...

Un macro como esta que acabo de hacer, te serviría:

Sub Imprimir_y_guardar()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
On Error Resume Next
'Imprimimos el cheque, que
'suponemos está en la Hoja1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'Suponemos que tenemos 4 datos a grabar:
'la fecha de hoy (que es cuando emitimos el cheque)
'el nombre del cliente, y el importe del cheque.
'Para ello, pasamos estos dos últimos a variables:
cliente = Range("B5") 'suponemos que el nombre del cliente está en B5
importe = Range("F4") 'suponemos que el importe está en F4
'guardamos los datos en la hoja2
Sheets("Hoja2").Select
'empezamos a escribir en la fila 2
'(en la fila 1, pondríamos los encabezados)
Range("A2").Select
'almacenamos los datos, para lo cual
'buscamos la primera fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'escribimos los datos en la columna A, B y C
ActiveCell = Date
ActiveCell.Offset(0, 1) = cliente
ActiveCell.Offset(0, 2) = importe
'volvemos a la hoja1
Sheets("Hoja1").Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje
MsgBox ("Los datos han sido almacenados")
End Sub

Saludos.

Anónimo dijo...

javier muchas gracias por tu forma de enseñarnos eres grande un saludo desde colombia

Anónimo dijo...

hola javier podrias ayudarme con el codigo asi como lo hago no me salta a la siguiente fila y me sobre escribe los datos gracias

Sub Rectánguloredondeado_Haga_clic_en()
Sheets("CHEQUE").Select
empleado = Range("B6")
valor = Range("B8")
'el nombre del cliente, y el importe del cheque.
'Para ello, pasamos estos dos últimos a variables:
'guardamos los datos en la hoja2
Sheets("PLANILLA").Select
'empezamos a escribir en la fila3
'(en la fila 2, tengo un encabezado)
Range("A2").Select
'almacenamos los datos, para lo cual
'buscamos la primera fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'ActiveCell.Offset(1, 0).Select
'escribimos los datos en la columna A, B
ActiveCell.Offset(0, 1) = empleado
ActiveCell.Offset(0, 2) = valor
'volvemos a la de cheques
Sheets("CHEQUE").Select
End Sub

Javier Marco dijo...

Fíjate que en tu macro, estás seleccionando la celda A2, pero en la columna A no escribes ningún dato.

Una de dos, o escribes el nombre del empleado en la columna A y el valor en la columna B, cambiando esto:

ActiveCell.Offset(0, 1) = empleado
ActiveCell.Offset(0, 2) = valor

por esto:

ActiveCell = empleado
ActiveCell.Offset(0, 1) = valor

O bien, sin cambiar lo anterior, la otra opción es cambiar esta línea:

Do While Not IsEmpty(ActiveCell.Offset)

por esta otra:

Do While Not IsEmpty(ActiveCell.Offset(0, 1))

Pruébalo, y elige lo que más se adapte a tus necesidades.

Saludos.

Anónimo dijo...

JAVIER una vez mas eres genial tome la opcion dos y me resulto muyyyy util gracias por tu invaluable ayuda

Anónimo dijo...

JAVIER
buenos dias, seria posible q me ilustraras es posible generar un numero para una rifa cada vez q presione un boton y que sea algo llamativo la idea es q sea en excel o tienes alguna sugerencia .. una vez mas muchas graciass

Javier Marco dijo...

Un día hice esta sencillísima tontería, que cae como anillo al dedo a lo que buscas: número aleatorio, con efecto de máquina tragaperras.

Si no sabes hacer macros, pásate por este artículo: aprender a hacer macros.

Saludos.

guetpu dijo...

Hola Javier.
A principios de año descargué éste archivo porque me daba una maginifica posibilidad, poder calcular amortizaciones semanales y quincenales.

Lo utilicé para hacer varios calculos hasta marzo de este 2011.

Hoy tenia que hacer otros cálculos y al proceder me da como resultado....el primer capital vivo correcto....pero a partir de ahi me vuelta para toda la tabla y sus totales #¡VALOR!.

Por aquello de la informática, que mejor que volverlo a descargar.....

Pues el mismo resultado.....


1 Amortización 31-12-2011 3.000,00 #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR!
2 " 31-12-2012 #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR!
3 " 31-12-2013 #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR!

El ordenador (portatil) es el mismo....

A que podria deberse ?

Sinceros saludos
Guetpu

Javier Marco dijo...

Con las pistas que das, poco podemos averiguar. Mira la primera celda que te da error (la celda que hay a la derecha de esos 3.000,00), y comprueba lo que aparece en la barra de fórmulas.

Debería aparecer esto:

=SI(B18<($F$14+1);0;SI(B18<=($F$9+$F$14);J18-G18;0))

Comprueba que en todas esas celdas haya algún valor.

guetpu dijo...

efectivamente, figura la fórmula que ya me preocupé de confirmar antes de publicar mi comentario.

Durante el fin de semana intentaré descargar el archivo desde otro ordenador.

A saber si por alguna razón o actualización algún duende de la informática ha "movido" algo

gracias por la pronta respuesta
Guetpu

guetpu dijo...

Siguiendo con los misterios de la informática.

Como comenté, he descargado el archivo en otro ordenador ....y ha funcionado correctamente

He copiado el archivo en una carpeta de dropbox y ahora lo he abierto en el ordenador que me daba problemas.

Resultado...La vista previa que me ofrece dropbox al seleccionar el archivo da el resultado correcto....al abrir el archivo para ponerme a trabajar.....otra vez sale el error que ya comenté.

Estoy pensando que sea un problema de configuración de excel....Como comenté ya comprobé que estuviesen las formulas.

Como las fórmulas estan relacionadas en celdas ocultas, no conozco el formato....agradeceria un apoyo en este problema.

De nuevo muchas gracias por la atención personal y especialmente por el maginifico trabajo que respresenta esta página.

Saludos
Guetpu

Javier Marco dijo...

Guetpu, siento no poder ayudarte mucho más, porque como verás en la primera celda donde te da el error (mira mi comentario anterior), verás que no hay nada del otro mundo, que todo es correcto.

Cabría la posibilidad de que tu excel, interpretara algunos datos como de texto. Para comprobarlo, reescribe tú directamente los datos de la tabla de arriba (el principal del préstamo, el tipo de interés, el número de cuotas mensuales, etc). Quizás alguno de esos datos te los toma como texto, en lugar de número, y por eso te da el error.

Si esto no te funciona, entonces quizás se solucione de la forma más fácil, que es reinstalando Excel.

Saludos.

Anónimo dijo...

Un simulador muy interesante, ¿se podria conseguir que mostrara otra hoja con la tabla del prestamo calculado en base a la TIE?

Anónimo dijo...

Set Sess = GetObject("C:\Program Files\MiPro\Sessions\diss")
Set Sys = Sess.Application
Set scr = Sess.Screen

Hola Javier.
Me encontre con este caso en una macro pero no se como interpretar que hace por ejemplo: Set Sys=Sess.Screen.
Ya que se que tienesaltos conocimientos en excel recurro a ti a ver si me puedes ayudar.
De antemano agradezco tu ayuda.

Anónimo dijo...

!Magnífico simulador el tuyo! De gran ayuda para los que todavía estamos aprendiento.

Descubrí tu bloc estos dias y me vino a la cabeza un trabajo antiguo que llegué a abandonar con una duda que te planteo por si tuvieras una solución: construí una hoja de cálculo para un préstamo personal cuyas celdas se actualizaban a dia 4 de cada mes segun la fecha del sistema. La cuota era constante y el interés podia variar por años ¿Podrías decirme si conoces alguna forma de parar la actualización automática cuando haya vencido, es decir, cuando el capital devuelto ya sea igual al del préstamo inicial?

Llorenç