What's Up with Excel's Goal Seek Feature
Occasionally you may encounter formulas in Excel where getting to an answer entails some trial and error. For instance, let's say that you're planning to buy a new car. You know the interest rate that will be charged, and you know the term of the loan that you're seeking. You also know that you'd like your payment to be $250 per month. The one piece of information that you're missing here is how much you can borrow to have a payment of $250/month for 48 months at 3%. If you didn't know otherwise, you'd find yourself entering various guesses into your spreadsheet to eventually get the formula to return a payment of $250. However, instead you can use the Goal Seek feature to eliminate the guesswork.
Let's first build a worksheet that we can use for Goal Seek. Enter the words Interest, Term, Principal, and Payment into cells A1:A4 of a blank worksheet. Enter 3% in cell B1 and 48 in cell B2. We'll leave cell B3 blank for the moment. In cell B4 enter this formula: =-PMT(B1/12,B2,B3).
The PMT worksheet function in Excel enables us to solve for a loan payment amount. The function has three required arguments: rate, nper, and pv. Two other optional arguments are available for those with more complex payment calculation needs. In this case the rate argument corresponds to our interest rate. Excel requires that interest rates be on the same basis as the term, which we're expressing monthly, so we divided the interest rate by 12 in our formula. The nper argument reflects the term of the loan, in this case cell B2. The pv argument is short for present value, which is another way of referencing the amount of money being lent.
Although you could try manually entering different values into cell B3 for pv until eventually the formula in cell B4 returns $250, let's instead use the Goal Seek feature. Activate Excel's Data menu, click on the What-If Analysis command, and then choose Goal Seek. A dialog box will appear from which you'll provide the following:
- Set cell: This is the address of the cell that contains a formula that you're trying to solve for, in this case cell B4.
- To Value: This is the amount that you would like the formula specified as Set Cell to return, in this case $250.
- By Changing: This is the address of the cell that contains the input that you want Excel to solve for, in this case cell B3.
When you click OK in the Goal Seek dialog box, Excel will start at zero and rapidly enter different numbers into the By Changing cell until eventually the Set Cell is equal to the To Value. In the case of particularly complex spreadsheets Excel may report that it cannot find an answer, but generally Goal Seek is a way to quickly ascertain an answer when you're missing a single input required by a formula. In this case you'll find that you can borrow $11,294.67 at 3% for 48 months to have a $250/month loan payment. If this amount appears as a negative value then you omitted the minus sign before the PMT function, which always returns a negative value. By adding a minus sign in front of PMT it returns a positive number for the payment, which means the result we're solving for will be positive as well.
A constraint of Goal Seek is that it can only solve for a single missing input. If you have situations where you need to perform an optimization, break-even analysis, or solve for two or more missing values, Excel offers the Solver feature as a next step. Solver is far more complex than Goal Seek but is yet another means for extending Excel's functionality.