Performs goal seek calculations to return the target rate for a defined target amount of profit sharing taking into accounts guaranteed rates. 

Syntax

=MM_PROFITSHARING_OPTI(RangeTX, RangePM, PB, [Tolerance])

• RangeTX
  Range that contains the guaranteed rates for all contracts. The range must be a column.

• RangePM
  Range that contains the mathematical reserves net of profit sharing. The range must be a column.

• PB
  The total amont of profit sharing to target.

• Tolerance (optional)
  0.01 recommended.
  The value of Tolerance is the maximum value accepted for the difference between the value of PB and the calculated amount of profit sharing . Once the difference is less or equal to the value of Tolerance, the root-finding algorithm stops.

Remark

If RangeTX and RangePM are not defined as columns, the function returns incorrect results.

Example

Let us take into consideration three contracts. 

Under "Rate", the formula: =MM_PROFITSHARING_OPTI(A2:A4,B2:B4,C2,0.01) returns 3%, which is the rate corresponding to the target PB value equal to 12.

Indeed, under the column "Ind PB", the total amount of profit sharing matches with the target PB when using a rate of 3%. 

The following formula was used for IndPB: 

IndPB = max(RangeTX, Rate).RangePM

 NB:

If your target value is lower than the minimum guaranteed value, the rate returned is 0%.