If you have a formula and want to show a specific result, but you do not know what input values to change within the formula, then Excel’s Goal Seek feature is the one for you.
Imagine you are calculating the payment terms on a loan.
Your PMT formula gives you an amount of $1,450 but you can only afford to repay $1,000. You can use Goal Seek to find out what Principal you can borrow based on your $1,000 budget.
STEP 1: Enter your 3 input variables that you will need to use for your PMT formula i.e. Interest Rate of 3.50%, Term of 240 months & Principal of $250,000
STEP 2: Enter the PMT function in cell C8 =PMT(Interest Rate/12, Term, Principal) which will give you a monthly payment amount of -$1,450
STEP 3: Select the cell C8 and go to Data > What If Analysis > Goal Seek
STEP 4: SET CELL: Enter the reference for the cell that contains the formula that you want to resolve. In our example, this reference is cell C8
STEP 5: TO VALUE: Type the formula result that you want. In our example, we want the payment to be -$1,000 (Note that this number is negative because it represents a payment)
STEP 6: BY CHANGING CELL: Enter the reference for the cell that contains the input value that you want to adjust i.e. One of our 3 variables (Interest Rate, Principal & Term). In our example, this reference is cell C7 for the Principal
STEP 7: Press OK and Goal Seek will run and produce a result. Press OK to keep the results or Cancel to discard