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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show The Difference From Previous Years With Excel... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you ...
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 2013Click to see tutorial on how to install Power Query in Excel 2010In...
Lock & Protect Formula Cells If you have a workbook with lots of formulas and you want to protect those formulas from being amended by other people who share your workbook, then you can!You need to follow these steps:1. Press the Go To Special shortcut CTRL+G2. Select the Constants box and press ...
What Microsoft Excel Version Do I Have? We know that Microsoft Excel has different features across different versions and there are several Excel version, like Excel 2003, 2007, 2010, 2013 and 2016!So whenever I use Microsoft Excel I want to know right away which Excel Version I am using.  And boy, do I get confuse...