Tiered bonus structure
Hi,
I am a bit of an excel novice, besides basic formulae. I would like to create a simple formula that will calculate bonus payments based on the following structure:
Income 0 - 150,000, bonus = 0
Income 150,001 - 300,000, bonus = 30% of this amount
Income 300,001 - 400,000, bonus = 35% of this amount
Income 400,000 +, bonus = 40% of this amount.
Any help on the best way of setting this up would be greatly appreciated.
C
you could use a table and lookup the value or an IF
the advantage of a table is that you can change the structure without changing all your figures
when you say 40% of this amount
assuming the value was 500,000 and so bonus would be 40% - is it simply 40% of the whole amount - so 500,000 *0.4 = 200,000
OR is it structured so that its only 40% of the amount above 300,000, so 200,000 @ 40% and + 100,000 @ 35% + 150,000 @ 30%
=80,000 + 35,000 + 45,000 = 160,000
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Check this out..
http://www.mcgimpsey.com/excel/variablerate.html
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi Wayne,
Thanks for the response, its the latter version i.e. if 500k, 100 @40%, 100 @35%, 150 @ 30%.
C
then I think this IF should do it for you
=IF(A2>400000, ((A2-400000)*0.4)+(100000*0.35)+(150000*0.3), IF(A2>300000, ((A2-300000)*0.35)+(150000*0.3),IF(A2>150000, ((A2-150000)*0.3),0)))
It does!! great, thank you so much for the help!
C
your welcome , thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks