What If Analysis in Excel: Goal Seek
You’ve plugged in the values for a PMT (payment) function to determine how much the payment will be only to find that the payment is a bit too much for your budget. You could keep plugging in numbers until you get the right answer, find out the exact answer in a couple of clicks with one of the “What If” analysis tools in Excel. In this example, we’ve calculated a loan payment based on a loan amount of $15,000, an annual interest rate of 5% and a 5 year loan term. The formula for the payment is =PMT(rate/ 12, term in months,- loan amt). We need to get to a $250 a month payment. Here’s how.
- From the Data tab, in the Data Tools group, choose the What If Analysis drop down button and select Goal Seek.
- In the Goal Seek dialog box, type 250 in the To value: field, and with your cursor in the By changing cell: field, select the Loan Amount value. Click the OK button.
- Examine the results. It’s telling us that we can borrow $13,247 at the same rate and terms and meet the need to keep our payment at $250. If that loan amount looks good, click OK. If not, click Cancel and try perhaps, setting By changing cell: to the term or interest rate if could vary as well.