Calcular el cuadro de amortización con interés variable

cuadro-amortización-interés-variable¿Te revisan el tipo de interés de la hipoteca y quieres saber qué cuota tendrás que pagar los próximos meses?

En mi entrada “Como calcular el cuadro de amortización de un préstamo por el método francés” te explicaba como calcular con Excel el cuadro de amortización de un préstamo mediante ese método, siempre que el tipo de interés no variase durante la vida del préstamo.

Hoy te quiero explicar como calcularlo pero en el caso de que el tipo de interés sea variable, como ocurre con la mayoría de las hipotecas. De está forma podrás prever, por ejemplo, como te afectará una subida o bajada del euribor.

Los préstamos con interés variable se caracterizan porque el tipo de interés está definido por una parte fija (diferencial) y una parte variable. Esta parte variable suele ser un índice de referencia como lo es el euribor.

Para explicarlo seguiré con el mismo ejemplo que en la entrada anterior, salvo que el tipo de interés será de un 7% más euribor, siendo el euribor aplicable al primer año del 1% y la revisión del tipo de interés anual.

Recordemos:

Capital inicial concedido: 10.000,00€

Tipo de interés: 7% mas euribor

Número de años: 5

Número de cuotas por año: 4

Empezaremos por hacer algunos cambios en la hoja de cálculo que teníamos:

  • En la celda A2 escribimos “diferencial” (en vez de tipo de interés).
  • En la celda B2 introduciremos el componente fijo del tipo de interés que nos aplica el banco. En nuestro ejemplo: 7%.
  • En la celda G6 escribimos el título de la nueva columna que tendrá nuestro cuadro de amortización “Euribor“.
  • En la celda G7 escribimos el euribor que nos aplicarán el primer año, es decir, 1%. Copiamos al resto de celdas, arrastrando desde la esquina inferior derecha hasta la última celda de esa columna, la G26 en nuestro ejemplo.

Puesto que nos revisarán el tipo de interés anualmente, las cuotas donde nos aplicarán los nuevos tipos de interés serán en la cuotas 5, 9, 13 y 17 (que corresponden a la primera cuota del segundo, tercero, cuarto y quinto año respectivamente). Por tanto, introduciremos el nuevo euribor aplicable para cada año en las celdas  G11, G15, G19 y G23.

Veamos que cambios sufrirán las fórmulas que habíamos introducido en el cuadro de amortización que calculamos en la entrada anterior:

Empecemos por modificar la primera cuota:

Columna intereses:

  • En la celda B7 vamos a hacer el siguiente cambio: El argumento tasa de nuestra fórmula en vez de ser $B$2/$B$4 será ($B$2+G7)/$B$4, ya que el tipo de interés ahora es diferencial + euribor. Observamos que la referencia al euribor “G7” es variable y no fija como la referencia al diferencial “$B$2”. Así actualiza los cálculos automáticamente cuando introduzcamos los nuevos valores del índice de referencia. Por tanto, la fórmula que tenemos en B7 será:

=PAGOINT(($B$2+G7)/$B$4;A7;$B$3*$B$4;$B$1).

  • Copiamos al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

Columna amortización:

  • En la celda C7 vamos a hacer el siguiente cambio: Al igual que en la columna de interés el argumento tasa de nuestra fórmula en vez de ser $B$2/$B$4 va a pasar a ser ($B$2+G7)/$B$4. Por tanto, la fórmula que tenemos en C7 será:

 =PAGOPRIN(($B$2+G7)/$B$4;A7;$B$3*$B$4;$B$1)

  • Copiamos al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

Columna cuota:

  • En la celda D7 vamos a hacer el siguiente cambio: Al igual que en las columnas anteriores el argumento tasa de nuestra fórmula en vez de ser $B$2/$B$4 va a pasar a ser ($B$2+G7)/$B$4. Por tanto, la fórmula que tenemos en D7 será:

 =PAGO(($B$2+G7)/$B$4;$B$3*$B$4;$B$1)

  • Copiamos al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

Comprobarás que el cuadro de amortización que tenemos ahora es exactamente igual al de la entrada anterior. Esto es debido a que el interés aplicado es 7%+1%=8% que es el que estábamos aplicando a nuestro ejemplo con interés fijo.

Veamos ahora que cambios hay que hacer en las fórmulas a partir de la segunda cuota.

Para que el cuadro de amortización se actualice automáticamente cada vez que introduzcas un nuevo valor en la columna euribor, a partir de la cuota número 2 (esta incluida), cada cuota se tiene que calcular como si fuese la primera de un préstamo con los siguientes datos:

  • el capital inicial es el capital pendiente de pago (capital vivo)
  • el número total de cuotas es el número de cuotas pendientes que le quedan de nuestro préstamo (a partir de esa).

