Financial
Calculate sales commission: flat-tier IFS vs marginal progressive brackets
Implement both retroactive flat-rate commission tiers with IFS and cliff-free marginal bracket commissions with MIN and MAX.
Retroactive flat tiers vs progressive marginal brackets
Suppose commission rates are 5% up to $50,000, 8% above $50,000 through $100,000, and 10% above $100,000. Under a retroactive flat tier (Sales * IFS(...)), crossing $50,000 applies 8% to the ENTIRE $75,000 ($6,000)—creating a discontinuity: $2,500 at $50,000 versus $4,000.08 at $50,001!
Under marginal brackets (like tax brackets), only the dollars inside each band earn that band's rate: 5% on the first $50k ($2,500) + 8% on the next $25k ($2,000) = $4,500.
These rates are hypothetical. Require nonnegative sales for this example and follow the actual compensation agreement for refunds, negative adjustments and rounding.
| SalesAmt (A) | Marginal Commission | Retroactive Flat-Tier Commission |
|---|---|---|
| 40000 | 2000 (40k*5%) | 2000 (40k*5%) |
| 75000 | 4500 (2500 + 25k*8%) | 6000 (75k*8%) |
| 120000 | 8500 (2500 + 4000 + 20k*10%) | 12000 (120k*10%) |
Marginal slice formula and flat-tier IFS formula
MIN(A2, 50000) caps Band 1 at $50k; MAX(0, MIN(A2 - 50000, 50000)) isolates Band 2; MAX(0, A2 - 100000) isolates dollars above $100k.
=ROUND(MIN(A2, 50000) * 0.05 + MAX(0, MIN(A2 - 50000, 50000)) * 0.08 + MAX(0, A2 - 100000) * 0.1, 2)Retroactive flat-tier IFS alternative
When comp plans intentionally pay the higher rate on dollar one once quota is hit, test highest thresholds first in IFS.
=ROUND(A2 * IFS(A2 > 100000, 0.1, A2 > 50000, 0.08, TRUE, 0.05), 2)Try the example
Use the original values or change them to test your understanding.