In Excel what if analysis lets you answer questions with data. With what if analysis Excel also lets you experiment with data.
To use Goal Seek (Example 2):
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.
To insert the PMT function:
- 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
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'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