Salary Breakup is a very powerful way to show the how the CTC (cost-to-company) on an employee is distributed in different fields. It breaks down the CTC into components to show a clear picture of how the salary is paid. So, in this article, we will show how to create salary breakup calculator in Excel.
Download Practice Workbook
You can download the practice workbook here.
Step-by-Step Procedures to Create Salary Breakup Calculator in Excel
In this article, we will describe how to make a salary breakup calculator in Excel. We will break it into parts and show in steps how the calculations are done. To illustrate clearly, we will use the sample salary structure of a hypothetical company. The salary structure shows the Basics, House Rent Allowance (HRA), Special Allowance, Date of increment, etc. for each employee.
Step 1: Calculating Basic Salary
The basic salary is the salary that an employee earns before any additions or deductions are made to their salary. The Salary Sturcture table shows the basic salary of each employee. However, the basic salary will change depending on the number of payable days an employee has worked.
Net Basic Salary = (Basic Salary * Number of Payable days)/Number of days in a month
- Firstly, select the B15 cell and write down the following formula,
=VLOOKUP(B7,Structure!$B$5:$D$8,3,0)/D7*E7- Then, hit Enter.
- Consequently, we will have the net basic salary of the employee for that month.
- Move the cursor down to autofill the rest of the cells.
Formula Breakdown:
- VLOOKUP(B7,Structure!$B$5:$D$8,3,0): The VLOOKUP function looks for a value in a range of cells and then returns a value from one of the columns in that range. Here, the VLOOKUP function looks for the EmplpyeeID A-102 in the range B5:D8 in the sheet named Structure where we have kept the salary structure of the company. Then, in the 3rd argument we will enter 3 because the value Basic Salary is in the 3rd column of that range. Finally, we set the range_lookup value to be zero. So, the function will return $1500 as fits with the employee ID A-102.
- VLOOKUP(B7,Structure!$B$5:$D$8,3,0)/D7*E7: We will try to find out the basic salary to be paid to the employee. It depends on how many days the employee joined the work. Since here, the employee has joined all the 31 days, so the basic salary will not change as 31 days on the top will nullify 31 days in the bottom.
Read More: How to Calculate Basic Salary in Excel (3 Common Cases)
Step 2: Measuring House Rent Allowance (HRA)
The house rent allowance is an allowance provided by the company. It is added to the employee’s salary. It varies from company to company. Here, we will show HRA equal to 50% of the basic salary. However, HRA too depends on the how many days did the employee attend his/hers work.
- Firstly, select the C15 cell and write the following formula down,
=VLOOKUP(B7,Structure!$B$5:$E$8,4,0)/D7*E7- Hit Enter.
- As a result, we will have the net HRA of the employee for that month.
- Lower the cursor down to the last cell to autofill.
Read More: How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
Step 3: Calculating Special Allowance
This is an allowance given to specific employees who have fulfilled certain requirements. It is a grant that is given by the company. This also depends on the number of days when the employee went to the office in that month.
- To begin with, choose the D15 cell and type the following,
=VLOOKUP(B7,Structure!B5:F8,5,0)/D7*E7- Then, press Enter.
- As a result, we will get a special allowance for specific employees.
- Move the cursor down to the last cell.
Read More: How to Calculate Bonus on Salary in Excel (7 Suitable Methods)
Step 4: Evaluating Gross Salary
The gross salary of an employee is the amount of salary that an employee gets after adding all allowances and before any deduction in provident funds. We will use the SUM function to add those.
Gross Salary = Basic Salary + All Allowances
- Choose the G15 cell and write the following,
=SUM(B15:F15)- Then, press the Enter button.
- As a result, we will have the gross salary of an employee for that month.
- Lower the cursor down to autofill the rest of the cells.
Read More: How to Calculate Gross Salary in Excel (3 Useful Methods)
Step 5: Determining Employees’ Provident Fund (EPF)
It is a fund voluntarily set up by both the Employer and the Employee to support the retirement of the employee. It varies with company, country, and even with the states of a country. Here, those whose salary is less than or equal to $2000 are going to give a 12% amount of the basic salary and those who earn greater than that will give $240.
- Firstly, select the H15 cell and type the following formula,
=IF(B15<=2000,B15*12%,240)- Then, hit Enter.
- Consequently, we will get the EPF amount for that employee.
- Lower the cursor to calculate the EPF for the rest.
Read More: How to Calculate Prorated Salary in Excel (3 Suitable Ways)
Step 6: Calculating Employees’ State Insurance Scheme (ESI)
Employees’ State Insurance Scheme is a fund for supporting the employee in uncertain times or accidents. Both the employee and employer provide a certain amount of money to this fund. It too depends on the company, country, and state of a country. Here, only those who earn less than $3000 in gross salary will be given ESI. Here, 0.75% of input is from the employee and 3.75% of input is from the employer.
- Firstly, select the I15 cell and write down the following,
=IF(G15<=3000,G15*0.75%,0)- Then, hit Enter.
- Consequently, we will have the ESI of the employee for that month.
- Move the cursor down to autofill for the rest of the employees.
Read More: How to Create a Salary Increase Matrix in Excel (With Easy Steps)
Similar Readings
- How to Calculate Annual Salary in Excel (with Detailed Steps)
- Salary Deduction Formula in Excel for Late Coming (with Example)
- How to Calculate Midpoint of Salary Range in Excel (3 Easy Ways)
- TDS Deduction on Salary Calculation in Excel Format
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
Step 7: Measuring Net Salary
The net salary is the amount of money that the employees take home after additional allowances and deduction in the name of EPF and ESI.
Net Salary = Basic Salary + Allowances -EPF-ESI
Or
= Gross Salary-EPF-ESI
- Choose the J15 cell and type the following formula in the cell,
=G15-H15-I15- Then, press Enter.
- As a result, we will get the net salary of the employee.
- Lower the cursor to autofill.
Read More: How to Calculate Net Salary in Excel (With Easy Steps)
Step 8: Calculating PF of Company
The company too has to put an amount in the Provident Fund for the employees’ retirement. It too varies with different parameters. Here, the company will add the same amount of money as the employee.
- Firstly, select the G7 cell and write the following in that cell,
=H15- Then, press the Enter button.
- As a result, we will get the company’s contribution to the PF.
- Finally, lower the cursor down to autofill the cells.
Step 9: Evaluating PF Expenses
The PF contribution of the company is accompanied by some costs. Here, the cost will be 1% of the basic salary if the salary is less than or equal to $2000. Otherwise, it will be $20.
- To start with, choose the H7 cell and type the following formula,
=IF(B15<=2000,B15*1%,20)- Then, press Enter.
- As a result, we will get the PF expenses on that employee by the company.
- Move the cursor down to autofill the rest of the cells.
Step 10: Calculating ESI Company
The company will provide certain percentage of the employees’ ESI. It depends on the company policy, country, etc. Here, the company will provide 3.25% of the ESI out of 4%.
- Select the I7 cell and write the following in the cell,
=I15/0.75*3.25- Hit Enter.
- As a result, we will get the ESI cost of the company on an employee.
- Finally, lower the cursor down to the last cell.
Step 11: Measuring Bonus
Bonus is the amount of money that an employee earns depending on his basic salary. It is too company and profession dependent. Here, those who earn less than $2000 will earn an 8.33% bonus on their basic salary.
- To start with, select the K7 cell and type the following,
=IF(J7="Yes",B15*8.33%,0)- Then, press the Enter button.
- Consequently, we will get the bonus for that employee (if eligible).
- Finally, lower the cursor down to the last cell.
Read More: How to Calculate Salary Increase Percentage in Excel
Step 12: Calculating CTC
Cost-To-Comapny (CTC) is the total amount of money a company invests on an employee including the employee’s gross salary, PF, PF expenses, ESI for the company, and bonuses. In this step, we will calculate the CTC to finally complete the salary breakup calculator in Excel.
- Select the L7 cell and type the formula below,
=G15+G7+H7+I7+K7- Hit Enter.
- As a result, we will get the monthly CTC of an employee.
- Finally, lower the cursor down to autofill the rest of the cells.
Conclusion
In this article, we have discussed how to create a salary breakup in Excel. This will allow a company to disburse the salary of their employees properly and also keep track of the flow of money on an employee.
Related Articles
- How to Calculate Income Tax on Salary with Example in Excel
- Daily Wages Sheet Format in Excel (with Quick Steps)
- How to Calculate Monthly Salary in Excel (with Easy Steps)
- How to Calculate Average Salary in Excel (6 Effective Methods)
- Leave Salary Calculation in Excel (With Easy Steps)
- How to Model Salary Regression Analysis in Excel (2 Ways)
