Commission calculator

[tonyna22]

Okay here's what i need...this may be difficult to explain

I'm figuring out a new commission structure. if you hit 103% of plan you get $1000...any percentage above that goes as this...110% would equal = $1100. Then take the 110% minus the 103% which gives you 7%. Take 7% x 2 = 14%. Take 14% of $1000 and add to the original $1100. So the example of 110% would be...$1100 + 140 = $1240.

I know it's confusing...I'm trying to put this in excel...so everyone can automatically track their payout...can anyone help...?

Thanks...

 

[gardnertoo]

How are you getting the first part of the bonus (the $1100 for a 110% achievement)? If 100% = $0, 103% = $1000, and 110% = $1100, what are the values for:
105%
125%
200%?

 

[Dan Waterloo]

Hi tonyna22,

I'm not clear on the first step. You said, "any percentage above 103% goes as this...110% would equal = $1100."

So, just for the first step,
would 117% equal $1170 and would 125% equal $1250?

If so, assuming that the percentage of plan is in A1 and the commission you want to calculate is in B1, put this formula in B1:
=if(a1 < 103%, 0, a1*1000 + 2*(a1 - 103%)*1000)

 

[Dan Waterloo]

Actually, I realize that the commission on 103% using this formula turns out to be $1030. That's not quite what you want but you'll have to look at the basis of the commission definition in order to make it completely consistent.

 

[Dan Waterloo]

The formula can be simplified to
=IF(A1 < 103%, 0, A1*3000 - 2060)

 

[tonyna22]

sorry basically there is no 100% it starts at 103%. The pool is $1000...so every % will be multiplied times $1000 to start then if you exceed the 103% the multiplier kicks in...

105% = $1050
125% = $1500
200% = $2000

 

[tonyna22]

Thanks

Dan Waterloo hooked me up...so thanks to Dan and everyone else...