Automate Commission Accelerator calculations based on quota attainment range.

Hi

I was hoping someone would be able to help with the following scenario.

I have a tiered accelerator component to my commission plan calculator and wanted to automate the calculations.


Summary:

Tier Rate . Range above Quota
T1 125% 101% - 125%
T2 150% 126% - 150%
T3 175% 151% - 199%
T4 200% 200%+

Scenario:

If an individual gets to 205% of quota they would hit all four tiers of accelerator (See calcualtions in attachment highlighted in yellow). I would like to have this automatically calculate. Any help would be greatly appreciated.
Note: Only the amount over quote within a given range is eligible for the accelerated rate in a given tier.

For example:

Rep's quota = $10,000
Rep's actual performance = $14,000. (140% of quota)
Accelerator: The rep in this case would hit T1 and T2 accelerators

T1 = $10,000 x 1.25 (range 101 - 125%) = $12,500 less quota $10,000 = $2500 eligible for T1 rate
T2 = $10,000 x 1.40 (range 126 - 150%) = $14,000 less T1 attainment $12,500 = $1,500 eligible for T2 rate


T1 = $2500 x 125% = $3125
T2 = $1500 x 150% = $2250

If you're looking to calculate the "additional" tiered commission based on YTD MRR, to append to the standard value, then the below should work:

Formula:

Please Login or Register  to view this content.

using your sample values the above would generate an additional commission of $52,570 which equates to:

7500 @ 1.25
7500 @ 1.5
14,700 @ 1.75
3,110 @ 2.00

Thanks it works!