If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you want to borrow money. You know how much money that you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal.
The following step-by-step example shows how Goal Seek can help you find the result that you are looking for.
Bemærk: Goal Seek works only with one variable input value. If you want to accept more than one input value, for example, both the loan amount and the monthly payment amount for a loan, use the Define and solve a problem by using Solver.
Step 1: Create an example workbook
Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.
Open a new, blank workbook.
In cell A1, type Loan Amount, in cell A2 type Term in Months, in cell A3 type Interest Rate, and then in cell A4 type Payment.
The next two steps will add the values.
In cell B1, type 1000000. This is the borrowed amount.
In cell B2, type 180. This is the number of months to pay off the loan.
Bemærk: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the sheet and specify the payment value at a later step, when you use Goal Seek.
In cell B4, type =PMT(B3/12,B2,B1).
This uses the PMT function to add the formula for which you have a goal. The formula refers to cells B1 and B2, which contain values that you specified in previous steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate. Because there is no value in cell B3, Excel assumes a 0% interest rate and, by using the values in the example, returns a payment of $555.56.
Step 2: Use Goal Seek to determine the interest rate
On the Data tab, under Analysis, click What-If, and then click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.
In the To value box, type the formula result that you want. In the example, this is -900. This number is negative because it represents a payment.
In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.
Bemærk: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.
Goal Seek runs and produces a result.
The value in cell B4 is the result of the formula =PMT(B3/12,B2,B1).
Goal seek to determine the interest rate in cell B3 based on the payment in cell B4.
The next two steps will format the target cell (B3) so that it displays the result as a percentage.
On the Home tab, under Number, click Increase Decimal or Decrease Decimal to set the number of decimal places.