
Advanced Project Finance Modelling
2 day duration Master the advanced modelling techniques required to drive the analysis of complex...
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.
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.
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.
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.
Follow these steps to solve:
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.
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.
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:
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:
2 day duration Master the advanced modelling techniques required to drive the analysis of complex...
This website uses cookies.
Some of these cookies are necessary, while others help us analyse our traffic, serve advertising and deliver customised experiences for you. For more information on the cookies we use, please refer to our Privacy Policy.
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.