Payroll management systems are crucial for ensuring accurate payment to the employees, compliance with regulations in India, and maintaining financial records. Excel is the easiest go-to tool for most of the users in India to perform calculations and maintain records in sheets. This article outlines a step-by-step process for conducting payroll in Excel, covering data input, calculation of gross pay, income tax computation, deductions, and net salary determination.
We know managing payroll for small businesses is not an easy task. However, we also want to emphasize on the limitations of manual payroll processing where you can transition to a better payroll management software. sumHR HRMS can be your solution for enhanced efficiency, automated payments, payslip creation, and personalized employee communication. This will ultimately streamline your HR processes and reduce administrative burdens.
What is Payroll Management System?
Simply said, a payroll management system is the method by which firms pay their employees’ salaries. It is also how businesses demonstrate their dedication to their employees, fulfil their commitments to government authorities, and maintain accurate financial records with payroll policies.
SumHR offers a comprehensive human resource solution aiming to streamline the process of HR managers and improve overall employee interaction within the organisation. SumHR provides powerful collaboration and communication tools for the best HR management system.
Why is Payroll Management Important?
Payroll administration is critical to the success of any organisation since it improves employee engagement, business processes and regulatory compliance. Employers may face wage claims and costly fines if they do not have an effective and correct method of paying employees, depositing and submitting taxes, and preserving records that can additionally burden Human resource management.
How to Make Payroll System in Excel?
Payroll management is troublesome, if you are just starting and your small firm has less than five employees, you may use Excel, as it is a powerful tool for payroll records. It offers a wide range of applications that may be carried out utilising its equations and functions such as pivot tables and formulas.
To start, check out our free excel payroll template.
Before we go ahead, I wanted to ask, why do you want to go through all the hassles when you can get a *Free HRMS tool to take care of all these?
sumHR HRMS Features:
- Payroll processed in 5 clicks, payslips in 5 minutes
- Bulk salary payout feature
- Save incomplete payroll runs in drafts for later
- Skip attendance details to speed up payroll processing
- Conduct trial payroll runs to reduce stress
- Customize salary components and formulas
- Automate or customize PF contributions
- Automated TDS calculations
- Manage payroll for multiple companies in one account
- Create unlimited configurable salary structures
- Flexible payroll calculation methods available
To do payroll in the free template of excel shown above, you need employee details
like Date of Joining, and Date of Exit. If the candidate is still with the company, mark it as ‘Y’
Keep Salary structure details handy like Basic Salary, DA (Dearness Allowance), HRA (House Rent Allowance), LTA (Leave Travel Allowance), Special Allowance, Statutory Bonus, etc.
Add number of days when the employees were Present, took Paid Leave, Week-Off, leaves for Festival/ Other leave, Loss of Pay Leave and the number of Days in the month (for ease of calculation). This will form the ‘Attendance columns’.
For ‘Gross Earnings’, you have to add relevant formulae in the cells, we have added relevant formulae for the cells, for which the instructions have been mentioned in another tab.
For ‘Deductions’, there are various columns for ESI (Employees’ State Insurance), EPF (Employee Provident Fund), Salary Advance, TDS (Tax Deducted at Source), LWF (Labour Welfare Fund), PT (Professional Tax). If there are any other deductions that you want to include, you can add columns for the same.
Once all the fields are added, you can check the net salary in the last column.
Nowadays, you have various alternatives for handling your payroll records. Manual payroll calculations, payroll using MS Excel, and specialist payroll management software are all optional for business owners.
Why should you use Excel for your payroll processes?
Let’s take a look at some of the reasons why Excel is so popular for various monthly payroll tab tasks with HR roles and responsibility.
1. Employee Data Collection and Tracking
Excel as software may be used for far too many things to enumerate. As a result, it can assist organisations right from the start of the monthly payroll tab. It is used to store and manage personnel information. Furthermore, this information might be kept alphabetically, by certain dates, or in other ways. This gives you a lot of flexibility in terms of how you enter, organise, and view data. Because Excel allows you to easily handle data, you can add, remove, and edit information whenever you choose. This is becoming an increasingly important benefit to avoid errors while recording payroll information.
2. Visual Presentation
Most people believe that Excel can only be used to create complex spreadsheets. But it’s so much more than that. One of the most significant advantages of conducting payroll with Excel is its visual attractiveness. Let’s face it when dealing with numbers and mathematics, who wouldn’t like it to appear less boring?
Excel can convert any data into charts, diagrams, or graphs. This is ideal for presentations and simple comprehension of complex payroll information. Furthermore, when you view data in this way, you may establish analogies between parameters. This contributes to a better understanding of your applicable payroll period with figures and employee pay scales.
3. High Precision
Managing payroll data for small businesses may be a difficult task. That is self-evident. Furthermore, the quantity of transactions increases as your organisation grows in size. It might be quite difficult to track and evaluate these regularly.
Then there’s a payroll sheet in Excel, which comes in handy because data manipulation is a breeze with this programme. Formulas can also be utilised in spreadsheet inputs. This makes it simple to put numbers into particular spots on the spreadsheet itself. Excel will also complete the necessary computations automatically. As a result, you will make little attempt to acquire paycheck outcomes. So you’re not only saving a lot of your essential company time, but you’re also minimising the amount of work you have to do for the applicable payroll period chore.
If you are completely new to Excel and Payroll, then follow the steps below.
9 Steps to complete payroll in Excel
Excel is a practical application. However, how do you run manual payroll processing in Excel? Here’s an easy walkthrough.
1. Open Microsoft Excel
Yes, we’re starting from the very beginning. Launch MS Excel from the programmes to create a new spreadsheet. This will open a blank Excel file for you to begin working on. If you don’t want to start completely from a blank page, you can download the above excel file and start modifying the cells as per your use.
2. Save the File (xlsx format)
The vast majority of individuals believe in saving their files when they have finished working on them. While this is OK for most file types, it is not acceptable for payroll information. Do you want to spend hours preparing a complete spreadsheet just to have it disappear at the last minute? No.
Save the file in a convenient location as xlsx format before you begin working on your payroll management in Excel. Name the file appropriately and keep it somewhere you can easily retrieve it.
3. Setting Up Payroll Management Parameters
The first step is to construct columns containing payroll sheet information. As a result, the data will be saved consistently and values will be inserted into the parameters appropriately.
- Employee Name: Contains your employee name
- Hourly pay: The per-hour pay rate
- Total Hours Performed: The total amount of time worked by an individual.
- Hourly Overtime: Overtime rate per hour
- Total Overtime Hours: Overall number of workers’ extra hours
- Gross Salary: Payable amount without deductions
- Income Tax: Tax payable on Gross Salary
- Other Deductions: Income Tax Deductibles such as EPF, PT, TDS, and so on.
- Net Pay: In-hand salary for the employee
4. Data Input
After you’ve decided on the factors that are important to your organisation in payroll management, it’s time to start entering data. As a result, without using a formula, add the column-by-column specifics.
5. Calculation of Gross Pay
Before proceeding with any further calculations, the first step is to calculate the gross salary. It is also computed as the product of Hourly Pay, Total Number of Actual Hours Worked, Actual Hours Overtime, and Total Number of Overtime Hours. Here’s a simpler illustration:
(Hourly Pay * Total Number of Actual Hours Worked) + (Overtime Hours* Total Number of Overtime Hours)
This is readily computed in Excel using the SUM and PROD tools. Furthermore, after you’ve computed it for one person, simply drag down the following cells for ‘Employee Name’ to receive values for all employees.
6. Determine Income Tax
To compute income tax with payroll management, determine what proportion of the employee’s gross compensation is taxed. Always compute income tax on a gross pay basis. In India, different tax slabs correlate to different gross salaries earned. The lowest income tax bracket is 5%, while the highest tax bracket for people is 30%.
The formula for calculating IT for various incomes is:
(Percentage of Income Tax * Gross Salary)
For instance, if it is 5%, the computation will be -0.05 * taxable salary.
7. Check and Calculate Deductions
This is a critical stage since it pertains to all tax deductions related to employee compensation. Employee Provident Fund, Professional Tax, Public Provident Fund, Life/Health Insurance Premium, Loan EMI, and so on. All investment statements must be processed promptly, and proofs must be authorised through the workflow.
8. Determine Net Salary
The exact amount credited to the employee’s account is referred to as net pay or salary. Furthermore, this is known as the in-hand pay and is the amount remaining after all deductions from the gross income has been made.
To determine Net Pay, subtract the total Income Tax and Tax Deductions from Gross Pay.
(Gross Earnings – Income Tax + Deductibles)
9. In Excel, Evaluate All Payroll Variables
Repeat the manual payroll processing for each employee by dragging each cell downwards. Furthermore, keep verifying each value in each component. Finally, format the cells and sum all values at the bottom of the spreadsheet.
What comes next after Excel Payroll Calculations
Once you’ve mastered the art of doing payroll in Excel, what happens next? As these are simply calculated raw data. You can’t provide this data to employees since you don’t want everyone to know how much each other makes. You can’t print all of them either; it’s a lot more labour and a lot of money. So, let us look at how to build on good payroll procedures using Excel formulas.
1. Making Payroll Payments
All of this is great and well, but how will you pay your staff efficiently? That’s how payroll management software works. Once you have the final data, payroll management software handles everything for you. Furthermore, it automates all of your payments while maintaining employee and financial secrecy. Furthermore, it is all done while keeping the deductibles and compliances in mind.
2. Payslip Creation and Distribution
This is yet another crucial payroll responsibility. Manual payslips, you see, take just as much time as manual payroll. Not to mention that they are prone to high degrees of inaccuracy and large-scale human mistakes. So, what’s the best alternative? An online payslip maker! All you have to do with this programme is enter values from your collected Excel data. You will receive an immediate payslip with your company logo within seconds. Furthermore, your employees may receive their pay stubs by mobile as well as WhatsApp.
3. Employee Email Personalization
Customization is the smartest method to go when it comes to delivering salary slip emails to employees. You may also utilise software like MailMerge, which interacts easily with Excel. MailMerge is commonly used to merge official forms and emails.
Conclusion
With SumHR all payroll operations can be automated with its payroll management software. Using payroll software for business owners may greatly reduce the majority of the difficulties and obstacles that HR faces on a regular basis. sumHR’s software can assist you in automating and simplifying critical operations.
