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

Looking for ways to know how to calculate bonus on salary in Excel? Then, this is the right place for you. Sometimes, employees are given bonuses depending on their performance, salary, or special occasions. Bonuses depending on their salary can be calculated in Excel using some easy methods. Here, you will find 7 different step-by-step explained ways to calculate bonus on salary in Excel.

Download Practice Workbook

7 Ways to Calculate Bonus on Salary in Excel

Here, we have a dataset of some employees containing their Names and Salary. The fixed amount of Bonus based on their Salary Range is also provided here. We will use this dataset to calculate their Bonus using different methods in Excel.

1. Using Nested IF Function to Calculate Bonus on Salary

In the first method, we will use the Nested IF function to calculate bonus on salary.

Follow the steps given below to do it on your own.

Steps:

• In the beginning, select Cell D5.
• Then, insert the following formula.

Here, we used the Nested IF function where we used 5 logical_test to check the Salary range for Bonus. First, we are checking if the Salary of C5 is greater than or equal to H9 or not. Next, as value_if_true we used the H9 cell and for value_if_false used another IF function to check another range H8. By following this we used 5 logical_test within 5 IF functions to calculate the Bonus of all ranges Salary.

• Now, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Finally, you will get the Bonus calculated based on Salary.

Read More: Bonus Calculation as per Bonus Act in Excel (3 Suitable Examples)

2. Employing IFS Function to Calculate Bonus on Salary in Excel

We can also calculate bonus on salary in Excel using the IFS function. Go through the following steps to do it on your own dataset.

Steps:

• First, select Cell D5.
• Then, insert the following formula

Now, we used the IFS function and declared 5 logical_test within 1 function. First, as a logical_test check if the value of C5 cell is greater than or equal to H9 cell or not. Then, as a value_if_true used H9 cell, and in value_if_false used another logical_test. By following this structure, we checked all existing salary ranges to calculate Bonus.

• After that, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, you can get the value of Bonus calculated using the IFS function.

3. Using VLOOKUP Function to Calculate Bonus on Salary

Now, we will use the VLOOKUP function to calculate bonus on salary. VLOOKUP is an Excel function that allows you to look up data in a vertically organized table.

Steps:

• In the beginning, select Cell D5.
• Then, insert the following formula.

Here, in the VLOOKUP function, we selected Cell C5 as lookup_value, selected Cell G5:H9 as table_array, 2 as column_index_number from where the Bonus amount will be extracted. Then, TRUE as range_lookup to get the Approximate Match.

• Now, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Finally, you will get the Bonus calculated based on Salary using the VLOOKUP function.

4. Applying LOOKUP Function to Calculate Bonus on Salary

In the fourth method, we will use the LOOKUP function to calculate bonus on salary. The function returns the corresponding value from another one-row or one-column range after doing a rough match lookup in a one-row or one-column range.

Steps:

• In the beginning, select Cell D5.
• Then, insert the following formula.

Here, in the LOOKUP function, we selected Cell C5 as lookup_value, selected Cell G5:G9 as lookup_vector and H5:H9 as result_vector.

• Now, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Finally, you will get the Bonus calculated based on Salary using the LOOKUP function.

Similar Readings

• How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
• Create Salary Slip Format with Formula in Excel Sheet
• Create Tally Salary Slip Format in Excel (With Easy Steps)
• How to Calculate Commission in Excel Using VLOOKUP

5. Using MAX Function to Calculate Bonus on Salary in Excel

Here, we have a dataset containing the Names and Daily Sales of some employees. Hurdle is provided as $300 and Bonus as 20%. Now, we will use the Max function to calculate bonus on salary in Excel for this dataset.

Follow the given steps below to do it on your own.

Steps:

• First, select Cell D5.
• Then, insert the following formula.

Here, we first subtracted Cell C5 by Hurdle and then, multiplied the value by Bonus then used it as number1 in the MAX function. We also provided 0 as number2 for comparison purposes. The function will return whichever is maximum.

• After that, press ENTER.
• Now, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, you can get the value of Bonus calculated using the MAX function.

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

6. Applying Arithmetic Operators to Calculate Bonus on Salary

We can also calculate bonus on salary by applying Arithmetic Operators in Excel. Go through the following steps to do it on your own.

Steps:

• In the beginning, select Cell D5.
• Next, insert the following formula.

Here, we applied an Arithmetic Operator named Boolean. We first subtracted Cell C5 from Hurdle and then, multiplied the value by Bonus. Then we used the logical_operator “greater than equal” to check if the value of C5 cell is greater than or equal to G9 cell or not.

• Then, press ENTER.
• Now, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, you can get the value of Bonus calculated using Arithmetic Operators.

7. Use of Generic Formula to Calculate Bonus on Salary in Excel

In the last method, we will use the Generic formula to calculate bonus on salary in Excel. Here, we have a dataset containing the Name, the value of Basic Salary+DA and No of Months Worked. Bonus is given as 8.33%.

Follow the steps to do it on your own dataset.

Steps:

• In the beginning, select Cell D5.
• Then, insert the following formula.

Here, we multiplied the value of Basic Salary+DA, No of Months Worked and Bonus percentage to get the resultant amount of Bonus for the employees.

• After that, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Finally, you will get the Bonus calculated based on Salary using the Generic formula.

Read More: How to calculate salary increase percentage in Excel [Free Template]

Practice Section

You will get a dataset like an image given below in this article to practice on your own.

Conclusion

So, in this article, you will find 7 ways to calculate bonus on salary in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Related Articles

• How to Make Salary Sheet in Excel with Formula (with Detailed Steps)
• Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
• How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
• Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)
• How to Calculate Tiered Commission in Excel (3 Easy Methods)