CTC vs In-Hand Salary Breakup in Excel

Q: Why is in-hand salary lower than CTC?

CTC includes the employer's EPF and gratuity, which are not paid in cash, and in-hand is further reduced by the employee's EPF, ESI, professional tax and TDS. The gap is mostly retirement and statutory money.

Q: What is the 50% basic salary rule?

Under the Code on Wages, Basic + DA must be at least 50% of CTC. Excess allowances are added back into "wages", which increases EPF, gratuity and bonus bases.

Q: Does a higher Basic reduce take-home pay?

It can. A higher Basic raises employee EPF (12% of Basic+DA) and gratuity provisions, slightly lowering in-hand pay while increasing retirement savings.

Q: Is HRA part of CTC?

Yes. HRA is an allowance within gross salary, which sits inside CTC. It is paid in hand (subject to tax) but does not count toward the 50% Basic+DA wage floor. ---

🧮 Prefer a quick answer? Try our free income tax calculator — instant, no sign-up.

Last updated: 27 June 2026 · Formulas tested in Excel 2010 and later.

A CTC-to-in-hand breakup in Excel splits the annual cost-to-company into Basic, HRA and allowances, adds the employer's statutory costs (EPF, gratuity), then subtracts the employee's deductions (EPF, ESI, professional tax, TDS) to reach monthly in-hand pay. Under the new labour codes, Basic + DA must be at least 50% of CTC. CTC is what the employer spends; in-hand is what reaches the bank.

Key takeaways

CTC = gross salary + employer's contributions (employer EPF, gratuity, and any insurance) — the total cost to the company.
In-hand = gross − employee deductions (employee EPF, ESI, professional tax, TDS).
New labour codes (since 21 Nov 2025): Basic + DA ≥ 50% of CTC; excess allowances are added back to "wages", raising EPF and gratuity.
A higher Basic raises EPF and gratuity but can lower take-home — model the trade-off in Excel.
Keep all rates (EPF 12%, ESI 0.75%, ₹15,000 and ₹21,000 thresholds, PT slabs) on a Settings sheet.
The gap between CTC and in-hand is mostly employer EPF + employee deductions, not "lost" money.

Fact box. Under the Code on Wages (in force since 21 November 2025), wages — Basic + DA + retaining allowance — must be at least 50% of total CTC. Allowances like HRA and conveyance cannot together exceed 50%; any excess is added back into "wages", which raises EPF, gratuity and bonus. (Source: Code on Wages, 2019; Ministry of Labour.)

How is CTC different from in-hand salary?

CTC is the employer's total spend; in-hand is what the employee receives after deductions. Three layers sit between them:

Layer	Items
CTC	Gross salary + employer EPF + gratuity + insurance
Gross salary	Basic + DA + HRA + allowances (CTC minus employer contributions)
In-hand (net)	Gross − employee EPF − ESI − professional tax − TDS

So In-hand = CTC − employer contributions − employee deductions. Most of the CTC-to-in-hand gap is retirement and statutory money, not a deduction lost to the employee.

How do I split CTC into a salary structure in Excel?

Work top-down from annual CTC, honouring the 50% wage floor. Put annual CTC in CTC:

Component	Formula (monthly)	Note
Monthly CTC	`=CTC/12`
Basic + DA	`=ROUND(CTC/12*50%,0)`	At least 50% of CTC
HRA	`=ROUND(Basic*50%,0)`	50% of Basic (metro) — adjust per policy
Employer EPF	`=ROUND(MIN(BasicDA,15000)*12%,0)`	Part of CTC, not paid in hand
Gratuity provision	`=ROUND(BasicDA*15/26/12,0)`	Annual 15/26 spread monthly
Special allowance	`=MonthlyCTC-Basic-HRA-EmployerEPF-Gratuity`	Balancing figure

The special allowance is the plug that makes the components add up to monthly CTC.

Tip: Build it as a two-way model — change CTC and the structure recalculates; or fix the structure and back-solve CTC. Keeping Basic at exactly 50% keeps you compliant with the wage rule and simplifies EPF and gratuity bases.

Fact box. A worked split of ₹12,00,000 annual CTC: Basic+DA ₹50,000/mo (50%), employer EPF ₹1,800, gratuity provision ≈ ₹2,403, leaving HRA and special allowance to fill the rest of the ₹1,00,000 monthly CTC. (Illustrative; actual HRA and allowance policy varies by employer.)

How do I get from gross to in-hand?

Subtract the employee's statutory deductions from gross. With Basic+DA and gross set:

Deduction	Formula
Employee EPF	`=ROUND(MIN(BasicDA,15000)*12%,0)`
ESI (if gross ≤ ₹21,000)	`=IF(Gross<=21000,ROUND(Gross*0.75%,0),0)`
Professional tax	`=VLOOKUP(State,PT_Table,2,TRUE)`
TDS	`=ROUND(AnnualTax/12,0)`
In-hand	`=Gross-EmployeeEPF-ESI-PT-TDS`

ESI usually applies only at lower wages (gross ≤ ₹21,000); at higher salaries the main deductions are EPF, professional tax and TDS.

How to build the breakup step by step

Enter annual CTC in one input cell.
On a Settings sheet, hold EPF 12%, ESI 0.75%, the ₹15,000 and ₹21,000 thresholds, and PT slabs.
Split CTC into Basic+DA (≥50%), HRA, employer EPF and gratuity; let special allowance balance.
Compute gross = CTC − employer contributions.
Subtract employee EPF, ESI, PT and TDS to get in-hand.
Show annual and monthly CTC, gross and in-hand side by side.
Re-check that Basic+DA stays ≥ 50% of CTC after any change.

How Ankeshan helps: Ankeshan turns a single CTC figure into a compliant salary structure inside Excel — enforcing the 50% wage rule and computing employer cost, gross and in-hand pay with current EPF, ESI, PT and TDS rates. (Launching soon — join the waitlist.)

Frequently asked questions

Why is in-hand salary lower than CTC? CTC includes the employer's EPF and gratuity, which are not paid in cash, and in-hand is further reduced by the employee's EPF, ESI, professional tax and TDS. The gap is mostly retirement and statutory money.

What is the 50% basic salary rule? Under the Code on Wages, Basic + DA must be at least 50% of CTC. Excess allowances are added back into "wages", which increases EPF, gratuity and bonus bases.

Does a higher Basic reduce take-home pay? It can. A higher Basic raises employee EPF (12% of Basic+DA) and gratuity provisions, slightly lowering in-hand pay while increasing retirement savings.

Is HRA part of CTC? Yes. HRA is an allowance within gross salary, which sits inside CTC. It is paid in hand (subject to tax) but does not count toward the 50% Basic+DA wage floor.

Sources

Code on Wages, 2019 — labour.gov.in (50% wage rule, in force 21 Nov 2025).
EPFO — epfindia.gov.in (EPF 12%, ₹15,000 ceiling); ESIC — esic.gov.in (0.75%, ₹21,000 threshold).
State professional-tax departments (PT slabs).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.