Análisis de Sensibilidad con Controles de Número

Archivo del Ejemplo

Cuando concluimos un modelo en nuestra hoja de cálculo llega el momento de empezar a "exprimirlo" para obtener toda la información que estimemos oportuna. Una tarea muy habitual es realizar cambios en uno o en varios datos de nuestra Entrada para comprobar cómo y en qué medida afecta a nuestro resultado.

Aunque en próximos artículos abordaré con más detenimiento este tipo de análisis, y veremos cómo utilizar la herramienta Tabla (menú Datos/Tabla), vamos a ver ahora cómo llevar a cabo esta tarea de manera rápida, sencilla y "estética". Para ello, vamos a aprovechar nuevamente el ejemplo del artículo "Cálculo de la cuota hipotecaria". En la Entrada de Datos de dicho modelo vamos a introducir Controles de Número, concretamente en el Plazo en Años y en Pre/Postpagable, como se puede observar en la siguiente imagen: 

¿Cómo introducimos estos Controles de Número y para qué?

Empezando por el final, estos controles nos permitirán, por medio de "clics" en las flechas hacia arriba o hacia abajo, incrementar o disminuir los números que aparezcan en las celdas B4 y B6. Si, por ejemplo, quisiéramos calcular la cuota a pagar para distintos plazos en años, bastará con hacer unos cuántos clics en el Control de Número y trabajo terminado (y no habrá tenido que teclear diversas cifras en la celda B4).  Lo mismo ocurrirá si quiere comprobar la diferencia en la cuota a pagar con pagos prepagables o postpagables. Cada vez que quiera realizar este cálculo deberá introducir en la celda B6 un uno o un cero, mientras que tras agregar el Control de Número bastará con hacer un clic hacia arriba o uno hacia abajo.

1. Abrimos el menú Ver/Barras de herramientas/Formularios. En la barra que se abre hacemos un clic encima del icono Control de Número, como puede ver en la imagen:

2. Nos desplazamos a la celda B6 y hacemos clic en el botón izquierdo del ratón y sin soltar el clic arrastramos hacia la derecha. Al soltar el clic habrá creado el Control de Número. Ahora tenemos que "programarlo" para que funcione correctamente.

3. Hacemos clic con el botón DERECHO del ratón encima del Control de Número y seleccionamos la opción Formato de control. Se abrirá la siguiente ventana:

En Valor mínimo escribimos 1, ya que será el plazo mínimo en años que contemplemos. En Valor máximo escribimos, por ejemplo, 40. En Incremento escribimos 1 ya que cada vez que pulsemos el control de número hacia arriba o hacia abajo querremos que la cifra (plazo en años) se incremente o disminuya en uno. Y, finalmente, en Vincular con la celda seleccionamos B4. Pulsamos Aceptar.

Fíjese que ahora tendrá incorporado en B4 un "estético" botón que al pulsarlo hacia arriba incrementará la cifra de Plazo en Años y, en consecuencia, modificará la cuota a pagar.

4. Para incorporar el Control de Número en B6 debe seguir los mismos pasos para crearlo y debe "programarlo" como sigue: en Valor mínimo escribimos 0. En Valor máximo escribimos 1 (son los dos valores posibles que puede tomar este argumento). En Incremento escribimos 1. Y, finalmente, en Vincular con la celda seleccionamos B6. Pulsamos Aceptar.

Como indiqué al principio, esta es una manera sencilla, rápida y "estética" de comprobar cómo afecta a nuestro resultado (en este caso la cuota a pagar) cambios en alguna/s variable/s. Pero no se trata de un de un análisis de sensibilidad "serio" porque, entre otras cosas, no nos permite guardar automáticamente los resultados obtenidos para cada prueba realizada. Para conseguir este objetivo, como también ya indiqué, tendremos que utilizar la herramienta Tablas. No se preocupe... lo veremos.

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)