Veamos un ejemplo:

Si hablamos de la cuota número dos, esta la vamos a calcular como si tuviéramos un préstamo donde esta es la cuota número uno de un préstamo con un capital inicial de 9.588,43 (que es el capital vivo que quedó después de pagar la cuota número uno) y que tenemos que devolver en 19 cuotas (que son las cuotas que quedan pendientes después de pagar la primera).

Para calcular en cada cuota el número de cuotas pendientes que quedan vamos a usar la función CONTAR. Esta función cuenta las celdas que contienen datos números de un rango indicado. En nuestro caso el rango de celdas que deberá contar será, dentro de la columna número de cuota, desde la celda A7 hasta la cuota justo anterior a la que queremos calcular. Esta función nos devolverá el número de cuotas que ya hemos pagado. El resultado se lo tendremos que restar al número total de cuotas que teníamos al principio, 20 ($B$3*$B$4).

Nota: Si os liáis mucho con la función CONTAR también podéis incluir otra columna en vuestro cuadro de amortización que se titule cuotas pendientes. En la primera celda de esa columna poneis 20 y luego usando series-rellenar (al igual que hicimos con la columna número de cuota) rellenáis el resto pero en este caso el incremento será de -1 y el último valor 1. Luego en vuestra función solo tenéis que hacer referencia a esta columna en el argumento NPer. Personalmente prefiero usar la función CONTAR así que seguiré la explicación con esta función.

Estos son los cambios a realizar:

Columna intereses (en la celda B8):

  • El argumento período de nuestra fórmula en vez de ser A8 va a ser 1.
  • El argumento NPer de nuestra fórmula en vez de ser $B$3*$B$4 será =($B$3*$B$4)-CONTAR($A$7:A7). Dejamos fija la primera celda del rango y variable la última para que así vaya variando  el tamaño del rango cuando copiemos al resto de las celdas de la columna.
  • El argumento VA en vez de ser $B$1 pasará a ser F7, que es el capital vivo que quedó después de pagar la primera cuota. Dejamos su referencia variable para que varíe cuando copiemos al resto de las celdas de la columna.

Por tanto, la fórmula que tenemos en B8 será

=PAGOINT(($B$2+G8)/$B$4;1;($B$3*$B$4-CONTAR($A$7:A7));F7)

Ya solo queda copiar al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

Columna amortización (en la celda C8):

En los argumentos de la función PAGOPRIN de la celda C8 haremos los mismos cambios anteriores, quedando la siguiente función:

=PAGOPRIN(($B$2+G8)/$B$4;1;($B$3*$B$4-CONTAR($A$7:A7));F7)

Copiamos al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

Columna cuota (en la celda D8):

En la celda D8 haremos los mismos cambios que en las dos anteriores con la salvedad de que aquí no tenemos el argumento Periodo. Por tanto, la fórmula que quedará en D8 será

=PAGO(($B$2+G8)/$B$4;($B$3*$B$4-CONTAR($A$7:A7));F7)

Copiamos al resto de celdas de esta columna arrastrando desde la esquina inferior derecha.

En este momento todos los datos del cuadro de amortización siguen igual.

Ahora supongamos que al inicio del segundo año el euribor sube hasta el 1,5%, al inicio del tercer año baja hasta el 1,2%, al inicio del cuarto año sube de nuevo hasta el 1,3% y al inicio del quinto año de 1,5%. Por tanto en las celdas de G11 a G14 introducimos 1,5%, de la G15 a la G18 1,2%, de la G19 a la G22  1,3% y de la G23 a la G26 1,5%.

Podemos ver como se ha actualizado el cuadro de amortización:

Interes variable

Modificando el capital vivo también podrás calcular como varían las cuotas y el cuadro de amortización. Por ejemplo, si en la cuota número 6 quieres hacer una amortización parcial de 2.000,00, sólo tendrás que restarle esa cantidad al capital vivo y podrás ver cómo quedarán las cuotas a partir de esa.

Si quieres ver como se calculan los cuadros de amortización por otros métodos que usan habitualmente los bancos puedes ver las siguientes entradas:

Préstamo: Amortización por el método americano (con fondo)

Préstamo: Amortización por el método italiano

Si no quieres perderte ninguna entrada, puedes seguirme en facebooktwitter, google + o suscribirte a la newsletter de Campus Administrativo y recibir en tu e-mail novedades y consejos para administrar una empresa de forma más eficaz.

 

Deja un comentario