In this tutorial, you will learn single-cell goal seek in Excel 2013. I have used a “mortgage loan” example to demonstrate the whole process.
Goal Seek feature is one way to do Reverse What If Analysis in Excel. Another way is using Excel Solver. Learn Excel Solver – A Step by Step Complete How-to-use Guide.
Single-cell goal-seeking is really a simple concept. Excel will determine the value in an input cell that produces a result in a formula cell. The following example will show you how single-cell goal-seeking actually works in Excel.
Excel 2013 Goal Seek Example
The following figure shows the mortgage loan worksheet that we’ve used in this post: A What-If Example in Excel. This worksheet has four input cells (C4:C7) and four formula cells (C10:C13). In the previous chapter, we used this worksheet to explain a what-if analysis example. But, in this example, we’re going to demonstrate an opposite approach. Rather than supply different input cell values to look at the calculated formulas, this example lets Excel find out one of the input values that will produce the desired result.
Suppose you want to buy a new home and you’re in the market. You calculated that you can afford a $1,800 monthly mortgage payment. You’re also informed that a lender can issue a 25-year fixed-rate mortgage loan for 7.50%, based on a 70% loan-to-value (that is, a 30% down payment). You may ask yourself: “What is the maximum purchase price I can handle when selecting a home?” In other words, what will be the value of cell C4 ( it is the purchase price) that will cause the formula cell C11 (it is monthly payment) to show the result $1,800? In this simple example, you could put in values into cell C4 until C11 displays $1,800. That is really a toilsome job and boring. With more complex models, Excel can usually determine the answer much more efficiently.
To answer the question presented in the preceding paragraph, first enter the values that you already know in the input cells. Specifically:
- Enter 30% in cell C5 (the down payment percent).
- Enter 300 in cell C6 (the loan term, in months).
- Enter 7.5% in cell C7 (the annual interest rate).
Next, choose Data ➪ Data Tools ➪ What-If Analysis ➪ Goal Seek. The Goal Seek dialog box shown in the following figure will appear. Completing this dialog box is similar to forming a sentence. Read the dialog box like this sentence: we want to Set cell C11 To value 1800 By changing cell C4. You can enter this information in the dialog box either by typing the cell references or by pointing the cells with the mouse. Click OK to begin the goal-seeking process.
In less than a second, Excel will display the Goal Seek Status dialog box shown in the following figure. Goal Seek Status box will show the target value and the value that Excel calculated (current value). In our case, Excel found an exact value. The worksheet now displays the found value in cell C4 ($347,965). As a result of this value, if you buy a home for $347,965, your monthly payment amount will be $1,800 for the above-given values (interest rate, down payment, and the loan term).
At this point, we have two options:
- We can click OK to replace the original value with the found value.
- We can click Cancel to restore our worksheet to the state that it had before we chose Goal Seek.
More on Goal Seek in Excel
Excel can’t always find a value that produces the result that you’re seeking. Sometimes, a solution simply doesn’t exist. In such a case, the Goal Seek Status box informs you of that fact.
Sometimes Excel may show that it is unable to find a solution, but you’re pretty sure that one exists. If that’s the case, you can try the following options:
- Change the current value of the By Changing Cell field in the Goal Seek dialog box to a value that is closer to the solution, and then reuse the command.
- Adjust the Maximum iterations setting on the Formulas tab of the Excel Options dialog box. To do this, choose File ➪ Options. If you increase the number of iterations (or calculations), it will make Excel try more possible solutions.
- Lastly, double-check your logic. Make sure that the formula cell exists, indeed, depend upon the specified changing cell.
Like all computer programs, Excel has limited precision. To demonstrate this limitation, enter =A1^2 into cell A2. Then use the Goal Seek dialog box to find the value in cell A1 (it is empty) that makes the formula return 25. Excel comes up with a value of 4.999976462, which is close to the square root of 25, but certainly not exact. You can adjust the precision on the Formulas tab of the Excel Options dialog box (make the Maximum Change value smaller).
In some cases, multiple values of the input cell may produce the same desired result. For example, the formula =A1^2 returns 25 if cell A1 contains either –5 or +5. If you use goal seeking when multiple solutions are possible, Excel gives you the solution that is closest to the current value.
So, this is how to use Goal Seek in Excel 2013. If you have any further questions on this topic, please feel free to put your thought in the comments section.
Download Working File
Download the working file from the link below: