Some of the most useful features in Excel, are hidden under the data ribbon button, What-If analysis. In this tutorial, we will try to explain and use the Goal Seek feature.
Practice file:
Transcript
This tool allows us to see how one data item in a formula, impacts another.
In our example, we are planning on buying a new car and have our eyes on a certain model with a cost of $20000.
Unfortunately, we don’t have the money and have to turn to a bank for a loan.
The initial offer from a banker is shown in the current spreadsheet.
The rate is 6% per year for 5 years of monthly payments (60 payments in total).
As we can see, the monthly payment needed to fill the requirement is about $386.
This happens to be over our budget and we want to explore more possibilities.
We feel that we can’t pay more than $300 per month, but we can handle a bigger duration of the loan.
Here is where goal seek kicks in. We choose from the data ribbon the what-if analysis button, and then the goal seek tool.
In “set cell”, we choose the cell we want to set to a new value, in our case E7, the value we want to set it to, -300 in our example, and the cell we can change to achieve our goal, C7.
We press OK and the result is a bit more than 81 months.
That sounds a lot. We click on OK to keep the result and decide, that 80 months in total are the top of the duration we can accept.
We change the Term to 80 and the payment increases to $304 approximately.
We click on goal seek again, to change the target payment to -300, but this time the goal is to alter the annual rate of the loan in order to achieve it.
We press OK. So, if we can find a bank that can provide 5.6% or less, in 80 terms or less, for a $20000 loan, with $300 monthly payments, we are within our goals.
Now we can be off to the banks and negotiate a loan, having a more precise idea, of what to expect and what to ask for.
Here are some examples of goal seek
Use the Goal seek, to calculate the amount of the monthly payments, so that the period of the loan becomes 10 years.
In the worksheet there is information about a loan. Use the Goal Seek to calculate, in how many months the loan would be repaid if the required amount of monthly installments were 900 dollars.