Commission Splits- Sales between 2 levels

Hi everyone,

I am trying to set up a commission split schedule and I am having issues when the commission splits two tiers. See example below:

50/50 Commission Split Program (% split and gross commissions)

50% Commission- $0 - $18,800.99
60% Commission- $18,801 - $52,640.99
75% Commission- $52,641 and more

If the agent had a gross commission of $18,000 and recently logged a commission that will push them into the next tier, I need $801 to come from the 50% tier and the rest to come from the 60% tier. I was able to do this manually (see cell in red), but I would like this to be done automatically.


Thank you in advance.

Michael

you can use a number of options to do that
1) would be to use an IF statement

=IF( cell with commission > 52640 , ((cell with commission - 52640)*0.75) + ((52640-18801) *0.6))+(18800*0.5), IF( cell with commission > 18800 , (( cell with commission - 18800)*0.65) +(18800*0.5), cell with commission *0.5 ))

I was not sure on your example which cell to use , as the 50/50 split was not clear how to apply


+

Hello M,

welcome to the forum. I could not quite understand your spreadsheet. It uses the number 1602 which isn;t part of the commission schedule.

Anyway, here is another way to split the commission across the ladder:

=Tier1percent*MIN(Tier1,Commission)+MAX(0,MIN(Commission-Tier1,Tier2-Tier1))*Tier2percent+MAX(Commission-Tier2,0)*Tier3percent

where
Tier1percent = 50%
Tier2percent = 60%
Tier3percent = 75%
Tier1 = 18,801
Tier2 = 52,541
Commission = the gross commission to be split across the commission ladder.

Cheers