How to Calculate Bonus in Excel (5 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article shows how to calculate the bonus in Excel. More precisely, here we will calculate the sales bonus in Excel. Normally, employees are rewarded for meeting job goals, doing particularly well, and completing milestones through sales bonus schemes. Here, we will take you through 5 easy and convenient methods on how to calculate the bonus in Excel.


How to Calculate Bonus in Excel: 5 Handy Methods

Previously mentioned that we intended to calculate sales bonuses in Excel. But, do you have knowledge of what a sales bonus is and why it is essential? Let’s go through this matter first.

A sales bonus is a monetary reward given to a sales representative who meets or surpasses a pre-determined target. For example, if he had a target of $10,000 daily sales and he made it $12,000, he will get a percentage of the sales amount as a bonus. It’s very important in a sales company because it :

  • Boosts employee motivation
  • Increases target achievement
  • Encourages healthy Competition
  • Creates loyalty of employees to the company
  • Reduces turnover of employees

Here we’ve got a dataset of the Daily Sales List of 7 Sales Reps and their consecutive amount of Daily Sales. Also, have a Sales Hurdle of $15,000 and a Bonus Percentage of 10%, which means they have to exceed the minimum limit of $15,000 sales target and for the remaining amount, they will be paid with a 10% bonus.

How to Calculate Bonus in Excel Dataset

Now, we will calculate their sales bonus based on those criteria using a handful of different methods.


1. Using IF Function to Calculate Bonus in Excel

The first method we’re using here is using of the IF function. Using this function we can apply a logical test that sales reps with higher than $15,000 will get a 10% bonus and those who have sales lower than $15,000 won’t get any incentive. To do so, follow our steps below.

Steps:

  • In the beginning, select cell D8, type down the formula below, and press ENTER.
=IF(C8>$C$4,(C8-$C$4)*$C$5,0)

How to Calculate Bonus in Excel Using IF Function

Note: Here “” sign means zero or not applicable.

Formula Breakdown

With the IF function, we have applied a logical test that if the daily sales of any sales rep are greater than or equal to $15,000, he’ll get a 10% bonus on the remaining amount. Which is subtracting $15,000 from his daily sales. On the other hand, if he failed to achieve the target of $15,000, then he won’t be eligible for any bonus.

  • Then, we used the Fill Handle tool to get the remaining results of column D. Clearly, you can see that in the above image.

Similarly, you can do it in the case of false values. We are just giving it to increase your thinking capability. To look at something in alternative ways.

In our previous method, we applied logical tests to get bonuses in true values and those who weren’t applicable came through the false values. But, we will apply the opposite logic in this method. Here, those who aren’t eligible for a bonus come through true values, and others will come through true values. Let’s see the procedure as follow.

Steps:

  • At first, select cell D8 as before, write down the formula below, and press ENTER.
=IF(C8<$C$4,0,(C8-$C$4)*$C$5)

How to Calculate Bonus in Excel Using IF Function

We can see that the results come here are as same as those applied in the case of true values.


2. Applying MAX Function to Calculate Bonus in Excel

Another tricky way to calculate bonuses in Excel is to apply the MAX function. Follow our work steps to calculate in this way.

Steps:

  • Firstly, select cell D8, paste down the formula as follows, and press ENTER.
=MAX((C8-$C$4)*$C$5,0)

How to Calculate Bonus in Excel Using MAX Function

Formula Breakdown

Here MAX function shows the maximum between the two numbers given as arguments. In our formula, MAX((C8-$C$4)*$C$5,0) the first number is the amount of Bonus which is before the comma, and the second number is zero. When the Daily Sales are greater than the Sales Hurdle, then the bonus becomes a positive number. Otherwise, the bonus becomes negative. So, between a positive number and zero, obviously, the positive number is bigger and as a result, it shows in the cell. In contrast, between a negative number and zero, zero is bigger, so then, it displays in the cell.


3. Engaging Boolean Logic

Another interesting way to calculate bonus in Excel is to use the Boolean logic in our formula. Go after the steps below we’ve done.

Steps:

  • First, select cell D8, enter the below formula into the Formula Bar, and then, press ENTER.
=(C8-$C$4)*$C$5*(C8>=$C$4)

How to Calculate Bonus in Excel Engaging Boolean Logic

Formula Breakdown

After (C8-$C$4)*$C$5 this part of the formula, we joined (C8>=$C$4) that with a multiplication sign. Easily we all can understand that the first part is for calculating the bonus amount. But do you have any idea what the 2nd part is for? Let’s tell the secret. This 2nd part is for applying Boolean logic to the formula. We know that, in Boolean algebra,

True = 1

False = 0

So, the logic we put here is that the daily sales amount has to be greater than or equal to the sales hurdle. If any sales rep fulfills the condition, then the result will be true, and as a result, his bonus will be multiplied by 1. On the contrary, if someone fails to meet the criteria, his negative bonus amount will be multiplied by 0 accordingly.


4. Implementing VLOOKUP Function

The most effective and time-saving among our entire methods is to use the VLOOKUP function. Now, using this function we’ll solve our problem.

For this method, we made a slight change in our dataset. First, get a look into it.

How to Calculate Bonus in Excel Using VLOOKUP Function

Previously, we fixed our sales hurdle at $15,000 with a 10% bonus. But now we have added another limit of $10,000 with a bonus percentage of 5%. Besides, we decreased the daily sales amount of Nick and Leon.

Now, follow our footsteps as follows.

Steps:

  • At the very beginning, select cell D10, type down the formula below, and press ENTER.
=VLOOKUP(C10,$B$5:$C$7,2)*(C10-$B$6)

How to Calculate Bonus in Excel Implementing VLOOKUP Function

  • Then, we used the Fill Handle tool and by dragging it down got the other values of column D.

5. Employing VBA Code to Calculate Bonus in Excel

A further way to solve this problem is to employ the VBA code. Applying VBA is always an amusing way. Follow our instructions carefully below.

Steps:

  • Right-click on the Sheet name and select View Code.

Applying VBA Code

  • Instantly, the Microsoft Visual Basic for Applications window opens. From Toggle Folders, right-click on Sheet7 (VBA) > select Insert > Module.

Applying VBA Code

  • It opens a code module, where paste the code below. Then, click on the Run button or press F5.
Sub Bonus()
Dim LastRow As Long
With ThisWorkbook.Worksheets("VBA")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("D8:D" & LastRow) = "=IF(C8>=$C$4,(C8-$C$4)*.1,0)"
End With
End Sub

Applying VBA Code

  • Now close the code module and return to the worksheet. You will be amazed to see that the cells of column D are automatically filled up with the correct result as our method 1. Select cell D8 and you can see the exact formula which we used in our method 1 in the Formula Bar.

Applying VBA Code

In our VBA code, we used the exact same formula as method 1. For using the IF function here, the results came is also the same.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


<< Go Back to Commission Bonus | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo