How to Create Salary Breakup Calculator in Excel

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.

Create Salary Breakup Calculator in Excel: Step-by-Step Procedures

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 Structure 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,

• 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.

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,

• 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.

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,

• Then, press Enter.

• As a result, we will get a special allowance for specific employees.
• Move the cursor down to the last cell.

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,

• 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.

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,

• Then, hit Enter.

• Consequently, we will get the EPF amount for that employee.
• Lower the cursor to calculate the EPF for the rest.

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,

• 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.

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,

• Then, press Enter.

• As a result, we will get the net salary of the employee.
• Lower the cursor to autofill.

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,

• 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,

• 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,

• 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,

• 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.

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,

• 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.

Download Practice Workbook

You can download the practice workbook here.

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.

<< Go Back to Salary | Formula List | Learn Excel