Excel Payroll Template (Table of Contents)
Introduction to Excel Template for Payroll
Payroll is the process of calculation and distribution of the employees’ wages in an organization. Most businesses use software like ADP or Tally for payroll or hire some outsourcing companies to provide them the payroll services. However, this consumes a lot of time and manpower. So businesses these days prefer to do payroll manually, which is done via MS Excel. In order to run payroll in Excel, we require a standardized payroll Excel template that can be used the month to month. The template should consist of tabs for each month with prefilled information like tax rates, and formulas that can calculate the employees’ salary, taxes, and deductions.
Below is some general information that is included in most of the payroll templates:
Employee details like:
- Employee Name
- Date of Joining of employee
- Wages or the pay of the employee
- Number of hours worked by the employee
- Leaves took by an employee
- Overtime rate of the employee
- Total overtime hours worked by an employee
- Gross pay paid to the employee
- Income tax and other deductibles
- Statutory payments made
- Taxable benefits
- Net pay paid to the employee
How to Create Payroll Template in Excel?
Let us say we wish to create or generate a payroll template in Microsoft Excel. Now following steps can be used to do so (basically in Excel 2013):
- Open a new workbook in Microsoft Excel and save it:
- Now create the following column names in a fresh worksheet:
- Employee Name: Consisting of a list of employees’ names
- Pay: Consisting of the employees’ wages or pay per hour
- Total Hours Worked: Consisting of the total number of hours worked by an employee in a month
- Overtime: Consisting of an employee’s overtime rate per hour
- Total Overtime Hours: Consisting of the total number of overtime hours worked by an employee in a month
- Gross Pay: This is the monthly payment amount to the employee without any deductibles
- Income Tax: This is the tax that is payable on gross pay
- Other Deductibles (If any): These are the deductibles other than the income tax
- Net Pay: This is the pay or salary that the employee will receive in hand after all the deductions from the gross pay.
- Now add these details of all the employees column-wise, like employee name in column A, per hour pay rate in column B, and so on till column E, i.e. till Total Overtime Hours. The following screenshot illustrates this:
So we can see in the above screenshot that we have added the required employee details under relevant column names in the table created. Pay and overtime rate are on an hourly basis, while overtime hours, total hours worked, gross pay, and net pay are all on a monthly basis.
- Now we calculate the Gross Pay, which is the sum of the product of per hour pay, total hours worked and per hour overtime rate, total overtime hours, i.e. Gross Pay can be calculated as Gross Pay= (Pay * Total Hours worked) + (Overtime* Total Overtime Hours). The following screenshot illustrates this:
- Now income tax is to be calculated on gross pay. Let us consider 18% tax for all the employees. So income tax can be calculated as: Income Tax = (0.18 * Gross Pay). The following screenshot illustrates this:
- Now other deductibles, if any, like some loans or health insurance premiums, can be mentioned under this. However, it would be zero for an employee if there is no other deductible. The following screenshot illustrates this:
- Now net pay is the final amount that an employee gets in hand after all the deductions from gross pay are made. So, if we deduct income tax and other deductibles from gross pay, we will get the net pay for an employee. So, Net Pay can be calculated as Net Pay= (Gross Pay – (Income Tax + Other Deductibles)). The following screenshot illustrates this:
So this is how a payroll template is created by ourselves in MS Excel.
This way we can create the payroll for all other employees by adding their details in the table created and then calculating their wages, tax, etc. We can find the sum total of all under all the columns so that the template looks like the below:
Now, this is the process for one month. We can now create tabs for each month in this manner so that this template is filled with prefilled information like tax rates and formulas that can calculate the employees’ salary, taxes, and deductions for each month and can be used and customized as and when required.
Things to remember about Excel Template for Payroll
- Payroll is used to keep a record of the employees’ information, their wages, payment dates, taxes, deductions, and the number of hours worked.
- Using payroll templates in business is a fast and easy method for them to standardize their payroll process.
- There is no single standardized form for the use of a template.
- Users can even create their own payroll templates in Microsoft Excel, which is beneficial as the payroll can be monitored by the user itself.
- Payroll templates can help in maintaining a confidential detailed list of the employees of a business.
- The free payroll templates are eligible or suitable for any number of customizations and can be edited or created just the way the user wants.
- The payroll templates in MS Excel can be easily converted to PDF format and sent to the client via mail.
- Some additional columns like premium of the health insurance, federal allowances, date of joining, pre-tax holdings, taxable benefits, statutory payments made, etc. can also be added to the payroll template.
- Some other softwares used for payroll calculation and that are available for free are:
- Employee Register
- Dashboard Payroll
- Employee Timesheet
- Payroll Accounting
- Employee Absence Schedule
- Employee Payroll Calculator
- Wage Tax Calculator
- Blue Timesheet, etc.
Each of these has separate tabs and worksheets for each section of the employee details and payroll services.
Recommended Articles
This has been a guide to Excel Template for Payroll. Here we discuss How to create Excel Template for Payroll along with practical examples. You can also go through our other suggested articles –
