How to Calculate Commissions in Excel with VLOOKUP

What Is A Sales Commission Structure?

Sales commission is most commonly known as the variable component of a total sales compensation package. While an on-target earning (OTE) is almost always established, the total commission earned is dependent on each salesperson’s individual goals and their performance.

Your commission structure ties a sales rep’s performance to the amount of money he or she will take home each paycheck. It’s no secret that accountability produces results, and a well-structured commission plan is an excellent way to incentivize top performance.

So, while the salary component of a salesperson’s comp package is fixed and pretty easy to understand, the variable portion has a large amount of room for flexibility and configuration depending on the type of sale and sales process a company has in place.


How to Choose the Right Sales Commission Structure For Your Sales Team

There’s no one-size-fits-all approach to sales commission structures. What works for Company A might lead to complete disaster for Company B.

So the question is, how to create a commission structure for your company that actually works? Simple: follow this six-step process:

Step 1: Determine company goals and priorities

The first thing to do is determine your goals and priorities. What are you trying to achieve? And how can you encourage your reps to do these things?

For example, are you looking to expand your territories? Or would you rather focus on landing a few major accounts? Is it more important to minimize expenses at this time? Or do you need to build a more collaborative environment for your reps?

Once you know what your goals and priorities are, you can choose the sales commission structure that best supports and enables them.

Step 2: Benchmark against industry commission rates

Your sales commission structure isn’t the only thing you need to decide. You also need to pick the right commission rates. If you don’t, your reps won’t stick around for long because another organization will pay them more for the same workload.

How do you determine the right rates? You study your industry.

What do your competitors pay their sales reps? Can you do the same? Better question: can you offer more than other organizations in your industry? If so, you’ll have an easier time attracting and retaining the best talent.

To research pay rates and incentives in your industry, take a look at Xactly’s benchmark database, which contains 15+ years of relevant information.

Step 3: Consider roles and responsibilities

Next up, look at the people on your team and the roles they’re asked to complete. Sales managers and sales reps, for instance, have different jobs and responsibilities. Compensating them in the same exact way doesn’t just make sense.

Because of this, you need to choose different pay structures for each role. That way your people are fairly compensated for the work they accomplish.

Step 4: Factor in turnover rates

You should also ask yourself, what is my sales department’s current turnover rate? This will tell you a little bit about how your current commission plan is working.

For example, if you experience high turnover, there’s a good chance you aren’t paying your reps enough money or providing them with enough security—two problems reps experience when working inside commission only models.

Step 5: Look at productivity levels

Do all of your reps perform at the same level? Unlikely…

Like most other organizations, your sales department probably has both high and low achievers. If so, consider some kind of tiered commission structure to reward your best sellers and encourage your weakest ones to step up their games.

As we mentioned earlier, money can be a great incentive. If you want your reps to close more deals, increase their commissions once they hit certain thresholds.

Run OTE simulations

Finally, you need to simulate on-target earnings, better known as OTEs.

An OTE is the total amount of money you’ll pay your sales reps once they achieve a specific sales target. It includes base pay plus commissions and incentives.

Can you afford this number? And is this number comparable to what other companies in your industry are paying their salespeople? If the answer to either of these questions is “no,” you need to re-evaluate your sales commission structure.

Related calculators

It’s often the case that you need to calculate a margin alongside the commission. You may also be offered a discount on your purchase (or you may offer one on a particularly hard sale). The real estate commission calculator is a specific application of this commission problem (check out the VAT version, too).

  • https:///wiki/Commission_(remuneration)

What are some examples of commission-based jobs?

  • Sales representatives
  • Brokers for real estates
  • Insurance agents
  • Medical representatives
  • Sales engineers
  • Financial service sales agents
  • Stockbrokers
  • Loan officers
  • Literary agents
  • Inside sales

How to Calculate Sales Commission

Knowing how to calculate sales commission matters. Be accurate, transparent, and efficient. Follow these straightforward steps to calculate sales commission, every time, accurately. Determine the Commission Period Firstly, confirm the commission period for transactions. Commission rates can change every month or quarter. Calculate the Commission Base Next, confirm how much revenue in a deal will pay sales commission under the plan. Maintenance revenue may not be eligible, and some special contract terms, like co-term payments, may not be either. With that confirmed, check what the base commission rate is for the different components in a deal. High-margin product revenue will likely pay one rate, and lower margin services a lower rate. Calculate the Payable Commission To calculate the payable commission, multiply the sales revenue by the sales commission rate. A 10 percent commission rate on a $10,000 product deal would pay $1,000 in commission. Apply Any Commission Variables Once you have the payable commission, you can apply commission variables for which a salesperson is eligible. A new customer deal might get a 20 percent uplift, as well as a standard commission. In our example, this takes the 10 percent rate to 12 percent, paying an extra $200. Apply Tiered Commission Rates If a plan uses tiered commission rates, then apply them here. In our example, the $10,000 deal closed in Q1 gets a 10 percent uplift, taking the 10 percent commission to 11 percent. If it closes in Q4, it will likely get no uplift, and the commission rate would remain at 10 percent. Calculate Any Overrides Overrides aim to reward high achievement. In our example, the 10 percent commission could be worth 12 percent if a salesperson hits 75 percent of their quota in the year. Deduct Returns Commission typically gets paid on contract signing. If, after 90 days, the customer still hasn’t paid, for whatever reason, it’s normal to claw back any commission paid through commission payments on other deals. Deduct these at the end. Split Commissions

