Tiered Fee Formula Help

Hope all is well. I'm trying to calculate a simple tiered commission formula.

If the multiple:

Less than 4.0x = no fee
Between 4.0x - 5.0x = 5% of the sale price on this portion
Between 5.0x - 6.0x = 7.5% of the sale price additional on this portion
Above 6.0x = 10.0% of the sale price additional on this portion

For clarity, if we assume the multiple achieved was say 5.4x, and the sale price was $1.2 million, the fee would then be 1.0 times sale price ($1.2M) times 5% or $60,000 plus 0.4x sale price times 7.5% or $36,000 for a total bonus of $96,000.

Any help would greatly be appreciated.

I'm trying to build on this concept and I think the additional variable is throwing me off.

http://www.excelforum.com/excel-form...mula-help.html

Originally Posted by Crooza

So the 7.5% only applies to the amount in excess of the 5x BUT you've applied the 5% to the entire 5x when you've said there is no fee for less than 4 so why isn't the 5% applied to only the portion between 4 and 5 which is the same logic you've sued for the portion between 5 and 5.4 !?

Sorry, you are correct, the 5% would be applied to only the portion between 4x-5x, then the excess, 0.4x would be multiplied by $1.2M then by 7%.

So if you had a 5.4x multiple, you would multiply 1.0x times $1.2M times 5% ($60,000) + 0.4 times $1.2M times 7.5% ($36,000) for a total fee of $96,000.

My apologies for the confusion.

I'm trying to build on this concept and I think the additional variable is throwing me off.

http://www.excelforum.com/excel-form...mula-help.html

Originally Posted by Crooza

Ok. I'm at kids soccer atm. Will come back and look later today

Much appreciated! I'll PM you the word for word verbiage so it provides enough color. Hope your kids win!

Sorry, you are correct, the 5% would be applied to only the portion between 4x-5x, then the excess, 0.4x would be multiplied by $1.2M then by 7%.

So if you had a 5.4x multiple, you would multiply 1.0x times $1.2M times 5% ($60,000) + 0.4 times $1.2M times 7.5% ($36,000) for a total fee of $96,000.

My apologies for the confusion.

Originally Posted by Crooza

Try this.

=SUMPRODUCT(C2:C5,IF(IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)<0,0,IF(E2-A2:A5>B2:B5-A2:A5,B2:B5-A2:A5,E2-A2:A5)))*E1

its an array formula and I could have hardcoded the arrays into the formula but I've set it up in the attached spreadsheet to show how it might be easier to use.

Can you check a few examples to make sure I've understood the logic and got the arithmetic right? thanks

And we won soccer too!

Congrats!!!!

I just tried a handful of scenarios and it seems to work for me. Laying it out was a great way for me to learn how the formula works, thank you for that. How would I hardcore the array?

Originally Posted by Crooza

To hard code put the value from the respective arrays inside squiggly brackets

For example if A2:A5 is 0, 4, 5, 6 then replace this A2:A5 with this {0,4,5,6} and so on.

Give it a go. If you can't work it out let me know and I'll tidy it up for you

Got it to work, thank you so much!!!!