In this article we will demonstrate several methods to calculate employee Bonuses in accordance with the USA’s Bonus Act in Excel.
Overview of the Bonus Calculation
A bonus can be defined as a reward that is paid to an employee as a work incentive. The intention of these rewards is to improve the productivity of certain employees. To calculate a bonus for any company in terms of the Bonus Act, we need to set a standard salary which is the average salary of the company. If we suppose that the average salary of a USA-based company is $5500, then we have two different approaches to calculate the bonus:
- If the salary is less than $5500, the bonus will be calculated using their actual basic salary. For example, if Dave earns a basic salary of $3500, his bonus will be calculated at a rate of 8.33% of the basic salary, $3500.
Formula:
Bonus = Basic Salary * 8.33%
= $3500 * 8.33%
= $291.55
- However if the salary is greater or equal to $5500, the bonus will be calculated using a basic salary of $5500, not the full basic salary amount. For example, if Dave gets $7000, his bonus will be calculated using a rate of 8.33% on a basic salary of $5500.
Formula:
Bonus = Basic Salary * 8.33%
= $5500 * 8.33%
= $458.15
Bonus Calculation as per the Bonus Act in Excel: 3 Suitable Examples
We have three different approaches by which to calculate a bonus as per the Bonus Act in Excel. Before using various functions to calculate bonuses, we need to set the salary range and corresponding bonus. To demonstrate our methods, we’ll use the following dataset containing some employee Names, ID No’s, and Basic Salaries.
Method 1 – Using the IFS Function
The IFS function is mainly applicable when there are multiple conditions.
Steps:
- To start with, set a salary range from which to calculate the bonus as in the image below.
To calculate the bonus, we use the Lower End salary amount, i.e. for the $3000-$3499 range, we’ll select $3000 as the lower range.
- Select cell D5.
- Enter the following formula in the formula box:
=C5*8.33%
- Press Enter to apply the formula.
- Use the Fill Handle to drag the formula down the column to cell D9.
When the basic salary is greater than or equal to $5500, we always take the basic salary of $5500 to calculate the bonus. Our last four salary ranges are equal to or above $5500.
- Select cell D10.
- Enter the following formula in the formula box:
=$C$10*8.33%
- Press Enter to apply the formula.
- Drag the Fill Handle icon down to cell C13.
- Now, to calculate the bonuses for our dataset, select cell E5.
- Enter 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)- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
Finally, we’ll calculate the overall amount the employee will receive after the bonus.
- Select cell F5.
We’ll use the SUM function to calculate the overall salary.
- Enter the following formula:
=SUM(D5:E5)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
Our required bonuses are calculated as per the Bonus Act using the IFS function.
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 ……): If cell D5 is less than $3500, then the value of cell D5 in the Bonus sheet will be returned. If this condition is not met, the formula jumps to the next condition.
⇒ IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6 ……): If cell D5 is not less than $3500 then the formula checks if cell D5 is less $4000. If TRUE, it returns the value of cell D5 in the Bonus sheet. If this condition is not met, it will jump to the third condition.
⇒ The formula will cycle through all the conditions in the same manner until the condition is met, whereupon the formula will end, returning the corresponding bonus value from the Bonus sheet.
Method 2 – Using the VLOOKUP Function
The VLOOKUP function is mainly used to search for a value within a column.
Steps:
- As in the first method, set a salary range from which to calculate the bonuses.
- Select cell D5.
- Enter the following formula in the formula box:
=C5*8.33%
- Press Enter to apply the formula.
- Use the Fill Handle to drag the formula down the column to cell D9.
Again, our last four salary ranges are above $5500 or equal, so we’ll use a basic salary of $5500 in these cases.
- Select cell D10.
- Enter the following formula in the formula box:
=$C$10*8.33%
- Press Enter to apply the formula.
- Drag the Fill Handle icon down to cell C13.
- To calculate the bonus using the VLOOKUP function, select cell E5.
- Enter the following formula in the formula box:
=VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
- Finally, to calculate the overall amount the employee will receive after the bonus, select cell F5.
- Enter the following formula:
=SUM(D5:E5)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
Our required bonuses are calculated as per the Bonus Act using the VLOOKUP function.
Breakdown of the Formula
VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)
This formula searches for the value in cell D5 within the range of cells C5:D13 in the sheet called Bonus. If it finds an approximate match (specified by the 3rd argument, TRUE), then it returns the corresponding value from column 2 (the Col_index_num). Here, the returned bonus is $333.2.
Method 3 – Using the LOOKUP Function
The LOOKUP function provides the same output as the VLOOKUP function but in a different way.
Steps:
- Aa above, set a salary range from which to calculate the bonuses.
- Select cell D5.
- Enter the following formula in the formula box:
=C5*8.33%
- Press Enter to apply the formula.
- Drag the formula down the column to cell D9.
- Select cell D10.
- Enter the following formula in the formula box:
=$C$10*8.33%
- Press Enter to apply the formula.
- Drag the Fill Handle icon down to cell C13.
- To calculate the bonus using the LOOKUP function, select cell E5.
- Enter the following formula in the formula box:
=LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
Finally, we calculate the overall amount the employee will get after the bonus.
- Select cell F5.
- Enter the following formula:
=SUM(D5:E5)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column.
Our required bonuses are calculated as per the Bonus Act using the LOOKUP function.
Breakdown of the Formula
LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13)
This function looks up the value of cell D5 in the specified range of cells in the Bonus worksheet. If a match is found, the corresponding value in column D of the Bonus sheet is returned. Here, it returns $333.2 for the salary of $4000.
Download Practice Workbook
<< Go Back to Commission Bonus | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
