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

Get FREE Advanced Excel Exercises with Solutions!

When you maintain a company, you have to give some bonus to your employees to make them more dedicated to your company. It is a trendy policy in every country. Employees who don’t feel good about their salaries must have lost motivation to work harder. To motivate them, you need to calculate the bonus as per the bonus act in Excel. In Microsoft Excel, you have a platform where you can easily calculate bonuses. This article will show every detail of bonus calculation as per the bonus act in Excel. I hope you find it really helpful and informative for your future purposes.


Overview of Bonus Calculation

The bonus can be defined as a reward that is actually paid to the employee to have better dedication towards the work. These rewards can improve the productivity of a certain employee. To calculate a bonus for any company, you need to set a standard salary which is the average salary of this company. Let, the average salary of any USA-based company is $5500. To calculate the bonus, we have two different approaches.

  • If the salary is less than $5500, the bonus will be calculated using their actual basic salary. For example, if someone gets $3500, his bonus will calculate using this $3500. As per the bonus act, the bonus will be 33% of the basic salary.

Formula:

Bonus = Basic Salary * 8.33%

            = $3500 * 8.33%

            = $291.55

  • Then, if the salary is greater or equal to $5500, the bonus will be calculated using the basic salary of  $5500. For example, if someone gets $7000, his bonus will be calculated using a basic salary of  $5500.

Formula:

Bonus = Basic Salary * 8.33%

            = $5500 * 8.33%

            = $458.15


Bonus Calculation as per Bonus Act in Excel: 3 Suitable Examples

To calculate the bonus as per the bonus act in Excel, we have found three different approaches through which you can easily calculate the bonus. To calculate bonus as per bonus in Excel, we can use multiple functions which we will show you in the upcoming sections. Before using any function to calculate a bonus, we need to set the salary range and corresponding bonus. That means, if the salary is in that range, the employee will get that salary. To show all the methods effectively, we take a dataset that includes some employees’ names, ID No., and their basic salaries.

Bonus Calculation as per Bonus Act in Excel


1. Using IFS Function

Our first method is based on the IFS function. This function is mainly applicable when you have multiple conditions. To use this function effectively, follow the following steps carefully.

Steps

  • At first, you need to set a salary range.
  • From that salary range, calculate the bonus.
  • To calculate the bonus, we take the lower salary. That means for the $3000-$3499 range, select $3000 as the lower range.
  • Then, select cell D5.

  • Write down the following formula in the formula box.

=C5*8.33%

  • Press Enter to apply the formula.

  • Then, drag the formula down the column to cell D9.

Bonus Calculation as per Bonus Act in Excel

  • When the basic salary is greater or equal to $5500, we always take the basic salary of $5500 to calculate the bonus. Our last four salary ranges are above $5500 or equal, in that case, we use a basic salary of $5500.
  • Select cell D10.

  • Then, write down the following formula in the formula box.
=$C$10*8.33%

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the cell C13.

Bonus Calculation as per Bonus Act in Excel

  • Then, to calculate the bonus of our dataset, select cell E5.

  • Write down the following formula in the formula box.
=IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6,D5<4500,Bonus!$D$7,D5<5000,Bonus!$D$8,D5<5500,Bonus!$D$9,D5<6000,Bonus!$D$10,D5<6500,Bonus!$D$11,D5<7000,Bonus!$D$12,D5>=7000,Bonus!$D$13)
  • Then, press Enter to apply the formula.

Bonus Calculation as per Bonus Act in Excel

  • After that, drag the Fill Handle icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

Bonus Calculation as per Bonus Act in Excel

  • Finally, we want to calculate the overall amount the employee will get after the bonus.
  • For that, at first, select cell F5.

  • Then we use the SUM function to calculate the overall salary.
  • Write down the following formula.
=SUM(D5:E5)

Bonus Calculation as per Bonus Act in Excel

  • Press Enter to apply the formula.

Bonus Calculation as per Bonus Act in Excel

  • Then, drag the Fill Handle icon down the column.
  • This will be our required bonus calculation as per the bonus act in Excel using the IFS function.

Bonus Calculation as per Bonus Act in Excel

🔎 Breakdown of the Formula

IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6,D5<4500,Bonus!$D$7,D5<5000,Bonus!$D$8,D5<5500,Bonus!$D$9,D5<6000,Bonus!$D$10,D5<6500,Bonus!$D$11,D5<7000,Bonus!$D$12,D5>=7000,Bonus!$D$13)

⇒ IFS(D5<3500,Bonus!$D$5 ……): This formula denotes that if cell D5 is less than $3500, then, it will go to the sheet called Bonus and returns the value of cell D5 of that Bonus sheet. That means if the salary is less than $3500, it will go to the Bonus sheet and return with the value of cell D5 which is the bonus of that range of salaries. If this condition doesn’t meet, it will go to the next condition.

⇒ IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6 ……): If the cell D5 is not less than $3500 then it will go to the next condition where it checks if the cell D5 is less $4000 then, it will go to the sheet called Bonus and returns the value of cell D5 of that Bonus sheet. That means if the salary is less than $4000, it will go to the Bonus sheet and return with the value of cell D5 which is the bonus of that range of salaries. If this condition doesn’t meet, it will go to the third condition.

