In this article we are going to look at Excel’s Goal Seek command. This command is actually quite easy to use, and it is very popular because of its ability to look backwards from a given result. A common example used to illustrate this command is finding the amount a person can borrow, at a given interest rate, based on a monthly payment; basically, pairing Goal Seek with the PMT function. We will look at that example, then we will look at a second example that will pair Goal Seek with a simple formula.
Keep in mind that with Goal Seek there are restrictions and limitations; we will only be able to change one data item at a time, and it must be applied to a function or a formula. Even given those restrictions, the following two examples will show you how Goal Seek can be useful.
Example 1: Monthly Payments Using the PMT Function
For this first example, we are going to use the PMT (Payment) function without a lot of detailed explanation. Any function or formula will serve the purpose of showing how to use Goal Seek, and PMT is as good as any other. (Aside: For those who would like to know what is happening under-the-hood; Goal Seek uses the standard numerical method – Newton’s method – to find the solution. )
First let us look at the arguments for PMT before we use Goal Seek. Refer to Figure 1. Suppose we want to know what the monthly payment will be, if the monthly interest rate is 0.3%, the length of the loan is 60 months, and the amount we want to borrow is $25,000.00 (notice that we are using months for both the interest rate and length of loan; the unit of those input arguments must be consistent). The input values are in locations A3, B3 and C3; and in cell D3 we have the function call =PMT(A3,B3,C3). (More details on using PMT are available from within Excel’s help command). The value in cell D3 is the output from using the function, namely $455.91 (it is in red because this is from the “accounting” point of view, in that the value will be removed from the total that we owe).
Suppose, however, that what we really want is the ability to set the monthly payment to a fixed value, and we want to find out how much we can borrow. For example: how much can I borrow with a monthly payment of $500, at 0.3% monthly interest and over 60 months. We basically want to use the PMT function in a “backwards” order. This is where Goal Seek can be used.
As seen in Fig. 2 the Goal Seek sub-command is located in the Data Tab and is one of the options available in the What-If-Analysis command group:
The next screen shot, Fig 3., shows the dialog box that results after selecting Goal Seek. Note that cell D3 was selected before we clicked on Goal Seek – which is why it is preloaded into the dialog box. This is the cell that has the output of the PMT function and the location of the value that we want to have locked.
Next, as seen in Fig. 4, we enter the value that we want to be locked, and in our case it is 500:
In the last box, seen in Fig. 5, we simply enter the location of the number we want Goal Seek to vary; cell C3 in our case (with absolute referencing):
Click on OK, and Fig. 6 shows the results:
So, according to PMT, if we want monthly payments of $500.00, at a monthly interest rate of 0.3%, over 60 months; then we can borrow $27,417.00 . With just a few button clicks and a few keystrokes, we were able to get Goal Seek to work with the PMT function to give us the answer we were looking for.
Example 2: Using Goal Seek with a Formula
For this next example we are going to use Goal Seek with a formula instead of a function. In a fictitious task let’s suppose we are going to build a canal. Also, let’s suppose we have some values for material and labor costs, again this is all fictitious and there are some obvious points missing – but for illustration purposes it will suffice.
Our example spreadsheet is shown below in Fig. 7:
The formula in cell E3, =A3*(B3+C3)+D3, just adds the Labor Cost/Meter to the Material Cost/Meter, multiples this by the value in cell A3, (which is the number “1”), then adds the Upfront Equipment Cost. The result can be seen in Fig. 7, and the total cost is over 927 thousand dollars for one meter.
Now let’s suppose that what we really what to know is, how long a canal can we build for 800 million dollars? This, as in the previous example, is where Goal Seek can be helpful.
As described in the previous example, simply click on: Data Tab -> What-If-Analysis -> Goal Seek. A dialog box will appear, as seen in Fig. 8. (Note that cell E3 was selected before we clicked on Goal Seek, which is why the Set cell already has the location in it):
Next, in Fig. 9, we enter 800000000 into the “To value” box:
Finally, as in Fig. 10, we enter the location $A$3 (note the absolute reference) that contains the value we want Goal Seek to vary until it finds a solution, namely the location of the number “1”:
Click on OK and as seen in Fig. 11 we have the result:
Basically, using the costs outlined, we will be able to build a canal that is a bit over 2.8 kilometers for 800 million dollars. This, and as we saw in the last example, with just a few button clicks and some keystrokes, we were able to get Goal Seek to work with our current formula and give us the answer we were looking for.
Even though we can only solve for one value at a time, Goal Seek can still be quite useful; partly since it is relatively easy to use, and partly because of its ability to work with functions and formulas that we already have in use.