Yousuf Khan
Microsoft Excel is a handy software. We use Excel functions and features for our educational, business, and other daily life purposes. Excel provides some cool functions to build payroll calculators in our workbooks. Most business organizations keep their monetary records in Excel. With this in mind, I will show you step-by-step procedures to create a payroll calculator in Excel.
Download Practice Workbook
You can download this workbook to practice by yourself.
What Is Payroll?
In simple terms, payroll is a process for giving wages to employees of an organization. It refers to collecting the lists of all the employees and their salaries & payment records, tracking work hours, monitoring tax information and other expenses, determining each employee’s benefits and compensations, etc. We can calculate payroll through Excel and monitor it from time to time.
Step-by-Step Procedures to Create a Payroll Calculator in Excel
Building a payroll calculator can be really cumbersome if we do not know the proper steps. In this article, you will see 7 steps to design a payroll calculator in Excel. Follow these steps carefully to quickly learn the process.
Step 1: Set up Employer Data
The first step of this procedure verily emphasizes setting up the employee dataset carefully. The dataset has to be errorless to make a proper payroll. With this purpose, follow the process.
- Make a correct header.
- Afterward, create 2 columns and type the sub-header.
- Furthermore, correctly input the employee name, accurate pay date, hourly rate, working hours & pay time.
- Thus, we get our dataset.
Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
Step 2: Input Overtime Working Hours
In the 2nd step, we will add additional working hours the employee worked that week. To do so, follow the process below.
- Type the subheader of overtime hourly rate and total overtime hours he worked.
- Also, input the overtime hourly rate and total overtime hours.
Read More: How to Calculate Payroll Overtime with Formula in Excel
Step 3: Include Employee Compensation
Similarly, in this step, we include employee benefits and compensations. For that, go through the following process.
- In row 12, add the performance bonus header and the amount.
- See the picture below to understand better.
Read More: How to Generate Payroll in Excel VBA (with Easy Steps)
Step 4: Calculate Gross Pay
In step 4, we will calculate the gross pay considering all the options. In this case, the Gross amount includes the total amount of basic pay, overtime pay, and other benefits. To do so, follow the points.
- First, in D14, write the formula,
=D8*D7+D10*D11+D12- See the picture below for a better understanding.
- Later, press Enter to obtain the result.
How does the formula work?
- Here, D8*D7 is a multiplication command. This command will multiply Hourly Pay and Total Hours to get the total payment amount.
- D10*D11 will multiply Overtime Hour Rate & Total Overtime Hours to see the total overtime amount.
- Finally, D8*D7+D10*D11+D12 will add the Performance Bonus with the obtained amounts to get the Gross Pay.
Read More: How to Make a Payroll System in Microsoft Excel with Payslip
Step 5: Insert Tax Information
On this occasion, we will add the employees’ taxes information and other expenses. To include these, learn the following process.
- Firstly, add FICA Tax, State Income Tax, and Insurance Amount as sub-headers.
- Next, input the tax amount in the right-side columns.
Step 6: Sum Gross Expenses
Previously, we have included tax information and other expenses of the employee. However, in this step, we will calculate the total gross expenses with some easy procedures. To calculate the gross expense, we need to sum up all the taxes and expense amounts. Let’s go through the given points.
- First of all, insert Gross Expense as the sub-header.
- Now, in D20, type the following formula
=D16+D17+D18- Finally, tap Enter.
- As a result, we obtain our gross expense.
Step 7: Calculate Net Payroll
In the last step, we will calculate the net payroll of the employee. Previously, we obtained the gross pay and gross expenses. Now, we will calculate the net payroll by subtracting them. Let’s follow the points below.
- To begin, in cell D22, insert the formula,
=D20-D14- This formula subtracts Gross Expense from Gross Pay.
- After that, press Enter.
- As a result, the net pay appears in our dataset.
- With this, we have built our payroll calculator.
Read More: Make a Payroll Accrual Calculator in Excel
Conclusion
In conclusion, we have discussed some easy steps to create a payroll calculator in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
