Amortización anticipada de un préstamo en Excel

2361

Ya hemos visto anteriormente cómo calcular un préstamo en Excel y lo fácil que es construir un cuadro de amortización. Ahora le daremos una vuelta de tuerca.

Por ejemplo, una vez tengamos calculado nuestros préstamo y saber la cuota mensual que tenemos que abonar, por ejemplo, podemos estar interesados, en un momento dado, en saber qué pasaría con nuestro préstamo si adelantamos cierta cantidad de dinero en un período o si por ejemplo queremos liquidar el préstamo totalmente cuando todavía nos quedan cuotas por pagar.

Partiendo del archivo que teníamos en la entrada de cómo calcular un préstamo en excel y haciendo unos pequeños cambios, podremos ver qué pasaría en los casos que hemos descrito anteriormente. Una vez hecho los cambios, debería quedarnos de la siguiente manera:

amortizacion anticipada prestamo excel

Aquí puedes descargar el archivo que nos permite ver el efecto de la amortización anticipada de un préstamo en Excel ya hecho.

Como vemos, a simple vista parece que solo hemos añadido una columna más, pero esa columna provoca que tengamos que realizar algunos cambios en las anteriores. El cuadro de datos de la izquierda, ese no lo hemos tocado, solo cambia la forma en la que calculamos las variables del cuadro de amortización.

La única columna a la que tenemos que realizar cambios es la columna que se corresponde con la mensualidad.

Cambios en la columna de la mensualidad de nuestro préstamo

Lo primero que debemos hacer es que la mensualidad se recalcule constantemente. Es decir, que lo haga a partir del capital vivo pendiente en el período anterior. Tendremos que hacer cambios en la función PAGO de Excel. Vamos a mostrar lo que teníamos cuando calculamos un préstamo normal sin la posibilidad de introducirle una amortización anticipada. La función PAGO en ese caso quedaba de la siguiente manera.

funcion pago prestamo excel

A continuación vamos a mostrar como hemos variado los parámetros a introducir en la función PAGO para que podamos reflejar los efectos de un adelanto de capital.

amortizacion anticipada prestamo excel

Como podemos observar, los cambios se realizan en los parámetros NPER y Va.

  1. El cambio en Va es debido a que en vez de coger el dato del cuadro la izquierda, lo cogemos directamente del primer término de la columna de capital vivo, y no le pondremos dólares ($) para que siempre se vaya recalculando la cuota en función del capital vivo pendiente. En el período 1, el capital pendiente (capital vivo) será el del período anterior, es decir, el período cero.
  2. Una vez hecho esto, tendremos que cambiar NPER. Lo que hemos hecho es, en vez de calcular la mensualidad para un período constante donde el capital vivo era constante también (a la hora de introducir la fórmula), lo que haremos es recalcular los períodos pendientes después de cada mensualidad. Es decir, en el período número 1, nos quedará por pagar el total de períodos del préstamo menos cero, en el período 2, nos quedará por pagar el total de períodos menos 1, y así hasta el final del préstamo.

Esto lo conseguimos hacer de la siguiente manera. En NPER, tendremos que poner en dólares ($) el número total de períodos que aparece en nuestro ejemplo en la celda B9 y le restamos la columna de períodos del cuadro de amortización sin dólares para que se vaya recalculando al arrastrar.

Nos situaremos en la celda F4 e introduciremos la siguiente función:

  • =PAGO($B$7;$B$9-E3;-I3)

$B$7 es el tipo de interés efectivo.
$B$9-E3 es NPER. Hay que fijarse que para el primer período I3 es el período cero. Esto es así porque en el primer período nos queda por pagar el total de las mensualidades sería 24(total períodos)-0(períodos pagados)
-I3 es el capital vivo pendiente en el período anterior.

Una vez hecho esto, podremos arrastrar hacia abajo la fórmula hacia abajo. Si lo hemos hecho bien, no veremos ningún cambio en el resultado, pero ahora, nuestro préstamo se recalcula en cada período.

Construir columna Amort. Anticipada

Ya tenemos nuestro cuadro de amortización prácticamente construido para poder introducirle una amortización anticipada. El siguiente paso es construir una columna nueva que se llama en nuestro caso Amort. Anticipada. En esta columna es donde introduciremos la cantidad que queramos entregar de manera anticipada y con la cual amortizaremos capital del préstamo. Por lo tanto, a partir de ese período, la cuota mensual debería (si está bien hecho) ser menor a la que teníamos en un principio.

Nos queda acudir otra vez a la columna de mensualidad y sumarle la amortización anticipada de cada período. Por ejemplo para el período 1, la fórmula quedaría de la siguiente manera:

=PAGO($B$7;$B$9-E3;-I3)+K4

Lo que hemos hecho es sumar K4, que es la amortización anticipada del período 1. No fijamos esta celda para poder arrastrar la fórmula hacia abajo.

Ahora ya hemos introducido la posibilidad de amortización anticipada en nuestro préstamo en Excel. Como vemos, no ha cambiado nada, pero si acudimos a la columna Amort. Anticipada e introducimos en una celda cualquiera una cantidad, veremos como se recalcula la mensualidad y se ajusta el cuadro de amortización.

amortizacion anticipada prestamo excel 2

Como podemos ver, en el período 7, a parte de la mensualidad que nos correspondía ese mes (438,22€) hemos anticipado 1.000€. Por lo tanto ese mes hemos pagado 1.438,22€ , esto provoca que el préstamo se recalcule, y a partir de ahí, las cuotas que pagaremos son menores. A su vez podríamos ir adelantando en otros períodos otras cantidades y el efecto sería el mismo.

Espero que os haya ayudado esta entrada, y si tenéis alguna duda o problema podéis dejar un comentario e intentaremos resolverlo.

  • Habría que modificar la tabla. Se podría hacer pero luego al armar la tabla si quieres que la duración sea dinámica en función a las aportaciones se complica formatearla para que no muestre los valores negativos, pero se podría hacer.

    Saludos