This free salary breakup calculator Excel converts CTC to in-hand salary under the 2026 Labour Code rules. Doing it in a spreadsheet is harder than it used to be: Since the Labour Codes came into force on 21 November 2025, PF, ESI, bonus and gratuity all depend on how "wages" are defined, and the EPF wage ceiling rose to ₹25,000 on 17 September 2026. The Excel applies those rules for you: change one number and it builds the full structure — Basic, HRA, allowances, employer contributions, deductions, net pay and CTC — with every formula visible.
.xlsx · 1 sheet · works in Excel and Google Sheets · no sign-up · updated October 2026
Salary Breakup Calculator Excel: What It Calculates
You change the blue input cells; everything else is a formula.
| You enter | The Excel calculates |
|---|---|
| Total remuneration (gross salary + employer PF) | Basic + DA, HRA, conveyance allowance and gross salary |
| State minimum wage | Keeps Basic + DA at or above it, and sets the statutory bonus base |
| HRA % of Basic (50% metro, 40% non-metro) | HRA |
| PF on ceiling or actual wages | Employee and employer PF |
| Professional Tax for your state | Total deductions |
| — | Statutory wages, employer ESI, statutory bonus, gratuity, employee ESI, net pay, monthly CTC and annual CTC |
| — | PASS / FAIL checks: minimum wage, 50% wage rule, and structure |
Change total remuneration up or down until net pay or CTC matches the figure you want. For complex structures — extra allowances, in-kind benefits, a CTC-based or wages-based start — use the online salary breakup calculator, which also exports a ready salary annexure in Excel or PDF.
Why Most Salary Breakup Excel Templates Are Wrong
Most templates in circulation were built before the Labour Codes and still use fixed assumptions:
| Item | Old templates | This Excel (2026) |
|---|---|---|
| PF base | 12% of Basic, or of total pay | 12% of statutory wages |
| PF ceiling | ₹15,000 — fixed ₹1,800 | ₹25,000 — up to ₹3,000 each side, or PF on actual wages if you choose |
| ESI eligibility | Gross salary ≤ ₹21,000 | Statutory wages ≤ ₹21,000 |
| ESI rates | Often out of date | 0.75% employee, 3.25% employer, rounded up to the next rupee |
| Statutory bonus | Paid to everyone, often on Basic | Only where wages are ₹21,000 or less, on ₹7,000 or the minimum wage, whichever is higher |
| Gratuity | 4.81% of Basic | 4.81% of statutory wages |
| Compliance checks | None | Minimum wage, 50% wage rule and structure, each PASS or FAIL |
The difference is real money. On ₹60,000 total remuneration, a template still capped at ₹1,800 understates employer PF by ₹1,200 a month — ₹14,400 a year — and shows a take-home ₹1,200 too high. The wage definition, the 50% rule and each statutory component are explained step by step, with worked scenarios, in the Salary Calculation Master Combo (eBook + Excel calculator).
How PF and ESI Are Calculated in This Excel
PF: wage-based, with the ₹25,000 ceiling
PF is 12% of statutory wages from both employee and employer. Choose Ceiling and wages above ₹25,000 are capped, so PF stops at ₹3,000 a month each side; choose Actual and PF runs on full wages. Employees with wages up to ₹25,000 are mandatorily covered, so both options give the same figure below the ceiling.
ESI: tested on wages, not gross
ESI applies where statutory wages are ₹21,000 a month or less (₹25,000 for persons with disabilities). Because the test is on wages, ESI can apply even when gross salary is above ₹21,000 — the ₹25,000 example below shows exactly that. Coverage is fixed for each six-month contribution period (April–September and October–March), so if wages cross ₹21,000 mid-period, ESI continues until the period ends.
Excel Salary Sheet Formulas (India)
These are the formulas in the free calculator, with the cells they use, so you can check the logic or copy it into your own salary sheet:
| Line | Excel formula | Rule behind it |
|---|---|---|
| Basic + DA (C11) | =MAX(ROUND(C6*50%,0),C7) | 50% of total remuneration, never below the minimum wage — so the 50% wage rule is always met |
| HRA (C12) | =ROUND(C11*C8,0) | HRA % of Basic |
| Conveyance (C13) | =C6-C11-C12-C17 | Balancing allowance |
| Statutory wages (C15) | =C11 | HRA, conveyance and employer PF are excluded items under the Code on Wages |
| PF employer (C17) | =ROUND(IF(C9="Actual",C15,MIN(C15,25000))*12%,0) | 12% of wages, capped at ₹25,000 unless "Actual" is chosen |
| ESI employer (C18) | =IF(C15<=21000,ROUNDUP(C15*3.25%,0),0) | 3.25% of wages up to ₹21,000 |
| Statutory bonus (C19) | =IF(C15<=21000,ROUND(MIN(C15,MAX(7000,C7))*8.33%,0),0) | 8.33% of ₹7,000 or the minimum wage, whichever is higher |
| Gratuity (C20) | =ROUND(C15*4.81%,0) | 15 days' wages a year on a 26-day month: 15 ÷ 26 ÷ 12 = 4.81% |
| ESI employee (C26) | =IF(C15<=21000,ROUNDUP(C15*0.75%,0),0) | 0.75% of wages up to ₹21,000 |
| Net pay (C29) | =C14-C28 | Gross minus PF, ESI and Professional Tax (before TDS) |
| Monthly CTC (C22) | =C14+C21 | Gross plus employer PF, ESI, bonus and gratuity |
Professional Tax is entered by hand because it depends on your state; check the amount with the professional tax calculator.
Two Worked Examples
Both examples use the Excel exactly as downloaded — minimum wage ₹10,000, HRA at 40% of Basic, PF on the ceiling and ₹200 Professional Tax — changing only total remuneration.
| Line (monthly, ₹) | ₹25,000 total remuneration | ₹60,000 total remuneration |
|---|---|---|
| Basic + DA | 12,500 | 30,000 |
| HRA | 5,000 | 12,000 |
| Conveyance allowance | 6,000 | 15,000 |
| Gross salary | 23,500 | 57,000 |
| Statutory wages | 12,500 | 30,000 |
| Employer PF | 1,500 | 3,000 |
| Employer ESI | 407 | — |
| Statutory bonus | 833 | — |
| Gratuity | 601 | 1,443 |
| Monthly CTC | 26,841 | 61,443 |
| Employee PF | 1,500 | 3,000 |
| Employee ESI | 94 | — |
| Professional Tax | 200 | 200 |
| Net pay (before TDS) | 21,706 | 53,800 |
| Annual CTC | 3,22,092 | 7,37,316 |
₹25,000 — ESI applies although gross is above ₹21,000. Gross salary is ₹23,500, but statutory wages are ₹12,500, so ESI applies: ₹407 from the employer and ₹94 from the employee. A template testing gross against ₹21,000 would leave out ESI entirely and overstate take-home by ₹94 a month. Statutory bonus of ₹833 (8.33% of the ₹10,000 minimum wage) is also a real employer cost inside CTC that most salary sheets miss. PF is ₹1,500 — 12% of actual wages, because they are below the ₹25,000 ceiling.
₹60,000 — PF at the ceiling, no ESI. Wages of ₹30,000 are above ₹21,000, so ESI and statutory bonus fall away, and PF stops at ₹3,000 each side. Switch PF to "Actual" and it becomes ₹3,600 each side: CTC stays ₹61,443, and net pay falls to ₹52,600.
How to Use the Excel (Quick Steps)
- Download the .xlsx file and open it in Excel or Google Sheets.
- Enter total remuneration — gross salary plus employer PF.
- Enter the state minimum wage for the employee's zone and skill.
- Set HRA at 50% of Basic for a metro or 40% elsewhere.
- Choose PF on the ceiling or on actual wages.
- Enter Professional Tax for your state.
- Read the result and confirm all three checks show PASS. If the structure check fails, total remuneration is too low for the minimum wage — increase it.
Income tax is not deducted in the sheet, because TDS depends on each employee's regime and declarations. For take-home after tax under both regimes, use the in-hand salary calculator. If a downloaded copy shows a calculation error, rename it, upload it to Google Drive, open it in Google Sheets and download it again.
Excel Salary Sheet Format for Monthly Payroll
The free calculator works out one employee's salary structure at a time. Monthly payroll for a team needs a salary sheet in Excel laid out as a register — one row per employee, with columns in the order payroll is processed:
| Column group | Columns |
|---|---|
| Employee details | Sl. no., employee name, employee code, department, location (state), UAN, ESI number |
| Attendance | Days in month, paid days, LOP days |
| Fixed (rate) salary | Basic + DA, HRA, other allowances, gross |
| Earned (paid) salary | Each component prorated for paid days, earned gross |
| Employee deductions | PF, ESI, Professional Tax, LWF, TDS, other deductions, total deductions |
| Employer contributions | PF, ESI, LWF, bonus, gratuity, insurance |
| Totals | Net pay, monthly CTC, annual CTC |
Prorate each component with =ROUND(Rate/DaysInMonth*PaidDays,0), then apply the PF, ESI, bonus and gratuity formulas above to the earned wages, so an employee with LOP days has correctly lower contributions. Overtime is paid at twice the ordinary rate of wages; the hourly rate is commonly worked out on 26 days of 8 hours. If you would rather start from a finished register, the Complete Indian Payroll Excel Workbook has monthly salary registers with PF and ESI ECR working sheets, a bank payment list and a payslip format. To check one employee's pay for days worked, use the monthly salary calculator.
Excel vs the Online Salary Breakup Calculator
| Free Excel | Online calculator | |
|---|---|---|
| Best for | A simple breakup offline, saving and editing your own copy | Any structure, on any device |
| Structure | Basic + DA, HRA, conveyance | Extra allowances, in-kind benefits, CTC- or gross-based |
| Professional Tax | Entered by you | Applied by state |
| Salary annexure | Print the sheet | One-click Excel or PDF annexure |
Who Should Use This Salary Breakup Excel
- Employees and job seekers — see what a package really pays and how much of it is employer cost you never receive.
- HR teams — draft compliant salary structures for offer letters and check them against the minimum wage and the 50% rule.
- Small businesses and startups — fix compliant salaries before setting up monthly payroll.
- Principal employers — check that contractors' wage sheets for contract labour apply the same wage, PF and ESI rules; the CLRA compliance and audit eBook covers the wider contractor audit.
Common Mistakes in Excel Payroll Calculation
- Calculating PF on Basic alone, or on total pay, instead of on statutory wages.
- Keeping the old ₹15,000 ceiling and the fixed ₹1,800 PF figure.
- Testing ESI eligibility on gross salary instead of wages, or using outdated ESI rates.
- Paying statutory bonus to employees whose wages are above ₹21,000.
- Not prorating PF and ESI for LOP days.
- Typing amounts over formulas, so the next month's figures stop updating.
Important Notes and Limitations
The Excel is for estimation and planning, not legal or tax advice, and it suits a simple breakup only. Actual payroll depends on company policy and employment terms. State Labour Code rules are still being finalised and minimum wages change during the year, so check the inputs against current notifications. Salary calculation is also only one step of payroll; for the full monthly cycle — EPF and ESIC operations, PT and LWF, TDS, full and final settlement and the compliance calendar — see the Indian Salary & Payroll Management practical eBook.
FAQ
Is the salary breakup calculator Excel free?
Yes. The .xlsx file downloads directly, with no payment or sign-up.
Is PF still ₹1,800 a month in this Excel?
No. The EPF wage ceiling is ₹25,000 from 17 September 2026, so PF on the ceiling is up to ₹3,000 a month each from employee and employer. Below the ceiling, PF is 12% of actual wages — ₹1,500 on ₹12,500 wages, for example.
Why does ESI apply when gross salary is above ₹21,000?
Because ESI is tested on statutory wages, not gross. At ₹25,000 total remuneration, gross is ₹23,500 but wages are ₹12,500, so ESI applies at 0.75% from the employee and 3.25% from the employer.
How do I calculate PF in Excel?
Use =ROUND(MIN(Wages,25000)*12%,0) for PF on the ceiling, or =ROUND(Wages*12%,0) for PF on actual wages, where Wages is statutory wages, not Basic alone or gross salary.
How do I calculate ESI in Excel?
Use =IF(Wages<=21000,ROUNDUP(Wages*0.75%,0),0) for the employee share and the same formula with 3.25% for the employer share.
What is total remuneration in this Excel?
Gross salary plus employer PF. Basic + DA is set at 50% of it, or at the minimum wage if that is higher, which keeps the structure within the Labour Code 50% wage rule.
Does the Excel calculate income tax?
No. Net pay is before TDS, because tax depends on each employee's regime and declarations. Use the in-hand salary calculator to compare the old and new regimes.
Can I use this Excel for monthly payroll of many employees?
The free Excel calculates one employee's salary structure at a time. For monthly payroll, build a register with the columns listed above and apply the same formulas row by row, or use a ready payroll workbook with salary registers, PF and ESI working sheets and payslips.
Does it work in Google Sheets?
Yes. It uses only standard functions — IF, MIN, MAX, ROUND, ROUNDUP and SUM — so it works in Excel and Google Sheets.
What is the difference between this Excel and the online calculator?
Both follow the same wage, PF, ESI, bonus and gratuity rules. The Excel handles a simple Basic, HRA and conveyance structure offline; the online calculator handles any structure, applies Professional Tax by state and exports a salary annexure.
Related Salary Calculators
- Salary breakup calculator — any salary structure online, with a downloadable salary annexure
- In-hand salary calculator — take-home after PF, PT and income tax, both regimes
- Monthly salary calculator — salary for days worked and LOP deductions
- Professional tax calculator · PF / EPF calculator · Gratuity calculator · Bonus calculator