Solver: cálculo de la TIR

Solver es un complemento de excel, que nos permite resolver diversos problemas matemáticos, como es el caso de la optimización con restricciones, o problemas que requieren de muchas iteraciones, por poner dos ejemplos.

Un ejemplo básico de optimización con restricciones, es por ejemplo calcular las horas óptimas de producción en una empresa, teniendo en cuenta que el tramo horario se tarifica por parte de la compañía eléctrica a diferentes precios (restricciones horarias), y teniendo en cuenta que el número de horas trabajadas por cada empleado, no puede exceder de 8 horas diarias (restricción legal). Podríamos incluir muchas más restricciones, como por ejemplo, las relativas a mantener un nivel de stock máximo, o la de producir como máximo un número X de unidades. Todo esto podríamos resolverlo con excel, a través de solver, un complemento que nos permite ahorrar tiempo y dolores de cabeza.

Un ejemplo relativo al tema de iteraciones, y que nos puede solucionar solver, es por ejemplo cuando tenemos una fórmula en la que cambiando determinada cifra que tenemos en otra celda, obtenemos un resultado, y a base de cambiar esa celda, vamos obteniendo un resultado cada vez más cercano a lo que nosotros buscamos. Podríamos resolverlo introduciendo diferentes valores, e ir ajustando cada vez más, esa cifra, hasta dar con una que nos permita obtener el resultado que buscábamos, o bien podemos utilizar solver, para economizar tiempo, y obtener resultados más fiables.

Vamos a ver el uso de solver con un tema de financiación al consumo, en el que pensé el otro día viendo la publicidad de una cadena de electrodomésticos. Seguramente os habréis fijado que casi todas las cadenas de tiendas, hipermercados, y grandes almacenes nos ofrecen la posibilidad de financiar nuestras compras, en determinados productos. Seguramente habréis visto también, que se ha puesto de moda en algunos sitios, financiar una compra que vale X euros, en 11 pagos de 1 décima parte el valor de X. Vamos a verlo con un ejemplo:

Supongamos que la cadena de tiendas "Electrodomésticos buenos, bonitos y baratos" (una cadena inexistente), nos ofrece un televisor de 42" de plasma, de una reconocidísima marca, y de ultimísima tecnología, a un precio de 800 euros. Imaginemos también que debido a la crisis, nuestros bolsillos carecen de liquidez, y que esa cadena de tiendas nos ofrece pagar el televisor durante 11 meses, a razón de 80 euros mensuales (1/10 parte del precio inicial). Nosotros, ingenuos a más no poder, pensaremos "bueno, no está mal, podemos pagarlo casi en un año, en unas módicas mensualidades". Pero al llegar a casa, hemos decidido que lo mejor será coger nuestro excel, y ver que tipo de interés implícito lleva inherente esa operación, antes de aceptar la propuesta de financiación del vendedor (aunque su obligación es informarnos es todo esto, incluida la TAE, es decir, la Tasa Anual Equivalente, o en palabras que todo el mundo entienda, el tipo de interés efectivo, o el tipo de interés real de la operación).

Vamos a introducir para ello, otro concepto básico: el valor actual (VA). El valor actual, es la suma de diferentes flujos, realizados en distintos periodos de tiempo, pero valorados en un momento dado (normalmente a fecha de hoy). Dicho en palabras más sencillas, y aplicado a nuestro ejemplo, el valor actual neto (VAN), será la suma de esos 80 euros mensuales, que pagamos al cabo del primer mes, en el segundo mes, en el tercer mes, y así, hasta el mes undécimo, pero valorados a día de hoy. Todos sabemos que no es lo mismo cobrar 100 euros hoy, que cobrar esos mismos 100 euros dentro de 20 años. ¿Por qué?. Pues porque esos 100 euros que cobremos dentro de 20 años, no tienen el mismo valor que hoy, …de hecho, esos 100 euros a cobrar dentro de 20 años, pueden ser hoy el equivalente a 5, 8, o 24 euros (no lo he calculado, porque habría que estimar una inflación media para esos 20 años), pero en ningún caso, esos 100 euros dentro de 20 años serían iguales que los 100 euros hoy. Siguiendo con nuestro ejemplo, la cuota nº 11 que pagaremos al final, tenemos que "traerla" al momento presente, es decir, ahora. Lo mismo haremos con el resto de las cuotas.

