The Problem with Forward Math
Most of the work done in Microsoft Excel relies on forward math. You input a set of known variables (e.g., the cost of a product, the number of units sold, and the tax rate), and Excel calculates the final result (total revenue). But in the real world of business and finance, you often face the exact opposite problem.
You already know what the final result must be. For example, your boss tells you that the department must hit exactly $150,000 in revenue this quarter. You know the price of your product, but you do not know how many units you need to sell to hit that exact target.
The amateur approach is to guess. You type “500 units” into your spreadsheet, and the revenue formula outputs $135,000. You try “600 units,” and it outputs $162,000. You try “550 units,” and it outputs $148,500. This process of trial and error is frustrating and incredibly inefficient.
Excel has a built-in mathematical solver designed specifically to automate this exact scenario. It is called Goal Seek, and it performs reverse mathematical modeling in a fraction of a second.
Setting the Scene
To use Goal Seek, you must have a spreadsheet where variables are connected by a formula. Let’s use a simple loan scenario.
- Cell B1 (Loan Amount): $250,000
- Cell B2 (Interest Rate): 6%
- Cell B3 (Term in Years): 30
- Cell B5 (Monthly Payment): Contains the PMT formula based on the cells above. It currently reads $1,498.88.
The Scenario: You are buying a house, but your absolute maximum budget for a monthly payment is exactly $1,300. You cannot change the interest rate, and you cannot change the 30-year term. The only variable you can change is the Loan Amount (i.e., you need to buy a cheaper house). How much house can you afford?
Executing Goal Seek
Instead of manually lowering the Loan Amount in Cell B1 until Cell B5 eventually hits $1,300, we will let Excel do the math backwards.
Step 1: Launch the Tool
- Navigate to the Data tab on the Excel ribbon.
- In the “Forecast” group, click What-If Analysis.
- Select Goal Seek… from the dropdown menu.
A very small, simple dialogue box will appear with only three fields.
Step 2: Configure the Three Fields
You must fill in these fields in a very specific logical order.
- Set cell: This is the cell containing the formula you want to control. In our scenario, this is the Monthly Payment cell. Click inside the box, then click Cell B5.
- To value: This is your target number. This box does not accept cell references; you must type the raw number manually. Type 1300. (Note: If your PMT formula outputs a negative number to represent cash flow, you must type -1300).
- By changing cell: This is the single variable Excel is allowed to adjust to reach your target. In our scenario, this is the Loan Amount. Click inside the box, then click Cell B1.
Read the dialogue box back to yourself to ensure it makes logical sense: “Set cell B5 to value 1300 by changing cell B1.”
Step 3: Solve
Click OK.
A small window will pop up showing the calculation status. Behind that window, you will see the numbers in your spreadsheet rapidly flashing as Excel tests thousands of different combinations. Within a second, it will stop.
Cell B5 now reads exactly $1,300.00. Look up at Cell B1. Excel has calculated that to achieve that payment, your maximum Loan Amount can be exactly $216,831.02.
If you click OK on the Goal Seek status window, the new numbers will permanently overwrite your old numbers. If you click Cancel, the spreadsheet will instantly revert to the original $250,000 scenario.
Conclusion
Goal Seek is one of the most underutilized tools in Excel. Whenever you find yourself manually typing numbers into a cell and hitting Enter repeatedly just to see how close you can get to a target result, stop immediately. Goal Seek will calculate the exact, mathematically perfect answer instantly.