Salary Sheet Format in Excel: PF, ESI, PT and TDS Explained

A salary sheet format in Excel has one row per employee and columns for fixed salary, paid days, earned pay, each deduction and net pay. Get the column order and three formulas right – pro-rata pay, PF and ESI – and the rest of payroll follows.

Below is the layout most Indian small businesses use, how each number is worked out for 2026-27, a worked example and the mistakes that cause trouble with PF, ESI and TDS returns.

The columns a salary sheet needs

• Employee details: code, name, designation, UAN, ESIC number, bank account.
• Fixed monthly salary: basic, DA, HRA, other allowances – the full-month figures.
• Attendance: days in the month, loss-of-pay (LOP) days, paid days.
• Earned salary: each component × paid days ÷ days in the month, plus overtime or incentives.
• Employee deductions: PF, ESI, professional tax, TDS, advances and loans.
• Net pay: earned gross minus deductions – the amount transferred to the bank.
• Employer contributions: PF (with EPS and EDLI), ESI and admin charges, to show the real cost to company.

Paid days and loss of pay

Earned salary is the fixed salary multiplied by paid days and divided by the days in the month. Paid days are days in the month minus LOP days; weekly offs, holidays and paid leave are paid. If an employee on ₹30,000 a month has 2 LOP days in a 30-day month, they earn ₹30,000 × 28 ÷ 30 = ₹28,000.

Some employers divide by 26 or 30 every month instead of the actual days. Any method is fine if it's written in your policy and applied consistently. Pull paid days straight from your attendance register so the two never disagree; an attendance and leave tracker can feed them in.

PF: 12% of basic + DA, with a ₹15,000 ceiling

The employee contributes 12% of PF wages (basic + DA). The employer also pays 12%, split into 8.33% to the pension scheme (EPS, capped at ₹1,250 a month because it's worked out on wages up to ₹15,000) and the rest to EPF, plus 0.5% for EDLI insurance and 0.5% admin charges.

Many employers contribute on the full basic + DA; others restrict PF to the ₹15,000 ceiling for employees who joined above it. Either way, keep it consistent and in your offer letters. Also remember the Code on Wages' 50% rule: if allowances are more than half of total pay, the excess counts as wages, which can raise PF.

ESI, professional tax and TDS

• ESI: applies to employees whose gross wages are up to ₹21,000 a month. The employee pays 0.75% and the employer 3.25% of gross wages.
• Professional tax: a state tax deducted monthly, up to ₹2,500 a year. Slabs differ by state, and some states have none – check your state's schedule.
• TDS on salary: under section 392 of the Income-tax Act, 2025, the employer deducts tax each month based on the estimated annual tax under the regime the employee chooses (the new regime by default). Spread the year's tax evenly and revise it when pay or declarations change.

Worked example

Rohit earns basic + DA of ₹15,500, HRA ₹6,200 and other allowances ₹3,300 – ₹25,000 a month. In a 30-day month he has 1 LOP day, so he is paid for 29 days.

1. Earned gross: ₹25,000 × 29 ÷ 30 = ₹24,167 (basic + DA earned: ₹14,983).
2. Employee PF: 12% of ₹14,983 = ₹1,798.
3. ESI: not applicable, as gross pay is above ₹21,000.
4. Professional tax: say ₹200 (depends on the state).
5. TDS: nil – at this pay, income is within the new regime's ₹12 lakh rebate.
6. Net pay: ₹24,167 − ₹1,798 − ₹200 = ₹22,169.
7. Employer PF: 3.67% EPF + 8.33% EPS (₹1,248) + EDLI and admin – about ₹1,948 on top of gross pay.

Due dates after the salary sheet

• Pay wages by the 7th of the following month (Code on Wages).
• Deposit PF and file the ECR by the 15th; pay ESI by the 15th.
• Deposit TDS by the 7th (30 April for March) and file the quarterly salary TDS statement.
• Pay professional tax by your state's due date.

Common mistakes

• Typing net pay by hand instead of using formulas – one wrong figure carries into the bank file.
• Forgetting LOP for joiners and leavers in their first and last months.
• Keeping ESI for an employee after their gross crosses ₹21,000 mid-period – coverage continues until the end of the contribution period.
• Not checking that employee PF, employer PF and the ECR total agree before uploading.

A ready salary sheet in Excel

Our free Salary Sheet & Payroll Register has these columns and formulas for up to 200 employees – LOP pro-rata pay, PF, ESI, professional tax, TDS, net pay and employer cost – with the monthly due dates. The Pro edition adds payslips, a bank transfer file and PF/ESI challan summaries. For a quick check on one person's take-home pay, use the salary calculator.