Tiered Bonus Formula

Good afternoon everyone,

I just joined the forum and am looking for help with a bonus structure. I have to create a bonus structure that is tiered based on fee generated revenue. The issue I have is that I am struggling to come up with a structure that allows me to properly allocate the bonuses as each tier threshold is met and bonus percentage changes.

For example, if the fee revenue generated is anywhere between $0 and $250k, then we pay out a 2.5%, in between 250k-500k we pay out a 5% bonus, in between 500-750k then we pay a 7.5% bonus.
My issue is with the change of bonus percentages. The first $250k will only get 2.5%, then we move on to the next tier which gets a higher percentage, and so on.

I used an if statement, but on the formulas i used, I wasn't able to assign the percentages to each number category so that it gradually increases as revenue generated goes up. If someone collected 400k in fees, then my formula is giving them 5% bonus on the whole amount, whereas I need the 5% to be allocated only to the portion above the initial 250k threshold. Any ideas on how I could possibly set this up?

Thanks in advance for the help.

Welcome to the forum.

I have done this before with a pretty slick formula but I find it easier to just bucket the revenue and then make the calculations as simple as possible but in multiple cells. I find this is easier to audit for people not wanting to dissect a slick formula.

How many buckets do you have for bonus?
1)0-250k,
2)251k-500k,
3)501k to 750k

If you only have three buckets then the formula above will work perfectly and I wouldn't bother breaking the formula into multiple pieces. If you have more bucket OR want them to change quickly then we can whip something else up as well.

I am not exactly sure if you want a model to calculate ONE bonus at a time or multiple, but this works for one.
I created a formula in Column E to calculate the commission per tier AND a second formula in column G that does the same thing, so it just depends which you like more.

If you want it for many rows to calc at the same time, its not unreasonable to build on what John already did with nested if statements, but I would likely through the hardcoded numbers into a reference table so if they change they are already referenced.