Formula to Calculate a Stepped Pay Incentive bonus

Hi and welcome
try the technique described at http://www.mcgimpsey.com/excel/variablerate1.html

Thank you, I feel like I am reading gibberish tho. Doesnt make any sense to me :-(

Build a small table like the one in the article, replacing "tax rates" with your amount and apply the formula adapted to your data. You can use the ranges from your table if needed
The explanation can be somewhat tedious, I agree

I feel the example attached is unclear as it is not populated
the other way to do this (if it is easier to understand)
L1 total hours *1
L2 max(total hours -15,0)*2
L3 max(total hours -40,0)
L4 max(total hours -50,0)

L5 max(total hours -60,0)

L6 max(total hours -80,0)

total cost =total hours *1+max(total hours -15,0)*2 + max(total hours -40,0)+max(total hours -50,0)+max(total hours -60,0)+ max(total hours -80,0)

total cost =d52*1+max(d52 -15,0)*2 + max(d52 -40,0)+max(d52 -50,0)+max(d52 -60,0)+ max(d52 -80,0)


you are add the differences between the bands, although a table as suggested is easier to keep updated

Hi Guys-

Thanks so much for your help and time- I must just be really to stupid to figure this out lol

Davsth: To answer you, L1 can not be the total hours *1

L1 is a calculation for ONLY the hours from 1-15 of the total worked (ie: 82 hrs total- this is the sum of hours 1-15, leaving 67 to calculate)
L2 is a calculation for ONLY the hours from 16-40 of the total worked (ie: 82 hrs total- this is the sum of hours 16-40 -Level one calculated 1-15-, leaving 42 to calculate)
AND SO ON.. (You continue calculating each level independently until all hours are calculated)

You have to calculate the total for each level separately and then create a sum total to get the total bonus payable.

I am a financial person and get the calculations but not an excel person so I can not set the sheet up. Can someone please assist me with this :-(

L1 min(d52, 15)
l2 median(d52-15,0,14) *3
l3 median(d52-40,0,9) *4
l4 median(d52-50,0,9) *5
l5 median(d52-60,0,19) *6
l6 max(d52-80,0) *7

you originally said you wanted a solution to calculate bonus, quoted $314 as the answer
not each constituent part of the bonus, hence you just got an answer for the total

hope this is closer