In Excel what if analysis lets you answer questions with data. With what if analysis Excel also lets you experiment with data.

Let's say 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 **will be.

In the image below, you can see that **Interest Rate** is left blank and **Payment** is $333.33. This is 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 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**.

- Select the cell where you want the function to be.
- From the
**Formula**tab, select the**Financial**command.The Financial command - A drop-down menu will appear showing all financial-related functions. Scroll down and select the
**PMT**function.Selecting the PMT function - A dialog box will appear.
- 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 fields - Click
**OK**. The result will appear in the selected cell. Note that this is not our**final**result because we still don't know what the interest rate will be.The monthly payment, not including interest

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

- From the
**Data**tab, click the**What-If Analysis**command. - Select
**Goal Seek**.Selecting Goal Seek - 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**. Because 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 fields - When you're done, click
**OK**. The dialog box will tell you whether 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 Seek