Solver y Buscar Objetivo

Descargar el fichero: doblar.xlsx

En Excel disponemos de dos magníficas herramientas para resolver ecuaciones sin necesidad de despejar la variable que deseamos obtener. Se trata de Solver y de Buscar Objetivo, aunque son mucho más que eso. En especial, Solver es una estupenda herramienta de optimización (cálculo de máximos y mínimos).


Buscar Objetivo
Utilicemos la ley de la capitalización compuesta.
Vamos a contestar a la pregunta siguiente:
¿En cuanto tiempo se doblara un capital en compuesta trabajando al 5%?
Para ello podemos utilizar la función financiera de Excel:
=NPER(tasa; pago; va; vf; tipo)
=NPER(5%;;-1000;2000)
Que arroja un resultado de 14,20669908 años.
Pero en este caso vamos a resolverlo con Buscar Objetivo.

 
Pasos:

1. Poner unos datos arbitrarios en las celdas C6:C8
2. Calculamos en C9 el capital final aplicando la ley de la compuesta
3. En C8 se pone el tipo de interés del 5% que es un dato del enunciado
4. Se lanza ‘Buscar Objetivo’ que esta en el menú Herramientas (en Excel 2007 esta en Datos, ‘Análisis Y si’)
5. En ‘Definir la celda’ siempre se debe poner la celda que lleva la fórmula, en este caso la C9
6. En ‘Con el valor’ se debe poner a mano (no deja pinchar una celda) el valor al que se quiere llegar
7. En ‘Para cambiar la celda’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C7.

Solver

Vamos ahora a resolver la siguiente cuestión:
Calcular a qué tipo se ha de trabajar en compuesta para doblar el capital en 10 años.
Se puede responder facilmente utilizando una función de Excel que calcula el tipo de interés de este tipo de operaciones. La función es:
=TASA(nper;pago;va;vf;tipo;estimar)
En nuestro caso:
=TASA(10;;-1000;2000)
que  da un resultado de: 7,17735% anual.
Podemos resolverlo con Solver al igual que lo haríamos con ‘Buscar Objetivo’. Aunque Solver es mucho mejor que ‘Buscar Objetivo’, no en vano es una potente herramienta de optimización.
Nosotros no la vamos a utilizar para calcular máximos y mínimos, sino para obtener valores a los que puede llegar una celda objetivo.
Solver sólo estará disponible si se activa el Complemento que permite su utilización. En Excel 2003 se activa en: Herramientas, Complementos, Solver. En Excel 2007 se activa pulsando el botón del Office (ese botón redonde que tienes arriba a la izquierda, le denominan The Ribbon), luego elige ‘Opciones de Excel’, y a la izquierda veras ‘Complementos’, selecciona Solver y activalo. Cuando actives Solver, de paso marca también ‘Herramientas para Análisis’ que permite disponer de muchas más funciones en Excel.
Una vez activado el complemento para usarlo en una hoja, en Excel 2003 lo tiene en Herramientas, y en Excel 2007 esta en Datos.



Pasos:

1. Poner unos datos arbitrarios en las celdas C16:C18
2. Calculamos en C19 el capital final aplicando la ley de la compuesta
3. En C17 se ponen los años que según el enunciado son 10
4. Se lanza ‘Solver’ que esta en el menú Herramientas (en Excel 2007 esta en Datos).
5. En ‘Celda Objetivo’ siempre se debe poner la celda que lleva la fórmula, en este caso la C19
6. Valor de la Celda Objetivo, marcar la casilla que pone ‘Valor de’ (no marcar ni Máximo, ni Mínimo) y poner el valor 2000
7. En ‘Cambiando las celdas’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C18.

---

La versión de Office 2010 Starter es una versión básica que ahora viene instalada en muchos ordenadores nuevos pero que no tiene muchas de las opciones de menú, ni herramientas que necesitamos.

Esta es una imagen de la versión Starter donde puedes ver los recortados menús de que disfruta.

---

Activar las Macros para que funcione Solver

