Préstamo a tipo variable con AA que reduce la duración

Puede descargar el fichero AA_reduce_tiempo.xlsm

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:

1. Cuadro de amortización 1. Representa la evolución del préstamo sin considerar que se pueden efectuar pagos adicionales con concepto de AA.
2. 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.
3. 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