Looking for help calculating tier-based commissions? In this article, we’ll describe different ways to calculate tier-based commissions – with examples. Tired of manually calculating commissions using spreadsheets? It’s time to automate your commissions. Visit us online to learn more.

  • Example 1 – simple tiers (revenue)
  • Example 2 – capped tiers (units sold)
  • Example 3 – net-based tiers (revenue)
  • Example 4 – rollup tiers (revenue)

The main parameters you will need to define for your commission structure are:

  • A tier metric (ex: revenue, margin, quantity, etc.)
  • Tier thresholds (ex: 0-100, 100-200, etc.)
  • Tier commission rates (applied to the portion within each tier)
  • Tier bonuses (applied to the total for hitting certain goals)
  • Tier caps (if you have limits within each tier)

Example 1 : simple tiers (revenue)

Structure
RevenueTier RateBonus RateFixed AmountMaximum
$10001.00%0.00%
$20002.00%0.00%
$50004.00%0.50%
$100005.00%1.50%
$250005.00%2.00%

Consider sales worth $12500. We will determine which portion of revenue falls in each tier, and apply the tier commission rate to each portion.

FormulaTierCommission
$1000 * 1%$0-1000$10
$1000 * 2%$1000-2000$20
$3000 * 4%$2000-5000$120
$5000 * 5%$5000-10000$250
$2500 * 5%$10000-25000$125
Total Commission$525

We will also pay an incremental cumulative bonus amount based on the highest attained tier (see the “Bonus Rate” column in our commission structure) over the entire amount.

FormulaExtra Commission
12500 * 1.50%$712.50

Example 2 – capped tiers (units sold)

Structure
UnitsTier RateBonus RateFixed AmountMaximum
101.00%0.00%
202.00%0.00%
504.00%0.50%90
1005.00%1.00%
2505.00%1.50%

Here we will work with units sold. We’ll set a cap of 90 units within the 50-100 tier. Consider sales of 150 units worth $100 each. We will determine which portion of the total quantity falls in each tier and apply the tier commission rate to each portion. To handle the cap, we’ll pay commissions over the entire 50 units within the 50-100 tier. However, because our cap of 90 was exceeded, we’ll deduct commissions for those 10 units above 90 within the 50-100 tier.

FormulaTierCommission
10 * $100 * 1%0-10 (units)$10
10 * $100 * 2%10-20 (units)$20
30 * $100 * 4%20-50 (units)$120
50 * $100 * 5%50-100 (units)$250
10 * $100 * 5%Cap = 90-100-$50
50 * $100 * 5%100-250 (units)$500
Total Commission$850

We will also pay an incremental cumulative bonus amount based on the highest attained tier (see the “Bonus Rate” column in our commission structure) over the entire amount.

FormulaExtra Commission
150 * $100 * 1%$150

Example 3 – net-based tiers (revenue)

Structure
RevenueTier RateBonus RateFixed AmountMaximum
$10001.00%0.00%
$20002.00%0.00%
$50004.00%0.50%
$100005.00%1.00%
$250005.00%1.50%

Consider sales worth $12500. We will determine which portion of revenue falls in each tier and apply the tier commission rate to each portion. However, within each tier, we won’t apply the commission rate to the entire in-band revenue. Instead, we will apply the commission rate to the in-band revenue minus the commission. Essentially, the commission is deducted from revenue before the commission rate is applied.

FormulaTierCommission
($1000 – ($1000 * 1%)) * 1%$0-1000$9.9
($1000 – ($1000 * 2%)) * 2%$1000-2000$19.6
($3000 – ($3000 * 4%)) * 4%$2000-5000$115.2
($5000 – ($5000 * 5%)) * 5%$5000-10000$237.5
($5000 – ($2500 * 5%)) * 5%$10000-25000$118.75
Total Commission$500.95

We will also pay an incremental cumulative bonus amount based on the highest attained tier (see the “Bonus Rate” column in our commission structure) over the entire amount minus the commission.

FormulaExtra Commission
($12500 – ($12500 * 1.5%)) * 1.5%$685.63

Example 4 – rollup tiers (revenue)

Structure
RevenueTier RateOverride RateBonus RateFixed AmountMaximum
1000$1.00%0.50%0.00%
2000$2.00%1.00%0.00%
5000$3.00%1.50%0.00%
10000$4.00%2.00%0.00%
25000$5.00%2.50%0.00%

Consider sales worth $12500 and the following hierarchy. Note the new “Override Rate” column in our commission structure. The key point here is that we won’t aggregate sales from Sam and Jane to calculate Bob’s commissions (indeed, this would lead to higher commission rates, which seems unfair). Instead, we’ll calculate Sam and Jane’s commissions separately, and do it again but using Bob’s override commission rates.

             Bob
              |
    __________|__________ 
   |                     | 
 Sam                    Jane
Sam
FormulaTierCommission
$1000 * 1%$0-1000$10
$250 * 2%$1000-2000$5
Total Commission$15
Jane
FormulaTierCommission
$1000 * 1%$0-1000$10
$250 * 2%$1000-2000$5
Total Commission$15
Bob
FormulaTierCommission
$1000 * 0.5%$0-1000$5
$250 * 1%$1000-2000$2.5
$1000 * 0.5%$0-1000$5
$250 * 1%$1000-2000$2.5
Total Commission$15

In Conclusion

In this article, we described different ways to calculate commissions based on tiers. Some of our tier-based commissions were non-cumulative – i.e. they were applied to the portion within each tier (in-band). Some of our tier-based commissions were cumulative – i.e. they were applied to the entire amount (as a bonus for hitting certain goals). We also showed you how you can deal with caps and overrides. Many other permutations are possible. The goal here was to get you thinking about various nuances of tiers and their mechanisms (cumulative or not, capped or not, net or gross, etc.).

The hard part if of course to implement your chosen approach using Microsoft Excel / Google Sheets formulas. Also, we showed you how to calculate commissions based on total sales. However, your reps would likely want to see a breakdown of commissions per sale. What this means is that you’d calculate commissions the same way, but on a per-sale basis. First, sort sales by date, and determine which sale fell within which tier. Also, some sales may cross tiers, in which case you should “split” those sales into 2 portions, and apply the right commission rate to each portion (blended rates) as we did previously.

This gets more complicated and messier when dealing with a large number of reps. If you’re tired of manually calculating commissions, or want to improve transparency & accuracy, automation is the way to go. Here is what your automated commissions could look like. Visit us online to learn more.