MM_GOALSEEK enables a specified cell to reach a target value by adjusting the value of the cell which contains the MM_GOALSEEK function. Instead of using an explicit formula, the function uses root-finding algorithms.

Syntax

=MM_GOALSEEK(GoalCell, GoalValue, [Tolerance], [Depth], ["MethodType"], [Min], [Max], [InitialValue, [StopCondition])

• GoalCell
  Cell. The cell that needs to reach the value GoalValue. It has to depend on the current cell to be adjusted. 

• GoalValue
  Numerical value. Only used in Newton and Bisection methods. The value the GoalCell needs to reach.

• Tolerance
  Numerical value. The value of Tolerance is the maximum value accepted for the difference between the values of GoalCell and GoalValue. Once the difference is less or equal to the value of Tolerance, the root-finding algorithm stops (if it has not already been stopped because the maximum number of iterations was reached). 

• Depth (optional)
  Integer. Maximum number of iterations for the root-finding algorithm used.

• "MethodType" (optional)
  String. The root-finding algorithm used. The following methods are supported: "Newton" and "Bisection". Newton method is chosen by default.

  In the hypothesis that a root can be found, Newton and Bisection methods algorithm stop when GoalCell reaches the value of GoalValue whereas Simple method stops when GoalCell meets the criterion StopCondition. If the criterion is not met, then another iteration of the algorithm begins with the new initial value of the current cell being the value of GoalCell for the previous iteration.

• Min (optional)
  Numerical value. Optional when using the Newton method but required in the Bisection method. Minimum value of the variable to be adjusted.

• Max (optional)
  Numerical value. Optional when using the Newton method but required in the Bisection method. Maximum value of the variable to be adjusted.

• InitialValue (optional)
  Numerical value. Starting value of the variable to be adjusted in the root-finding algorithm. It is basically guesses on the solution of the algorithm. If the sequence of numbers calculated by the algorithm does not converge, a different starting value can change the outcome of the algorithm. It is also especially useful for functions that have more than one solution. Input InitialValue to select the solution in the appropriate interval.

• StopCondition (optional)

  Only used in Simple method. Criterion that the GoalCell must meet to stop the root-finding algorithm. Ex: ">=0" or ">100".

Warning: If one optional parameter is entered into the function, all parameters to the left have to be input as well. Indeed, Mind does not accept empty parameters. 

Remarks

• Unlike the Goal Seek process in Excel, the function is directly embedded in the cell which calls the MM_GOALSEEK and is calculated during the model run. 

• Depending on the complexity of the function, the algorithm may fail to find a solution. Changing InitialValue may solve the issue. 

Example : Calculation of annuities with Newton or Bisection methods

In cells B5 and C5, the present value is calculated using the Excel function PV(rate,Nper,Pmt). With Pmt equal to -€150.00, the present value is equal to €1407.76.

You wish to have a present value of €1500 and wonder what is the corresponding value of Pmt (leaving aside Rate and Nper). To this end, you can use MM_GOALSEEK function in the cell which contains the input value you want to adjust, in our case Pmt. See the highlighted cell. 

=MM_GOALSEEK(C5, 1500), with C5 the present value with adjustment.

In Excel, as this causes a circular reference, it will return 0 as a default value in the workbook.

Mind, on the other hand, returns -€159.83 after launching a run. In fact, you find the same result using the built-in Excel function PMT. When possible, it is best to either use formulas or existing functions in order to optimize run-time. For more complex calculations, use MM_GOALSEEK.