It is important for labour law consultants to be able to calculate and maintain the payroll of all employees in a company. With the help of modern technology, this process is quite easy to do. In this article we explain how to use MS Excel for payroll calculation, in a simple method which is easy to understand for all beginners.

Steps for Excel Payroll Calculation

Basic employee details

• The top of a basic excel payroll calculation sheet can contain the company name and address. If you are a consultant then it can have your consultancy name as well.
• The first column of the excel payroll calculation will have the Serial Number or the Employee Code Number.
• Second column will have the name of the employee.
• Third column will have the employee’s father’ name or spouse’s name. This will help to differentiate between employees with the same name.

Basic salary rate details

• The next slab on the excel payroll calculation will contain the employee’s salary details.
• First column must have the Basic Salary details of the employee.
• Next column can have the Dearness Allowance (DA) details. To calculate DA, type “=(cell of Basic)X10%” and press enter. Drag down the cell to fill in DA for the full column.
• Next column can have the House Rent Allowance (HRA) details. To calculate HRA, type “={(cell of basic)+(cell of DA)}X30%” and press enter. Drag down the cell to fill in HRA for the full column.
• Last column of this slab will have the Total Salary calculation. For this, type “=SUM(cell of basic, cell of DA, cell of HRA). Drag the cell to fill up the column for all employees.

Attendance details

• The next slab will have attendance details of all employees. These will be Present Days, Paid Leaves, Weekly Offs, Festivals Offs and Total Pay Days.
• To calculate pay days, type “=Sum{(cell of present day)+(cell of paid leave)+(cell of weekly off)+(cell of festival off)}”. Drag the cell to fill up the column for all employees.
• Important to note here, that number of Pay Days can never exceed 31. Since no month has more than 31 days.

Gross earned salary details

• This slab will calculate the actual salary earnings of the employee on basis of salary rate and attendance.
• First column will be for Basic Salary in the excel payroll calculation. To calculate type “={(cell of basic rate)X(cell of pay days)} / no. of days in month”.
• Second column will be for Dearness Allowance calculation. To calculate type “={(cell of DA)x(cell of pay days)} / no. of days in month”.
• Third column will be for HRA calculation. To calculate type “={(cell of HRA)x(cell of pay days)} / no. of days in month”.
• Drag all the columns down to fill in excel payroll calculation for all employees.
• Fourth column will have any Incentive applicable for individual employee.
• Fifth column will calculate total monthly salary for each employee. To calculate type “=SUM(cell of basic)+)cell of DA)+(cell of HRA)+(cell of incentive)”.

Deduction details

• First column will have ESI calculation. Type “=(cell of total salary)X0.75%”. For employees with total salary over Rs. 21,000, ESI is not applicable and must be removed.
• Second column will have PF calculation. Type “={(cell of basic)+(cell of DA)}X12%”.
• Third column will have Advance details for any employee who has taken an advance payment.
• Fourth column will have TDS values per employee.
• Fifth column will have LWF values per employee.
• Sixth column will have values for Professional Tax deduction per employee.
• Seventh column will have Total Deductions calculation. To calculate, type “=SUM(cell of ESI)+(cell of PF)+(cell of Advance)+(cell of TDS)+(cell of LWF)+(cell of Professional Tax)”. Drag down the column to fill in all rows.

Net salary details

• The last column of the excel payroll calculation will have Net Salary. to calculate type “=(cell of total salary)-(cell of total deductions)”.
• Drag down the column to fill in net salary details of all the employees.

Payroll Processing Course

To get a more in depth knowledge of Payroll Processing we have an ongoing online course which anyone can subscribe to. This online payroll course has 45 lectures with 100+ modules, giving you a complete step by step process of payroll processing.

You can get the following in Payroll, Excel & Labour Law Courses for career growth and in A-to-Z of Payroll Course.

For further Course Details Call/WhatsApp +91-9116847363/9116853688.

Watch the full process on excel payroll calculation in the video below.

Join the LLA telegram group for frequent updates and documents.
Download the telegram group and search ‘Labour Law Advisor’ or follow the link – t.me/JoinLLA
It’s FREE!