Data Analysis [Part 4 of 5] – Goal Seek in Excel

This is the fourth article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use Goal Seek in Excel.

Download Example File

Watch Video – Goal Seek in Excel

Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.

For example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments. [Monthly payment is in red and in round brackets as it is an outflow].

I use the following PMT formula in cell B4 to calculate the Monthly payment:

=PMT(B2/12,B3,B1)

Now, I want to calculate the loan amount so that my monthly payment is  $1,000. I can try and use some trial and error by putting random values in Loan amount, but there is a better way to do this – using Goal Seek in Excel.

Using Goal Seek in Excel

• Go to Data –> Data Tools –> What-If Analysis –> Goal Seek.
• In the Goal Seek Dialogue Box, use the following details:
  • Set Cell: B4 (this is the cell with the goal/target).
  • To Value: -1,000 (this is the goal value. Its negative here as it is an outflow).
  • By Changing Cell: B1 (this is the loan amount that we want to change to achieve the goal value).
• Click OK. This will open the Goal Seek Status dialogue box.
  • Goal Seek Status dialogue box will inform you when a solution is found. If you wish to accept the solution, click on the OK button, and it will change the cell values. If you do not want to accept the solution, click on Cancel.
  • It may happen that the Goal Seek is not able to find a solution. It will show you the relevant prompt in that case.
• Once you accept the solution, it will be reflected in the cells.

Try it yourself..Download the file

You May Also Like the Following Excel Tutorials:

• Calculating Standard Deviation in Excel.
• Making Histogram in Excel.
• Calculating Weighted Average in Excel.
• Calculating CAGR in Excel.
• How to Get Descriptive Statistics in Excel?