Using Goal Seek in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Using Goal Seek in Excel

For planning new projects

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on February 23, 2020 Tweet Share Email stevecoleimages / Getty Images Tweet Share Email MS Office Excel Word Powerpoint Outlook The Excel Goal Seek feature allows you to see what would happen if you alter the data used in a formula. With Goal Seek, you can compare different results to find out which one best suits your requirements. The instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Enter the Tutorial Data

This tutorial uses the PMT function to calculate the monthly payments for a loan. After the loan payments are calculated, Goal Seek is used to lower the monthly payment by altering the loan period. To follow along with the tutorial, enter the following data into the cells indicated: Cell D1: Loan RepaymentCell D2: RateCell D3: # of PaymentsCell D4: PrincipalCell D5: PaymentCell E2: 6%Cell E3: 60Cell E4: 225,000Cell E5: Leave this cell blank. Here's what the tutorial data looks like in the worksheet: Follow the steps below to implement the PMT function within your worksheet: Select cell E5 and type the following formula: =PMT(E2/12,E3,-E4) Press Enter. The value $4,349.88 appears in cell E5. This is the current monthly payment ​for the loan.​​

Alter the Monthly Payment Using Goal Seek

After you enter the PMT formula, use Goal Seek to peek into different data options. In this tutorial, you'll see how Goal Seek is used to show the change in the total number of payments to be made when the monthly payment is lowered. We'll reduce the monthly payment to $3000.00 to see the difference in the number of payments. Select the Data tab. Select What-If Analysis to open a drop-down list. Select Goal Seek. In the Goal Seek dialog box, place the cursor in the Set cell line. Select cell E5 in the worksheet. In the Goal Seek dialog box, place the cursor in the To value line. Type 3000. Place the cursor in the By changing cell line. Select cell E3 in the worksheet. Select OK. Goal Seek calculates the solution. When it finds one, the Goal Seek dialog box informs you that a solution has been found. Goal Seek shows that by lowering the monthly payment, the number of payments in cell E3 changes from 60 to 94.2355322. To accept this solution, select OK in the Goal Seek dialog box. To find a different solution, select Cancel in the Goal Seek dialog box. Goal Seek returns the value in cell E3 to 60. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine the ROUND and SUM Functions in Excel Use the Excel RIGHT Function to Extract Characters How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Round Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Subtract Dates in Excel Use Excel's EOMONTH Function to Add or Subtract Months How to Create an Excel Lookup Formula With Multiple Criteria Excel SUM and OFFSET Formula How to Use Excel's MROUND Function How to Nest Multiple IF Functions in Excel How to Use the Excel TRUNC Function How to Calculate Percentage in Excel Ignore Error Values When Finding the Average in Excel How to Create an Excel Left Lookup Formula Using VLOOKUP Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies