How to Create Payroll Calculator in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Set up Employer Data

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.

Input Overtime Working 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.

Include Employee Compensation to Create Payroll Calculator

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.

Calculate Gross Pay to Create Payroll Calculator

  • 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.

Sum Gross Expenses to Create Payroll Calculator

  • 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.

Calculate Net Payroll to Create Payroll Calculator

  • 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.


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo