* Salary Breakup* is a very powerful way to show the how the

*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*

**CTC (cost-to-company)***.*

**Excel****Table of Contents**hide

## 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
cell and write down the following formula,**B15**

`=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):**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**the VLOOKUP function**in the range**EmplpyeeID A-102**in the sheet named**B5:D8**where we have kept the salary structure of the company. Then, in the**Structure**argument we will enter**3rd**because the value**3**is in the**Basic Salary**column of that range. Finally, we set the**3rd**value to be zero. So, the function will return**range_lookup**as fits with the employee ID A-102.**$1500****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 thedays, so the basic salary will not change as**31**days on the top will nullify**31**days in the bottom.**31**

**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

*of the basic salary. However,*

**50%***too depends on the how many days did the employee attend his/hers work.*

**HRA**- Firstly, select the
cell and write the following formula down,**C15**

`=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
cell and type the following,**D15**

`=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
cell and write the following,**G15**

`=SUM(B15:F15)`

- Then, press the
button.**Enter**

- 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

*amount of the basic salary and those who earn greater than that will give*

**12%***.*

**$240**- Firstly, select the
cell and type the following formula,**H15**

`=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,

*of input is from the employee and*

**0.75%***of input is from the employer.*

**3.75%**- Firstly, select the
cell and write down the following,**I15**

`=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
cell and type the following formula in the cell,**J15**

`=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
cell and write the following in that cell,**G7**

`=H15`

- Then, press the
button.**Enter**

- 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
cell and type the following formula,**H7**

`=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
cell and write the following in the cell,**I7**

`=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

*bonus on their basic salary.*

**8.33%**- To start with, select the
cell and type the following,**K7**

`=IF(J7="Yes",B15*8.33%,0)`

- Then, press the
button.**Enter**

- 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
cell and type the formula below,**L7**

`=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)**