HRA Calculation in Excel: A Step-by-Step Guide

You are working in an organisation and receive your monthly salary. It is documented in a salary slip that contains Basic Salary as the fixed component. In addition, there are other variable allowances, such as Dearness Allowance, House Rent Allowance (HRA), and many more such components. There are many ways to calculate the House Rent Allowance, but in this blog, we discuss HRA Calculation in Excel.   

We know that MS Excel is a tool that can be used for many applications, and financial analysis is one of them. According to a report by Financial Post, about 750 million global users use Excel. It is a tool that can process and communicate massive amounts of information to users. In this article, we'll go through some easy Excel formulas for calculating HRA on a basic salary with HRA exemption. Continue Reading to know more! 

Table of Contents 

1) Definition of House Rent Allowance (HRA)  

2) Ways to perform HRA calculation on basic salary in Excel 

3) Compute HRA exemption in Excel 

4) Claiming Deduction in HRA: Take note of the mandatory points  

5) Conclusion

Definition of House Rent Allowance (HRA)

HRA is an acronym for House Rent Allowance, which in the Indian Payroll System forms a part of the variable allowance of the salary offered by any organisation. The employer pays HRA to the employee in addition to the basic salary paid. HRA is given to help employees to benefit from tax towards accommodation expenses per year.    

Factors like salary structure, amount based on the grade or level of the employee in the organisational structure, and the city or town where the employee resides decide how much the employer will be willing to pay HRA to that particular employee. 


 

Ways to perform HRA calculation on basic salary in Excel

Some factors affect the HRA amount to any employee. There are three ways to determine how to Calculate HRA in Excel, and they are illustrated with the help of an example below by performing the calculation in Excel. First, let us take an example of a sample salary slip prepared in Excel for Employee 1, working at XYZ Company Private Limited for January 2023. 



The different salary components with their amount are displayed in cells B and C, respectively. With this data available, we will now calculate HRA on basic salary using Excel. 

Actual HRA Calculation on Basic Salary in Excel 

With the basic salary an employee receives every month, HRA is the next component the employer gives. Here the figures that are written in Excel are monthly. 



Both the value of the amount is written in cell K. Using this data input in Excel; we will perform the calculation of both Basic Pay and HRA on a yearly basis. This is done by multiplying cell K by 12; the answer is displayed on cell L. This is shown in the image below: 
 

Finally, for one complete term, the HRA is computed by inserting the value found from the yearly calculation and displayed next to the Actual HRA for that year.






Through this method in Excel, we have computed the actual HRA on Basic pay. 

Using Annual House Rent & Basic Salary calculate the value of HRA in Excel 

The second method involves the inclusion of yearly house rent paid by the employee and basic pay. Here the figures are again written in Excel on a monthly basis which is shown below: 






To compute the HRA based on the house rent and basic pay we use the formula 
HRA= House Rent − 10% Basic Pay  

We will use the above formula in Excel to calculate the annual HRA which the employee has to receive. The formula is written by using the values from the cell L of the expenditure data. It will help perform HRA calculation formula in Excel.  

Obtain HRA on 50 percent of Basic Salary in Excel 

The third method is fairly simple and involves just the basic pay as the input. The HRA calculated is just half of the basic pay. Here the figures for basic pay in Excel are monthly. 




Next, we will multiply the basic pay by 12 to get the annual basic pay, and the values are displayed in cell L.  



To calculate the annual HRA we will just multiply the yearly basic pay by 50% and display the value next to the HRA. 





 

Learn to know about VBA and built macros that can automate complicated processes by signing up for our Microsoft Excel VBA And Macro Training course now! 

Compute HRA exemption in Excel 

From the last section, we have seen how annual HRA was computed in Excel using three methods. Now taking the HRA values from all three cases, we will compute the minimum of HRA while taking into account paying taxes.



To find the minimum annual HRA out of the three cases, we use the minimum function available in Excel and compute the minimum of three annual HRA values. 





 

Once the employee files tax annually, the minimum amount computed out of the three is the amount that is deducted from the total tax amount. 

Claiming Deduction in HRA: Take note of the mandatory points 

You need to consider some important points to claim HRA deduction. They are discussed below:    

a) If you are living in a house that you solely own, you do not have the eligibility to claim HRA. This is because it is only meant to cover the cost of your rented accommodation, i.e. if you stay in a rented home. Secondly, you are not allowed an HRA exemption, even if you pay rent to your spouse.  

b) If you are staying in Delhi, Mumbai, Kolkata, and Chennai, which are metropolitan cities, you can claim up to about 50 percent as a tax deduction. Whereas, if you live in a non-metropolitan city like Bengaluru, Hyderabad, or Pune, etc., you can only claim up to 40 percent as a tax deduction.  

c) You can claim HRA exemption if you live with your parents and provide a rent receipt in their name. But, when it comes time to file their income tax returns, they must include the same rent as part of their income.  

d) The landlord's PAN is necessary to be exempt from HRA if you pay more than Rs 1 lakh in annual rent. A signed declaration should be sufficient without a PAN in the landlord's name.  

Conclusion 

After reading this blog, you will have learned the definition of House Rent Allowance and the factors on which the HRA is paid to an employee working in that organisation. Next, we have also gone through three different ways of HRA Calculation in Excel and finally found the minimum HRA for exemption. Knowing HRA for tax exemption is very beneficial for salaried employees. 

Want to learn the top 10 Excel shortcuts and gain knowledge on highlighting Excel data cells by signing up for our Microsoft Excel Masterclass course now!