What If Analysis in Excel: Goal Seek — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
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.
Whether your challenge is summarizing totals as percentages, analyzing with formulas, or introducing interactivity, this program will pack your toolbox with features that will help you turn your plain Pivot Table into a data-crunching, information-revealing machine. Understand the capabilities (and limitations) of this incredibly important tool....Click here to find out more.