Employee Salary Break Up Report is a ready-to-use Template in Excel, Google Sheet, and OpenOffice Calc that helps you to calculate salary breakup paid under each head.
It includes the breakup of salary like how much basic salary, allowances, deductions, etc are paid/deducted during that time.
Salary Breakup Report Excel Template can be useful to HR assistants, HR professionals, and office admins. Accountants can use this template to predict future budgets and other financial reports related to payroll.
Table of Contents
What is Salary Breakup?
Salary breakup is the detailed statement total compensation paid to all employees during a specific period of time. It includes heads like basic salary, DA, HRA, Conveyance, medical, special allowance, bonus, TA, etc. It also includes deductions like Contribution to provident fund, profession tax, TDS, Advance Salary, etc.
In simple terms, it is the bifurcation of salary a company pays to its employees. It bifurcates the amount a company pays against basic, DA, HRA, etc.
Purpose of Salary Breakup Report
The main purpose of preparing a salary breakup report is to know the amount of compensation paid under each head. This bifurcation helps the employer to understand the percentage of the compensation they are paying against their income as well as sales turnover.
Moreover, this further helps the management to makes necessary changes to their pay structure to improvise employee retention.
Components of Salary Breakup Report
To prepare a Salary Breakup Report, historical data will be required. Hence, first of all, we need monthly Salary Sheets for the past months.
Each salary sheet bifurcates the paid and unpaid compensations. Extract the monthly totals of each component and insert it in the yearly breakup report.
Source: www.rentomojo.com
There are 3 major components of Salary: Basic Salary, Allowances, and Deductions.
Basic Salary
Basic salary is the fixed amount that an employer pays to an employee without adding any allowances or subtracting any deductions. Bonuses, overtime, dearness allowance, etc are not a part of basic pay. It is a part of your take-home amount.
It is the base of your pay structure. Other components of your total pay are calculated depending on your basic pay. These components include Annual Gratuity, Employee Contribution to Provident Fund or ESIC are determined according to your basic.
Allowances
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.
Allowances may include the following:
Dearness Allowance (DA)
House Rent Allowance (HRA)
Conveyance Allowance
Medical Allowance
Special Allowance
Travel Allowance (TA)
Child Education Allowance (CEA)
Basic Salary along with the allowances forms your Gross Salary. Hence, Basic + Allowances = Gross Salary. If there are any kind of reimbursements, you need to add them to the gross pay.
Deductions
Employee Deductions are the amounts subtracted from an employee’s gross pay to reach net pay. Deductions include the following:
Employee Contribution To PF
Employee Contribution to EPS
ESI – If applicable
Professional Tax
TDS – Tax Deducted At Source
Salary Advances
The employer contribution to PF and ESI is not a part of the take-home salary. But it will be added to the salary breakup report. Employee contribution to PF and EPS will be deducted from the salary.
Professional Tax is a tax that a government levies on professionals based on their salary/monthly income. TDS is the income tax that an employer deducts from the salary of the employee. Hence, the employer deposits this collected tax to the IT Department.
Salary Breakup Report Template (Excel, Google Sheets, OpenOffice Calc)
We have created an easy and ready-to-use excel template for Salary Breakup Report with predefined formulas. Either you can copy-paste the salary data from your monthly salary sheet or maintain the records in this template.
Using the linking function of excel, each sheet has been linked to the report. You need to enter the data only once and it will automatically display the totals for you.
Excel Google Sheets Open Office Calc
Click here to Download All HR & Payroll Excel Templates for ₹299.
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.
Furthermore, you can also download other useful HR templates like Employee Training Log Template, Employee Vacation Tracker Template, Salary Arrears Calculator Template, and Resume/Cover Letter Template.
Let us understand how to use this template in detail.
Contents of Salary Breakup Report Template
This template consists of 13 sheets;
- Employee wise Monthly Salary Data Sheet (12 Sheets – 1 sheet for each month)
- Consolidated Monthly & Yearly Salary Breakup Report.
Employee wise monthly Salary Data Sheet
In this sheet, you need to punch salary data or each employee and it will automatically display the monthly total for each head as well as Net Payable amount for each employee.
If you have the data in another sheet you can also copy-paste the data in light blue colored cells. Dark Blue Colored cells have the formulas so do not enter any data in these cells.
Usually, different companies have different heads for salary and deduction. You can amend them according to your needs. Don’t forget to amend the formulas. If you face any issues, we are there to assist you.
Consolidated Monthly & Yearly Salary Breakup Report.
The consolidated sheet consists of the final total of each month as well as the yearly total of each head along with the net payable amount for each month and each year.
By any chance, if you make changes in monthly sheets then the Salary Breakup Report Template must also be changed accordingly.
Please note that here you don’t need to enter any data. it will automatically fetch the data from the monthly sheets.
We thank our readers for liking, sharing, and following us on different social media platforms.
If you have any queries or suggestions please share in the comment section below. I will be more than happy to assist you.
Frequently Asked Questions
What is the breakup of salary?
The salary breakup is Basic Salary + Allowances + Reimbursements – Deductions = Net Salary.
What does CTC include?
CTC = Earnings + Deductions. Earnings include Basic, DA, HRA, Conveyance, Medical, Special allowance, bonus, and reimbursements. Deductions include PF, ESI, TDS, Professional Tax, etc.
How do I calculate other allowances in salary?
Basic Salary – 40% of the CTC – It is completely taxable.
DA – 20 % of the basic salary. This amount usually depends on the company policy and is taxable.
HRA – 50% of the basic salary. This allowance is applicable if the employee is living in a house other than provided by the company.
Medical reimbursement is fixed and is Rs. 1250 per month. Usually,
LTA depends on the company policy and is paid against the submission of bills. Tax exemption is up to a certain limit.
The special allowance is not fixed and depends on company policy. This allowance is fully taxable.
What is Net Pay?
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.
How many work weeks are in a year?
There are 52 workweeks in a year.
What is Leave Encashment?
Employers provide money to their employees in lieu of accumulated leave. This refers to Leave Encashment. Thus, an employee can convert the unused leaves to cash based on the company’s leave policy. It requires approval from concerned authorities.
Kuldeep Janghala says
Hi Fahim,
You have done a wonderful work by creating excel templates for many of professional requirements.
I really admire your work and dedication.
thank you so much.
Fahim Lashkaria says
Hey Kuldeep,
Thanks for the appreciation. Glad to know you liked our work and recognized our efforts. Your words mean a lot to us and inspire us to do more. Please help us reach more people by sharing these words of appreciation on different social media platforms by adding our link. That would be a great help. Thanks
KAVYA says
TDS TO BE DEDUCTED ON CTC OR BASIC SALARY.
admin says
This depends on the actual computation of income that is being paid tot he employee and so it is better to consult a CA on the matter.