Your videos are excellent: short, sweet, and to the point.”

Online Learner

Creating Opportunities For A Better Life.®

Excel 2010

Single page view print copy

Using What-If Analysis

To use Goal Seek (Example 2):

Suppose you need a loan to buy a new car. You already know you want a loan amount of $20,000, a 60-month term (the length of time it takes to pay off the loan), and a payment of no more than $400 per month. However, you're not sure yet what the interest rate is going to be.

In the image below, you can see that Interest Rate is left blank, and Payment is $333.33. That's because the payment is being calculated by a specialized function called the PMT (Payment) function, and $333.33 is what the monthly payment would be if there were no interest ($20,000 divided by 60 monthly payments).

Function calculating the monthly paymentFunction calculating the monthly payment

If we typed different values into the empty Interest Rate cell, we could eventually find the value that causes Payment to be $400, and that would be the highest interest rate that we could afford. However, Goal Seek can do this automatically by starting with the result and working backward.

To insert the PMT function:

  1. Select the cell where you want the function to be.
  2. From the Formula tab, select the Financial command.
    The Financial commandThe Financial command
  3. A drop-down menu will appear showing all finance-related functions. Scroll down and select the PMT function.
    Selecting the PMT functionSelecting the PMT function
  4. A dialog box will appear.
  5. Enter the desired values and/or cell references into the different fields. In this example, we're only using Rate, Nper (the number of payments), and Pv (the loan amount).
    Entering values into the necessary fieldsEntering values into the necessary fields
  6. Click OK. The result will appear in the selected cell. Note that this is not our final result, as we still don't know what the interest rate will be.
    The monthly payment, not including interestThe monthly payment, not including interest

To use Goal Seek to find the interest rate:

Now that we've added the PMT function, we can use Goal Seek to find the interest rate we will need.

  1. From the Data tab, click the What-If Analysis command.
  2. Select Goal Seek.
    Selecting Goal SeekSelecting Goal Seek
  3. A dialog box will appear containing three fields:
    • Set cell: This is the cell that will contain the desired result (in this case, the monthly payment). In this example, we will set it to B5 (it doesn't matter whether it's an absolute or relative reference).
    • To value: This is the desired result. We'll set it to -400. Since we're making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.
    • By changing cell: This is the cell where Goal Seek will place its answer (in this case, the interest rate). We'll set it to B4.
    Entering values into the Goal Seek fieldsEntering values into the Goal Seek fields
  4. When you're done, click OK. The dialog box will tell you whether or not Goal Seek was able to find a solution. In this example, the solution is 7.42%, and it has been placed in cell B4. This tells us that a 7.42% interest rate will give us a $400-per-month payment on a $20,000 loan that is paid off over five years, or 60 months.
    Solution found by Goal SeekSolution found by Goal Seek
1 2 3 4 5