Tiered Bonus Calculator

Dnewbery

New Member
Joined
Apr 24, 2019
Messages
3
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,979
Office Version
365, 2019, 2016
Platform
Windows
with company profit in A1,

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

Dnewbery

New Member
Joined
Apr 24, 2019
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,132
Perhaps?

Excel 2012
ABCDEF
1Company profitMax potential Profit Percent
2 $ 7,000,000.00 $ 6,000.00 $ - 0
3 $ 3,500,000.00 15%
4Payout $ 7,000,000.00 45%
5 $ 2,700.00 $ 10,300,000.00 60%
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
C5=VLOOKUP(A2,E2:F5,2)*C2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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

Dnewbery

New Member
Joined
Apr 24, 2019
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,132
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.
 

Forum statistics

Threads
1,085,056
Messages
5,381,461
Members
401,740
Latest member
jphermans

Some videos you may like

This Week's Hot Topics

Top