Salary Slip in Excel with PF, ESI and Professional Tax

📘 Excel for India Course · Lesson 4 of 7

A salary slip is a small calculation with a few India-specific rules. Here is a template that handles them.

Inputs

B2 Basic (monthly)        B3 HRA        B4 Special allowance
B6 Days in month          B7 Days paid (after loss-of-pay)

Earnings for days worked

Basic earned:    =ROUND(B2*B7/B6,0)
HRA earned:      =ROUND(B3*B7/B6,0)
Special earned:  =ROUND(B4*B7/B6,0)
Gross:           =SUM(D2:D4)

Provident Fund (employee 12%)

PF is 12% of basic (+DA). Many employers cap the wage at ₹15,000:

Employee PF:  =ROUND(MIN(D2,15000)*12%,0)      → max ₹1,800

If your company contributes on the full basic, use =ROUND(D2*12%,0) instead.

ESI (only for lower salaries)

ESI applies when gross wages are up to ₹21,000 a month. Employee share 0.75%, employer 3.25%:

Employee ESI:  =IF(D5<=21000,ROUNDUP(D5*0.75%,0),0)
Employer ESI:  =IF(D5<=21000,ROUNDUP(D5*3.25%,0),0)

Professional tax

PT is a state tax with state-specific slabs. Keep a small table per state and use XLOOKUP with the approximate-match mode. Example for a state where PT is ₹200 a month above a threshold:

=XLOOKUP(D5,PT[From],PT[Amount],0,-1)

Net pay

Total deductions:  =PF + ESI + PT + TDS
Net pay:           =Gross - Total deductions

TDS on salary is the next lesson.