Sometimes you may need to Calculate Dearness Allowance (DA) on Basic Salary in Excel. In this article, I’m going to explain to you three methods of how to calculate DA on Basic Salary in Excel.
What Is Dearness Allowance (DA)?
Dearness Allowance is paid to the employee by the authority for balancing the impact of inflation. Basically, Dearness Allowance is a percentage of the basic salary. Which will be included in your salary to cope with the present situation of the monetary value of your country’s currency.
In addition, Dearness Allowance(DA) depends on the terms & conditions of individual companies, sectors, and countries.
Calculate DA on Basic Salary in Excel: 3 Methods
For your better understanding, I’m going to use an example of the following dataset. Which contains four columns. These are Name, Salary, Total Days, and Worked Days. Now, I will describe to you about the methods to Calculate DA on Basic Salary in Excel. The methods are given below:
1. Using IF Function to Calculate DA on Basic Salary
You can use the IF function to calculate DA in Excel. For this method, I’m going to use the following dataset. Which contains two columns. Those are Name and Salary.
Steps:
- Firstly, select a different cell D5 where you want to calculate the DA percentage.
- Secondly, use the corresponding formula in the D5 cell.
=IF(C5>=7000,"8%","7%")
Here, in the IF function, I used a logical_test where I want to check the values greater than or equal to $7000 to return the DA percentage from the Salary range. If the salary is greater than or equal to $7000 then it will return the DA percentage as 8% otherwise it will return 7%.
- Then, press ENTER to get the value in the DA Percentage column.
At this time, you will get the DA value in percentage form.
- Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D5:D9.
After that, you can see the following result.
Now, I’m going to find the final DA Amount.
- For this firstly, select the cell E5 where you want to show the DA Amount.
- Secondly, use the corresponding formula in the E5.
=C5*D5
Here, in this formula, I have multiplied the Salary range by the DA Percentage to get the final DA Amount.
- Now, press ENTER to get the DA Amount.
- Similarly, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E5:E9.
2. Applying IF and Round Functions to Calculate DA on Basic Salary
You can apply IF and ROUND functions to calculate DA on basic salary. Suppose, you have the following dataset. Which has four columns. These are Name, Salary, Total Days, and Worked Days.
Steps:
- Firstly, select a different cell F5 where you want to calculate the DA Amount.
- Secondly, use the corresponding formula in the F5 cell.
=IF(E5=0,"",ROUND(C5/D5*E5*7%,-1))
Formula Explanation
- E5=0 : this is the condition of the IF Function.
- “” : When the IF condition satisfies, it will return a blank cell.
- ROUND(C5/D5*E5*7%,-1) :When the IF function does not satisfy it will round the DA value of to the decimal figure.
- IF(E5=0,””,ROUND(C5/D5*E5*7%,-1)): It will check the Worked Days values equal or not equal to 0 to return the DA amount. If the worked days equal to 0 then it will return the DA amount as a space otherwise it will return the DA amount
- Now, press ENTER to get the value of DA Amount.
Finally, you can see the DA Amount.
- For other data, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F5:F9.
Finally, you will get all the DA Amount.
3. Using Generic Formula to Calculate DA on Basic Salary
You can use generic formula as well to calculate DA on basic salary. For this method, let you have the following dataset.
Steps:
- At first, select a different cell D5 where you want to calculate DA.
- Then, use the corresponding formula in the D5 cell.
=(C5*12)/100
Here, in this formula, I have multiplied Salary by 12 and then divided it by 100.
- Then, press ENTER to get the value of Dearness Allowance .
- Finally, follow the Fill Handle procedure and you will get the calculated Dearness Allowance.
Things to Remember
- For your own company, firstly you must know the terms and conditions of Dearness Allowance (DA). According to that policies you have to edit the values used in this workbook.
Practice Section
You can download the free workbook and there you will find a practice section.
Download Practice Workbook
You can download the practice workbook from here:
DA Calculation on Basic Salary.xlsx
Conclusion
I hope you found this article helpful. Here, I have explained 3 methods of how to calculate Dearness Allowance (DA) on basic salary in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
<< Go Back to Salary | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!