What is Goal Seek

Usually, we have a function to calculate a result (output) based on input values to the function. The process of finding what an input value should be to achieve the result (output) we want is called Goal Seek.

How to use the Goal Seek feature in Excel

On an Excel sheet, type in the following details:

  • In cell A1 type Loan Amount; in cell B1 type 100,000
  • In cell A2 type Term in Months; in cell B2 type 100
  • In cell A3 type Interest Rate; in cell B3 type 5%
  • In cell A4 type Payment; in cell B4 type =PMT(B3/12,B2,B1)

Press Enter and you should have the result as below (you may have decimal values and you can change the cell format to display 0 decimal place)

Please be noted that the value in cell B4 is negative as it represents a payment. It means that each month you need to pay $1,225 during the course of 100 months.

What about you want to pay only $1,000 per month and would like to know how long it takes to pay off the loan (Term in Months). In this case, you can use the Goal Seek feature in Excel. Follow the steps below:

  • Click on Data -> What-If Analysis -> Goal Seek…
  • Type B4 for Set cell, -1000 for To value, and B2 for By changing cell
  • Click on OK

The Goal Seek feature finds that you need 130 months to pay off the loan.

Goal Seek using VBA

What about when you are a bank staff helping out many customers per day with home loans. You may need to run the Goal Seek feature so many times a day. This this case, having a VBA button that allows you to achieve Goal Seek would be very useful.

You can follow this tutorial to learn how to create a button that runs a VBA function.

Create a button and name it e.g. “Goal Seek”. Below the VBA function for this button.

Sub findTerm()
    Range("B4").GoalSeek Goal:=-1000, ChangingCell:=Range("B2")
End Sub

Click on the button to run this function to find out “Term in Months”. This just only cost you one click to achieve the same thing as in the previous section.

What about each customer wants a different payment amount. Well, you could go to the VBA code and change the Goal to a different amount. However, this is inconvenient. You can modify this function asking for user input of the Goal value each time the button is clicked. Below is the content of the function to achieve this.

Sub findTerm()
    Dim inputValue As Variant
    Dim payment As Double
    inputValue = InputBox("Enter the montly payment")
    
    If Not IsNumeric(inputValue) Then
        MsgBox "Invalid input data type! Please enter a number.", vbCritical
    Else
        payment = CDbl(inputValue)
        Range("B4").GoalSeek Goal:=-payment, ChangingCell:=Range("B2")
    End If
End Sub

To be more convenient for the user, this time this time let the user input a positive value and let the VBA code convert to a negative value representing the payment (Goal=-payment).

Run the button and try to input 2000:

Click on the OK button and see the value of cell B4 changes to -$2,000 and the value of cell B2 changes to 56.

References

  • Check out this article from Microsoft
  • Follow this tutorial for a VBA introduction