How to calculate sales commission (basic formula)

At a fundamental level, there is the sales commission rate: the percentage of a sales price paid to an individual for their efforts in selling a product or service. This rate is typically based on the amount of revenue or profit generated by the sale, and it can vary depending on the industry, company, and type of sale.

The basic formula for a percentage-based sales commission: 

Total Sales ($) X Commission Rate (%) = Total Commission ($)

Example: A salesperson receives a 5% commission on all sales they make, so if they sell $10,000 worth of products in a month, they will earn an additional $500 in commission. This commission is in addition to their regular salary or wages, so if they make $2,000 per month, their total monthly earnings would be $2,500.

But again, that’s the basic formula. There are more!

Before we go into some of the different commission structures, know that all are variable commissions. Oxford Languages defines a variable as “an element, feature, or factor that is liable to vary or change.”

Variable commission is a “catch-all” for any type of commission based on the amount of revenue or products/services sold rather than a fixed salary. As this infographic highlights, more than half of reps receive 25 to 49% variable pay. 

However, they’d prefer that number to be at least a 50/50 split (or even higher variable proportion). The good news? Most managers (69%) agree!

So what are the common types of variable sales commissions? 

1. Graduated or Tiered Commissions: This type of commission structure rewards salespeople for reaching specific sales targets, with higher commissions paid for higher levels of sales.
2. Gross Profit (or Gross Margin) Commission: Commission is calculated based on the percentage of the gross profit margin of each sale.
3. Commission Bonuses: Some companies offer bonuses or incentives for salespeople who exceed sales targets or milestones.

In short: Different commission rate formulas can be used, such as graduated commission rates that pay commissions based on sales tiers, gross profit commission rates that pay commissions depending on the amount of profit a product brings in, and bonus commission rates that are paid when specific sales goals are met. Each commission rate formula has pros and cons, so it’s important to understand how each type works before deciding which rate is best for a business. 

Some commission structures have a maximum amount of commission that a salesperson can earn, called commission caps. With commission caps, businesses can adjust their commission structures as needed and make sure they stay within budget. For example, a salesperson might have a commission cap of $500 per month, so even if they make more than $10,000 in sales, they would still only earn $500 in commission.

Now, let’s look at each of these commission rate examples.

Graduated or tiered commission (includes Excel calculation!)

In a graduated or tiered (often used interchangeably) commission structure, the percentage of commission you earn increases as you sell more products. Multiple commission levels or "tiers" correspond to different sales volume milestones. 

Are you looking to motivate your sales team? Hit certain sales milestones? Launch a new product or service? Enter a new market?

If so, a graduated commission structure may be a good option. This model is often used in industries where salespeople have the potential to earn significant amounts of money through commissions, such as real estate, automobile, insurance, and financial services. 

Here’s an example of what this could look like: 

• For sales between $0 and $10,000, the commission rate is 5%
• For sales between $10,001 and $20,000, the commission rate is 7%
• For sales over $20,001, the commission rate is 10%

As you can see, there’s an incentive to sell more because the commission rate increases at each level. So if you crack the $10,000 level, you’ll bump to a 7% commission rate. And so on.

There are often “additional incentives” associated with a tiered commission structure – bumps for selling new products or services, selling add-ons, upgrades, and so on.

In layman’s terms, the formula for calculating tiered commission is multiplying the salesperson's sales by the appropriate percentage for each tier. 

Using the example above, let’s say they made $25,000 in sales. Commission is calculated as follows:

1. For the first $10,000 in sales, the commission is $10,000 x 5% = $500
2. For the next $10,000 in sales (between $10,000 and $20,000), the commission is $10,000 x 7% = $700
3. For the remaining $5,000 in sales (over $20,000), the commission is $5,000 x 10% = $500
4. The total commission is $500 + $700 + $500 = $1,700

To calculate tiered commission in Excel, you can use the following formula:

=SUM(IF(A1:A10<=B1,A1:A10*C1,0), IF(A1:A10>B1,(A1:A10-B1)*D1,0), IF(A1:A10>B2,(A1:A10-B2)*E1,0))

In this formula, A1:A10 is the range of cells containing the sales amounts, B1 is the upper limit of the first tier, C1 is the percentage for the first tier, D1 is the percentage for the second tier, B2 is the upper limit of the second tier, and E1 is the percentage for the third tier.

Again using the example above, you could use the following formula:

=SUM(IF(A1:A10<=10000,A1:A10*0.05,0), IF(A1:A10>10000,(A1:A10-10000)*0.07,0), IF(A1:A10>20000,(A1:A10-20000)*0.1,0))

This formula will calculate the commission for each sale in the range A1:A10, based on the tiered commission structure. The SUM function is then used to add the commissions for all the sales in the range to give the total commission earned.

Gross profit commission

As indicated by this commission structure, gross profit (or gross margin) is tied to the profitability of a specific sale. 

Not all sales are created equal. Some have higher profit margins compared to others. If you sell more high-profit margin deals, you’ll make more commission. More low-profit margin deals ... less commission. Note: the commission rate is usually the same, but the total commission will vary.

A gross profit commission calculator could look like this: 

Revenue ($) - Costs ($) = Gross Profit Margin ($) X Commission Rate (%) = Total Commission ($)

Example: You close a $100,000 deal. However, the cost of that business was $10,000. The gross profit margin is $90,000. If you receive a 10% commission, that would equal $9,000 in commission.

Note: If this were a “straight” commission model, you’d earn $10,000 in commission (10% of $100,000).

To calculate gross profit commission in a spreadsheet, you can use the following formula:

=(SUM(A1:A10)-SUM(B1:B10))*C1

In this formula, A1:A10 is the range of cells containing the sales amounts, B1:B10 is the range of cells containing the cost of goods sold, and C1 is the commission percentage.

So if you want to calculate a 10% commission on the gross profit of sales in the range A1:A10, with the cost of goods sold in the range B1:B10, you could use the following formula:

=(SUM(A1:A10)-SUM(B1:B10))*0.1

This formula will first calculate the gross profit for each sale in the range A1:A10, by subtracting the cost of goods sold from the sales amount. It will then multiply the gross profit by the commission percentage to calculate the commission for each sale. The SUM function is then used to add up the commissions for all the sales in the range to give the total commission earned.

Commission bonuses

Commission bonuses are typically based on performance or delivery of expected objectives and results, giving employees a tangible reward for their hard work beyond a salary increase. They are typically leveraged to motivate employees to focus on important areas of the business and drive positive results.

For example, a salesperson might earn a $1,000 bonus if they exceed their sales target by a certain amount.

To calculate a commission bonus in a spreadsheet, you can use the following formula:

=IF(A1>B1,C1,0)

In this formula, A1 is the sales amount, B1 is the threshold sales amount at which the bonus is earned, and C1 is the bonus amount.

For example, if you want to calculate a $1,000 bonus for sales over $20,000, you could use the following formula:

=IF(A1>20000,1000,0)

This formula will check whether the sales amount in cell A1 is greater than $20,000. If it is, the formula will return the bonus amount of $1,000. If it is not, the formula will return 0.

You can use this formula for each sales amount in a range of cells and then use the SUM function to add up the bonuses for all the sales in the range to give the total bonus earned. For example, if you have sales amounts in the range A1:A10, you could use the following formula to calculate the total bonus earned:

=SUM(IF(A1:A10>20000,1000,0))

This formula will apply the IF formula to each sales amount in the range and then use the SUM function to add up the bonuses for all the sales in the range to give the total bonus earned.

Automate sales commission calculations with CaptivateIQ

Sales commission calculations and formulas don't have to be complicated. 

In fact, with the right commission automation tool in place, you can make calculating commissions a breeze — saving your company time and money. With CaptivateIQ's Rep Experience, teams have better visibility into commissions and overall team performance.

If you're interested in seeing how our software can help your team reach its full potential, request a demo today.