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.

You can use the Goal Seek feature in Excel by clicking Goal Seek in the tools menu.

When you know the desired result of a formula, but not the input value, the formula needs to determine the result. Goal Seek 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. The accompanied workbook can be downloaded, which illustrates the examples in this tutorial.

To use Goal Seek, a base model must 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 in 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 selected the ‘Set cell’, click the ‘To value’ cell and type in the desired value. Finally, click or tab to the ‘By changing cell’ and select the cell that you wish to change, then click ‘OK’.

As soon as you select ‘OK’, you will see that Goal Seek recalculates 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:

For example, based on the calculation, the monthly payment that needs to be made is AUD 4,182.20. You are the mortgage analyst in a bank, and your prospective client instead has a target monthly payment of AUD 4,000.

Calculate how the term of the loan will be affected if the monthly payment is changed to AUD 4,000.

Follow these steps to solve:

• Create cell E10 – call this cell ‘Delta’
• Enter formula in E10 to calculate the variance between target monthly payment and the calculated (i.e., $4,000 – $4,182.20 = $(182.20))
• Activate the Goal Seek dialogue box in the tools menu
• Select E10 as ‘set cell’
• Enter ‘to value’ as zero
• ‘By changing cell’ C7 which is loan term
• The Goal Seek solution in cell C7 is 240 months

This means that to achieve a monthly payment of AUD 4,000, the loan term needs to be extended from 240 months to 270 months. You can also solve for the loan amount (cell C5), instead of the term, to achieve the target.

Goal Seek example 2: Term loan

Now, let’s do another case where you are the analyst in a bank deciding the pricing for a term loan.

Refer to the calculation tab to learn how the repayment and the debt service coverage ratio (DSCR) are derived. The calculated average DSCR is 1.96x; however, the target average DSCR is 2.00x.

Calculate what is the interest margin to achieve the target average DSCR of 2.00x.

• Create cell E26 – call this cell ‘Delta’
• Enter formula in E26 to calculate the variance between target average DSCR and the calculated (i.e., 2.00x – 1.96x = 0.04x)
• Activate the Goal Seek dialogue box in the tools menu
• Select E26 as ‘set cell’
• Enter ‘to value’ to zero
• ‘By changing cell C22 which is interest margin
• The solution in cell C22 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 an interest margin of 2.49% p.a.

Following the same steps, you may also wish to change the facility limit (cell F15) instead:

• Select E25 as ‘set cell’
• Enter ‘to value’ as zero
• ‘By changing cell C14, which is the facility limit
• The Goal Seek solution in cell C14 is AUD 319.20 million

Corality Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

Some of the more popular courses that relate to this topic include:

• Best Practice Project Finance Modelling
• Advanced Project Finance Modelling