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", "Bisection" and "Simple". 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. Only used in the Bisection method. Minimum value of the variable to be adjusted.

• Max (optional)
  Numerical value. Only used 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 1: Calculation of annuities with Newton or Bissection 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. 

Example 2: Stop condition

If you want to solve the equation with a less restrictive criteria, you can use the "Simple" method and the StopCondition parameter. The StopCondition is a less restrictive criteria as you can use inequalities such as ">=0" or ">100". However the algorithm is quite simplistic and a solution will not always be found. If the criterion StopCondition is not met for the current iteration, a new iteration is launched and the value which should be adjusted is then replaced by the value of GoalCell for the previous iteration.

In the following example, the initial value is 100. The stop condition is "<0". The goal is to find x so that f(x) < 0. Mind returns x≈ 0.4234. Let us see in details how Mind calculated the value x.

1) On the first iteration x is equal to the initial value, that is 100. Because log(100)≈4,61, which is positive, a new iteration begins.

2) x is now equal to the last calculated value of the function log, that is log(100). Mind calculates log(log(100)) which is approximately 1.52.

3) x is now equal to the last calculated value of the function log, that is log(log(100)). Mind calculates log³(100) ≈ 0.42.

4) x is now equal to the last calculated value of the function log, that is log³(100). Mind calculates log⁴(100) ≈ -0.85, which is negative. The algorithm stops there and the final value of x is log³(100). It is an approximate solution which depends on the choice of the initial value.

In Mind, the cell with MM_GOALSEEK under column x will directly return the value 0.4234227.