Commission Formula w/Accelerators based on Quota Range & Term Length? attatched worksheet

I'm in need of a formula for the following:

Up to 100% of annual quota - they get 8.5% (BUT - any deal less than twelve months must also be calculated based on term length as well)

greater than 100 of less than 133% of quota - 12% commission rate - again, anything less than 12 months on a deal should calculate on the appropriate rate PLUS the term length

greater than 133% of quota - 15% (again, anything less than 12 months on a deal should calculate on the appropriate rate PLUS the term length)

Seems a bit too complicated for this Excel brain - any help out there? Much appreciated. Thank you!

HI! How happy I am to hear this reply

1) Formula can go in E7 - also need to make sure formula works still in M16 based on last thread - think it should still be ok but getting confused :-0

2) The amount in E7 should reflect sales of both

3) Amount in M16 should reflect that the sale has a term of 9 months

Thank you!!!!

Hi again - I just realized something - the quota is actually like this..

Everything earned UP TO or EQUAL to 100% of quota (200K) is at 8.5% rate - anything over that initial quota then gets the following:

Over 100% up to or equal to 133% is 12% rate
Over 133% gets 15%

So it's not calculating correct yet - the first 200K (the 100% of quota) would be at 8.5% rate - and then the two other rates come into play

and...is there a way the line items could be entered under the Net New ACV Column (Column N) as well?

Hello - I realized that the commission structure is a bit trickier than when looked at further.

Up to equal to 100% of annual quota - they get 8.5% and then the next rate kicks in (greater than 100% or less than 133% of quota @12% rate).

However, should part of the deal that pushes them into the next tier still fall within the first 8.5% rate, then we need to consider that as well into the formula (and for the next tier --greater than 133%) as well.

Example -
Deal #1: 100K - would be at 8.5% rate
Deal #2: 150K - 100K of this deal would be calculated into commission at 8.5% rate and 50K of it at 12%

Thank you.

Originally Posted by JeteMc

Here is a formula that will do what you are asking. Paste this into O16 and down:

=IF(M16<=B$4,M16*B$6,IF(M16<=(B$4*1.33),B$4*B$6+(M16-B$4)*B$7,B$4*B$6+(B$4*1.33-B$4)*B$7+(M16-B$4*1.33)*B$8))

Let me know if you have any questions.

Hello...I think the formula is close but I realized that it's not calculating that part of the deal (second line) ..the portion that should be in the base rate and the portion in 12%. Should the Net New ACV in red column somehow be incorporated into this formula as well?

see attached - made the Net New ACV in red (Column F) a formula Screen Shot 2016-01-28 at 3.56.37 PM.png

Workbook3.xlsx

Wondering if someone can help - We are using a different quota (annual quota) but that should not be much different. The formula was changed a bit and seems close - if anyone has time can you see what i'm missing?

Up to equal to 100% of annual quota - they get 8.5% and then the next rate kicks in (greater than 100% or less than 133% of quota @12% rate).

However, should part of the deal that pushes them into the next tier still fall within the first 8.5% rate, then we need to consider that as well into the formula (and for the next tier --greater than 133%) as well.

Example -
Deal #1: if up to 100% or equal to - would be at 8.5% rate
Deal #2: Total Net New ACV pushes them over annual quota - BUT, if part of this deal still falls within the "up to or equal to annual quota" that amount would be calculated into commission at 8.5% rate and the remainder at the next tier

Thank you.

Formula needed in Column N

As a general formula

Try

=MIN(D1,A1)*0.085+IF(D1>A1,MIN(D1-A1,D1-0.33*A1)*0.12+MAX(0,D1-1.33*A1)*0.15,0)

D1=Annual earnings

A1=Annual Quota