How to calculate bonuses and commissions in Excel

Everyone loves a bonus, but sometimes calculating the bonus can be a little complicated – at first. Here’s how to calculate how much in Microsoft Excel you or your employees will pay.

commission check

Photo: iStock / AndreyPopov

If you’ve ever had a job that earned a commission, you know how hard it can be to calculate. to me Flat The commission pays the same price regardless of the total. For example, if the rate is 2%, you get 2% whether your totals are $1,000 or $1,000,000. to me gradual Commission (usually) encourages sales, because the better you perform, the higher the percentage.

In this article, we are going to build a simple multilevel system in Microsoft Excel that changes the rate based on the total. Each teacher raises the rate. Although we use the term commission, you can use the same structure for bonuses. It’s the best place to start when dealing with graded regimens, and it won’t require any acetaminophen.

We see: 60 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can work on previous versions. For your convenience, you can download the .xlsx demo file. This solution is compatible with the web version.

The simplest hierarchy

A simpler hierarchy might not be a true hierarchy at all from an accounting point of view, but we’ll treat it as such. For example, let’s say the first rate of 2% is paid out totaling $50,000. Sales over $50,000 get 2.5%, and totals over $80,000 get 3%. It is important to note at this point that the percentage is paid All Totals once the criterion is met – that’s why some may not consider this a true tiered system.

We see: Windows 10: Voice Command Menus for Speech Recognition and Dictation (Free PDF) (TechRepublic)

Business rules are enforced by the company that pays commissions. There will be no single rule for all of them. It is not difficult, but you should know the rules for preparing this. For this reason, I would encourage you to work dynamically so you can easily update when key and percentage values ​​change.

If you’re stressed by the idea of ​​creating a set of unique expressions that represent your organization’s level system, don’t worry. Start by writing it in words, so everyone is on the same page Here are the tiered system rules we’ll apply in this simple demo:

  • Monthly totals under $50,000 are multiplied by .02; $50,000 is the first stage.
  • Monthly totals under $80,000 are multiplied by 025; $80,000 is the second stage.
  • Monthly totals over $80,000 are multiplied by .03; With only three levels, there is no third teacher.

Once you break it down this way, it becomes much easier to distinguish expressions. Now, let’s get started.

How to create a level table

My first tip is Not Enter key and percentage values ​​in your expressions. Instead, create and reference input cells. This way, you can easily adapt the sheet when the feature and percentage values ​​go up (or unfortunately, down).

We see: Technical Budgets for 2021: A Handbook for Operations Managers (Free PDF) (TechRepublic)

as you see in Figure A, I’ve created a simple layer table that reflects the prices from the previous section. (the paper in Figure A Simple on purpose.) Apply Accounting Format to F1:F3 and Number Format to G1:G3. You may want to build the commission table on another sheet, but I show it all on one sheet so you can see the solution and the original data together. Our next step is to add the expressions that collect the data and the level table to return the monthly commissions for all employees. That’s why the level table is slightly offset.

Figure A

exceltieredcommissions-a.jpg

Use the input cells for key and percentage values.

How to create an expression to calculate commissions

If you are looking for tiered commission solutions, you may feel a little frustrated and want to give up. In our case, the solution is much simpler than most of what you’ll find. It’s not because I’m very witty. Instead, we’re looking at a simple problem. Simply put, we need an expression that compares the monthly total to the nearest key value in column F of the level table and returns the corresponding rate in column G. The VLOOKUP() function can do this. Then, it’s simply a matter of multiplying that rate by your monthly total.

First, we need a new table. Figure B Displays a matching table object without data or expressions. Simply copy the table and remove the data. Enter the following expression in G7 (commission table) and copy it to fill in the remaining data range (G7:H15):

= VLOOKUP(C7,$F$1:G$3.2) *C7

Commissions, like magic, fill the commission table, as you can see in Figure C.

Figure B

exceltieredcommissions-b.jpg

Create a table of commission values.

Figure C

exceltieredcommissions-c.jpg

Commission expression with commission values.

Let’s choose the expression. First, the VLOOKUP() function compares a monthly total in the sales table, $52,008 (in C7). The nature of this function helps us because it doesn’t have to find an exact match, and it keeps evaluating the list until it encounters a value greater than the original value. In this case, that amount is $80,000 – $80,000 > $52008. Argument 2 tells the function to go to one of the features in column F and return this value, which is 0.025. Multiplies the last part of the expression .025 by $52,008 to return $1,300.20.

Now, let’s break it down as an expression:

= VLOOKUP (52008, USD 1: USD 3.2 USD) * C7 =

=VLOOKUP(52008,{0,0.02;50000,0.025;80000,0.03},2)*C7 =

= 0.025 * 52008 =

= 1300.20

We see: How to Use Sheet View for More Flexible Collaboration in Excel (TechRepublic)

How to change commissions in an Excel table

Since landmarks and percentages are input cells, you can quickly update all commissions, by changing the values ​​in the level table, as shown in figure d. Prices have gone up a bit! By changing the value of one or all of them, you can instantly update the accrued commissions. When changing these values, remember that the key values ​​in column F must be in ascending order. In addition, table objects automatically ingest new rows in the two table objects.

figure d

exceltieredcommissions-d.jpg

Change a milestone and some modifiers to update the schedule.

keep following

This solution turns out to be simpler than you think. If you are expecting a complex and complex expression, I am happy to surprise you. This easy solution barely touches the surface on the subject of the tiered commission system. In a later article, we’ll make things more difficult.

see also