Sometimes persons would use scenarios to try out various values, especially if you know the result you want from the formula but are not sure what input the formula needs to get that result. For instance, you borrow some money from the bank but want to know what interest rate you will need to meet your loan goal. The Goal Seek feature in Microsoft Excel will help users to determine that easily.
How to use Goal Seek in Excel
Follow the steps below to use the Goal Seek feature in Excel:
- Launch Excel.
- Create a loan table with Loan Amount, Interest Rate, Term in months, and Monthly Payment.
- Use the PMT function to calculate the Monthly Payment
- Now, we will use Goal Seek.
- On the Data tab, click the What-if Analysis button in the Forecast group and select Goal Seek from its menu.
- A Goal Seek dialog box will open.
- In the Set Cell box, enter the reference for the cell that contains the formula you want to resolve.
- In the Value box, enter the formula result you want.
- In the By changing cell box, enter the reference for the cell you want to adjust.
- Then click Ok.
- A Goal Seek status box will open, showing the Target value and Current value results.
Let us see this in detail now.
Launch Excel.
Create a loan table with Loan Amount, Interest Rate, Term in months, and Monthly Payment.
Use the PMT function to calculate the Monthly Payment, for example, in the photo above, we have the formula =PMT(B2/12, B3, B1), but because there is no value in B2 (Interest Rate), Excel assumes a 0% interest rate and returns the value of ($500.00).
Now, we will use Goal Seek.
On the Data tab, click the What-if Analysis button in the Forecast group and select Goal Seek from its menu.
A Goal Seek dialog box will open.
In the Set Cell box, enter the reference for the cell that contains the formula you want to resolve. We will enter B4 the (Monthly Payment).
In the Value box, enter -800, which is the formula result you want. The reason why we placed a negative on 800 is that it represented a payment.
In the By changing cell box, enter the reference for the cell you want to adjust. We enter B2 that contains no value.
Then click Ok.
A Goal Seek status box will open, showing the Target value and Current value results. Then click OK.
You will notice that an Interest Rate of 7% is in the B2 cell, and in B3, the Monthly Payment is changed to ($800.00).
We hope this tutorial helps you understand how to use Goal Seek in Excel; if you have questions about the tutorial, let us know in the comments.
What is the main difference between Goal Seek and Variable data tables?
Both Goal Seek and Data tables are What-if Analysis features, but they have differences. The difference between the two is that Goal Seek is used to find the input for the right value you want, while the Data table is to see the result of multiple outputs at the same time.
Read: How to use Concatenate in Excel to improve data formatting
How is the Scenario manager different from the Goal Seek feature?
Scenario Manager is also a part of the What-if Analysis feature in Excel. The Scenario Manager allows users to create different groups of values or scenarios and switch between them, while Goal Seek is used to find the result for the value you want.
