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

loan calculator

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

goal seek - set cell

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)

goal seek - to value

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

goal seek - by changing value

STEP 7: Press OK and Goal Seek will run and produce a result.  Press OK to keep the results or Cancel to discard

goal seek status

loan calculator end result




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn