How to Calculate Bonus in Excel (5 Handy Methods)

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.

Download Practice Workbook

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

5 Methods to Calculate Bonus in Excel

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.

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.

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.

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

Read More: How to Calculate Commission in Excel Using IF Function

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.

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.

Read More: How to Calculate Bonus on Salary in Excel (7 Suitable Methods)

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.

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 2^nd part is for? Let’s tell the secret. This 2^nd 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.

Read More: How to Calculate Sales Commission Formula in Excel (3 Easy Ways)

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.

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.

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

Read More: How to Calculate Commission in Excel Using VLOOKUP

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.

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

• 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

• 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.

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.

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. Please visit our website ExcelDemy to explore more.

Related Articles

• Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)
• How to Calculate Tiered Commission in Excel (3 Easy Methods)
• Bonus Calculation as per Bonus Act in Excel (3 Suitable Examples)