If a deal involves more than one salesperson, you can split the final commission as the company sees fit.

Remember this: the combination and sequence of these incentives matter when calculating sales commission. Be transparent with everyone about how all these factors work together to manage everyone’s expectations better.

Types of commission rate models

Since there are multiple commission rate models, it is important to understand how each model might affect your income. Here are the three main types of model:

Straight commission

The straight commission model makes your income reliant upon your sales. Here is how to calculate a straight commission:

Sales x Commission rate = Income

Base plus commission

The base plus commission model allows you to make sales commissions in addition to a base salary. This can be an attractive model for employees, but continual employment could rely on sales quotas. Here is how to calculate a base plus commission:

Base salary + (Sales x Commission rate) = Income

Draw against commission

In this model, you receive an advance payment from your employer that acts as a loan that you need to pay back or it will be subtracted from your income once you make sales. Here is how to calculate a draw against commission:

(Sales x Commission rate) – Advance pay = Income

Commission Calculations with XLOOKUP

We can also use the new XLOOKUP function for this calculation. It works very similar to VLOOKUP when finding the closest match.

Advantages with XLOOKUP

Advantages with XLOOKUP

There are two main differences and advantages with XLOOKUP:

  1. With XLOOKUP we specify the lookup array and return array as separate ranges. This is B4:B7 and D4:D7 in the image above. This is an advantage over VLOOKUP because we can insert or delete rows between columns B and D, and the formula will still work. When we add/delete columns within the table array of VLOOKUP, we have to manually change the column index number or use a formula to dynamically calculate it.
  2. XLOOKUP does NOT require the data to be sorted when using Exact match or next smaller/larger item for match mode. XLOOKUP will actually look for the next smaller/larger item and return the result from that row/column.VLOOKUP requires the data to be sorted when the last argument is TRUE. Typically you will want to sort your data for these types of tables anyways, but it’s good to know that you can just put new rate tiers at the bottom of the table and XLOOKUP will work.

Drawback of XLOOKUP

The main disadvantage with XLOOKUP is compatibility. Both you and all of the users of your file must be on a version of Microsoft/Office 365 that has XLOOKUP. It’s is NOT backward compatible or available on older versions of Excel.

Therefore, VLOOKUP works great in this scenario and is much easier to write than a nested IF formula.

How is Commission Rate Calculated?

How companies determine their commission rates is based on various factors, like average monthly sales, or total profits of the business, etc.

The following is a possible commission rate formula a company might use to determine their commission rate:

commission rate = variable sales compensation / quota

Variable sales compensation is the portion of an employee’s earnings that will be variable or subject to fluctuation based on commission. The quota is the revenue an employee needs to make in sales. Some other numbers are required to make this calculation, such as the earnings each employee is estimated to make.

Here is another example to see how to find the commission rate. A company projects sales employees to make $100,000 a year, where 50% of that pay is fixed, and the other 50% is variable. So the quota each sales employee needs to meet is $500,000.

First, we plug in the values:

commission rate = 50,000 / 500,000.

We are dividing the employee’s expected income by 2 to get the variable sales compensation value because half of the employee’s earnings are variable, which gives us a value of 50,000. Next, we simplify:

commission rate = 0.10

This company has a commission rate of 0.1, or 10%, per sale.

Some companies even place floors and/or ceilings on their commissions. Floors are the minimum number of sales an employee must make before they begin making their commission, and ceilings are a limit to how much an employee can make from the commission. The needs of each business vary, and some will pay out higher percentages for sales than others. Ceilings can be particularly effective in fostering a friendly sales environment founded on teamwork. Everyone is trying to help each other make enough sales to meet their quota rather than outperforming each other and being too competitive.

Profit, Revenue, and Expenses

An important factor in determining commission rates is by calculating their profits, which can tell a company how much it should be paying its employees. Using that information, companies can determine how much of that income is fixed and how much of it an employee will make from their commission. Put simply, profit is the difference between expenses and revenue and is determined by a simple formula:

profit = revenue – expenses

revenue is the earnings from selling a product. If a company sells shirts at $10 a shirt and sells 100 shirts, their revenue is $1,000. However, this does not tell us how much the company earned in profit. We need to determine the expenses of the business. Expenses are the costs of selling a product. This typically involves the cost of materials for the product. So, if each shirt that the company is selling costs $3 to make, and they made 100 shirts, they have $300 in expenses. When we calculate for profit, we subtract expenses from revenue, which in this example, gets us a total of $700 in total profits.

How Do You Use VLOOKUP with Closest Match?

The VLOOKUP with closest match technique can also be used for tax bracket calculations, price matching, etc.  What do you use this technique to calculate?

Please leave a comment below with any questions or suggestions.  Thanks!


Leave a Comment