Hi Excel Expert,

I need help on the Part 1 & Part 2 of my attached sample tiered commission rate.


Part 1: How to calculate the tiered rate?



Total annual variable = 120,000
Annual Quota = 1,600,000
Rate = 120,000/1,600,000 = 7.50%


If my commission = (110,000/120,000)*3% the rate would be 2.75% blended rate for sales between 0 -480,000

Part 2:
any orders between 0-480,000 (480,000 sales) would be paid at 3% or blended rate
any orders between 480,000-1,100,000 (next 640,000 sales) would be paid at the next rate of 8% or blended rate

How can I showed the commission tiered rate and commission amount?
Sales Rate % Commission $
100000 Help with tier rate based on 100,000 ?
200000 Help with tier rate based on 200,000 ?
170000 Help with tier rate based on 170,000 ?
470000 (total from above) ?

Next sales Rate % Commission $
110000 100,000 would be at the rate above and 100,000 would be next rate (8%)

Attached Files

Sample Tiered Commission Rate.xlsx (30.2 KB, 11 views)