Using Goal Seek to find the input for a required output!

There might be several instances where you might want to find out what is the value the input should take achieve the desired output. We’ll use Goal Seek to find that. Sometimes it can be as simple as “What should the value of a number be in a series of numbers whose sum should be the desired result say 1000? “Or it can be as complicated as “What should be the number of months to repay a loan of a certain amount say $20,000 with a certain rate of interest (say 18%) given a certain EMI (Easy Monthly Installment) as the goal?”

This problem can be solved by reverse calculating the required input from the desired output. But, this type of calculation is simple and easy to perform in the cases like the first example mentioned above. But, it gets trickier with situations like the second one. In such cases, Goal Seek of Excel comes in handy. To get a better understanding of Goal seek, let’s try out both the examples.

Example 1: Goal seek to achieve certain Sum by changing one of the inputs

  1. Open Excel and make sure you enter the data as shown in the image below and enter the SUM formula in C2 to add the given set of numbers
  2. As discussed earlier, let’s try to check what should be the value of the first number to make the SUM as 1000
  3. Goal Seek is an option under the What-If Analysis
  4. Go to Data>What-If Analysis>Goal Seek
  5. In the pop-up window, enter the following values in the respective sections. Note that first and third parameters and cell references.
    1. Set Cell: C2
    2. To Value: 1000
    3. By changing cell: A2
  6. Essentially, we are telling Excel to change the value in cell A2 to make the value of SUM the value in C2 as 1000
  7. Click OK and the calculation of changes begin
  8. Once completed, there is another pop-up as shown below:
  9. You can click OK to accept the changed values or click Cancel to go back and you can repeat the process to try out different Goal

Now that you have understood the functioning of Goal Seek with a simple example. We shall go to the second example which is slightly advanced compared to this one.

Example 2: Goal seek to find the number of months to repay a Loan

  1. Since we are trying to figure out what will be the number of months to repay a certain amount of loan ($ 20,000) at 18 Rate of Interest, please recreate the information as shown in the image
  2. You’ll notice that B1:B3 are manually entered data, whereas B4 has a formula i.e. PMT formula used to calculate the EMI given the loan amount, No. of months and Rate of Interest
  3. The syntax of PMT function is as follows:

    PMT(rate, nper, pv, [fv], [type])

    • Rate: The interest rate for the loan (per year)
    • Nper: The total number of payments for the loan
    • Pv: Required. The principal amount. In case of Loans enter a negative value
    • Fv: Optional. The future value or a cash balance you want to attain after the last payment is made. If FV is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0
    • Type: Optional. The number 0 (zero) or 1 and indicates when payments are due
  4. Now let’s say we want to check what will be the number of months if we are willing to pay only $ 605 as the EMI
  5. Use Steps 4 and 5 from the previous section to launch Goal Seek. The Goal is 605 for cell B4 and Cell to change is B2
  6. Click OK and the calculation of changes begin
  7. Once completed, there is another pop-up as shown below:
  8. You can click OK to accept the changed values or click Cancel to go back and you can repeat the process to try out different Goal

Now you know how to use Goal Seek! You can use the following Excel Workbook to follow all the steps:

Download Workbook

Please share

Did you find this useful?  Why not share the love and tell your friends on the different social media platforms.

 

Join the newsletter & Get your CHEAT SHEET!

Keyboard shortcuts ebook cover %281%29

Enter your name and email to get our Excel Shortcuts cheat sheet and Join the Newsletter!

Shreyas M

Shreyas M

I'm a Senior Business Analyst by profession. Working with Excel is my passion. I've been working on Excel since 2011, it has been a great learning experience for me so far. I would like to share my knowledge with fellow Excel users!
Shreyas M
By |2017-10-27T02:53:57+00:00June 16th, 2017|Uncategorized|0 Comments

About the Author:

I'm a Senior Business Analyst by profession. Working with Excel is my passion. I've been working on Excel since 2011, it has been a great learning experience for me so far. I would like to share my knowledge with fellow Excel users!

Leave A Comment

Send this to a friend