⇒ The cell D5 will go through all the conditions until it meets the condition. When it meets the condition, it will stop in there and returns the bonus value from the Bonus sheet.


2. Applying VLOOKUP Function

Our next method is based on the VLOOKUP function. This function is mainly used to search the value across the column. When you use a large dataset or multiple datasets, in that case, to search some values across the column, we use the VLOOKUP function. To calculate bonus as per bonus act in Excel using the VLOOKUP function, follow the steps carefully.

Steps

  • At first, you need to set a salary range.
  • From that salary range, calculate the bonus.
  • To calculate the bonus, we take the lower salary. That means for the $3000-$3499 range, select $3000 as the lower range.
  • Then, select cell D5.

  • Write down the following formula in the formula box.
=C5*8.33%

  • Press Enter to apply the formula.

  • Then, drag the formula down the column to cell D9.

Bonus Calculation as per Bonus Act in Excel

  • When the basic salary is greater or equal to $5500, we always take the basic salary of $5500 to calculate the bonus. Our last four salary ranges are above $5500 or equal, in that case, we use a basic salary of $5500.
  • Select cell D10.

  • Then, write down the following formula in the formula box.
=$C$10*8.33%

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the cell C13.

Bonus Calculation as per Bonus Act in Excel

  • To calculate the bonus using the VLOOKUP function, select cell E5.

  • Write down the following formula in the formula box.
=VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)

Bonus Calculation as per Bonus Act in Excel

  • Then, press Enter to apply the formula.

  • After that, drag the Fill handle icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

Bonus Calculation as per Bonus Act in Excel

  • Finally, we want to calculate the overall amount the employee will get after the bonus.
  • For that, at first, select cell F5.

  • Then we use the SUM function to calculate the overall salary.
  • Write down the following formula.
=SUM(D5:E5)

Bonus Calculation as per Bonus Act in Excel

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.
  • This will be our required bonus calculation as per the bonus act in Excel using the VLOOKUP function.

Bonus Calculation as per Bonus Act in Excel

🔎 Breakdown of the Formula

VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)

This formula denotes that at first, it searches cell D5. Then it goes to the sheet called Bonus and goes through the whole range of cells from C5 to D13. After that, it moves to column 2 (Col_index_num) where they find out the approximate match which is why we use True for an approximate match. The function returns the value for which it matches the Bonus worksheet range of cells. Here, it returns the bonus of $333.2.


3. Use of LOOKUP Function

Our last method is based on the LOOKUP function. This function also provides the same output as the VLOOKUP function but in a different way. To calculate the bonus as per the bonus act in Excel using the LOOKUP function, you need to follow the steps carefully.

Steps

  • At first, you need to set a salary range.
  • From that salary range, calculate the bonus.
  • To calculate the bonus, we take the lower salary. That means for the $3000-$3499 range, select $3000 as the lower range.
  • Then, select cell D5.

  • Write down the following formula in the formula box.
=C5*8.33%

  • Press Enter to apply the formula.

  • Then, drag the formula down the column to cell D9.

Bonus Calculation as per Bonus Act in Excel

  • When the basic salary is greater or equal to $5500, we always take the basic salary of $5500 to calculate the bonus. Our last four salary ranges are above $5500 or equal, in that case, we use a basic salary of $5500.
  • Select cell D10.

  • Then, write down the following formula in the formula box.
=$C$10*8.33%

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the cell C13.

Bonus Calculation as per Bonus Act in Excel

  • To calculate the bonus using the LOOKUP function, select cell E5.

  • Write down the following formula in the formula box.
=LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13)

Bonus Calculation as per Bonus Act in Excel

  • Then, press Enter to apply the formula.

  • After that, drag the Fill Handle icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

Bonus Calculation as per Bonus Act in Excel

  • Finally, we want to calculate the overall amount the employee will get after the bonus.
  • For that, at first, select cell F5.

  • Then we use the SUM function to calculate the overall salary.
  • Write down the following formula.

=SUM(D5:E5)

  • Press Enter to apply the formula.

Bonus Calculation as per Bonus Act in Excel

  • Then, drag the Fill Handle icon down the column.
  • This will be our required bonus calculation as per the bonus act in Excel using the LOOKUP function.

Bonus Calculation as per Bonus Act in Excel

🔎 Breakdown of the Formula

LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13)

This function demonstrates that at first, it will look up cell D5. Then, go through the range of cells from the Bonus worksheet and find out possible matches with cell D5. If it gets any match then, it returns the value corresponding to cell D5. Here, it returns $333.2 for the salary of $4000.


Download Practice Workbook

Download this practice workbook.


Conclusion

We have shown three different and effective methods to show bonus calculation as per the bonus act in Excel. These methods include several Excel functions. Here, we have taken a range of salaries. Within these ranges, bonus changes. All the processes are fairly easy and applicable for your day-to-day purposes. I  hope you find this article very informative and gain some valuable knowledge regarding this issue.


<< 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.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo