Rafiul Hasan
If you run an institution or work for a company, then you may need to calculate the basic salary of the employees of the institution. In this article, I will show you how to calculate the basic salary of some employees in an Excel worksheet.
Download Practice Workbook
Useful Common Terms of Salary
Basic Salary
Basic Salary is the base part of the total salary which doesn’t include any other allowances. Overtime, bonus, medical allowance, etc. are not part of the basic salary. Adding all other allowances to the basic salary comes out the total salary received by an employee. So, the basic salary is a fixed amount for an employee of a company.
CTC
CTC means “Cost To Company” which indicates the total amount of expenses a company spends on an employee including the Gross Salary and all other benefits an employee gets during the service period.
PF
PF (Provident Fund) refers to the amount which is deducted from the salary and put away in a PF account. It is designed to ensure enough funds are saved to get an employee through retirement adequately.
3 Common Cases to Calculate Basic Salary in Excel
In this section, you’ll find a method for calculating basic salary in Excel for 3 different cases. Let’s try them now!
1. Calculate Basic Salary Using Gross Salary
Suppose, we have a data set of the Gross Salary of the employers of a company and the percentage of Gross Salary which will denote the Basic Salary of the employees.
Now, let’s start the procedure.
- First of all, add a column where you want the Basic Salary of the employers. Then add the following formula to the cell where you want to get the result.
=C5*D5Here,
- C5= Gross Salary
- D5= Basic Salary Percentage
- Click ENTER and the desired output will come.
- Now use the Autofill to drag the formula to every cell you want the output and you will get the basic salary of every employee.
That’s how we can calculate the Basic Salary of the employees of an institution based on the Gross Salary.
Read More: How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
2. Calculation of Basic Salary Using CTC
Let’s say, we have a set of data of CTC for the employees and the Basic Salary percentage of CTC. We want to calculate the Basic Salary from this percentage.
So, let’s start the procedure.
- Firstly, apply the following formula to the cell where you want to get your desired output.
=C5*D5Here,
- C5= CTC
- D5= Basic Salary Percentage of CTC
- Press ENTER and you will get the output.
- Use the Autofill to drag the formula to every cell you want to get the output and now your result will be ready.
Thus if we have the data set of CTC and the Basic Salary percentage of CTC, we can quickly get the Basic Salary of the employees.
Read More: How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
3. Deducting PF to Calculate Basic Salary
Sometimes we may have a set of data of the PF (Provident Fund) which is deducted from the salary of the employees every month.
We want to get the basic salary using the PF deduction percentage. Let’s check the procedure.
- Firstly, go to the cell you want to get the result and apply the following formula:
=C5*(1/D5)Here,
- C5 = PF Deduction
- D5 = PF Deduction percentage
- Press ENTER and you will get the output at the selected cell.
- Now, use the Autofill to drag the formula to every cell you want and you’ll get your desired output.
Thus, we can easily get the basic salary of employees of a company by applying the simple mathematical calculation by using the PF deduction percentage.
Read More: How to calculate salary increase percentage in Excel [Free Template]
Conclusion
In this article, we have learned how to calculate the basic salary of the employees of an institution in an Excel worksheet. I hope from now on you can easily calculate the basic salary for as many employees as you want in an Excel worksheet. If you have any queries regarding this article, please don’t forget to leave a comment below. Have a great day!
