Employee Salary Sheet Template in Excel, Google sheet, and OpenOffice Calc to process salaries of 50 employees as per Indian laws. With the help of this template, you can process the salaries of 50 employees in just minutes.
Moreover, you can print all 50 salary slips only at the click of one button. Furthermore, it prepares an annual compensation report along with graphical representation.
Table of Contents
What is an Employee Salary Sheet?
Payroll departments manage payroll data of employees on a micro-level. This involves keeping records of overtime, leaves, medical, health insurance, EPF (employee provident fund), ESI (employee state insurance), etc.
An Employee Salary sheet is a document that helps you process the salaries of multiple employees in few minutes and generates salary slips of all employees in just one click.
These salary calculations are prepared according to the Indian salary structure and laws of employment. Thus, the calculations include provident fund and employee allowances which are mandatory by law.
Important Formulas To Calculate Components of Salary
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
Download Employee Salary Sheet Template in Excel, OpenOffice Calc, and Google Sheet as per the Indian Salary Structure
We have created a ready-to-use Salary Sheet Template with predefined formulas according to Indian rules of employment to simplify your payroll process.
This template is available in 3 different file spreadsheet programs: Excel, Google Sheet, and OpenOffice Calc. Click the link below to download the desired file format.
This template operates similarly in each file format. It consists of the same number of sheets, columns, and rows. Instructions for some functions that aren’t supported in OpenOffice Calc or Google sheets are given below for your convenience.
For Excel User: The file consists of VBA code and hence is a macro-enables file. Thus, after download, a security warning will appear as below.
Kindly, click on “enable the content” and click “ok”. If you will not do this, the SpellNumber function will not function properly.
For Google Sheet Users: Google sheet uses SpellNumber Indian Rupees function manually added to google sheet. Follow the instructions given in the article to add this function to your sheet.
For OpenOffice Calc Users: OpenOffice Calc file uses NUMTEXT and MONEYTEXT function which is an add-in installed. To install this in your OpenOffice Calc file follow the instruction given in the article.
You can also download the Simple Salary Sheet Template if the size of your organization is small.
How To Use Employee Salary Sheet Excel Template?
This template consists of the following 17 sheets: Salary Calculation Sheet, Approval Sheet, Salary Slip Printing Sheet, Salary Slip Generator, 12 sheets monthly salary data, and Annual Compensation Report.
This sheet is a fully automated sheet with predefined formulas and functions. Hence, you can process the salaries of 50 employees in just minutes and without any manual calculations.
Just insert the following 8 details every month and your salary slip is ready to print:
Leave Taken
CTC
Conveyance Allowance
Medical Allowance
Professional Tax
Salary Advance
Bonus
Furthermore, the rest of the columns consist of predefined formulas and are auto-populated. Thus, this is the simplest way to process the salaries of employees using any spreadsheet program.
The user needs to enter data only in grey-colored cells. The orange-colored cells contain predefined formulas. Thus, do not delete or insert any data in orange-colored cells.
Let us discuss the contents of each sheet in detail.
Salary Calculation Sheet
Insert your company name and company address. This name and address will reflect in the salary slips of employees.
Select the month and year from the drop-down menu. Insert total working days of the month as well as the number of allowed leaves per month.
Employee General Information
All this employee information needs to be entered only one time. It changes only when an employee leaves, resigns, or is terminated.
The serial number column is auto-populated. Insert employee id. The first two alphabets of employee id are for company initials. The following two digits are for the current year and the last 2 digits for the employee number.
Insert name, designation, and department of the employee. Select the location and gender of the employee from the dropdown list. Based on location, the template will calculate HRA.
Attendance Information
Insert leaves taken by each employee in this column and it calculates the working days. Usually, every company has a stipulated number of days as allowed leave. The hr department calculates the working days based on these allowed leaves.
For example, company A has allowed leave of 2 days. For January, the working days are 31. If Mr. A has observed leaves of 3 days, the calculation will be as follows:
Days Payable = Total Working Days – Leave observed + Leave Allowed
Salary Calculations
CTC
Insert CTC of each employee. This section consists of predefined formulas. Thus, it automatically calculates the basic and house rent allowance (HRA).
Basic Salary
In this template, the basic is 60% of CTC. The formula implied here is =IF(J10=0,””,K10/$G$7*J10*60%). To change the basic salary according to your company policy, just change the % in the formula. Usually, it is between 40% – 60% of basic.
Allowances
Depending on the selection made in the general information section about the location of the employee, this cell will automatically calculate the HRA.
House Rent Allowance is 50% of the basic salary if the employee lives in a metro whereas it is 40% if the employee lives in a non-metro. The formula implied here is =IF(G10=”Metro”,L10/$G$7*J10*50%,IF(G10=”Non-Metro”,L10/$G$7*J10*40%, 0)).
Insert the fixed amount of conveyance allowance as per the company policy. This cell also consists of predefined formula and calculates the actual conveyance allowance based on attendance. The conveyance amount is divided by the total days payable.
The calculations for the medical allowance are also similar to the conveyance allowance. You need to enter a monthly fixed medical allowance and it automatically calculates the amount based on the attendance of the employee.
An employer pays a fixed allowance to his employees to meet certain requirements over and above the basic salary. These allowances fall into the special allowance category. This can include purposes like child’s education, mining, outstation travel for business, offshore living for conducting business activities, etc.
The template sums up all the allowances in the total allowances column. Gross salary is basic plus all the allowances. The cells contain predefined formulas and are auto-populated.
Deductions
As per the Indian salary structure, there are some mandatory deductions from the salary such as professional tax, income tax, and provident fund.
Insert the applicable professional tax amount. The professional tax amount is nil if the salary is below 15000, Rs. 150 if the salary is between Rs. 15001 – Rs. 20000 and Rs. 200 if the salary is above Rs. 20000.
TDS rate is fixed by the government. Earlier it was 7.5%. Currently, it is 10% if the yearly salary is above Rs. 5,00,000 threshold limit. The template automatically calculates the TDS for you based on 12 months gross salary. The formula implied here is =IF(U10*12<500000, 0, U10*W10).
EPF is auto-calculated. The formula implied here is =IF(U10=0,0,IF(U10*12%<1800,1800,U10*12%)).
An employee must contribute a minimum of 12% of Rs. 15000 that is Rs. 1800 hundred. If the salary is above Rs. 15000, then 12% on the actual amount will be calculated.
The EPF or provident fund is a retirement scheme for salaried employees in India. It is mandatory by law for every employer to register with EPFO if the company has more than 20 employees.
Insert advance salary amount if an employee has taken it priorly or leave it blank. The sum of all the above amounts equals the total deductions. Insert bonus as per company policy.
Net Salary is calculated using the following formula =IF(J10=””,0,(U10-AA10+AB10)).
In the end, enter the bank detail of each employee. This information is fixed and seldom changes.
That’s it and your payroll is done.
Salary Approval Sheet
Navigate o Approval sheet. This sheet consists of payroll data for that particular month. Just print the approval sheet and present it to the higher management for approval.
Salary Slip Printing Sheet
Navigate to the Salary Slip Printing sheet. Click on the orange “Print” to print all 50 salary slips in one go. This sheet auto-populates all the salary slips using the Vlookup function.
Salary Slip Generator
Moreover, if the user wants to generate a salary slip for an individual employee then go to the Salary Slip generator sheet. Select the employee id and it will automatically fetch the respective data. Click on the print button if you want to print the salary slip.
Monthly Salary Record Sheet
This file is a copy of the salary calculation sheet. Once you have processed the salary and printed the salary slips of employees, copy cell B6 to AE55 every month from the Salary Calculation sheet to the respective month.
Annual Compensation Report
Furthermore, this sheet automatically prepares an annual compensation report with the data copied every month in salary record sheets.
In addition to this, the report displays the data for each month in the form of a pie chart. Hence, it consists of a total of 12 pie charts, one for each month. The pie chart displays the percentage of each head paid in compensation every month.
Frequently Asked Questions
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.