Most HRA calculators take one rent figure for the whole year. Real years aren't like that: rent goes up at renewal, you move cities, or you start paying rent in October. The exemption is worked out on the salary, HRA and rent of each period, so a single annual sum gets it wrong. This sheet works it out month by month, and you can see every formula.
⬇ Download: HRA calculation in Excel (.xlsx, free)What's in the sheet
- Twelve rows, April 2026 to March 2027, each with its own basic + DA, HRA received, rent paid and city.
- A Metro / Other dropdown per month, using the eight-city list for tax year 2026-27.
- All three limits worked out for every month, the exempt amount, and the taxable HRA.
- Year totals, plus a flag that tells you when rent crosses ₹1 lakh and your employer needs the landlord's PAN.
How to fill it
- Enter April's basic + DA, HRA and rent; every later month copies April.
- Overwrite only the months that changed: a rent hike from the renewal month, a new city from the month you moved.
- Read the totals: HRA exempt for the year, and the taxable HRA your employer adds back to salary.
The formulas, for one month
Row 6 is April; every other month repeats the same five formulas on its own row.
HRA formulas in Excel
10% of salary: =ROUND(B6*10%,0)
Rent minus 10%: =MAX(0,D6-F6)
50% or 40% of salary: =ROUND(B6*IF(E6="Metro",50%,40%),0)
HRA exempt (least): =MIN(C6,G6,H6)
Taxable HRA: =C6-I6
B = basic + DA, C = HRA received, D = rent paid, E = Metro or Other
Two rules that changed or catch people out
- The 50% list grew this year: from tax year 2026-27 it is Delhi, Mumbai, Kolkata, Chennai, Bengaluru, Hyderabad, Pune and Ahmedabad. Older sheets still use only the first four.
- HRA exemption exists only in the old tax regime. In the new regime the whole HRA is taxable, so compare both before you choose.
Frequently asked questions
- How do I calculate HRA exemption in Excel?
- For each month, work out three figures: the HRA you received, rent paid minus 10% of basic + DA, and 50% (metro) or 40% (other cities) of basic + DA. The exemption is the smallest, with =MIN(). The sheet above does this for all twelve months.
- Which cities count as metro for HRA in 2026-27?
- From tax year 2026-27, Delhi, Mumbai, Kolkata, Chennai, Bengaluru, Hyderabad, Pune and Ahmedabad get the 50% limit. Every other city gets 40%.
- Can I claim HRA in the new tax regime?
- No. HRA exemption is available only in the old regime. In the new regime all HRA received is taxable.