TDS on Salary in Excel: New vs Old Regime Calculator

📘 Excel for India Course · Lesson 5 of 7

Employers deduct TDS from salary each month based on the estimated tax for the year. Here is a compact calculator for tax year 2026-27 (slabs unchanged by Budget 2026).

New regime (default)

Slabs: nil up to ₹4 lakh, then 5% steps every ₹4 lakh up to 30% above ₹24 lakh. Standard deduction ₹75,000 for salaried employees.

B2  Annual gross salary
B3  Taxable income:   =MAX(B2-75000,0)
B4  Slab tax:         =SUMPRODUCT((B3>{400000,800000,1200000,1600000,2000000,2400000})
                               *(B3-{400000,800000,1200000,1600000,2000000,2400000})*0.05)

The trick: each slab adds 5 percentage points on the income above it, so one SUMPRODUCT covers all slabs.

Rebate and marginal relief

Income up to ₹12 lakh pays no tax (rebate up to ₹60,000). Just above ₹12 lakh, tax can’t exceed the income above ₹12 lakh:

B5  After rebate:  =IF(B3<=1200000,0,MIN(B4,B3-1200000))
B6  Cess 4%:       =ROUND(B5*4%,0)
B7  Annual tax:    =B5+B6
B8  Monthly TDS:   =ROUND(B7/12,0)

Worked example: gross ₹15,00,000 → taxable ₹14,25,000 → slab tax ₹93,750 → no rebate → cess ₹3,750 → ₹97,500 a year, about ₹8,125 a month.

Old regime

Standard deduction ₹50,000, plus deductions such as 80C and 80D. Slabs for under-60s: nil to ₹2.5 lakh, 5% to ₹5 lakh, 20% to ₹10 lakh, 30% above.

Taxable:    =MAX(B2-50000-Deductions,0)
Slab tax:   =SUMPRODUCT((T>{250000,500000,1000000})*(T-{250000,500000,1000000})*{0.05,0.15,0.1})
Rebate:     =IF(T<=500000,0,SlabTax)

Compare and choose

Put both regimes side by side and highlight the lower total with conditional formatting. For most salaries without large deductions the new regime wins; with big home-loan interest and 80C/80D claims the old one can still be better.