That SRCommAmt field is just for reporting. Can’t really use it for invoice lines.
But I did do some math to figure out how to calculate the Rep Rate to get a value with the minimal error from my target. It works by using the same rep twice, with two different Splits and Rates.
-
Calculate the ideal commission rate in % (call it V).
V = DesiredCommission / CommissionableAmount * 100%
V will have more decimals that the 2 allowed by the RepRate fields.
-
Calculate Rate1 (call it R1).
R1 = Floor(V*100,1)/100
The 100's
are because RepRate has two decimal places
-
Calculate Rate2 (call it R2).
R2 = Ceiling(V*100,1)/100
-
Calculate Split1 (call it S1)
S1 = Round((R2 - V) * 10000,1)
The 10000's
are because we want the 3rd and 4th digits (100 x 100) .
-
Calculate Split2 (call it S2)
S2 = Round((V - R1) * 10000,1)
The way it works is to find the smallest “whole number” below the ideal value (V), and the next larger “whole number” above V. The Floor and Ceiling functions do this. The difference between these values and the ideal value is an indicator of how far the R1 and R2 values are from the ideal value. The sum of (R2 - V) and (V - R1) is always “1”.
This is used as a weighting factor between the two, and applied as the Split value for each. But since the closer value should have the higher weight we use the distance from V to R2 as the split weight for R1, and vice versa.
examples:
|
Example 1 |
Example 2 |
Example 3 |
Commisionable Amount |
$12,150.08 |
$12,150.00 |
$321,456.78 |
Desired Commission |
$1,500.00 |
$1,500.00 |
$1,500.00 |
Ideal Rate |
12.3456 |
12.34567901 |
0.466625716 |
|
|
|
|
Ideal Rate (V) |
12.3456 |
12.34567901 |
0.466625716 |
Rate 1 (R1) |
12.34 |
12.34 |
0.46 |
Rate 2 (R2) |
12.35 |
12.35 |
0.47 |
Split 1 (S1) |
44 |
43 |
34 |
Split2 (S2) |
56 |
57 |
66 |
|
|
|
|
S1 Portion of Line Amount |
$5,346.03 |
$5,224.50 |
$109,295.31 |
S2 Portion of Line Amount |
$6,804.04 |
$6,925.50 |
$212,161.47 |
Comm at R1 of S1 |
$659.70 |
$644.70 |
$502.76 |
Comm at R2 of S2 |
$840.30 |
$855.30 |
$997.16 |
|
|
|
|
Tot Commission |
$1,500.00 |
$1,500.00 |
$1,499.92 |
Error |
$- |
$0.00 |
$(0.08) |
You will start to get a difference from the ideal when the commissionable amount and desired commission get very large.