This template will help you calculate commissions for your account executive (AE) team.
It's primarily based on a tiered ARR commission, which is common in the industry.
Commission plan structure
At a high-level, this plan pays out commission according to this formula:
The following sections explain these components in more detail.
Tiered ARR commission
The main part of this model is a tiered commission paid on the ARR achieved. Tiered commission means that AEs will be paid different commission rates for different levels of target achieved.
The provided workbook lets you define both achievement levels and commission rates.
Flat consulting / support commission
AEs sell not only ARR but also other items eg. consulting and support. This workbook allows you to calculate a flat commission on non-ARR items.
No worries If your plan doesn’t pay commission on non-ARR, it’s easy to exclude this element from the calculation.
New logo bonus
On top of the commission, AEs usually get a bonus for bringing new customers. This workbook allows you to set the new logo bonus.
Another bonus that AEs can earn is a bonus for closing a 2-year (or longer) contract. You can set this up in the provided workbook.
Bonus for crossing 100% of the target
In addition to the previous bonuses, sometimes it happens that AEs get a bonus for achieving their target. This can also be set up in the workbook.
How to use the commission workbook?
This commission workbook will calculate the commission for one Account Executive. Once you set up the parameters according to your commission plan, you can create a copy for each AE.
In summary, to configure the commission workbook:
- Configure the ARR tiers
- Configure the bonuses
- Insert the deal data
- Review the calculation
- Print the commission statement
- Copy the workbook and redo the process for the rest of the AE team
Below we elaborate on this process in more detail.
1. Configure the ARR tiers
- Start in the “2. Tiers” sheet.
- Set up the ARR target.
- Adjust the tiers according to your commission plan.
- Set up your commission rates for each tier.
Explanation of how the tiers work:
If the AE closes $130,000.00 in ARR, with the parameters set as in the picture above, they will get 1% commission for 80,000.00 ARR, 2% for 40,000.00 ARR and 5% for 10,000.00 ARR.
Their ARR commission will equal:
$80,000.00 x 1% + $40,000.00 x 2% + $10,000.00 x 5% = $800.00 + $800.00 + $500.00 = $2,100.00
2. Configure the bonuses
- Open the “3. Bonuses” sheet.
- Adjust the parameters according to your commission plan:
- New logo bonus - This is a fixed amount a sales rep will be paid for each new logo deal. If your commission plan doesn’t include it, you can set it to zero.
- Multi-year bonus - This is a % value. A sales rep will get this on top of the ARR commission for each multi-year deal they close. If your commission plan doesn’t include it, you can set it to zero.
- Flat commission on consulting / support - This is a % value. A sales rep will get this commission for non-ARR items they sell. If your commission plan doesn’t include it, you can set it to zero.
- Bonus for crossing 100% of the target - This is a fixed amount a sales rep will be paid for achieving their ARR target. If your commission plan doesn’t include it, you can set it to zero.
3. Insert the deal data
- Open the “4. Deal data” sheet.
- Copy the data for the Sales rep that you want to calculate the commission for from your CRM and paste it into this sheet.
- You don’t need to follow the column order that is present in the workbook.
- Your columns’ names can be different than the ones present in the workbook.
- Before pasting the data for your sales rep, please make sure that you deleted the exemplary data present in the workbook.
4. Configure the commission plan rules
- Open the “5. Config” sheet.
- Set the commission period start and end dates. This workbook can be used for different commission periods, eg. monthly, quarterly or yearly.
- Specify the deal data column that contains the effective date of the result..
- Specify the deal data column that contains the ARR amount.
- Specify the deal data column that contains the consulting and support fees. If your commission plan doesn’t pay the flat commission on non-ARR items, leave the cells C10:F10 empty.
- If the deal data contains the value in C13 in the column specified by C12, then that specific deal is considered a "new logo" deal, and will be subject to the new logo bonus.. If your commission plan doesn’t pay a new logo bonus, leave the cells C12:C13 empty.
- In C15 specify the deal data column that contains the contract length. In C16 specify the minimum value required for a deal to be subject to the "multi-year bonus. If your commission plan doesn’t pay a multi-year bonus, leave the cells C15:C16 empty.
- Add the company name and name of the sales rep, which will be visible on their commission statement.
5. Review the calculation
- In the sheet “6. Calculation” you can review the commission calculations
- Cells B2:D25 break down the calculation into a sequence of logical steps.
Below the calculation overview, you can see more detailed calculations.
WARNING: Don’t change anything in this sheet!.
6. Print the commission statement
- Open the “7. Commission statement” sheet.
- Change the logo to your company logo.
- Rows 13:27 contain the bonuses, delete those that are unused.
Now you can print the commission statement and share it with the relevant account executive! :)
7. Copy the workbook and calculate the commission for other AEs
You have adjusted the workbook to your company's needs. Now you can easily duplicate it, change the user-specific parameters, paste deals from other users and create their commission statements.
Sign up for our newsletter
Want to stay in the loop and learn more about our next-generation commission management platform?