- Excel Book Excerpt
Excel Back into an Answer Using Goal Seek
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I’ve determined that I want to obtain a 60-month loan for a car. The interest rate is 5.25%. I want to find out what loan amount would result in a $425 monthly payment. Currently, the payment for a $25,995 car is too high at $493 as shown previously in Figure 330.
Strategy: Although you could use the PV function to calculate the price of the car, it is easier to use the Goal Seek command:
1. Select Data, What-if Analysis, Goal Seek. This will bring up the Goal Seek dialog.
2. Indicate that you want to set cell B5 to $425 by changing cell B1.
Figure 332 Back into an answer.
In a simple case like this one, Goal Seek will almost always succeed. Excel considers different input values until it finds your solution. Within a second, it will report back that it found the correct input cell value.
Figure 333 Excel finds the price to yield the desired payment.
3. To accept the solution, click OK. To revert to the original value, click Cancel.
Results: Thanks to Goal Seek, you find that you can afford to borrow $22,384.93.
Additional Details: The formulas are still live after you use Goal Seek. You can continue to change terms, rates, and prices to calculate new payments.
Gotcha: When there is not a linear relationship between the two cells, Goal Seek may fail to find a solution.
For more resources for Microsoft Excel: