Calculating commissions in Excel? Start here.

If you’re not ready yet to automate your sales commission reporting and payouts, no worries! You’re not the only one calculating commissions in Excel.

Despite rapidly rising numbers in business intelligence and analytics, it seems people still prefer spreadsheets. Specifically, 63 percent of businesses do. A recent International Data Corporation survey found that nearly two-thirds of companies run various payment processes, like commissions, via Excel.

For small businesses with simple commission structures, bulk spreadsheets can deliver exactly what you need. In this article, we will walk you through the steps and formulas to create a sales commission spreadsheet.

We love and respect a spreadsheet, but the disadvantages to running manual commission calculations can be an error-filled time suck. Any change to a compensation plan requires manual updates. Every new deal must be added, along with any bonuses or spiffs that impact commission payouts. These can take as little as a few minutes to make or two business days depending on their complexities. 

At QuotaPath, we’re big on transparency. It’s a theme woven throughout our team, our platform, and our relationships with our customers. Unfortunately, spreadsheets often fail in fostering transparency. Permission-based spreadsheets block sales reps and leaders from real-time insights into their earnings and forecasts. Anytime a commission question pops up on a specific deal or paycheck, a gatekeeper to the spreadsheet awaits. 

Beyond an Excel commission calculator

If your business is planning on scaling its sales organization this year or adding new compensation plans, please consider automating this process. Better yet, fancy a chat with QuotaPath as your sales performance management system. We can track, calculate, forecast, and prepare earnings payouts for all of your sales teams on variable compensation plans. 

By our estimates, QuotaPath can save your teams that are calculating commissions in Excel 17 hours a month. Some customers have even reported four business days of work freed up since implementing QuotaPath.

Depending upon how complex your compensation plans are, your spreadsheets should take you between two to eight hours to build. Then, for monthly upkeep, approvals, and finalizations for payroll, plan to spend between one to four days a month preparing and addressing discrepancies. 

Note: Bulk spreadsheets do not carry over month-per-month. Any exceptions or modifications to a rep’s commissions will have to be re-added each month.

Additional note: Individual rep commission statements will need to be manually created, with other rep info fully deleted from the spreadsheet. 

Now that we got that out of the way, we’re ready to begin calculating commissions in a spreadsheet! To help you get started, we’ve outlined the steps and definitions below. 

Free Sales Commission Calculator Template

A free spreadsheet to simplify the commission tracking process. Track what you or your team have earned in 4 inputs.

Download Now

Follow these steps to start calculating commissions in Excel

How to create a commission calculator in Excel: First, identify what you want to include in your spreadsheet. 

We recommend, if applicable:

• Date Closed
• Customer
• Order #
• Revenue 
• Sales Rep
• Commission Rate
• Commission Amount
• Bonus 
• Deductions
• Payout
• Date Paid

Next, will your bulk spreadsheet track multiple months? If so, you can either duplicate the spreadsheets for every month or, extend columns out to the right. Beware, the more columns you add, the trickier it’ll be to scroll through and find what you’re looking for later. 

Once you’ve built these fields out, review your commission structure and compensation plan. 

Ask yourself, what kind of compensation plan do I have?

If it’s a single-rate sales commission model, good news, this spreadsheet will work well. A single rate, or flat rate, pays a set percentage from the deals you close. 

Whatever your single rate is, you’ll apply this percentage to every revenue amount earned by you or your reps in your spreadsheet. 

Within your spreadsheet, single-rate commissions might look like this:

You’ll input this formula in the third column to calculate the commissions earned:

To apply to the fields below, grab the corner of the cell with the formula in it, and drag it through the applicable cells. This action will modify the same formula for the corresponding rows.

Pat yourself on the back; you’re doing great. Just remember that although all often understand single raters, they fall short in rewarding overperformance.

If you need help with compensation plan strategy, we’ve got free consultations available with our Chief of Staff Graham Collins. 

Need a Google Sheet Commission Template? Download Yours Below.

How to track multiple-rate commissions in Excel

Your spreadsheet grows more complex when you venture outside of simple-rate compensation plans.

Unlike single-rate, multiple-rate commissions factor in different percentage earnings on deals. The rate earned could vary based on quota attainment, deal size, or how much a rep sold already that month or quarter. 

You may have also heard multiple-rate commission plans referred to as accelerators, escalators, tiered commission, or multipliers. They all mean the same thing and act as a great way to motivate reps and reward performance. 

If your plan classifies as such, should you be calculating commissions in a spreadsheet? Not on our watch! 

We can make this process so much easier for you through our automated commission tracking software.

But can you? Absolutely. 

To account for the various rates, you will need to create a rate table. This can be built out on the same page of the spreadsheet.

A commission rate table in Excel might look like this:

Note: There are no formulas within this rate table.

