How to Calculate Tiered Commission in Excel
Tiered Commission Formula is a Excel function that calculates commission marginally across tiers. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Use a marginal tiered commission formula when each slice of revenue earns a different rate, such as 5% on the first $50K and 8% on the next $50K.
The Formula
"Calculate commission on a tiered structure: 5% on first $50K, 8% on $50K-$100K, and 12% above $100K"
=MIN(sales,50000)*5%+MAX(MIN(sales-50000,50000),0)*8%+MAX(sales-100000,0)*12%This formula calculates commission marginally across tiers. The first slice of sales earns 5%, the next slice earns 8%, and only the amount above 100,000 earns 12%.
Step-by-Step Breakdown
- MIN(sales,50000)*5% calculates 5% on the first 50,000 of sales.
- MAX(MIN(sales-50000,50000),0)*8% calculates 8% only on the amount between 50,000 and 100,000.
- MAX(sales-100000,0)*12% calculates 12% only on sales above 100,000.
- MAX(...,0) prevents negative commission when sales have not reached a tier.
- Use named cells or structured references so the formula is readable in a sales commission calculator spreadsheet.
Edge Cases & Warnings
- Sales amount exactly at a tier boundary should not double-count the threshold.
- Zero sales should return zero commission.
- Negative sales or clawbacks need a separate policy decision before using the formula.
- Non-numeric CRM exports can make the commission formula return #VALUE!.
- accelerators above quota can be added as another tier after the base commission calculation.
- Flat-rate tiers are different from marginal tiers; confirm which compensation policy applies.
Examples
"Sales Amount: 40,000"
Commission: 2,000"Sales Amount: 75,000"
Commission: 4,500"Sales Amount: 125,000"
Commission: 9,000Frequently Asked Questions
What is a tiered commission formula?
A tiered commission formula applies different rates at different sales thresholds. In a marginal plan, each slice of revenue is paid at its own rate.
Can I modify the thresholds?
Yes. Replace 50,000, 100,000, 5%, 8%, and 12% with your compensation plan's thresholds and rates.
How do I add quota accelerators?
Add another MAX tier for the revenue above quota, or wrap the formula in an IF that applies an accelerator only after quota attainment.
What happens if I input a non-numeric value?
The formula returns #VALUE!. Clean CRM exports first or wrap the sales value with validation before calculating commissions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.