Sabemos que el valor actual se rige por esta fórmula:


Como sabemos que hoy, el valor del televisor es de 800 euros (valor hoy = valor al contado), el plazo es de 11 mensualidades, y que cada una de ellas es de 80 euros, entonces podemos escribirla así:


Como todos los pagos son iguales (80 euros mensuales), podemos escribir la función genérica, de la siguiente forma:


Y para nuestro ejemplo, y dado que las cuotas no son anuales, sino mensuales, el tipo de interés tendremos que pasarlo de "tipo de interés anual", a "tipo de interés mensual". Quedará así:


Si cambiamos el valor de 800 de lado, obtendremos el Valor Actual Neto (VAN) igual a cero, y es en ese momento donde i se convierte en la TIR (tasa interna de rentabilidad), aunque para nuestro caso, la llamaremos TAE (tasa anual equivalente). Por tanto, la TIR será el tipo de interés implícito de la operación, que hace que el VAN sea cero:


Para resolver esa ecuación, tenemos dos opciones:

a) Probar diferentes valores para "i", y así saber que tipo de interés nos cobra la cadena de tiendas que nos está financiando la compra.

b) Ejecutar solver, introduciendo los parámetros necesarios.

Vamos a ver como solucionar esto con solver. Para ello, en el menú Herramientas, miraremos si nos aparece Solver... por algún lado. Si no fuera así, desde el menú Herramientas, seleccionaremos Complementos..., y marcaremos con una muesca el complemento de solver. A partir de ahora, ya tendremos solver visible desde el menú Herramientas.

Supongamos que tenemos los siguientes datos, en las siguientes celdas:

F4 = 800
F5 = F4/10
F6 = 11
E7 = -F4+((F4/10)*((1+F7/12)^11 - 1)/((F7/12)*(1+(F7/12))^11))
F7 = X

El significado de cada celda es el siguiente:

F4 = Corresponde al valor al contado.
F5 = La cuota que pagaremos: 1/10 parte de la cantidad que aparece en F4.
F6 = El número de meses a financiar la operación.
E7 = En esta celda hemos introducido la fórmula que hace que el VAN sea cero, por tanto, en esta celda deberemos obtener cero.
F7 = Este es el valor a determinar, en nuestro caso, el tipo de interés implícito, es decir, la "i". Para que todo funcione como debe funcionar, lo recomendable es introducir un valor cualquiera, para que a partir de él, Solver ejecute los cálculos. Podemos introducir un 1, un 2, o la cifra que queramos.

Ahora nos vamos a Herramientas, y seleccionamos Solver.... Deberemos introducir en "Celda objetivo:" $E$7 (si lo preferimos, podemos omitir los símbolos del dólar). El valor de la celda objetivo, debe ser cero (VAN = 0), por tanto, en "Valores de:" pondremos 0. Finalmente en "Cambiando las celdas", pondremos $F$7 (aunque podemos omitir los símbolos del dólar).


Ya solo nos quedará darle al botón "Resolver", y se nos presentará esta otra pantalla:


Pulsamos sobre el botón aceptar, una vez hemos seleccionado "Utilizar solución de Solver", y veremos con en la celda F7 nos aparece el tipo de interés anual real, que nos cobrará la cadena de tiendas, por financiarnos el televisor de plasma (un 19,4776%). Veremos algo como lo que muestra la siguiente imagen:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo. Debéis tener presente que con independencia del importe que introduzcáis, si el plazo es de 11 mensualidades, y la cuota es 1/10 parte del importe al contado, el tipo de interés efectivo de la operación siempre será el mismo (el 19,4776%), así que ya sabéis, desde el punto de vista del consumidor, una compra financiada en esas condiciones, no es una buena opción, por el elevado coste que supone. En cambio, si tu eres quien vende ese televisor, para ti será una excelente inversión.