Payroll Excel Template with Attendance a fully automated template in Excel, OpenOffice Calc, and Google Sheet to manage the complete payroll process.
A simple 3 step process to manage your company payroll:
Table of Contents
Simple, easy, and fully automated Payroll Excel Template with Attendance predefined formulas and functions that help you process the payroll of 50 employees in just a few minutes. A payroll template is also referred to as Salary Sheet. The template follows the rules of the Indian salary structure.
Click the link below to download the desired file format:
You can also download other HR templates such as Simple Salary Sheet or Employee Salary Sheet depending on the company requirement.
Additionally, the attendance sheet consists of predefined formulas that automatically displays every day off with different color. Just select the month and it will automatically mark the weekend for you.
Moreover, the template consists of easy navigation buttons. These buttons help you easily move along the multiple sheets in the template. You can print all 50 salary slips or payslips at the click of a button.
Furthermore, the template also helps you store monthly payroll data that helps the HR staff prepare multiple reports.
Note: There are no navigation buttons in Google sheet and OpenOffice Calc file format. The template functions in the same manner in all the file formats: Excel, Google sheet, and Openoffice Calc. Google Sheet and Openoffice Calc users have to manually copy the monthly data to the “Salary Data” sheet.
This template consists of 6 sheets: Employee Attendance Sheet, Salary Calculation Sheet, Approval Sheet, Salary Slip Printing Sheet, Salary Data Sheet, and Salary Slip Generator.
Let us discuss each sheet individually to understand the working of the payroll excel template with attendance and the payroll process it follows.
Every salary process starts with attendance. It helps to count working days for each employee.
In this sheet, first, you need to select the month and year. Based on your selection, the template will automatically display the weekdays. Each Sunday column fills in orange automatically with the help of conditional formatting.
In case, you want to mark Saturday and Sunday both as a week off then you need to change the conditional formatting by following these steps:
The Employee ID and Employee Name are auto-populated based on entries made in the salary calculation sheet.
Mark the attendance. “P” with presence and “L” with Leave of the employee on each day of the week. In the second last column, insert “Allowed Leaves” as per your company policy.
With the help of COUNTIF functions, the template calculated the leaves and presence. It will deduct the allowed leaves from the same and will display the days payable accordingly.
The last row of the attendance sheet displays the daily strength of the organization.
At the start of every new month use the “Clear All” button at the top to clear the contents of attendance. This will clear the data from cells E9 to AI58.
Initially, the user needs to insert the following details one time only as these details do not change unless an employee leaves or the company recruits a new employee:
Sr.No
Employee ID
Employee Name
Designation
Department
Gender
Location
CTC
Conveyance Allowance
Medical Allowance
Professional Tax
TDS
Salary Advance
Bonus
Bank Name
Branch
Account Number
TDS rate: Currently, the TDS rate is 10% till May 2020 which was 7.5% earlier. These rates have been increased for FY 2020-2021. Insert applicable tax rate in this column based on employee’s salary. This task needs to be done one time.
Professional Tax: The user needs to enter this amount manually. This amount differed based on salary payable. If the salary of the employee is Rs. 15000 or below then NIL. From Rs. 15001 to Rs. 20000 it is Rs. 150 and above Rs. 20000 it is Rs. 200. This cannot be more than Rs. 2400 yearly.
As soon as you insert the above details the following fields are auto-calculated as per the predefined formulas:
Based on your selection in the Attendance Sheet, this column is auto-populates both month and year from Attendance Sheet.
The salary sheet fetches these data automatically from the attendance sheet.
The basic salary is calculated here as 60% of the CTC. You can change the formula according to your company policy. Thus, keep in mind that once you change the formula, kindly copy the same formula to the whole column using the fill handle or copy-paste option.
Depending on days of attendance the column calculates the Basic salary. The formula applied here is:
=IF(N8=0, ” “, O8/K8*N8*60%).
The template calculates HRA as 40% or 50% based on the location of the employee. If the HR selects the location of the employee as metro then 50% and if non-metro then 40% of basic salary.
Moreover, similar to Basic Salary, HRA is also calculated based on attendance. The formula applied here is :
=IF(G8=”Metro”,P8/K8*N8*50%,IF(G8=”Non-Metro”,P8/K8*N8*40%, 0))
As the user enters the allowed conveyance and medical allowance, the Conveyance and Medical Actual amounts column will calculate the amount based on attendance. Often, these allowances can differ from company to company.
These columns are also auto-populated based on the remaining amount of CTC after subtracting basic, HRA, medical allowance, and conveyance allowance.
The formula applied for the Special Allowance column is =IF(N8=0,” “, O8-(P8+Q8+R8+T8)). Similar to other allowances, the sheet calculates special allowance also according to attendance.
This column sums up all the allowances adding HRA, Conveyance, Medical, and Special allowances.
Basic in addition with Allowances equals Gross salary. This amount is before any kind of deductions. The following formula has been applied here: =Q8+S8+U8+W8.
This column calculates 10% TDS on annual gross salary above Rs based on the above-mentioned TDS rate. 500000. If you change the formula, then copy the same to other cells of the same column.
EPF stands for employee provident fund. Companies with more than 20 employees must register for EPF. The employer deducts 12% of the gross salary from the employee’s salary.
If this amount is less than Rs. 1800 then the minimum amount of Rs. 1800 will be deducted or else the actual amount will be deducted. The formula applied here is =IF(Y8=0,0,IF(Y8*12%<1800,1800,Y8*12%)).
Insert the amount of salary advance taken by the employee and if not then leave blank.
This column populates the total amount of deductions applicable to salary. This sums up Professional Tax, TDS, EPF, and salary advances.
Insert if applicable or else leave blank. Usually, in India, companies pay a bonus during Diwali. Thus, this column will usually remain blank.
This column is auto-populated. Gross Salary minus total deductions plus bonus equals Net Salary.
Once you are done with this, click on the “Save Data” button to save the monthly payroll data to the Salary Data sheet every month.
For more details on Salary components kindly view the FAQ section below this article.
Click on the “Approval Sheet” Button to navigate to that sheet.
This sheet doesn’t require any manual entry. Just print the sheet and present the same for approval to the HR authority.
This sheet displays only basic salary, total allowances, and total deductions. Click the print button above and print this approval sheet.
By clicking the “Save Data” button in the Salary Calculation sheet this sheet auto-populates itself. Do not click the “Save Data” button more than once otherwise same data will be copied.
After approval and saving data now comes the salary payment. Transfer the amount to the respective bank accounts. Now you can print the salary slip. To print the salary slips click on the “Salary Slip” button.
This sheet consists of filter options. Hence, the HR staff can use this data for analysis. You can prepare the monthly, quarterly, half-yearly, and yearly salary reports.
Moreover, this sheet can help you prepare an employee-wise report for each year.
Not only that, with the help of the filter option you can choose n number of criteria. Thus, it helps you to prepare a customized report as per the requirement of your management.
This sheet consists of 50 salary slips that automatically fetch data of each employee. Just click the “Print” button and this will print all 50 salary slips in one click.
You can generate the salary slip of an individual employee by navigating to Salary Slip Generator. Select the employee ID. The sheet will display the salary slip for that respective employee.
Click on the “Print” button to print the individual salary slip.
A Payroll sheet is a document that records basic pay, allowances, deductions, attendance, leaves, holidays, bonuses, etc of the employees on a monthly basis. The HR department of the company uses this sheet to calculate the salary of employees at the end of every month.
CTC = Gross Salary + PF + Health Insurance
Gross Salary = Basic + Allowances
Basic = 40% to 60% of CTC Amount
Allowances = HRA + Medical + Conveyance + Travel Allowance + Special Allowance
HRA = 50% of basic salary – metro city.
HRA = 40% of basic salary – non-metro.
Deductions = Professional Tax + TDS (Tax Deducted At Source) + EPF Employee Contribution
TDS = 10% of Gross Salary
EPF = 12% of (Basic + DA)
Net Salary or Take Home Salary = Basic + Allowances – Deductions
Basic Salary = 40%-50% of CTC.
1. HRA = 50% of basic salary – metro city.
2. HRA = 40% of basic salary – non-metro.
3. EPF = 12% of (Basic + DA).
4. ESIC
5. Gratuity
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.
We also design customized templates according to your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.
Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.
