Salary Calculator Excel Sheet Free Download
Salary Sheet is a ready-to-use template in Excel, Google Sheets, OpenOffice Calc, and Apple Numbers that helps you to perform the salary calculations with ease.
It is a payroll document in which you can record payroll data for multiple employees along with a Salary slip prepared according to Indian rules of Employment, which includes provident fund and employee allowances.
What is A Salary Sheet?
A salary Sheet is a document that includes complete details of the amount payable to an employee for work done during a particular period. It includes details like basic pay, allowances, deductions, and overtime, etc. of employees.
A salary sheet or payroll sheet is the Human Resource document that a company uses to calculate the salaries of their employees. It is also known as payroll or payroll sheet. HR maintains financial records of employees like wages, salaries, deductions, bonuses, holidays, leaves, sick leave, etc. in Payroll.
Generally, Payroll is made daily for wages, weekly, biweekly, or monthly for salaries. It differs from one period to another as it is calculated based on actual working days. Thus, each month working days, overtime, leaves, and other variables are different for each employee.
Formula To Calculate Salary
Take-Home Salary = Basic Salary + Allowances – Deductions.
Download Salary Sheet Template
Hr and Payroll staff needs to micro-manage payroll data of employees, which include basic salary, HRA, TA, Conveyance, Leave details, provident fund deductions, etc.
We have created an automated Salary Sheet Template with predefined formula. You can prepare the salary of multiple employees within minutes with the help of this template.
This template is available in 4 file formats – Excel, Google Sheets, Open Office Calc, and Apple Numbers. Click on the button below to download the desired format:
Excel Google Sheets Open Office Calc Apple Numbers
Click here to Download All HR & Payroll Excel Templates for Rs 199.
The functioning and formulation in all the four file formats are the same except the spellnumber function as it is not supported in OpenOffice Calc, Google Sheets, or Apple Numbers.
Important Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the "Make a Copy" option from the File menu.
Additionally, you can download other HR and Payroll related templates like Salary Slip, Attendance Sheet, Weekly Timesheet, and much more.
Let us discuss the contents of the template in detail.
Contents of Salary Sheet Template
Note: After you download the excel and open the office template, don't forget to enable the content in the security warning pop-up below the ribbon as shown in the images below:
Salary Sheet template consists of the following three sheets: Employee Database, Consolidation Sheet, and Employee Salary Slip
Employee Database
The database sheet consists of the master data where data of each employee will be entered. Major data in this sheet needs to be entered one time.
The first section consists of employee details such as employee name, designation, salary month, allowed leaves, leaves taken, and working days.
You can select the month and year of salary from the dropdown list. When the user selects the month and year for the first employee, it automatically displays the same for other employees. We have kept a provision of 20 employees. If you want to make the salary for more employees, you can drag the same using the fill handle.
The user can select the total days of the month (30, 31, 28, or 29) from the dropdown list. When the user selects the month and year for the first employee, it automatically displays the same for other employees.
Allowed leaves are different according to the designation and company policy. Hence, you need to enter individually.
Insert actual leaves availed by the employee and the template automatically calculates the working days. The allowed leaves are deducted from the total leaves and this is how the actual working days are calculated. The template uses the "=IF(G4>=F4, E4-G4+F4, E4)" formula to calculate the working days.
Insert CTC for each employee as per the employee contract. CTC = Direct benefits + Indirect benefits + Saving Contributions. For more information on CTC click here.
CTC is calculated based on working days. Hence, the template calculates the CTC proportionately according to the working days for that particular month. The template uses "IF(H4=0, "", ROUND(I4/E4*H4, -1))" formula.
Components of Salary
There are three major components of the Salary: Basic Pay, Allowances, and Deductions.
Basic Pay/Basic Salary
Basic salary is the fixed amount to be paid to an employee addition of any allowances or subtraction of any deductions. Bonuses, overtime, dearness allowance, etc are not a part of basic pay. For more information about Basic Salary click here.
The template auto-calculates the basic salary based on CTC. Usually, it is 40% or 50% of the basic salary. Basic Salary is proportionate to the working days. It uses the following formula to calculate the basic salary "=IF(H4=0, "", ROUND ($J4/E4*H4*50%, -1))".
Next comes the allowances section. Allowances are the financial benefits an employer provides above the Basic Salary to his employees. These allowances differ based on company policy, industry, job profile, and department. The template adds all the following amounts to form the gross salary.
Dearness Allowance
DA or dearness allowance is an allowance which the employer pays to its employees against the price rise in the economy to reduce the impact of inflation. In India, Pakistan, and Bangladesh, employers provide DA as the cost of living adjustment allowance. Government employees, public sector employees, and pensioners get to DA.
DA is auto-calculated. DA (Dearness Allowance) is 4% to 10% of basic pay depending on the contract. It uses the following formula to calculate DA "=IF(H4=0," ", ROUND (J4/E4*H4*10%, -1))". For more information on Dearness Allowance click here.
House Rent Allowance
HRA stands for House Rent Allowance. House Rent Allowance is the amount allocated by an employer to the employee as a portion of their CTC salaries. The employer decides the house rent allowance depending on different criteria like the city of residence and salary grade.
For employees in a metro city, it is 50% of the basic pay whereas for employees of a non-metro city it is 40% of the basic pay. Generally, when these rules aren't applicable it is 10 % of the basic salary. For more information on House Rent Allowance click here.
House Rent Allowance is auto-calculated. Usually, it is 25%-30% of CTC. Insert the percentage in the formula as per the employment contract. It uses the following formula to calculate HRA "=IF (H4=0, " ", ROUND (J4 / E4 * H4 * 25%, -1))".
Conveyance Allowance
Allowance or money to compensate for an employee's travel expenses between their residence and workplace is Conveyance Allowance. It is also known as transport allowance.
You need to insert the conveyance allowance in the formula as per the employee contract. The conveyance working column calculates the conveyance amount based on employee attendance.
It uses the following formula to calculate the conveyance allowance "=IF(H4=0, " ", ROUND (N4/E4*H4, -1))".Furthermore, if the employee's working days are 0 then it will display 0.
To know more about Conveyance Allowance click here.
Medical Allowance
Medical Allowance is a fixed allowance that an employer pays to its employees as a part of their salary to meet their regular medical needs. The employer credits a fixed amount as a medical allowance along with the salary to the employee's account every month.
Medical allowance is calculated based on applicability to employees as per the contract. If you have selected yes in the medial allowance column at the end, then it will calculate otherwise it will display 0.
Furthermore, if the employee's working days are 0 then it will display 0. It uses the following formula to calculate the medical allowance "=IF(H4=0, " ", ROUND(P4/E4*H4,-1))".
For more information on medical allowance click here.
Special Allowance
The special Allowance is auto-calculated by deducting the basic pay and other allowances. The special allowance is a fixed allowance given to employees to meet certain requirements over and above the basic salary.
The template uses the following formula to calculate special allowance "=IF(H4=0, " ", ROUND(J4-T4,-1))". You can insert manually if it is different for each employee.
Children Education Allowance, Hostel Allowance, Miner's Allowance, Outstation Allowance, Offshore Allowance, etc are some of the special allowances.
Bonus Pay
Insert the amount of bonus pay if it is applicable for any employee for any particular month. Bonus pay is a reward an employer pays to his employee for his good work. Usually, in countries like India Pakistan, and Bangladesh, companies pay bonuses yearly based on the overall financial performance of the organization and are paid during festive seasons.
TA – Travel Allowance
Insert TA for the employee on actually spent by the employee for that particular month. TA stands for Traveling Allowance. Traveling allowance is the amount paid or allotted to an employee by the employer for traveling to another place for business purpose.
The traveling allowance includes modes of travel, accommodation, meals, and other incidentals. To know more about Travel Allowance click here.
All the above things compile the gross salary. Gross Salary is the total of all the components of your monthly payout before any kind of deductions.
Followed by the allowances, the templates consist of deductions. Employee Deductions are the amounts subtracted from an employee's gross pay to reach net pay. The template deducts all the following amounts from the gross salary.
Employee Contribution To Provident Fund
As per the Indian Labor Law, an employee has to contribute a part of the salary towards the provident fund. Currently, it is 12%. The template uses the following formula to calculate "=ROUND (MIN (1800, IF (AG4="Yes", SUM(K4: L4) * 12%, 0)), -1)".
Employee Provident fund is a type of retirement benefits scheme for salaried employees. Under this scheme, the employee makes a stipulated contribution from his salary. The government decides this percentage.
Salary Advance
Insert the amount of salary advance for each employee if applicable. When an employee draws in full or portion of salary in advance, it refers to as Salary advance. While calculating the salary the employer deducts the same from the salary.
Professional Tax
If the professional tax is applicable then insert the monthly amount of professional tax. Professional Tax is a tax that a government levies on professionals based on their salary/monthly income. Usually, it is around Rs. 200 a month which amounts to the maximum payable Rs. 2500 in a year in India.
Tax-Deducted At Source
Insert the actual TDS amount. TDS stands for Tax Deducted at Source. It is the income tax that an employer deducts from the salary of the employee. The employer deposits this collected tax to the IT Department. Click here for more information on TDS, TDS Threshold, TDS Return, etc.
Subtracting the total amount of deduction from the gross salary compiles the net payable salary to the employee.
General Information
Select the gender and prefix of the employee from the dropdown list. Also, select the authorized signatory of the department for each department if it is different from the dropdown list.
Select "Yes" if PF is applicable. Select "Yes" if the employee has submitted the medical bills. If the user selects "No", the template will not add the medical allowance to the salary.
Given above are details preparing the salary according to the Indian Salary structure.
The user needs to enter data only in blue colored cells. The pink-colored cells contain predefined formulas. You need not enter data in pink colored cells. See the image below:
In this sheet, some data input columns have a drop-down menu, which makes it easier for the user to select and enter data. Month, Year, Title, Gender, etc are the drop-down menu. See the image below:
The template consists of buttons at the top of the sheet for easy navigation in as shown below:
After entering data in the Database Sheet clicking on the "Consolidation" button. It will navigate you to the Consolidation Sheet.
Consolidation Sheet
The consolidation sheet consists of a month-wise sheet of salary data entered in the Database Sheet. This sheet links the Database sheet to each cell and automatically fetches the date from the Database sheet. You don't need to enter any data into this sheet.
If you enter the month or year in the database sheet wrong then it will not display in the consolidation sheet for that particular month.
You can print a monthly report of salary for approval from higher management. Once, the signing authority approves the salary you can pay the employee by cheque or direct bank transfers.
Click on the "Go to Salary Slip" button to navigate to Salary Slip Sheet to print individual salary slip for employees.
Salary Slip
The salary slip sheet uses the VLOOKUP Function to program the sheet. On the extreme right, select the name of the employee from the drop-down list. The template displays the salary data of the respective employee. Click on the print button to print the Salary Slip.
Your Payroll process is complete. This template is very user-friendly and easy to use for everyone. This template can be helpful for Accounts assistants, Office Admin, HR Assistants, Executive Assistants, etc for preparing salaries.
If you want to make only a Salary slip without keeping the data you can use our ready-to-use Corporate Salary Slip Template.
We thank our readers for liking, sharing, and following us on different social media platforms.
If you have any queries please share in the comment section below. We will be more than happy to assist you.
Frequently Asked Questions
What Are Payroll Expenses?
Payroll Expenses include salaries, wages, bonuses, commissions, EFB, PF, EPS, company contributions such as a company-paid health plan, and the company-paid portion of taxes.
An employer pays the payroll expenses to its employees in the form of salary/wage for their services/work and associated expenses such as employee benefits and state and federal payroll taxes.
What is Gross Salary?
Gross Salary is the salary amount including all benefits and allowances before any kind of deductions. In simple terms, Gross Salary is the total of all the components of your monthly payout before any kind of tax deductions. Gross Salary = Basic Salary + Allowances + Benefits. For more information on components of Gross Salary click here.
What is Net Pay or Take Home Salary?
The net pay or the take-home pay is the amount of salary/wages of an employee that remains after all deductions(taxes, health insurance, benefits, etc.) have been subtracted. In simple terms, Net pay is the amount an employee receives after all taxes/deductions during a particular pay period.
How To Calculate Overtime?
Overtime Pay means the amount compensated for hours or days worked more than the maximum limit mentioned in your contract or set by federal or provincial law. Click here to get the formula for calculating Overtime.
How much percentage of CTC is the Basic Salary?
Basic salary is usually 40% – 50% of CTC.
How much percentage of Basic Salary is Dearness Allowance(DA)?
Dearness Allowance is usually 5% to 10% of Basic Salary.
On which amount PF deduction is calculated?
PF is calculated on Basic Salary.
Source: https://exceldatapro.com/download-salary-sheet-template/
Posted by: alecsansoucye0192105.blogspot.com