Background Information
What-If Analysis is the label for a collection of tools to analyze and predict values. An especially helpful tool in this collection is Goal Seek, which allows you to determine what value is necessary to achieve a specific result in a formula or function.
Goal Seek
If you know the result you want a formula to produce, the Goal Seek feature can work backwards to determine how to achieve the result. The example in the attached pdf shows how to use Goal Seek to calculate the maximum amount a person can borrow (cell C2) based on a monthly payment that they specify (cell C5). This is determined based on a set loan term (cell C3) and set annual interest rate (cell C4).
- From the Data Tab, click the What-If Analysis button, then click Goal Seek. This will open the Goal Seek dialogue box.
- In the Set cell field, enter the cell address that contains the value to be calculated (this cell must include a formula/function).
- In the To value field, type the desired number that should appear in the Set cell address.
- In the By changing cell field, enter the absolute cell address of the value which should be changed to achieve the number listed in the To value field.
- Click OK. This will open the Goal Seek Status dialogue box.
- Click OK to accept the change.
- Click Cancel to display the original values.
Review a Sample Exercise in the attached pdf.
- Assuming the Amount Borrowed and Term in Months stays the same, how would you use the Goal Seek function to determine the required Interest Rate to achieve a Monthly Payment of $1,500.00?
- Interest Rate: ______________________
To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.