Hoja0
Supongamos un préstamo a tipo variable sobre el que podemos efectuar aportaciones adicionales en forma de
Amortización Anticipada (AA) para reducir la duración total del préstamo. Las mensualidades no disminuyen por el hecho de haber amortizado anticipadamente, sino que siguen su evolución prevista incluso con variaciones ante cambios en el tipo de interés aplicado. Lo que cambia es la duración total del préstamo, que se acorta al incidir la AA en la reducción del Capital Vivo.
Para resolver este caso necesitamos elaborar tres cuadros de amortización:
- Cuadro de amortización 1. Representa la evolución del préstamo sin considerar que se pueden efectuar pagos adicionales con concepto de AA.
- Cuadro de amortización 2. La mensualidad se obtiene copiando la del cuadro 1. El resto del cuadro se calcula y se añade la columna de AA. Al incluir importes en la AA se consigue que en los periodos finales se obtengan capitales vivos negativos.
- Cuadro de amortización 3. Es igual que el cuadro 2 salvo por que los últimos periodos con capitales vivos negativos desaparecen, salvo la fila donde aparece el primer capital vivo negativo. En esa fila, se han de realizar los ajustes necesarios para amortizar únicamente lo necesarios para que el capital vivo sea cero y el capital amortizado en ese mes sea justo igual al principal del préstamo.
La tabla del Euribor muestra únicamente el número de periodos necesarios según los años que hemos indicado previamente en la celda amarilla de los años.
Lo mismo sucede con los tres cuadros de amortización que muestran únicamente el número de meses necesarios según los años de duración de préstamo. Para 5 años muestran 60 meses.
El tercer cuadro es el definitivo y en él se muestran únicamente los meses necesarios para la amortización del préstamo. Se puede observar que en este ejemplo hemos pasado de los 60 meses iniciales, ya que se indicó una duración de 5 años, a los 26 meses totales que dura el préstamo debido a la fuerte reducción que supone haber realizado las dos amortizaciones anticipadas, una de 120.000 € y otra de 170.000 €.
En el último mes del cuadro 3 se ha de ajustar el importe amortizado para que justamente se llegue a amortizar el principal. Observe que la última mensualidad es inferior a la de su año debido a este ajuste.
Hoja1
El caso de la Hoja0 se resolvió usando fórmulas hasta el año 50, que se ocultan o se ven según el número de años que indiquemos en los datos. En la Hoja1 proponemos una solución que no utiliza fórmulas ya escritas en la hoja de cálculo sino que el cuadro de amortización se crea por parte de una macro que actúa de forma automática al variar los datos de las celdas amarillas, que son donde introducimos los datos del caso práctico.
Sub Euribor()
Dim edad As Double
Dim lista As Byte 'da el nº de años enteros
Dim i As Integer, n As Byte
Dim A
Application.Calculation = xlManual
Worksheets("Hoja1").Activate
edad = Range("C5")
If Int(edad) - edad = 0 Then
lista = edad
Else
lista = Int(edad) + 1
End If
Range("B10").Select
Selection.CurrentRegion.Select
n = Selection.Rows.Count - 1
[A] = Range("C11:C" & n + 10).Value
Range("B" & lista + 11 & ":E60").Clear
Range("B11:E11").Copy
Range("B11:E" & lista + 10).Select
ActiveSheet.Paste
For i = 11 To WorksheetFunction.Min(lista + 10, n + 10)
Cells(i, "C") = A(i - 10, 1)
Next i
For i = 1 To lista
Cells(i + 10, "B") = i
Next i
Application.CutCopyMode = False
Range("C5").Select
Application.Calculation = xlAutomatic
End Sub
Sub Genera()
Dim i As Integer, j As Integer
Dim A() As Double 'para la tabla del Euribor
Dim B() As Double 'para los cuadros de amortización
Dim n As Byte, m As Integer
Dim ultimo As Integer
Dim aqui As String
Worksheets("Hoja1").Activate
n = Range("C5").Value 'años
m = Range("C6").Value 'meses
ReDim A(2, n)
ReDim B(2, 9, m) 'Cuadro, columna, fila
For i = 1 To n
A(1, i) = Cells(i + 10, "C").Value 'toma el Euribor
A(2, i) = (A(1, i) + Range("C7").Value) / 12 'Calcula i12
Next i
B(1, 6, 0) = Range("C4").Value
B(2, 6, 0) = Range("C4").Value
Range("M11") = Range("C4").Value
For i = 1 To m
B(1, 1, i) = Int((i - 1) / 12) + 1 'año
For j = 1 To n
If B(1, 1, i) = j Then B(1, 2, i) = A(2, j) 'Tipo int. mensual
Next j
'B(1, 3, i) = WorksheetFunction.Pmt(B(1, 2, i), m - i + 1, -B(1, 6, i - 1))
B(1, 3, i) = B(1, 6, i - 1) / ((1 - ((1 + B(1, 2, i)) ^ -(m - i + 1))) / B(1, 2, i)) 'mensualidad
B(1, 4, i) = B(1, 6, i - 1) * B(1, 2, i) 'intereses
B(1, 5, i) = B(1, 3, i) - B(1, 4, i) 'amortización
B(1, 6, i) = B(1, 6, i - 1) - B(1, 5, i) 'Cap. Vivo
B(1, 7, i) = B(1, 7, i - 1) + B(1, 5, i) 'Cap. amort.
B(1, 7, i) = Cells(i + 11, 15) 'AA
'Cuadro 2
B(2, 2, i) = B(1, 2, i)
B(2, 3, i) = B(1, 3, i) + B(1, 7, i) 'nueva mensualidad
B(2, 4, i) = B(2, 6, i - 1) * B(2, 2, i) 'intereses
B(2, 5, i) = B(2, 3, i) - B(2, 4, i) 'amortización
B(2, 6, i) = B(2, 6, i - 1) - B(2, 5, i) 'Cap. Vivo
B(2, 7, i) = B(2, 7, i - 1) + B(2, 5, i) 'Cap. amort.
B(2, 8, i) = Cells(i + 11, 15) 'AA
If B(2, 6, i) <= 0 And B(2, 6, i - 1) > 0 Then ultimo = i 'último mes
Next i
'Borrar
aqui = ActiveCell.Address
Range("G" & ultimo + 12 & ":O613").Clear
Range(aqui).Select
'Cuadro 3
For i = 1 To ultimo - 1
Cells(i + 11, 7) = i 'mes
Cells(i + 11, 8) = B(1, 1, i) 'año
Cells(i + 11, 9) = B(2, 2, i) 'Tasa i12
Cells(i + 11, 10) = B(2, 3, i) 'mensualidad
Cells(i + 11, 11) = B(2, 4, i) 'intereses
Cells(i + 11, 12) = B(2, 5, i) 'amortización
Cells(i + 11, 13) = B(2, 6, i) 'Cap. Vivo
Cells(i + 11, 14) = B(2, 7, i) 'Cap. amort.
Next i
Cells(ultimo + 11, 7) = ultimo 'mes
Cells(ultimo + 11, 8) = B(1, 1, ultimo) 'año
Cells(ultimo + 11, 9) = B(2, 2, ultimo) 'Tasa i12
Cells(ultimo + 11, 11) = B(2, 4, ultimo) 'intereses
Cells(ultimo + 11, 12) = B(2, 6, ultimo - 1) 'amortización
Cells(ultimo + 11, 13) = 0 'Cap. Vivo
Cells(ultimo + 11, 14) = B(2, 7, ultimo - 1) + B(2, 6, ultimo - 1) 'Cap. amort.
Cells(ultimo + 11, 10) = B(2, 4, ultimo) + B(2, 6, ultimo - 1) 'mensualidad
'Formato
aqui = ActiveCell.Address
Range("G12:O12").Copy
Range("G12:O" & ultimo + 11).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Range(aqui).Select
End Sub
Para automatizar el recálculo automático del cuadro de amortización al cambiar los años contamos con una macro que controla el evento Change que actúa ante cambios en el Target que es la celda C5, donde introducimos los años. Las macros de este tipo no se encuentran en los módulos sino que se han de colocar en el la zona del Editor de VBA que hace referencia a la hoja con la que estamos trabajando.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
Euribor
Genera
End If
If Target.Column = 15 And Target.Row >= 12 And Target.Row <= 611 Then
Genera
End If
End Sub
Precio y rentabilidad son las dos caras de una misma moneda. Si me das el precio puedo calcular la rentabilidad. Y viceversa, si me das la TIR puedo calcular el precio.
Ambos procesos se ven en el vídeo. Primero me dan la TIR y así puedo calcular el precio, y luego, dado ese precio como dato puedo calcular la TIR, que al salir un 4% queda comprobado que el cálculo del precio es correcto.