Solver es un complemento de Excel, que está programado en lenguaje de macros. Las macros las usaremos más adelante para hacer algunas formulas personalizadas. Eso ya os lo contaré más adelante, cuando veamos rentas. De momento tienes que saber que una Macro es un programa y que como tal hay gente que en su día programaron virus en lenguaje de macros. Por este motivo Microsoft estableció varios niveles de seguridad,
para indicarle a Excel si quieres que al abrir un fichero que lleve macros las habilite o no.

Si tienes Excel 2003 o versiones anteriores para habilitar las macros debes hacer lo siguiente:
Herramientas, Macros, Seguridad, Nivel bajo o Nivel Medio.

Lo recomendable suele ser poner nivel medio de seguridad, de esa forma cuando abras un fichero de Excel que lleva macros te preguntará que si quieres habilitarlas. Si te fías de la fuente o has pasado un antivirus le dices que SI quieres habilitar las macros, y si no te fías no las habilitas, pero entonces no son operativas.

Como todos tenemos antivirus más o menos actualizados y efectivos, puedes poner el nivel bajo de seguridad, así no te estarán preguntando cada vez que abras un fichero que lleve macros.

Para Excel 2007, la cosa se complica. Primero debemos obtener la pestaña PROGRAMADOR que es una pestaña que de entrada no aparece. Las que aparecen con la instalación son: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar, Vista.

Para obtener la pestaña PROGRAMADOR (en Excel 2007) debes ir al botón redondo de la esquina superior izquierda (se llama 'el botón de Office' porque es común a todos los programas de Microsoft Office: Word, PowePoint,..). Pulsando en ese botón redondo, verás abajo que pone 'Opciones de Excel'. En la ventana 'Mas frecuentes' verás una opción que esta desmarcada y pone: "Mostrar ficha Programador en la cinta de opciones". Marca esa opción y pulsa Aceptar. Así conseguirás tener la pestañita (ficha) PROGRAMADOR.

Se supone que ya tenemos la ficha PROGRAMADOR. Pulsa en ella, y luego en 'Seguridad de Macros', y elige 'Habilitar todas las macros'.

De esta forma tendremos habilitadas las macros. Para que esto sea efectivo debemos salirnos del fichero y volver a entrar.

Y para todos, si es importante aprender a conseguir la ficha PROGRAMADOR (si tienes Excel 2007), y conseguir poner un nivel bajo de seguridad o nivel medio.

---

Buscar Objetivo

Puedes usar la función TASA que esta disponible en todos los ordenadores con Excel,
sean de la versión que sean. O puedes usar 'Buscar Objetivo' que aunque menos preciso
que Solver, para este ejemplo es más que suficiente. Y también esta disponible en todos
los ordenadores. No es necesario instalar nada adicional.

---

Para Excel 2003 y anteriores


¿Dónde esta Buscar Objetivo?


Esta en el menú Herramientas.

¿Dónde esta SOLVER?


Al ser Solver un complemento, primero se ha de instalar. Para instalarlo haz: Herramientas, Complemento, y marca Solver, y ya que estamos marca 'Herramientas para Análisis'. Al aceptar veras que el ordenador carga el complemento. Si en su día realizaste una instalación completa, tarda en cargar los complementos un segundo. Si en su día no realizaste una instalación completa de Excel te pedirá que introduzcas el disco con el programa Office que instalaste en su día.

Comprobemos ahora que ya tengo Solver disponible. Lo encontrarás en el menú Herramientas, Solver. Pruebalo con un ejemplo. Verás que es mucho mejor que Buscar Objetivo. Es importante conseguirlo ya que en la práctica 2 y sucesivas lo usaremos de forma intensa.

'Herramientas para Análisis' lo que hace es aumentar muchísimo las funciones disponibles. Esto nos resultará necesario para las próximas prácticas, ya que utilizaremos algunas de estas funciones complementarias.

Vídeo: Solver y Buscar objetivo

Vídeo: Buscar objetivo y Solver

Caso práctico: Equilibrar una operación financiera

Veamos un caso práctico de tipo financiero. Vamos a calcular la cuantía necesaria para cuadrar una operación financiera. La calcularemos por tres métodos siendo uno de ellos Solver y otro Buscar objetivo.

