How to Calculate Gross Pay & Overtime Pay in Excel

Save
Gross pay is your total pay before taxes are withheld.
Gross pay is your total pay before taxes are withheld. (Image: Comstock/Comstock/Getty Images)

You can use Microsoft Excel to calculate your wages from overtime hours and your gross pay. Gross pay is the total amount of money you earn before any deductions are withheld from your paycheck, such as taxes or insurance. According to the U.S. Department of Labor, overtime hours are hours an employee works in excess of 40 hours in a regular work week. A company whose employees are covered under the Fair Labor Standards Act (FLSA), which includes most public and private employees, must pay its employees at least one and one-half times their regular rate of hourly pay for overtime hours.

Click in cell A1, type the number of regular hours you worked in a week up to 40 and press “Enter.” For example, click in cell A1, type “40” and press “Enter.”

Click in cell A2, type “=,” the total number of hours you worked in a week, “-40” and press “Enter.” This calculates your total overtime hours worked in a week. For example, click in cell A2, type “=48.5-40” and press “Enter.” Excel shows 8.5 in cell A2, which represents your total overtime hours worked in a week.

Click in cell B1, type your regular hourly rate of pay and press “Enter.” For example, click in cell B1, type “$20” and press “Enter.”

Click in cell B2, type “=B1_” and the multiple your overtime hourly pay is of your regular hourly pay and press “Enter.” Type “1.5” as your overtime multiple to represent one and a half times your regular hourly pay, or type “2” as your multiple to represent double your regular hourly pay. For example, click in cell B2, type “=B1_1.5” and press “Enter.” Excel multiplies your regular hourly pay in cell B1 by 1.5 to calculate your overtime hourly rate of pay. Excel shows $30 in cell B2.

Click in cell C1, type “=A1*B1” and press “Enter.” Excel multiplies the value in cell A1 by the value in cell B1 to calculate your total regular hourly pay for the week. In the example, Excel shows $800 in cell C1.

Click in cell C2, type “=A2*B2” and press “Enter” to calculate your total overtime pay for the week. In the example, Excel shows $255 in cell C2.

Click in cell C3, type the total amount of any bonuses, commissions or other pay you earned in the week and press “Enter.” For example, click in cell C3, type “$100” and press “Enter.”

Click in cell C4, type “=SUM(C1:C3)” and press “Enter” to calculate your gross pay. Excel calculates the sum of the values in the range of cells from C1 to C3, which include your regular hourly pay, overtime pay and other pay. In the example, Excel shows $1,155 in cell C4, which represents your gross pay.

Tips & Warnings

  • If you worked 40 hours or less in a week, skip Steps 2, 4 and 6 because you do not have any overtime for that week.

Related Searches

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

4 Credit Myths That Are Absolutely False

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!