The following dataset contains the salary slip of an employee. B12:C17 showcases the income sources and the total salary for May.
Method 1 – HRA Calculation on the Basic Salary in Excel
- Enter the amount of basic salary and HRA for May in C5 and C6.
- In D5 use this formula to multiply C5 by 12 to calculate the annual basic salary.
=C5*12
- Using the same formula, multiply C6 by 12 or drag the Fill Handle to D6 to copy the formula.
- Enter this formula in D7 to calculate the actual HRA on basic salary in 1 annual term.
=D6
The table showcases the actual HRA on basic salary.
Method 2 – Calculate HRA with Annual House Rent & Basic Salary
- The formula below is used.
HRA = Rent - 10% Basic Salary- Enter the amount of basic salary and house rent in C5 and C6.
- In D5 multiply C5 by 12 using a 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 D6.
=C6*12
- Enter this formula in D7 to calculate the annual HRA with house rent and basic salary.
=D6-D5*10%
The table showcases HRA with annual house rent and basic salary.
Method 3 – Get HRA on 50% of Basic Salary in Excel
- Apply the same multiplication process in D5 to calculate the annual basic salary.
- Enter the formula below in D6 to see HRA based on 50% of your basic salary:
=D5*50%
The table below showcases HRA on 50% of the basic salary.
How to Calculate HRA Exemption in Excel
- Insert the calculated amounts in C5, C6 and C7.
- Enter 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.
Download Workbook
You can find the sample workbook here. Download it and practice.
<< Go Back to Salary | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
