Excel 2010

Using What-If Analysis

2 of 5

What-If Analysis

Launch video!Watch the video (4:08). Need help?

In many worksheets, there may be some cells whose values are unknown, or you may just want to change certain cells to see what the outcome is. What-if analysis is perfect for these situations. It allows you to experiment and answer questions with your data, even when the data is incomplete.

Watch the video to learn about using what-if analysis.

 

Goal Seek

Goal Seek is a type of what-if analysis that is useful if you know the desired result, but need to find the input value that will give you that result. For example, suppose you need a loan to buy a new car. You already know that 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 a 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.

You'll need to understand how functions work before you use what-if analysis. If you want, you can review functions in our Working with Basic Functions lesson.

To Insert the Payment 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