domingo, 28 de septiembre de 2008

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.


17 comentarios:

  1. hola, necesito me ayudes, acabo de publicar un libro basado en excel, pero de caracter no solo matematico sino economico, donde desarrollo, los sistemas de credito actual, te pido lo revises, y si es de tu interes, lo publiques o lo uses, como desees, o simplemente que divulgues su existencia, tienen al principio poco nivel, pero al final, se vuelve de alucine, te agradezco si me ayudas.

    gracias.

    www.dondennaceeldinero.wordpress.com

    ResponderEliminar
  2. lo escribi mal, es
    www.dondenaceeldinero.wordpress.com

    gracias.

    ResponderEliminar
  3. No funciona.

    Ocurre el mismo error descrito en
    http://www.adicae.net/hipotecas/peligros.asp

    que al final no es tal error después de consultar con el banco y se debe a un error en la aplicación de la fórmula teórica.

    Si ponemos un capital 371100 euros por ejemplo,
    diferencial 0,39%
    q=1,02
    carencia 0 años

    sin ninguna amortización anticipada

    Interés de salida 1º año 2,75
    segundo año 3,7
    tercer año 4,5
    cuarto año 5

    veremos que sale un Capital Amortizado Negativo.

    Hay que corregir la fórmula de cálculo.

    ResponderEliminar
  4. He entrado en la tabla pero aunque indica la posibilidad de carencia, si en la carencia de 3 años indicada pongo 0 resulta que la tabla se descompone, me gustaría saber si es un error de cálculo, o hay alguna razón para ello.

    Muchas gracias por el blog esta fantastico

    ResponderEliminar
  5. Hola llunablava.

    No veo que se descomponga el cuadro de amortización. Si pones cero (con número) en la celda de la carencia el cuadro se recalcula bien. Debes tener activadas las MACROS.

    En esta imagen puedes ver una imagen del cuadro funcionando bien con carencia cero.

    Imagen con Carencia cero

    ResponderEliminar
  6. Hola Multisensor DATA Fusion.

    Con los datos que propones no llega a dar una cuota de amortización negativa.

    Aquí puedes ver una imagen con esos datos:

    IMAGEN_1

    Pero si cambias la razón q y la pones como 1,50, veras que la cuota de amortización se hace negativa.

    Puedes ver el efecto en esta imagen:

    IMAGEN_2

    La fórmula teórica está BIEN, y el cuadro está bien programado. La cuota de amortización ha de ser necesariamente negativa, para conseguir que se cumpla que:

    mensualidad = Cuota de Intereses + Cuota de Amortización

    Si al poner una razón tan elevada como 1,50; lo que estamos haciendo es exigir incrementos en la mensualidad del 50% anual acumulado. Tal incremento es enorme y hace que exista una gran diferencia entre la primera mensualidad y la última. O dicho de otra forma, la primera mensualidad es muy muy pequeña. Tan pequeña que no llega a cubrir los intereses devengados ese mes. Por tanto no queda más remedio que hacer la Cuota de amortización negativa para conservar la igualdad anterior.

    Al ser negativa la cuota de amortización el capital vivo (o saldo financiero) crece en lugar de disminuir. Al transcurrir de los años y al crecer la mensualidad abonada se conseguirá que el capital vivo disminuya hasta hacerse cero al final de la vida del préstamo.

    Por tanto, es correcto que se puedan dar casos con Cuotas de Amortización negativas. Sucede lo mismo en el caso de existir carencia total, esto es, periodos en los que el prestatario no paga nada. En estos casos necesariamente el capital vivo crece, y esto se hace a base de tener Cuotas de Amortización negativas.

    Un cordial saludo.

    ResponderEliminar
  7. Soy de Costa Rica, y quiero felicitar el empeño que has puesto a Excel, ya que nos ayuda muchisimo. Soy profesor de Informatica en I.N.A de Costa Rica, y siempre utilizó tus ejercios e invito a mis alumnos a que entre!! ya que es una biblia para Excel Avanzado. Te felicito por el manejo en Macros!!!! Saludos desde Guacimo, Limón Costa Rica

    ResponderEliminar
  8. Soy de Costa Rica, soy docente en I.N.A de Costa Rica; te felicito por el aporte que le has dado a todos mis alumnos del curso basico de excel avanzado. Gracias por trabajar con macros. Gracias por el aporte y saludos desde Costa Rica!!!! Te felicitamos, ya que es una biblia para ellos.

    ResponderEliminar
  9. Hola, muchas gracias por tomarte tu tiempo y mostrarle a la gente como nosotros como hacer las cosas

    ResponderEliminar
  10. Una duda:
    ¿Por qué cuando pones más de 24 años de hipoteca, las primeras mensualidades te salen negativas???

    Entiendo que es un error, ¿Puede corregirse eso de algún modo?

    Gracias,

    ResponderEliminar
  11. Hola Tioteto.

    Se pueden llegar a obtener cuotas de amortización negativas debido a que si en el periodo no pagas ni siquiera los intereses devengados tu deuda con el banco aumenta y eso supone tener una cuota de amortización negativa.

    Esto es más frecuente en préstamos en progresión geométrica done la razón es grande, pero no es exclusivo de este tipo de préstamos.

    Simplemente piensa en un préstamo con dos fases: en la primera el pago es constante pero no cubre ni los intereses, y en la segunda fase el pago también es constante pero de mucho mayor importe para poder llegar a amortizar todo finalmente. En la primera fase tendrás cuotas de amortización negativas y capitales vivos crecientes.

    Por tanto, podemos concluir diciendo que non es un error sino una consecuencia lógica de préstamos en los que en los primeros periodos no se cubren ni los intereses con los pagos tan pequeños que se hacen.

    Un cordial saludo.

    ResponderEliminar
  12. Hola.
    Soy un auténtico novato en esto del excell. Simplemente soy un usuario. He modificado u tabla para adaptarla a mi hipoteca actual, que es de 35 años. Lo que no se es cómo hacer para que el periodo de carencia, en vez de figurar en años sea en meses (en mi caso 15). Podrías ayudarme? Muchas gracias.

    ResponderEliminar
  13. Buenas tardes;
    Estoy utilizando el excel para hacer el cuadro de amortización de una hipoteca con el sistema de cuotas crecientes en progresión geométrica pero mi problema es que la hipoteca tiene un tipo de interés fijo durante los 6 primeros meses y luego las revisiones son semestrales y no anuales.
    Me podéis decir como puedo modificar el excel para poder obtener el cuadro de amortización correcto?
    Muchas gracias!

    ResponderEliminar
  14. Buenos dias.
    Estoy intentando calcular en prestamo de este tipo, pero pagando solo dos cuotas mensuales y no consigo que lo calcule bién.
    Podrias decirme que tengo que modificar para poder realizar el calculo bien.
    Gracias

    ResponderEliminar
  15. Excelente blog Adolfo,
    Cual seria la función equivalente a nper para calcular el nº de periodos en los que se pagara el préstamo manteniendo la cuota y la razon de progresión? esta formula se aplicaría a amortizaciones a reducir vencimiento manteniendo la cuota actual de préstamo.

    Muchas gracias

    ResponderEliminar
  16. Muchas gracias por compartir tu trabajo, la verdad es que es excelente y me está ayudando mucho a entender mi hipoteca.
    El caso es que estoy utilizando la tabla que has facilitado para hacer simulaciones de amortizaciones de diferentes importes y me he dado cuenta de que la amortización se hace sobre la cuota y nosotros estamos interesados hacerlo en plazo. ¿Qué tendría que hacer para poder modificar la tabla para hacer amortizaciones sobre plazo?
    Muchas gracias

    ResponderEliminar
  17. antes de nada enhorabuena por el trabajo realizado, sirve para hacernos una ida a aquellos que nos han metido la banca el puro de una hipoteca con amortización periódica progresiva geométrica en mis caso ABANCA. En su Excel se supueden añadir años, es decir, hay hasta diez años, se pueden añadir hasta veinte. Se que con ir cambiando datos en la propia tabla, valdría, es tan solo por tener todo mi prestamos en esa tabla. gracias de antemano

    ResponderEliminar