Compounding Commission Schedule

[CMJ]

Hi All,

I'm very new to this forum so please forgive me. I'm trying to create a compounding commission schedule that is as follows:

• 10% of the amount from $0 - $50,000 with a minimum of $1,000
• 5% of the amount from $50,001 – $100,000
• 3% of the amount from $100,001 - $250,000
• 2% of the any amount above $250,001

For example, a sale of 70K would provide 5,000 in commission on the first 50K, and 1000 in commission on the next 20K.

I've tried creating a nested if statement, but I'm having trouble wrapping my head around it. To make issues worse, there's also a minimum commission amount of 1000$ no matter how low the selling price.

I'd truly appreciate any help you all could provide! Thank you in advance for your help.

Chris

 

[T. Valko]

Hi All,

I'm very new to this forum so please forgive me. I'm trying to create a compounding commission schedule that is as follows:

• 10% of the amount from $0 - $50,000 with a minimum of $1,000
• 5% of the amount from $50,001 – $100,000
• 3% of the amount from $100,001 - $250,000
• 2% of the any amount above $250,001

For example, a sale of 70K would provide 5,000 in commission on the first 50K, and 1000 in commission on the next 20K.

I've tried creating a nested if statement, but I'm having trouble wrapping my head around it. To make issues worse, there's also a minimum commission amount of 1000$ no matter how low the selling price.

I'd truly appreciate any help you all could provide! Thank you in advance for your help.

Chris

See if you can adapt the technique described here:

http://mcgimpsey.com/excel/variablerate.html

 

[gsistek]

Assuming the sale number is in A1:
=IF(A1 > 50000,IF(A1 > 100000,IF(A1 > 250000,12000+(A1-250000)*.02,7500+(A1-100000)*.03),5000+(A1-50000)*.05),MAX(A1*.1,1000))

 

[shg]

Welcome to the board.

=MAX(1000, SUMPRODUCT((A1 > {0;5;10;25}*10000)*(A1 - {0;5;10;25}*10000) * {10;-5;-2;-1}%))

 

[CMJ]

Welcome to the board.

=MAX(1000, SUMPRODUCT((A1 > {0;5;10;25}*10000)*(A1 - {0;5;10;25}*10000) * {10;-5;-2;-1}%))

Thank you for some great answers. I must admit, these are much more elegant than the formulas I was working with. It's good to know I've still got a lot left to learn!

To that effect, I have no idea how the formula above is generating the answer it is. An explanation would be great to facilitating future use!

Many Thanks,

Chris

 

[shg]

The formula says ...

If the amount is > 0, you get 10% commission on everything (too much, but hold on)

If the amount is > 50K, you lose 5% commission on everything over 50K

If the amount is > 100K, you lose another 2% commission on everything over 100K

If the amount is > 250K, you lose another 1% commission on everything over 250K

... all wrapped with Max to ensure a minimum of $1K.

The {10;-5;-2;-1}% is the change in commission between tiers.