Excel Goal Seek Feature
Goal seeking is the process of calculating backward to find out an input that would result in a specific output. This is also known as What-If analysis. Goal Seek function in Excel is a powerful feature that allows you to change the data used in a formula and find out how the changes affect the result. Thus, you can compare different results using Goal Seek and finalize the one that best suits your needs. Instead of following trial and improvement method, Goal Seek function follows a more logical approach.
Let’s look at the data given below in order to understand Goal Seek function.
Here, a person needs to pay $1891.19 every month for 12 months if the loan amount is $20,000 and interest rate is 24%. Suppose the person is capable of paying $2730.2 every month and we need to find out in how many months he can complete the loan. So, let’s analyze how the data will be changed if we change the monthly payment to 2730.2.
Go to Data -> Data Tools -> What-If Analysis -> Goal Seek which will open a new Goal Seek dialog box. Click on the text box next to “Set cell:” and then click on cell E1 as we are going to change the monthly payment for this loan and analyze the results. Next, enter the value 2730.2 in the “To value:” textbox because we wish to increase the monthly installment to 2730.2. Finally, click on the text box next to “By changing cell:” and click on the cell B2 because we want to find out the number of payments to be made so that monthly payment becomes 2730.2. After entering the values, your Goal Seek dialog box should appear as follows.
Click OK and you will get a dialog box informing you the solution that has been found out. From the solution you can understand if you pay $2730.2 for 8 months, you can complete your loan. If you click OK, your formula and data will be changed accordingly. Otherwise, you can click Cancel button if you don’t want to implement the solution found out by Goal Seek function. Now, if you wish to change the interest rate instead of number of payments, then click on cell B3 after selecting the textbox next to By Changing Cell:.
Suppose, you want to calculate the loan amount you can afford if you are ready to pay $2500 monthly for 12 months at an interest rate of 24%, then your Goal Seek function will change as follows.
Once you click OK, Goal Seek will display a solution as given below.
Here, you can find that you can afford an amount of $26,438.35 as per the specified criteria. If you click OK, your worksheet will be changed as follows.
In short, Goal Seek function can be considered as opposite to formulas. When formulas help us to find out the output by providing inputs, Goal Seek helps to decide what input should be given in order to get a specific output. You can use Goal Seek function to plan your budget, estimate your monthly savings in order to meet your retirement goals, find the return rate of an investment and so on.
Read More about Excel here