Sanjida Mehrun Guria
Before we calculate HRA on basic salary in excel, we must know what this term means. It refers to the full form of House Rent Allowance. Basically, it is a part of our gross salary which we receive along with our basic salary and other benefits. It is only applicable when we live in rented accommodation. The ultimate goal to calculate HRA on basic salary is to avail of HRA exemption during annual tax payments.
Download Workbook
You can find the sample workbook here. Download it and practice by yourself.
3 Quick Methods to Calculate HRA on Basic Salary in Excel
We can calculate HRA on basic salary in excel with 3 easy and quick methods. After getting the 3 results we will calculate the minimum amount of HRA as the final output. To begin the calculation, we prepared a dataset of the salary slip of an employee. Cell B12:C17 represents the sectors of income with amount along with the total salary for the month of May.
Now we will calculate HRA on basic salary in excel using the following 3 methods:
Method 1: Actual HRA Calculation on Basic Salary in Excel
Actual HRA is the amount the employer gives to the employee every month with the basic salary. Here we calculated actual HRA on basic salary in excel for 1 year or 12 months:
- First, input the amount of basic salary and HRA in cells C5 and C6 respectively that you received in May.
- Then, in cell D5 use this simple multiplication formula to multiply cell C5 by 12 to calculate the annual basic salary. (see screenshot)
=C5*12
- Following the same formula, multiply cell C6 by 12 or you can simply drag the bottom right corner of cell D5 to cell D6. It will automatically show the multiplied result.
- Lastly, insert the below formula in cell D7 to calculate the actual HRA on basic salary in 1 annual term.
=D6
- That’s it, we have got the actual HRA on basic salary.
Read More: How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
Method 2: Calculate HRA with Annual House Rent & Basic Salary
In this 2nd method, we will calculate HRA based on annual house rent and basic salary in excel. It follows the formula below:
HRA = Rent - 10% Basic SalaryLet’s look at the calculation now with these simple steps:
- Firstly, insert the amount of basic salary and house rent in cells C5 and C6.
- After that, in cell D5 multiply cell C5 by 12 using simple multiplication formula to calculate the annual amount of basic salary & house rent.
=C5*12
- Follow the same method to calculate the annual house rent in cell D6.
=C6*12
- Now, input this formula in cell D7 to calculate annual HRA with house rent and basic salary.
=D6-D5*10%
- Finally, we successfully calculated HRA with annual house rent and basic salary
Method 3: Get HRA on 50% of Basic Salary in Excel
The last method that helps us to find HRA is to calculate it with 50% of the basic salary. Let’s go through the below steps:
- First, Calculate the annual basic salary at first with the same multiplication process in cell D5.
- Secondly, in cell D6 type the formula below and you will get the HRA based on 50% of your basic salary:
=D5*50%
- Finally, we completed the calculation to get HRA on 50% basic salary in excel.
Read More: How to calculate salary increase percentage in Excel [Free Template]
How to Calculate HRA Exemption in Excel
Now, after finding the 3 HRA amounts we will calculate the minimum one for HRA exemption during tax payment. From the results above, we can easily understand the minimum amount. Otherwise, we can go for the process below:
- Insert the amounts that we calculated in cells C5, C6 and C7.
- Then, put this formula to find the minimum amount of HRA.
=MIN(C5:C7)
- This final amount is the HRA on basic salary. It will be deducted from the total amount of tax at the time of tax payment.
Things to Remember
To calculate HRA on basic salary, we must keep these in mind:
- Make sure you are calculating based on basic salary, not gross salary.
- HRA is not applicable for privately owned accommodation.
- HRA exemption is always calculated on an annual basis.
Conclusion
In this article, we have gone through 3 quick methods to calculate HRA on basic salary and finally found out the minimum HRA for exemption. It is very beneficial for salaried employees. Download the workbook, practice by yourself and don’t forget to give your wise suggestions.
