Usando la herramienta de búsqueda de objetivos de análisis Y si… de Excel

Aunque la larga lista de funciones de Excel es una de las características más tentadoras de la aplicación de hoja de cálculo de Microsoft, hay algunas gemas subutilizadas que mejoran estas funciones. Una herramienta que a menudo se pasa por alto es el análisis de suposición Y si…

La herramienta de análisis de suposición Y si… de Excel se divide en tres componentes principales. La parte que se discute aquí es la poderosa función de Búsqueda de Objetivos que permite trabajar hacia atrás a partir de una función y determinar las entradas necesarias para obtener la salida deseada de una fórmula en una celda. Sigue leyendo para aprender a usar la herramienta de Excel “What-If Analysis Goal Seek”.

Ejemplo de la herramienta de búsqueda de objetivos de Excel

Supongamos que quieres pedir un préstamo hipotecario para comprar una casa y te preocupa cómo el tipo de interés del préstamo afectará a los pagos anuales. La cantidad de la hipoteca es de 100.000 dólares y pagará el préstamo en el curso de 30 años.

Utilizando la función PMT de Excel, puedes calcular fácilmente cuáles serían los pagos anuales si la tasa de interés fuera del 0%. La hoja de cálculo probablemente se vería algo como esto:

Un simple cálculo de pago de hipoteca en Excel

La celda en A2 representa la tasa de interés anual, la celda en B2 es la duración del préstamo en años, y la celda en C2 es la cantidad del préstamo hipotecario. La fórmula en D2 es:

=PMT(A2,B2,C2)

Desgraciadamente, ningún prestamista hipotecario te prestará 100.000 dólares al 0% de interés. Supongamos que haces algunos cálculos y descubres que puedes pagar 6.000 dólares al año en pagos de hipoteca. Ahora te preguntas cuál es la tasa de interés más alta que puedes tomar para el préstamo para asegurarte de que no termines pagando más de 6.000 dólares al año.

Muchas personas en esta situación simplemente empezarían a escribir números en la celda A2 hasta que la cifra en D2 alcanzara aproximadamente 6.000 dólares. Sin embargo, puedes hacer que Excel haga el trabajo por ti usando la herramienta de búsqueda de objetivos del análisis Y si… Esencialmente, harás que Excel trabaje hacia atrás desde el resultado en D4 hasta que llegue a una tasa de interés que satisfaga tu pago máximo de 6.000 dólares.

Comienza haciendo clic en la pestaña Data en la cinta y localizando el botón What-If Analysis en la sección Data Tools. Haga clic en el botón What-If Analysis y elija Goal Seek del menú.

Herramienta de búsqueda de objetivos de análisis Y si...

Excel abre una pequeña ventana y le pide que introduzca sólo tres variables. La variable Set Cell debe ser una célula que contenga una fórmula. En nuestro ejemplo aquí, es D2. La variable To Value es la cantidad que quieres que la celda en D2 esté al final del análisis.

By Changing Cell es el tipo de interés que quieres que Excel encuentre para ti para que la hipoteca de 100.000 dólares te cueste sólo 6.000 dólares al año. Así que, usa la célula A2.

Variables de búsqueda de objetivos de Excel

Click the OK button and you may note that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. En nuestro caso, la celda en A2 debería leer ahora alrededor de 4.31%.

Resultados de un análisis de Excel de la búsqueda de objetivos

Este análisis nos dice que para no gastar más de 6.000 dólares al año en una hipoteca de 30 años y 100.000 dólares, es necesario asegurar el préstamo a no más del 4,31%. Si quieres seguir haciendo análisis de “qué pasaría si”, puedes probar diferentes combinaciones de números y variables para explorar las opciones que tienes cuando intentas asegurar una buena tasa de interés en una hipoteca.

La herramienta “What-If Analysis Goal Seek” de Excel es un poderoso complemento de las diversas funciones y fórmulas que se encuentran en la típica hoja de cálculo. Trabajando hacia atrás a partir de los resultados de una fórmula en una celda, puede explorar las diferentes variables en sus cálculos más claramente.

Fundador del Help Desk Geek y editor gerente. Empezó a escribir en el blog en 2007 y dejó su trabajo en 2010 para escribir a tiempo completo. Tiene más de 15 años de experiencia en la industria de la tecnología de la información y posee varias certificaciones técnicas. Lea la biografía completa de Aseem.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *