For example, if we want to change our Monthly payment to OurBank (see screenshot above) to -$1,000, this is the value to enter in this box.Ħ. Next, in the To Value box, enter the result you wish to obtain. In our screenshot example above, if we wanted to change the Monthly payment amount in cell E4, this would be the cell reference to insert in this box.ĥ. Click in the Set Cell box and select the cell reference for the value you wish to change. Choose Goal Seek from the menu list to launch the Goal Seek dialog box.Ĥ. In the Data Tools group, click on What-If Analysis.ģ. We can use Goal Seek to change the Monthly payment to a different amount by having Excel automatically recalculate one of the other variables that we choose (Loan amount, Period of repayment or annual rate of interest).Ģ. This gives a monthly payment of $2,636.11. For example, Chocolates R Us Confectionary wishes to loan an amount of $56,000 from OurBank, pay it back over 24 months at an annual interest rate of 12%. This function calculates a monthly loan payment based on the loan amount, the number of repayments and interest rate. In this example, column E, Monthly payment is calculated by using the PMT function. For example, if you wish to solve a formula based on a result that you already have to see what variables the formula needs in order to return this result, you could use Goal Seek. This may sound like cheating the books (and indeed this is a great function should you be criminally minded), but Goal Seek does have many legit uses. It allows you to tell a financial sheet what you want and it changes numbers to ensure your goal is reached. Goal Seek is a target-orientated personality’s dream-function.
#Xlminer analysis toolpak goal seek free#
Test your Excel skills with the corresponding FREE Online Multiple Choice Use Solver, data tables, and Goal Seek to test multiple values and see how the results change. Free Excel Tutorial – What-If Analysis – Excel 2010 Training What-If Analysis Excel Tutorialįree Online Microsoft Excel Tutorial * Using Goal SeekĮver wonder what would happen if you changed a number in a formula? A What-if analysis lets you see how those changes will affect the outcome of the formula.