Goal Seek function in Excel

Goal Seek is a very powerful tool in Excel for finding break-even points or to perform tailored what-if analysis.

When you know the desired result of a formula but not the input value the formula needs to determine the result, you can use the Goal Seek feature in Excel available by clicking Goal Seek on the Tools menu. This back-solves the problem and finds the input value that satisfies your requested output value. You can change the value of a specified cell until the formula that is dependent on the changed cell returns the result you want.

The Goal Seek function has thousands of applications and this tutorial looks at some specific solutions for mortgage or loan decisions. An accompanied workbook can be downloaded which illustrates the examples in this tutorial.

Goal Seek function

To use Goal Seek, a base model must to be set up in your Excel worksheet with the inputs and formulas already in place and working. The function is activated by clicking Goal Seek on the Tools menu.

• Set cell – The output cell
• To value – the target value of the output cell
• By changing cell - The cell that should change

The “Set cell” must always contain a formula or a function. The “By changing cell” must contain a value only, not a formula. Once you have already selected the “Set cell”, click to the “To value” cell and type in the desired value. Then finally click or tab to the “By changing cell” and select cell that you wish to change and click OK.

As soon as you select “OK” you will see that Goal Seek re-calculates the formula. Then you have options either to “OK” or “Cancel”. If “OK” is selected the new solved value will be inserted into the Worksheet. If “Cancel” is selected, the value in the worksheet will return to its original state.

Iterations in Goal Seek

Note that when goal seeking, Microsoft Excel backs into a solution using numerical iterations, so it won’t necessarily find the “exact” solution. It might come “close enough” and stop, or it might not be able to find the solution that you would like to achieve. 

Goal Seek example 1: Housing Mortgage

The following variables are used to calculate “Monthly Payment” in a typical housing mortgage:

Screenshot: Housing Mortgage example.

Based on the calculation, the Monthly Payment that needs to be made is AUD 4,182.20. For example, you are the Mortgage Analyst in a bank and your prospective client has a target Monthly Payment of AUD 4,000 instead.

Question: Calculate how the term of the loan will be affected if the Monthly Payment is changed to AUD 4,000. The steps to solve:

• Create cell H11 – Call this cell “Delta”
• Enter formula in H11 to calculate variance between Target Monthly Payment and the Calculated, i.e. $4,000 - $4,182.20 = $(182.20)
• Activate Goal Seek dialogue box in Tools menu
• Select H11 as “Set cell”
• Enter “To value” to zero
• “By changing cell” F8 which is loan term
• The Goal Seek solution in cell F8 is 270 Months or 22.5 Years.

This means to achieve a Monthly Payment of AUD 4,000, it is required to extend the loan term from 240 Months to 270 Months. You can also solve for the Loan Amount (cell F6) instead of the Term to achieve the target.

Screenshot: Mortgage Goal Seek

Goal SeekExample 2: Term Loan

Now let’s do another case where you are the Analyst in a bank deciding the pricing for a Term Loan.

Screenshot: Term Loan example

Refer to the Calculation tab to learn how the repayment and the DSCR are derived. The calculated Average DSCR is 1.96x, however the Target Average DSCR is 2.00x.

Question: Calculate what is the Interest Margin to achieve the Target Average DSCR of 2.00x.

• Create cell H26 – Call this cell “Delta”
• Enter formula in H26 to calculate variance between Target Average DSCR and the Calculated, i.e. 2.00x – 1.96x = 0.04x
• Activate Goal Seek dialogue box in Tools menu
• Select H26 as “Set cell”
• Enter “To value” to zero
• “By changing cell” F20 which is Interest Margin
• The solution in cell F20 is 2.49% p.a.

This means in order to achieve a Target Average DSCR of 2.00x, the Loan needs to be priced at Interest Margin of 2.49% p.a.

Screenshot: Term Loan Goal Seek

Follow the same steps, you may also wish to change the Facility Limit (cell F15) instead:

• Select H11 as “Set cell”
• Enter “To value” to zero
• “By changing cell” F15 which is Facility Limit
• The Goal Seek solution in cell F15 is AUD 319.20 M

COMMENTS