Solver y Buscar Objetivo

24 julio 2008

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).

Disponemos de un fichero con ejemplos: doblar.xls

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.

Préstamo Blindado

30 junio 2008

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.

Puedes descargarte el Ejemplo: Préstamo Blindado

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.