Sea un préstamo a tipo fijo con las siguientes características:
* Principal 700.000 euros
* Duración 8 años
* TIN 6%
Al final del mes 30 se entregan 100.000 euros, en concepto de amortización anticipada que permite reducir la duración del préstamo. Calcular la última mensualidad.
Ver Ej.9 del fichero prestamos.xlsm
Ver también el siguiente caso de amortización anticipada.
Método 1
Se elabora el cuadro de amortización con la mensualidad inicialmente calculada. Al final del mes 30 se añaden los 100.000 euros en concepto de amortización anticipada. Para considerar este aporte adicional hemos creado una columna adicional y la fórmula de la mensualidad suma el importe de esta columna. Así, la celda D17 es:
=PAGO($L$19;$L$17;-$L$15)+I17
Esta fórmula se copia hasta el final de su columna.
Al añadir los 100.000 euros de amortización anticipada observamos que al final comienzan a producirse capitales vivos negativos. Eliminamos todas las filas donde se encuentran los capitales vivos negativos, salvo la primera de ellas. En este caso resulta ser la fila del mes 82.
Ahora debemos ajustar los valores del mes 82 para lo cual hacemos coincidir la cuota de amortización A82 con el capital vivo del periodo anterior. Por tanto, la celda F98 es:
=+G97
Finalmente hacemos que D98 sea:
=+F98+E98
ya que la última mensualidad debe cumplir, al igual que todas las demás, que es igual a la Cuota de Intereses más la Cuota de Amortización.
Observar como al efectuarse el pago de los 100.000 € en concepto de Amortización Anticipada no se modifica la mensualidad posteriormente. Sigue siendo de 9.199 €.
Al no modificarse la mensualidad lo que sucede es que se acorta la duración del préstamo. Ya no es necesario ir hasta los 96 meses para amortizar completamente el préstamo. Observamos que en el mes 82 conseguimos obtener un capital vivo incluso negativo. Por eso es necesario ajustar el último mes. Para ello hacemos que la última cuota de amortización coincida con el capital vivo del mes 81. Esto es, hacemos iguales las dos celdas verdes.
Finalmente ajustamos la celda de color naranja que es la última mensualidad y hacemos que sea igual a la suma de la cuota de intereses más la cuota de amortización.
Veamos las fórmulas del cuadro de amortización. La clave está en la fórmula del término amortizativo.
Método 2
En la celda F116 calculamos la mensualidad constante a, con la función PAGO.
En F117 calculamos el Capital Vivo C30 por el método prospectivo, con la función VA.
Si quisiéramos utilizar el método retrospectivo la expresión empleada debiera ser la de la celda G127:
=+C116*(1+C120)^30-VF(C120;30;-F116)
En F119 calculamos n con la expresión:
=+NPER(C120;F116;-F118)
Tomamos la parte entera que es 51.
Calculamos el Capital Vivo en t=51, que es C51, por el método retrospectivo. Eso se calcula en la celda F21 con la siguiente expresión:
=+F118*(1+C120)^F120-VF(C120;F120;-F116)
La última mensualidad a52, en F122, se obtiene capitalizando el capital vivo anterior.
Vídeo
Para calcular n hemos de despejar tomando logaritmos. Veamos cómo se consigue despejar.
