How to Use Excel Goal Seek for Reverse Engineering Formulas

Microsoft Excel is traditionally used to move forward through a problem: you input the raw data (e.g., unit price, quantity sold, tax rate), write a formula, and Excel calculates the final result (total profit). But what if you know the exact result you want, and you need to figure out the raw data required to get there? For example, if you know you need exactly $50,000 in profit to secure a bonus, how many units do you need to sell to hit that exact number? Instead of manually changing the “quantity sold” cell over and over again—guessing until the profit cell hits $50,000—you can use Excel’s built-in reverse engineering tool: Goal Seek.

Step 1: Set Up the Formula

Goal Seek requires a working formula to manipulate. It cannot guess numbers out of thin air; it must understand the mathematical relationship between your variables.

Imagine a simple scenario: you are calculating a monthly mortgage payment.

• Cell B1 (Loan Amount): $300,000
• Cell B2 (Interest Rate): 5%
• Cell B3 (Term in Months): 360
• Cell B4 (Monthly Payment): The formula here is =PMT(B2/12, B3, B1). This results in a monthly payment of roughly $1,610.

Now, assume you cannot afford $1,610 a month. Your absolute maximum budget is $1,300 a month. You want to know exactly how much house you can afford (the Loan Amount) to get the payment down to exactly $1,300.

Step 2: Launch Goal Seek

1. Navigate to the Data tab on the Excel ribbon.
2. In the “Forecast” group, click the What-If Analysis dropdown button.
3. Select Goal Seek…

A small, floating dialog box will appear with three simple fields.

Step 3: Define the Parameters

You must fill in the three fields to instruct Excel on how to solve the equation.

1. Set cell: This is the cell containing the formula (the result you want to change). In our example, click on Cell B4 (the Monthly Payment).
2. To value: This is the magic number you are trying to reach. Type -1300 (Mortgage payments are technically negative numbers in financial formulas). You must type a hard number here; you cannot reference another cell.
3. By changing cell: This is the raw data variable you want Excel to manipulate to achieve the goal. Click on Cell B1 (the Loan Amount).

Step 4: Execute the Calculation

Click OK.

Excel will instantly run through hundreds of calculations in the background, rapidly changing the number in the Loan Amount cell until the Monthly Payment formula outputs exactly -$1,300. A status box will pop up announcing that Goal Seek found a solution.

Look at your spreadsheet. Cell B1 has been permanently changed from $300,000 to approximately $242,168. You now know precisely what your budget is.

Limitations of Goal Seek

While Goal Seek is incredibly fast and useful, it is important to understand its two main limitations:

1. It only changes one variable: Goal Seek can only manipulate a single cell (e.g., it can change the Loan Amount, or the Interest Rate, but not both simultaneously). If you need to optimize multiple variables at the same time to hit a target, you must use the more advanced Solver add-in.
2. It requires a linear path: If your formula involves complex IF statements or volatile functions that cause the result to jump erratically rather than move in a smooth mathematical curve, Goal Seek may fail to find an answer, returning a “May not have found a solution” error.

For standard financial forecasting, pricing models, and grading curves, Goal Seek is the fastest way to get the answers you need without doing the algebra yourself.