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.

DOWNLOAD EXCEL WORKBOOK

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

Excel Goal Seek

HELPFUL RESOURCE:

Bill_Jelen_Podcast_Banner_EXCEL

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Convert Values to Dates Using Flash Fill In Excel   Flash Fill in Excel is a new feature that was introduced in Excel 2013. Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column. One of the cool uses of Flash Fill is convert your values into Excel dates a...
Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values. The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry,...
Unpivot Data Using Excel Power Query Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013: Click to see tutorial on how to install Power Query in Excel 2013 Click to see tutorial on how to install Power Query in Excel 2010 In...
Clear a #REF! Error in Excel When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error. This means that the formula refers to a cell that isn’t valid. To get rid of this error message we have to select the cell(s) with this error, ...