MrExcel.com - Your One Stop for Excel Tips & Solutions

Back into an Answer Using Goal Seek

February 08, 2022 - by

Back into an Answer Using Goal Seek

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 319.

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.

The Goal Seek dialog says to set B5 to $425 by changing the Price in B1.
Figure 321. 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.

Goal Seek finds that a price of $22384.93 will get you to a $425 monthly payment. You can either click OK to accept the new values or Cancel to return to the original model.
Figure 322. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by 愚木混株 cdd20 on Unsplash