Wasim Akram
People who work in a company mostly need to make a salary sheet to calculate the salary of employees. In Microsoft Excel we can calculate the monthly salary sheet very easily just by using some simple formulas. In this article, I am explaining how to calculate monthly salary in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Quick Steps to Calculate Monthly Salary in Excel
In the following article, I have shared 3 quick steps to calculate monthly salary in excel.
Suppose we have a dataset of some Employee Name, Basic Salary of employees, and Total Working Days. Now we are going to calculate the monthly salary in an excel sheet.
Step 1: Determine Gross Salary for Each Employee
In this step firstly we will make the payment parts where we will calculate all the payments that need to be paid. With basic salary, there are some allowances paid by the company.
In the following screenshot, you will find some new columns where allowances-Conveyance, House Rent, Medical will be calculated according to the company’s terms and conditions. After that, we will calculate the Gross Salary.
- Select a cell (E7) to calculate conveyance in the chosen cell.
- Apply the formula-
=IF(C7>=8000,C7*3%,C7*2%)Where,
- (C7>=8000,C7*3%,C7*2%) stands for the company’s term. The term is if an employee’s salary is equal to or more than $8000, he/she will get 3% of conveyance, otherwise, he/she will get 2%.
- Press Enter and drag down the “fill handle” to fill.
- Here we have calculated the conveyance payments for all employees.
- Now, we will calculate the house rent allowance.
- Choose a cell. Here I have selected cell (F7) to apply the formula.
- Put the formula down-
=IF(C7>=8000,C7*10%,C7*8%)
- Hit the Enter button and then drag it down to fill all the cells.
- Here we have house rent calculated for all the employees.
- To calculate the medical allowances select a cell and apply the formula to the cell. Here I have chosen cell (G7).
- Write the following formula-
=IF(C7>=8000, C7*4%,C7*3%)
- Click the Enter button to continue.
- Pull the “fill handle” down to get the output.
- Now we have calculated the output of medical allowances.
- Finally we can calculate the total gross salary. To do so we will sum all the allowances and then sum with the total working days to get the value.
- Choose a cell (H7) and apply the following formula-
=SUM(E7:G7)+C7/31*D7
- Hit the Enter button and drag it down to get all the employee’s gross salary.
- So, we have finished and got our values in our gross salary column representing the amount an employee will be paid.
Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
Similar Readings
- How to Create Tally Salary Slip Format in Excel (With Easy Steps)
- Calculate Bonus on Salary in Excel (7 Suitable Methods)
- How to calculate salary increase percentage in Excel [Free Template]
Step 2: Perform Necessary Deduction from Gross Salary
This time we will calculate the deduction from the gross salary to calculate the monthly salary in excel.
Here we have created some new columns which will represent Income Tax, Provident Fund (PF), and Total Deduction.
- According to terms income tax is 5% whose salary is over or equal to $9000.
- To get the output according to the terms choose a cell to apply the formula. I have chosen cell (I7).
- Write the formula down-
=IF(H7>=9000,H7*1.5%,0)
- Press Enter and pull the “fill handle” down.
- Now we have income tax values in our hand which will be deducted from the gross salary.
- To calculate the provident fund select a cell (J7) and write the formula down-
=H7*5%
- Click Enter and drag down the “fill handle” to fill with precious output.
- We have calculated provident fund values for all the employees.
Now let’s sum the income tax and provident fund to get the total deduction value.
- Choose a cell (K7).
- Apply the formula-
=SUM(I7:J7)
- Press Enter and fill the cells pulling the “fill handle” down.
- Here we have total deduction values.
Read More: How to Create Salary Slip Format with Formula in Excel Sheet
Step 3: Calculate the Monthly Net Salary
It’s time for the final part to calculate the monthly net salary which will be actually paid after the end of the month.
- Select a cell (L7) and put the following formula down-
=H7-K7
- Hit the Enter button and drag the “fill handle” down.
- Finally, we have our desired output where we got the output by subtracting the deduction amount from the gross salary.
- After all the steps are completed our final table will look like this.
Things to Remember
- Sometimes a term might come for some employees’ advanced salary. Just open a new worksheet and attach the values in the deduction part using the VLOOKUP function.
Conclusion
In this article, I have tried to cover all the simple methods to calculate monthly salary in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
