Using Goal Seek in Excel

For planning new projects

By

Updated on February 23, 2020

Manager in Warehouse — stevecoleimages / Getty Images

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 Repayment
Cell D2: Rate
Cell D3: # of Payments
Cell D4: Principal
Cell D5: Payment
Cell E2: 6%
Cell E3: 60
Cell E4: 225,000
Cell 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.