How to Use Goal Seek in Excel: A Guide with Real Examples

How Goal Seek works

Goal Seek is used when you know the result you want from a formula but are still determining the input value needed to achieve it. You work backward by setting the desired outcome and letting Excel adjust the input variable to find the solution.

Common applications of Goal Seek

Though Goal Seek is used in different fields or domains, the most common uses are in financial modeling and academic scenarios. Some of these applications are:

• Budget Forecasts: Goal Seek helps determine how much savings or investment is needed to reach a financial goal. For example, if you want to save a certain amount by the end of the year, Goal Seek can calculate the monthly savings required to meet that goal.
• Loan Payments: It can calculate the loan amount you can afford based on a fixed monthly payment. If you have a specific amount you can pay monthly, Goal Seek can adjust the loan principal to see how much you can borrow.
• Break-Even Analysis: Businesses can use Goal Seek to find the sales volume needed to cover costs and reach a break-even point. Goal Seek can adjust the sales quantity or price by setting the profit to zero to determine the break-even point.
• Grade Calculations: Students and educators can use Goal Seek to determine the grade needed on a final exam to achieve a desired course grade. For example, if a student wants to know what score they need on the final exam to secure an overall grade of 85%, Goal Seek can calculate it based on the current grades and the weight of the final exam.
• GPA Targets: Goal Seek can also determine the grades needed in upcoming courses to reach or maintain a specific GPA. For instance, if a student wants to raise their GPA to a certain level, Goal Seek can determine the grades required in future courses to meet that target.

Let’s now explore real examples in Microsoft Excel.

How to Use Goal Seek in Excel

Applying Goal Seek for your data models primarily involves three repeatable steps.

Step 1: Setting up your data

Begin by setting up your data and formulas in a way that Excel can interpret them. Ensure the formula you want to solve is correctly linked to the input cell, which will be adjusted.

A sample data for Goal Seek calculation. Image by Author.

According to the formula in the image above, since Result C depends on Input A and Input B, we can change either Input A or B.

Step 2: Running Goal Seek

First, select the cell with the formula whose result you want to adjust (say, C2). Go to the Data tab, click What-If Analysis, and choose Goal Seek.

Selecting Goal Seek in Excel. Image by Author.

In the Goal Seek dialog, specify the cell with the formula (Set cell), the desired result (To value), and the input cell to be changed (By changing cell).

Goal Seek dialog box. Image by Author.

Click OK to allow Excel to find the appropriate input value.

Goal Seek final step. Image by Author.

Alternatively, you can click Cancel to void the Goal Seek operation if you want to keep the original values. This is common when we’re unhappy with the change and wish to run a different Goal Seek operation next.

Step 3: Analyzing the result 

After running Goal Seek, Excel automatically adjusts the selected input cell to reach the target output. The updated value in the input cell can now be analyzed to understand the outcome of your model.

Goal Seek result. Image by Author.

In the image above, by changing the Input B to 200 from 100, we have achieved our target Result C as 1000. These three steps form the standard process for using Goal Seek across different data models. 

Let’s explore how these steps apply to real-world problem-solving. These examples demonstrate how Goal Seek can effectively solve common financial problems, such as determining loan payments or achieving sales targets.

Excel Goal Seek Examples

Let's take a look at a couple Excel Goal Seek examples that you might encounter in your work. 

Example 1: Loan payment calculation

Imagine you’re planning to buy a house and want to determine the maximum loan amount you can afford with a monthly payment of $1,000. Based on your research on various banks, you have an interest rate and loan term in mind, but you need to figure out the loan principal to request. You can use Goal Seek to solve this.

Step 1: Setting up your data 

Open a new Excel worksheet and create the setup, as shown in the image below. 

Loan payment dataset. Image by Author.

Enter your researched interest rate (say, 5%) in cell A2, the loan term (say, 30 years) in cell B2, and an initial estimate for the loan amount (say, $200,000) in cell C2. In cell D2, enter the formula for calculating the monthly payment using the PMT function:

=PMT(B1/12, B2*12, -B3)

This formula divides the annual interest rate by 12 (for monthly interest), multiplies the loan term by 12 (for the total number of payments), and uses the negative loan amount to calculate the monthly payment.

Step 2: Running Goal Seek 

Our goal is to find the loan amount that would make the monthly payment of $1000. In other words, we will modify cell C2 so that D2 is set to $1000. As learned earlier, we will invoke Goal Seek within the What-If Analysis section under the Data tab to get the following dialog box.

Goal Seek options for the loan payment example. Image by Author.

In the Goal Seek dialog box, we enter the following:

• Set Cell: D2, the monthly payment cell.

• To Value: Enter 1000 (your desired monthly payment).

• By Changing Cell: Enter C2 (the loan amount).

Once we click OK, Excel will adjust the loan amount in cell C2 to determine how much you can borrow while keeping your monthly payment at $1,000.

Step 3: Interpreting the results 

The Goal Seek operation runs and stops at the below image:

Goal Seek result for the loan payment example. Image by Author.

Once we click OK, Excel will show the adjusted loan amount in cell C2. This is the maximum loan principal you can afford with the given interest rate, loan term, and monthly payment.

The adjusted loan amount is $186,282. We can afford this maximum loan amount with a $1,000 monthly payment. Feel free to experiment with different numbers and run the Goal Seek operation multiple times.

Example 2: Achieving a revenue target

Suppose you’re a sales manager aiming to achieve a revenue target of $500,000 for a specific product. You already know the product's price per unit but are still determining how many units need to be sold to reach this revenue goal. Goal Seek can help you determine the exact number of units required.

Step 1: Setting up your data  

Open a new worksheet and enter the data, as shown in the image below. 

Sales target dataset. Image by Author.

In cell A2, we enter the actual price per unit and an estimate for the number of units in cell B2. We enter the following formula for calculating total revenue in cell C2:

=A2 * B2

This formula multiplies the price per unit by the number of units sold to calculate total revenue.

Step 2: Running Goal Seek  

Let’s invoke Goal Seek within the What-If Analysis section under the Data tab to get the following dialog box.

Goal Seek options for the sales target example. Image by Author.

In the Goal Seek dialog box, we enter the following:

• Set Cell: Type C2 (your total revenue cell).

• To Value: Enter 500000 (your desired revenue target).

• By Changing Cell: Enter B2 (the number of units sold).

Excel will adjust the number of units sold in cell B2 to determine the units you need to sell to reach $500,000 in revenue.

Step 3: Interpreting the Results  

The Goal Seek operation runs and stops at the below step:

Goal Seek results for the sales target example. Image by Author.

Once you click OK, Excel will show the adjusted number of units in cell B2. As a result, it shows 2,000 units — you need to sell 2,000 units at $250 each to meet your revenue target of $500,000. If the unit price or the revenue target changes later, you can rerun the Goal Seek operation with the updated values.

Common Errors While Using Goal Seek and How to Handle Them

If you review the two examples above, we follow the same steps in a particular fashion to obtain the results. However, a few common errors can throw off your calculations when using Goal Seek in Excel. Let’s have a look at those.

1. Excel Goal Seek may not have found a solution

One of the most frequent problems with Goal Seek is when it can’t find a solution that meets your target. This can happen in two scenarios:

• The solution doesn’t exist.
• The solution couldn’t converge in the steps defined.

Let’s understand these from the sales target example we saw earlier. Suppose we had mistakenly provided the unit price as $0 instead of $250. The data looks as follows:

Sales target dataset with error. Image by Author.

We will follow our usual Goal Seek steps to run the operation:

Goal Seek on the sales target dataset with error. Image by Author.

After we click OK, what happens next?

Goal Seek may not have found a solution error. Image by Author.

We see the result as “Goal Seek may not have found a solution.” This error occurred because the equation is not solvable. Before running a Goal Seek operation, it’s essential to check the formula and the input values to ensure it is solvable.

If you’re sure that the solution exists, then it could be that the Goal Seek operation couldn’t converge within the allocated 100 steps. To help Goal Seek converge to a solution:

1. Increasing the Max Iterations: A higher iteration value allows the Goal Seek operation to run longer. You can change this from File -> Options -> Formulas -> Max Iterations.
2. Start with a Better Estimate: If you can guess the range of the correct number of units, enter an initial rate within that range.

Checking if the equation is solvable and allowing it to converge within the defined steps will prevent the error of the solution not being found.

2. The cell must contain a value error

Goal Seek expects the cell we change to be a value and throws the “Cell must contain a value” error otherwise. This is a common occurrence when the value in the cell is derived from other cells through an equation.

Let’s understand the error using the same sales target example. Suppose the price per unit is calculated by dividing the total costs by total units. Our data setup would look like below:

Updated sales target dataset. Image by Author.

As we attempt to run Goal Seek, an error occurs when we select cell A2 for the By changing cell option.

The cell must contain a value error. Image by Author.

This occurred because Goal Seek cannot be run when the cell to be modified does not contain a value. Ensuring that the cell to be modified contains a value would prevent this error.

Conclusion

This article introduced you to a commonly used What-if Analysis tool — Goal Seek. Through multiple example scenarios, we learned how to use it in Excel in simple steps. We also understood the common errors faced during the implementation and how to avoid them.

I encourage you to experiment with Goal Seek in your data models to familiarize yourself with the tool and its applicability. If you’re interested in learning more about financial modeling in Excel using tools similar to Goal Seek, you may want to check out our beginner-friendly Financial Modelling in Excel course.