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

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.