How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)

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.

3 Quick Methods to Calculate HRA on Basic Salary in Excel

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.

Actual HRA Calculation on Basic Salary in Excel

  • 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

Actual HRA Calculation on Basic Salary in Excel

  • 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

Actual HRA Calculation on Basic Salary in Excel

  • 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 Salary

Let’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.

Calculate HRA with Annual House Rent & Basic Salary

=C5*12

Calculate HRA with Annual House Rent & Basic Salary

  • 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%

Calculate HRA with Annual House Rent & Basic Salary

  • Finally, we successfully calculated HRA with annual house rent and basic salary

Calculate HRA with Annual House Rent & 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.

Get HRA on 50% of Basic Salary in Excel

  • Secondly, in cell D6 type the formula below and you will get the HRA based on 50% of your basic salary:
=D5*50%

Get HRA on 50% of Basic Salary in Excel

  • 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.

How to Calculate HRA Exemption in Excel

  • Then, put this formula to find the minimum amount of HRA.
=MIN(C5:C7)

How to Calculate HRA Exemption in Excel

  • 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.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo