Tiered Bonus Calculator

[Dnewbery]

Hi,

I am trying to build a bonus calculator based on a tiered/stepped outcome.

For example.

An employee’s salary is 30,000 their max potential is 20% oftheir salary = 6000

If company profit is 3.5 million pay out 15% of a persons maxpotential
At 7million pay out an additional 30% of a persons max potential
At 10.3 million pay out an additional 15% of a persons max potential.

In total the company profit would equate to 60% of the max potential. In between the above steps then it will pay our proportionally (this is the bit I’m stumped at)
I have tried IF/AND statements but can’t quite get it to match, I’ve also had a look at tiered commission but again can’t tweak it to my purpose.
I’m after a formula (or a few, I’m not picky) that would ping out the % of the 60% which I can apply to everyone.

Hope that makes sense and thanks in advance

 

[AlanY]

with company profit in A1,

=IF(A1>=3500000,IF(A1>10300000,60%,IF(A1>=7000000,45%,15%)))

 

[Dnewbery]

Hi,

Thanks for coming back to me, this is what i had, however it doesn't take into account the proportional steps in the middle for example 4.5million would pay out 15% but also some of the 30% in the next step. That is the part i'm stuck on.

Thanks

 

[Eric W]

Perhaps?

Excel 2012

	A	B	C	D	E	F
Company profit	Max potential	Profit	Percent
Payout

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ 7,000,000.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 6,000.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 3,500,000.00 [/TD]
[TD="align: right"]15%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"] $ 7,000,000.00 [/TD]
[TD="align: right"]45%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2,700.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 10,300,000.00 [/TD]
[TD="align: right"]60%[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=VLOOKUP(A2,E2:F5,2)*C2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



At what point does the employee get 100% of the max potential?

 

[Dnewbery]

Hey Eric

There are 2 more elements that each pay out 20% making the full 100%.

This calculator is something we want to give people access to so they can plug in the figures and see what it means for them. Would that vlookup do the proportional steps in the middle of the targets? for example 4.5mil would pay out all the 15% and then some of the 30% in the next step.

 

[Eric W]

I don't think you're describing the way that the bonus is calculated very well, and in fact, I'm not sure you understand it very well yourself. For example, in order for someone to get 100% of the bonus, then the company must reach the top tier, AND the top rate (100%) must be applied to the entire max potential. Given that, it makes sense that the proper rate must be applied to the entire max potential, there would be no partial application of 15% for one range and 30% for another. So in order for your formula to work, you'd just need to add your other two tiers to the table, and change the formula to reference them too.

You may wish to calculate the bonus for various scenarios and see if this formula works for you. If it doesn't, please supply an example, AND the expected result, AND the steps you need to do to get that amount.