Préstamo geométrico con amortización anticipada

Descargar el fichero GeoTotal.xlsm

Este ejemplo resuelve el caso de un préstamo variable en progresión geométrica anual, fraccionado mensual, a tipo de interés variable, con un posible periodo de carencia y amortización anticipada en cualquier mes. Ha sido necesario crear una función en el Editor de Visual Basic para calcular la nueva mensualidad, al producirse un pago adicional que acelera la amortización.

Las celdas amarillas son datos y se pueden modificar. Podemos cambiar el Euribor de los 10 años, el principal del préstamo, la duración, la razón anual de la progresión geométrica (q), el diferencial que se aplica al Euribor y los años de carencia.

La mensualidad se calcula aplicando una fórmula que utiliza una función definida por el usuario. Estas funciones se programan en el Editor de Visual Basic que es donde se programan las Macros.

La fórmula de la primera mensualidad es:

=SI(B16<=$C$12;F16+J16;(Origen(C16;H15;$C$10;$C$9;D16)/vageo(1;$C$10;$C$9-B16+1;(1+D16)^12-1))/VF(D16;12;-1)+J16)

Utiliza dos funciones definidas por el usuario: VAgeo y Origen.

Código:

Function VAgeo(C, q, n, i)
If q = 1 + i Then
  VAgeo = C * n / (1 + i)
Else
  VAgeo = C * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
End If
End Function

Function Origen(s, Csm1, q, n, i12)
If q = 1 + i Then
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * n / (1 + i)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
Else
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
End If
End Function

Para resolver el problema de la amortización anticipada a mitad de un año se recurre a la idea de calcular el capital vivo al inicio de ese año bajo el supuesto de que la mensualidad calculada tras la amortización anticipada sea la que se mantiene constante durante todo el año.