Caso práctico: Efectivo de una Letra del Tesoro

Préstamo Blindado

Puede descargar el siguiente archivo de Excel.

• v_blindado.xlsx

Un préstamo blindado es aquel en el que se fija el pago periódico (la mensualidad, por ejemplo) pese a estar contratado a tipo variable. Al variar el tipo de interés lo que varía es la duración del préstamo, de forma que si el tipo de interés aplicado aumenta la duración total del préstamo también ha de aumentar y si el tipo de interés disminuye conseguiremos disminuir los periodos necesarios para amortizar completamente el principal solicitado.
El préstamo blindado es el que mantiene el término amortizativo constante, pese a estar contratado a tipo variable, siendo esto posible al variar el plazo total del préstamo.

En el ejemplo se pagan todos los meses 800 € independientemente del tipo de interés aplicado.

Tenemos que determinar el primer mes en el que el Capital Vivo (deuda pendiente, o saldo financiero) tiene signo negativo. En nuestro caso, esto se produce en el mes 153. Esto supone que necesitamos hacer un nuevo cuadro en el que los 152 primeros meses son copia del anterior, pero en el mes 153 debemos ajustar para saldar la operación.

Para determinar el cierre del Cuadro de Amortización, se han de seguir estos pasos:

1º En el mes 153 se amortiza justo por el importe del Capital Vivo del periodo anterior. Por eso la fórmula de la celda U167 es =+V166.

2º Se calcula la mensualidad (término amortizativo mensual) como suma de la Cuota de Intereses (Is) más la Cuota de Amortización (As).

3º El Capital Vivo del mes 153 es igual al del mes anterior menos lo que hemos amortizado este mes. Este valor debe ser cero para que el Préstamo quede saldado.

Utilidad y peligros del Préstamo Blindado

En momentos de posibles subidas de tipos de interés futuros permite al prestatario una cierta estabilidad en la cuantía de los pagos comprometidos a futuro, a costa de no garantizar el plazo de vencimiento.

El importe de la mensualidad se garantiza hasta cierto punto ya que para tipos de interés muy altos y/o términos amortizativos muy ajustados pudieramos encontrarnos en el caso límite del préstamo americano. Esto es, si la mensualidad únicamente cubre los intereses el plazo se hace infinito, y si los tipos de interes siguieran aumentando es muy dificil que el prestamista nos permitiera mantener el pago de una mensualidad que no cubre ni siquiera los intereses, ya que en este caso, el capital vivo o deuda pendiente aumenta con el tiempo, y esta situación no se podría mantener a largo plazo.

Vídeo

Se denominan préstamos blindados a los préstamos que siendo a tipo de interés variable suponen el pago de una cantidad constante durante toda la vida del préstamo, salvo en el último periodo, donde se ha de pagar lo necesario para ajustar la operación. La duración no se conoce a priori debido a que los tipos de interés futuros pueden variar. No podemos disminuir el término amortizativo (lo que pagamos en cada periodo) tanto como quisiéramos ya que al menos se han de cubrir los intereses devengados.

Préstamo geométrico con amortización anticipada

Descargar el fichero GeoTotal.xlsm

Este ejemplo resuelve el caso de un préstamo variable en progresión geométrica anual, fraccionado mensual, a tipo de interés variable, con un posible periodo de carencia y amortización anticipada en cualquier mes. Ha sido necesario crear una función en el Editor de Visual Basic para calcular la nueva mensualidad, al producirse un pago adicional que acelera la amortización.

Las celdas amarillas son datos y se pueden modificar. Podemos cambiar el Euribor de los 10 años, el principal del préstamo, la duración, la razón anual de la progresión geométrica (q), el diferencial que se aplica al Euribor y los años de carencia.

La mensualidad se calcula aplicando una fórmula que utiliza una función definida por el usuario. Estas funciones se programan en el Editor de Visual Basic que es donde se programan las Macros.

La fórmula de la primera mensualidad es:

=SI(B16<=$C$12;F16+J16;(Origen(C16;H15;$C$10;$C$9;D16)/vageo(1;$C$10;$C$9-B16+1;(1+D16)^12-1))/VF(D16;12;-1)+J16)

