Payroll Exercises in Excel: 5 Exercises

Download Practice Workbook

Download the Excel file here.

Problem Overview

The first dataset shows pay information for the month of November about twenty employees at a multi-state company. The month had 19 working days, and the company pays its employees hourly on a monthly basis. Overtime, paid time off (PTO), and regular working hours are provided.

Problem Overview

Exercise 1 – Find the Gross Pay.

You want to calculate the gross pay. The working hours and the hourly pay are provided in the “Reference table” sheet. Additionally, the overtime pay is 1.25 times the regular pay and the payment for the paid time off is the same as the regular pay.

Solution: Use the VLOOKUP function to find pay rates. The hourly pay is different for different employees.

Exercise 2 – Calculate the Tax Deduction Amount.

The employees work in five different states. The state tax rate is different. Use the tax rates from the “Reference table” sheet to find the tax deduction amount.

Solution: There are four kinds of taxes in this exercise. Apply the state tax using the VLOOKUP function.

Exercise 3 – Find the Net Pay:

You want to calculate the net pay..

Solution: Use the INDEX MATCH formula, input the benefits & other deductions. Find the total deductions. Calculate the net pay.

Exercise 4 – Calculate Working Hours.

The dataset below, contains daily office entry time and exit time of “Ross Johnson” in December. Your task is to find worked time, late entry (in hour) if any, and overtime.

Solution: Employees must enter the office before 10 AM. More than 8 hours of work is considered overtime. Duration is in hour:minute format. Multiply it by 24 to get the hourly value.

The GIF below shows the formula to find the total worked hours.

Exercise 5 – Calculate the Monthly Pay of an Employee.

Calculate the actual pay of the employee in December.

Solution: If the value of worked time is more than 8 hours, consider overtime pay. If the employee enters the office after 10 AM, a penalty will be applied. A flat 12% tax is applicable to the gross pay. The actual pay = 0.88*daily. Add daily pay to find the total monthly pay.

The following image shows the solution to the first exercise.

Payroll Exercises Excel