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 Fig 332.
Strategy: Although you could use the PV function to calculate the price of the car, it is easier to use the Goal Seek command:
- Select Data, What-if Analysis, Goal Seek. This will bring up the Goal Seek dialog.
- Indicate that you want to set cell B5 to $425 by changing cell B1.
- 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.
- Excel finds the price to yield the desired payment.
- 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.