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 | ||||
---|---|---|---|---|

Revenue | Tier Rate | Bonus Rate | Fixed Amount | Maximum |

$1000 | 1.00% | 0.00% | ||

$2000 | 2.00% | 0.00% | ||

$5000 | 4.00% | 0.50% | ||

$10000 | 5.00% | 1.50% | ||

$25000 | 5.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.

Formula | Tier | Commission |
---|---|---|

$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.

Formula | Extra Commission |
---|---|

12500 * 1.50% | $712.50 |

**Example 2 – capped tiers** **(units sold)**

Structure | ||||
---|---|---|---|---|

Units | Tier Rate | Bonus Rate | Fixed Amount | Maximum |

10 | 1.00% | 0.00% | ||

20 | 2.00% | 0.00% | ||

50 | 4.00% | 0.50% | 90 | |

100 | 5.00% | 1.00% | ||

250 | 5.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.

Formula | Tier | Commission |
---|---|---|

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.

Formula | Extra Commission |
---|---|

150 * $100 * 1% | $150 |

**Example 3 – net-based tiers** **(revenue)**

Structure | ||||
---|---|---|---|---|

Revenue | Tier Rate | Bonus Rate | Fixed Amount | Maximum |

$1000 | 1.00% | 0.00% | ||

$2000 | 2.00% | 0.00% | ||

$5000 | 4.00% | 0.50% | ||

$10000 | 5.00% | 1.00% | ||

$25000 | 5.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.

Formula | Tier | Commission |
---|---|---|

($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.

Formula | Extra Commission |
---|---|

($12500 – ($12500 * 1.5%)) * 1.5% | $685.63 |

**Example 4 – rollup tiers** **(revenue)**

Structure | |||||
---|---|---|---|---|---|

Revenue | Tier Rate | Override Rate | Bonus Rate | Fixed Amount | Maximum |

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 | ||
---|---|---|

Formula | Tier | Commission |

$1000 * 1% | $0-1000 | $10 |

$250 * 2% | $1000-2000 | $5 |

Total Commission | $15 |

Jane | ||
---|---|---|

Formula | Tier | Commission |

$1000 * 1% | $0-1000 | $10 |

$250 * 2% | $1000-2000 | $5 |

Total Commission | $15 |

Bob | ||
---|---|---|

Formula | Tier | Commission |

$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.