After defining your rate ranges, build out the table within the spreadsheet. Then select all of the cells within the rate table, scroll up to the top left corner where the cell numbers usually appear, and name it “RateTable.” 

How to get my commissions spreadsheet to acknowledge my rate table

Now that your rate table is ready, it’s time to get your spreadsheet to recognize it and apply it to your commissions. 

Cue: Excel’s “LOOKUP” function!

To get your spreadsheet to recognize what rate a closed deal should be paid out on, you will add the LOOKUP command to your Commission Rate column.

In this example, the formula is citing cell D2, which is where the revenue amount for this deal lives. Then, it’s pulling in the appropriate rate based on the range we set previously. For $3,500, reps earn 4 percent commission.

Deep breath, you’re doing amazing, sweetie!

Add in your deals

From here, you’re now ready to add the won deals for your reps that fall within this compensation plan. For reps outside of this compensation model, such as a sales development rep or a sales leader, you’ll need to create a different spreadsheet. (Psst, in Quotapath, we can build and distribute your different plans for you.)

Congratulations, you just built a model for calculating sales commissions in a spreadsheet!

Additional variables to consider

As you thrive in your new spreadsheet utopia, here are a few more items to consider. Along with rate plans, make note of any single-rate bonuses, multiple-rate bonuses, and/or milestone bonuses. 

Bonuses represent something entirely different from rate plans. Commissions calculate from the revenue earned on a deal closing. Meanwhile, reps get bonuses, or set amounts of money, for completing a specific task. A single-rate bonus, for example, could be earning an extra $500 for hitting a monthly quota. 

You can add bonuses manually at the time a rep hits the conditions within your spreadsheet. Or, you can have individual spreadsheets for each rep and add a formula that inputs bonuses based on your conditions.

This might look like a bonus of $1,000 upon hitting $50,000 in total revenue. That bonus is in addition to the rep’s commission rate. When that rep closes a deal that pushes them to $75,000 in total revenue, they might earn another bonus of $2,000. That’s a multi-rate bonus.

To account for this, you’d have a cell in your spreadsheet dedicated to total revenue for the year, month, or quarter, depending on your bonus parameters. The formula, assuming your revenue totals are in the “A” column, looks like this:

Commissions spreadsheet checklist

Lastly, to ensure your spreadsheet remains accurate and secure, run through this checklist monthly.

• Use naming conventions that include time periods and rep names.
• Check that all formulas and multipliers are correct before sending and upon receiving.
• Password-protect each spreadsheet.
• Make a backup of each spreadsheet.
• Only send the spreadsheets to the designated recipients, and triple check the email address before sending. 
• If sending to reps, only include that rep’s commission information by deleting the cells related to other reps. 
• Always look for hidden rows upon receiving commission spreadsheets (tips)

Until next time

Thank you for making it this far! 

This may seem a bit overwhelming, and honestly, it is. 

That’s why QuotaPath launched in 2018 to remove this burden from sales, RevOps, and finance teams. If it’s not the right time to chat, and you need additional spreadsheet tips, check out this lengthy list.

For free commission calculators, quota attainment calculators, and commission tools, check out ours here:

• Sales Compensation Calculator
• Sales Quota Planner
• Quota:OTE Ratio vs. Sales:Earnings Ratio Calculator
• Commission calculator Excel template
• Comp Plan Builder

FAQs

Can I calculate different commission rates based on different sales tiers?

Yes, you can calculate different commission rates based on different sales tiers. Here are two methods:

Method 1: Using the IF function

The IF function is a logical function that allows you to test a condition and return a value if the condition is true, or another value if the condition is false.

To calculate different commission rates based on different sales tiers using the IF function, you would need to create a table with the different sales tiers and their corresponding commission rates. Then, you would use the IF function to calculate the commission rate for each sale.

Method 2: Using the VLOOKUP function

The VLOOKUP function is a lookup function that allows you to search for a value in a table and return the corresponding value from another column in the table.

To calculate different commission rates based on different sales tiers using the VLOOKUP function, you would need to create a table with the different sales tiers and their corresponding commission rates. Then, you would use the VLOOKUP function to look up the sales tier for a given sale and return the corresponding commission rate.

Can I automate commission calculations for multiple salespeople?

You can automate commission calculators for multiple salespeople using commission tracking software such as QuotaPath. Build your compensation plans using QuotaPath’s plan builder, assign plans to your reps, then sync your CRM to automatically pull data from your sales pipeline and calculate commissions accordingly.

What if I need to calculate commissions regularly?

If you need to calculate commissions regularly, the easiest and most accurate solution is to invest in a sales compensation management platform like QuotaPath. In doing so you can shave down the time it takes to run commissions from days to minutes. Try it out for yourself in a free 30-day trial.