Utiliza dos funciones definidas por el usuario: VAgeo y Origen.

Código:

Function VAgeo(C, q, n, i)
If q = 1 + i Then
  VAgeo = C * n / (1 + i)
Else
  VAgeo = C * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
End If
End Function

Function Origen(s, Csm1, q, n, i12)
If q = 1 + i Then
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * n / (1 + i)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
Else
  If s - Int(s / 12) * 12 = 1 Then
      Origen = Csm1
  Else
      n = n - Int(s / 12)
      If (s / 12) - Int(s / 12) = 0 Then n = n + 1
      s = s - Int(s / 12) * 12
      If s = 0 Then s = 12
      i = (1 + i12) ^ 12 - 1
      ro = i / (1 - (1 + i12) ^ (-s + 1))
      beta = ro * (1 - (q / (1 + i)) ^ n) / (1 + i - q)
      Origen = -Csm1 * (1 + i12) ^ (-s + 1) * beta / (1 - beta)
  End If
End If
End Function

Para resolver el problema de la amortización anticipada a mitad de un año se recurre a la idea de calcular el capital vivo al inicio de ese año bajo el supuesto de que la mensualidad calculada tras la amortización anticipada sea la que se mantiene constante durante todo el año.

Histograma de frecuencias

Descargar el fichero: HistoricoIBEX.xlsx

En las columans B y C encontraremos los datos correspondientes a las cotizaciones de cierre del índice IBEX-35 de la bolsa española, desde su creación hasta julio 2008. El Ibex esta compuesto por los 35 valores de mayor capitalización de la bolsa española y se utiliza como su principal indicador.

En la columna D calculamos la variación porcentual diária. Pretendemos hacer un estudio para determinar cómo se ditribuye esta variación diária que representa lo que ha subido o bajado la bolsa en un día. Lo más habitual son los días en los que las variaciones son pequeñas y los días en los que se producen grandes ganancias o grandes caidas en bolsa son más escasos. Esta idea ha de venir recogida en un gráfico de frecuencias que si la teoría es correcta se debe comportar según una distribución normal.

¿Cómo se hace el análisis de frecuencias?

Pasos:

1. Calculamos la variación máxima y mínima, y junto a ellas consideramos esos valores más o menos un poquito más para asegurarnos que entre esos dos extremos se encuentran tambien incluidos los valores máximo y mínimo. Celdas H3 y H4.



2. Determinamos de forma arbitraria el número de intervalos de frecuencia, que en el gráfico se traducirá en el número de barras. Tomamos 50 intervalos. Esto hace que en la columna F hagamos una lista que va de 0 a 50, esto determinará los 50 intervalos.



3. En la celda G6 ponemos el mínimo (por defecto).



4. En la celda G7 ponemos una fórmula que nos de los valores de los intervalos. La fórmula es: Anterior + amplitud del intervalo entre 50. Y copiamos esa fórmula hacia abajo.



5. En la columna H vamos a emplear la función =FRECUENCIA que es una fórmula matricial y nos dará cuantos valores de la columna D (variación porcentual) estan dentro de cada intervalo definido en la columna G. Hemos de estar atentos ya que se trata de una función matricial cuyas peculiaridades aclaramos seguidamente.

La función:

=FRECUENCIA(datos;grupos)

es una función matricial. Estas funciones normalmente devuelven el resultado en varias celdas. Para su buen funcionamiento requiren tres pasos:

1. Seleccionar con el ratón o con el teclado el rango de celdas donde la función matricial depositará sus resultados. En nuestro caso, hemos de tener seleccionado el rango: H6:H56.



2. Escribir la función. En nuestro caso: =FRECUENCIA(D:D;G6:G56)



3. Para validar una función matricial no se ha de pulsar la tecla INTRO, sino que se han de pulsar las tres teclas siguientes: ctrl+shift+enter

Finalmente hacemos un gráfico de columnas verticales y veremos la distribución que en la mayoría de los casos se ajustará a una normal (campana de Gauss).

También puede ser interesante que vea el siguiente post.

• Simulación del Máximo Beneficio