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

Create a Named Range in Excel Whenever I work with spreadsheets, there's no escaping the fact that I have to work with a lot of ranges.Some of these ranges I have to reuse time and time again to create different calculations!Good thing there are Named Ranges in Excel!By using a Named Range, you ca...
Show The Percent of Difference From Previous Years...  I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer.One is using Formulas, but that will take time to set up and you are exposed to errors!The other method is t...
Autofill Formulas in an Excel Table One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste.When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column.As you add extra...
Turn Text To Values With Paste Special Values Many times you would have received data from your IT system which is formatted wrong!  Well a gazillion times if you work in a mid sized firm 🙂When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value....