Cálculo de cuotas hipotecarias y Buscar Objetivo

En el artículo de hoy desarrollaré un modelo sencillo para calcular las cuotas a pagar de un préstamo. Una vez desarrollado, procederemos a realizar análisis donde conocemos todos los datos excepto uno, la incógnita, que queremos calcular. Por ejemplo:

* Dado un tipo de interés y sabiendo que el máximo que puedo pagar mensualmente son 800€ ¿Qué importe máximo podría financiar?

* Dado un importe a financiar y el plazo en años ¿qué tipo de interés me permitiría pagar esta cuota (800€)?

* Dado un tipo de interés y un importe a financiar ¿a cuántos años debería financiarme para pagar la cuota indicada?

Para ello utilizaremos, básicamente, la función PAGO y la herramienta Buscar Objetivo. Los pasos a seguir son los siguientes:

1. Preparamos el "esqueleto" del modelo tal y como se muestra en la imagen, y que corresponde a la Entrada de Datos (rango A2:B7) y a la Salida de Datos (celda B10). 

2. Nos situamos en la celda B10 y escribimos la siguiente fórmula:

=-PAGO(B3/B5;B4*B5;B2;B7;B6)

La función PAGO calcula la cuota a pagar basándose en pagos constantes y tipo de interés fijo (método de amortización francés). Presenta la siguiente sintaxis y argumentos:

=PAGO(Tasa;Nper;Va;Vf;Tipo) donde:

Tasa: es el tipo de interés

Nper: es el número total de pagos

Va: es el importe a financiar

Vf: saldo en efectivo que se desea lograr tras efectuar el último pago (en nuestro ejemplo lo equipararemos a una posible opción de compra).

Tipo: Si el pago es al principio del periodo tomará el valor 1 (prepagable) y si el pago es al final del periodo tomará el valor 0 (postpagable)

La función PAGO devuelve por defecto un valor con signo negativo. Si prefiere trabajar con la cifra en positivo sólo tiene que añadirle un signo "-" delante de la función:  -PAGO.

El modelo que planteo es sencillo pero bastante completo. Si desea practicar algunas de las herramientas comentadas en este blog puede probar a añadir botones de opción o cuadros combinados para el argumento Tipo en la entrada de datos (no olvide que tendrá que realizar pequeñas modificaciones en la fórmula introduciendo un condicional en el argumento Tipo de la función PAGO).

Como hemos hecho correctamente "los deberes" al separar la entrada de la salida de datos, ahora será muy sencillo realizar distintas pruebas para comprobar que resultado obtenemos simplemente modificando los datos en el rango B2:B7. Pero qué ocurre si queremos calcular, por ejemplo, lo siguiente:

a) ¿Cuál es el importe máximo que podría financiar si la cuota máxima que puedo pagar es de 800€?

Para dar respuesta a esta pregunta podríamos empezar a probar datos en la celda B2 hasta obtener la cuota indicada (y esto siempre que haya respetado el modelo Entrada-Salida de Datos...). Pero nos podría llevar un buen rato que, en cualquier caso, es innecesario. Para resolver este tipo de problemas vamos a utilizar la herramienta Buscar Objetivo:

3. Vamos al menú Herramientas/Buscar objetivo y se abrirá la siguiente ventana:

4. En el cuadro Definir la celda seleccionamos B10. Definir la celda debe hacer siempre referencia a una única celda y que contenga una fórmula.

5. En el cuadro Con el valor escribimos 800. Este cuadro, por desgracia, no nos permite vincular con celdas de la hoja, por lo que tendremos que escribir el valor dentro del cuadro.

6. En el cuadro Para cambiar la celda seleccionamos B2. En este cuadro sólo podemos hacer referencia a una celda y que contenga un dato (no una fórmula):

Fíjese que con lo que hemos hecho le estamos pidiendo que pruebe valores en B2 hasta conseguir que B10 valga 800, que es precisamente lo que estamos buscando. El resultado, tras pulsar Aceptar será el siguiente: 

Fíjese que la celda B2 nos presenta el resultado del objetivo buscado. Es decir, que para una cuota de 800€ el máximo que podríamos financiar (manteniendo el resto de datos -tipo de interés, plazo, etcétera- constantes) sería 124.165€. Para terminar, debe pulsar Cancelar , en la ventana que tiene abierta y que puede ver en la imagen, si quiere mantener el importe a financiar original y Aceptar en caso que quiera modificarlo e incorporar el resultado de Buscar Objetivo.

Con los datos originales, pruebe a calcular lo siguiente:

b) ¿Cuál sería el Tipo de Interés Nominal Anual (TIN Anual) que debería ofrecerme el banco para que mi cuota fuera de 800€ -resto de datos constantes-?

c) ¿Cuál sería el plazo en años si quisiera pagar 800€ de cuota -resto de datos constantes-?

Solución b: TIN Anual del 4,10% (más concretamente 4,09927032812685%)

Solución c: Plazo en Años igual a 46 (más concretamente 46,325239162993 años)