Goal Seek: Excel’s Crystal Ball

Goal Seek is one of Excel’s most useful tools for creating predictive models. You can use Goal Seek to help optimize something as simple as a mortgage payment or incorporate it into a larger, more complicated data analysis. So, if you know what you want your mortgage payment to be and, you want to determine how much money you should put down (or what interest rate you need to obtain), Goal Seek makes this quick and easy! It allows you to target different variables within your data model and use the results to determine the best inputs. It allows you to quickly change the values of variables within your model so that you can easily see what the effects are on the final result.

Getting Started With Goal Seek

In order for Goal Seek to be effective, it is important to have the data in your spreadsheet laid out properly. You need to establish one “main” formula cell that contains the primary formula that you want to change. You will then need to have one or more feeder cells which feed into your primary formula. In the example below, the main formula is the Monthly Payment formula (bolded in Cell C1). Cells B2-B5 contain the four variables that make up the Monthly Payment Formula (=PMT(B4/12,B3,-B5).
Goal Seek Data Setup

From here, simply navigate to the Data Tab on the Ribbon, click the “What-If Analysis” button, and then select “Goal Seek”. You’ll then get a screen that looks like this:
Goal Seek Screen

Setting Up the Simulation

The “Set Cell” value should be set to the cell that contains your main formula (in this case, C1). The second field “To Value”, should be set to whatever number you want your main formula to produce. So, for example, if we wanted to lower the Monthly Payment result to $700 (from its current $859.35), you would enter “700” in this field. The last field, “By Changing Cell”, is the cell that Goal Seek will change in order to produce the desired result in the main formula (in our example, $700). So, if you wanted to lower your Monthly Payment by increasing your Down Payment, you would enter “B2” in this field.

Your screen should look like this:
Goal Seek Screen Final

Click “OK” to run the simulation. You’ll get a results screen that looks like this:
Goal Seek Result Screen

As you can see, Excel has adjusted the amount of the Down Payment to $53,377 (from its prior $20,000) in order to produce the desired monthly payment of $700. Click “OK” to save the results, or click “Cancel” to discard the changes. You can run this simulation for any of the other variables (such as Term and Interest Rate) to determine how long you would need to extend the payments or how low the interest rate would need to go in order to obtain the $700 monthly payment. Goal Seek is very flexible and a good way to quickly determine how much a variable needs to change in order to obtain the desired final result.

Learn About Excel’s Other Data Tools

Besides Goal Seek, there are several other Data Tools that you can use to simulate different data scenarios. You can learn all about them by signing up for our free email newsletter. You’ll get valuable Excel tips and tricks directly in your inbox and, you’ll also receive exclusive discounts on our training classes.

Posted in Excel and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *