Aunque la larga lista de funciones de Excel es una de las características más atractivas de la aplicación de hoja de cálculo de Microsoft, hay algunas gemas infrautilizadas que mejoran estas funciones. Una herramienta que a menudo se pasa por alto es el análisis What-If.

La herramienta de análisis What-If^{(What-If Analysis)} de Excel se divide en tres componentes principales. La parte discutida aquí es la poderosa función Goal Seek que le permite trabajar hacia atrás desde una función y determinar las entradas necesarias para obtener el resultado deseado de una fórmula en una celda. Siga leyendo para aprender a usar la herramienta de búsqueda de objetivos de análisis hipotético de Excel.^{(What-If Analysis Goal Seek)}

Ejemplo de herramienta de búsqueda de objetivos de Excel

Suponga que desea obtener un préstamo hipotecario para comprar una casa y le preocupa cómo la tasa de interés del préstamo afectará los pagos anuales. El monto de la hipoteca es de $100,000 y pagará el préstamo en el transcurso de 30 años.

Con la función PMT^(PMT) de Excel , puede averiguar 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 así:

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 el monto del préstamo hipotecario. La fórmula en D2 es:

=PMT(A2,B2,C2)

y representa los pagos anuales de una hipoteca de $100,000 a 30 años al 0% de interés. Observe^(Notice) que la cifra en D2 es negativa ya que Excel asume que los pagos son un flujo de efectivo negativo de su posición financiera.

Desafortunadamente, ningún prestamista hipotecario le prestará $ 100,000 al 0% de interés. Suponga^(Suppose) que hace algunos cálculos y descubre que puede pagar $6,000 por año en pagos de hipoteca. Ahora se pregunta cuál es la tasa de interés más alta que puede aceptar para el préstamo para asegurarse de no terminar pagando más de $6,000 por año.

Muchas personas en esta situación simplemente comenzarían a escribir números en la celda A2 hasta que la cifra en D2 llegara a aproximadamente $6,000. Sin embargo, puede hacer que Excel haga el trabajo por usted utilizando la herramienta Búsqueda de objetivos de análisis hipotético . ^{(Analysis Goal Seek)}Esencialmente, hará que Excel trabaje hacia atrás desde el resultado en D4 hasta que llegue a una tasa de interés que satisfaga su pago máximo de $6,000.

Comience haciendo clic en la pestaña Datos en la ^(Data)cinta^(Ribbon) y ubicando el botón Análisis hipotético en la sección ^{(What-If Analysis)}Herramientas de datos^{(Data Tools)} . Haga clic en el botón Análisis de hipótesis y ^{(What-If Analysis)}seleccione Buscar objetivo^{(Goal Seek)} en el menú.

Excel abre una pequeña ventana y le pide que ingrese solo tres variables. La variable Establecer celda^{(Set Cell)} debe ser una celda que contenga una fórmula. En nuestro ejemplo aquí, es D2 . La variable Hasta el valor^{(To Value)} es la cantidad que desea que tenga la celda en D2 al final del análisis.

Para nosotros, es -6,000 . Recuerde que Excel ve los pagos como un flujo de caja negativo. La variable Al cambiar de celda^{(By Changing Cell)} es la tasa de interés que desea que Excel encuentre por usted para que la hipoteca de $100,000 le cueste solo $6,000 por año. Entonces, usa la celda A2 .

Haga clic en el botón Aceptar^(OK) y puede notar que Excel muestra un montón de números en las celdas respectivas hasta que las iteraciones finalmente convergen en un número final. En nuestro caso, la celda en A2 ahora debería leer alrededor de 4.31%.

Este análisis nos dice que para no gastar más de $6,000 por año en una hipoteca de $100,000 a 30 años, necesita asegurar el préstamo a no más del 4.31%. Si desea continuar haciendo análisis hipotéticos, puede probar diferentes combinaciones de números y variables para explorar las opciones que tiene al tratar de asegurar una buena tasa de interés en una hipoteca.

La herramienta de búsqueda de objetivos de análisis hipotético^{(What-If Analysis Goal Seek)} de Excel es un poderoso complemento para las diversas funciones y fórmulas que se encuentran en la hoja de cálculo típica. Al trabajar hacia atrás a partir de los resultados de una fórmula en una celda, puede explorar las diferentes variables en sus cálculos con mayor claridad.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is one of the most enticing features of Microsoft’s spreadsheet application, there a few underutilized gems that enhаnce these functions. Оne оften-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Cómo usar el análisis What-If de Excel

• Cómo usar la función Hablar celdas de Excel

• 2 formas de usar la función de transposición de Excel

• Cómo entender el análisis hipotético en Microsoft Excel

• Cómo eliminar líneas en blanco en Excel

• Use nombres de rango dinámico en Excel para menús desplegables flexibles

• Cómo usar declaraciones If y If anidadas en Excel

• Cómo crear una tabla dinámica simple en Excel

• Cómo hacer un gráfico circular en Excel

• Cómo usar referencias absolutas en Excel

• ¿Qué es una matriz VBA en Excel y cómo programarla?

• Cómo compartir un archivo de Excel para facilitar la colaboración

• Cómo eliminar filas duplicadas en Excel

• ¿Qué es Microsoft Publisher? Una guía para principiantes

• Cómo abrir Word y Excel en modo seguro

• 4 formas de usar una marca de verificación en Excel

• Cómo personalizar la función de pegar de Word

• 4 formas de convertir Excel a Google Sheets

• Cómo quitar líneas de cuadrícula en Excel

• Clasificación básica de datos de una y varias columnas en hojas de cálculo de Excel