# Variable Commission Rates

1. ## Variable Commission Rates

I am trying to build a waterfall calculation for commissions. I can't get the partial commissions in months when he is at two rates. Can anyone help? See attached. I am looking for a formula to copy down and use for multiple sales people based on the same criteria.

Greatly Appreciated.

2. ## Re: Variable Commission Rates

You can dispense with the various rows and have the total in one row if preferred... eg using your sample file add the following:

``Please Login or Register  to view this content.``
To get total commission say in row 21

``Please Login or Register  to view this content.``
If incremental monthly amounts preferred then subtract the prior YTD balance from the above:

``Please Login or Register  to view this content.``

3. ## Re: Variable Commission Rates

If it's a standard "complex tiered" commission, meaning the first 80,000 is always at 15&#37;, the next amount up to 150,000 is at 20% (but the original 80k is still only 15%), then this standard tiered formula will do that all in one cell, no charts needed:

=SUMPRODUCT(--(B\$14 > {0,80001,150001,400001,750001}), B\$14 - {0,80001,150001,400001,750001}, {0.15,0.05,0.02,0.03,0.05})

If it's a "basic tier" meaning the entire commision is calculated at the total amount (so a sales volume of 100,000 would generate a 20% commission on the whole amount), then this would do that, again in one cell:

=B\$14 * (LOOKUP(B\$14, {0,80001,150001,400001,750001}, {0.15,0.20,0.22,0.25,0.30}))

4. ## Re: Variable Commission Rates

If you prefer to keep the chart onscreen and easily editable, then I would lose the "MAX" column and just work from the "Tier Starts" range. This sample sheet shows both types of commission structures:

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1