Salary Structure Planner Excel — FY 2026-27
Professional salary structuring workbook aligned with the Code on Wages 2019 (Basic ≥ 50% of CTC floor), Income-tax Rules 2026 amendments (CEA ₹72,000, CHA ₹2,16,000, Meal ₹1,05,600, Gift ₹15,000 p.a.), 8-metro HRA classification, and waterfall allocation so every input reconciles exactly to CTC.
⬇ Download Excel — Free No signup. No email required. Direct download.What you get
Sheet-by-sheet overview
Workbook contents (4 sheets)
- Cover — Version, legal basis, step-by-step guide, password note.
- CTC Breakup — CTC input, Basic %, city, PF cap Y/N, Gratuity Y/N, all allowance inputs. Auto-computes 12+ components that reconcile to CTC.
- Tax Optimisation — Old vs New regime tax liability side-by-side with standard deduction, Sec 10 exemptions, Chapter VI-A deductions.
- HRA Exemption — Sec 10(13A) formula — minimum of actual HRA, rent paid minus 10% Basic, 50%/40% Basic depending on city.
How to use
- Open the workbook and go to CTC Breakup.
- Enter CTC, Variable Pay, city (Metro / Non-Metro), Basic % (50% minimum), PF cap (Yes/No), Gratuity (Yes/No).
- Enter your desired allowance amounts (CEA, CHA, Meal, Gift, LTA, Medical insurance). These are preferences — the waterfall auto-caps each to the available CTC budget.
- Review the Total CTC (Calculated) row — it will exactly match your CTC input. The 3-state banner tells you whether Basic floor engaged, allowances were auto-capped, or the structure fit cleanly.
- Open the Tax Optimisation sheet — enter 80C, 80D, HRA exemption, NPS, home loan interest to see which regime saves you more.
- Open HRA Exemption to fine-tune the exemption calculation with rent paid.
Why this utility matters for CAs and salaried professionals
Salary structuring in India sits at the intersection of four different rulebooks — the Income-tax Act (Sec 10, Sec 16, Sec 17, Sec 115BAC), the Income-tax Rules (Rule 2BB, Rule 3(7), Rule 2A amended via Rules 2026), the Code on Wages 2019 (Sec 2(y) definition of wages, which mandates Basic ≥ 50% of total remuneration), and PF / Gratuity / Professional Tax statutes. A structure that ignores any one of these is either non-compliant or tax-inefficient. This workbook weaves all four into a single reconciled model.
The single most common structuring mistake — seen on hundreds of payslips — is setting Basic at 40 percent of CTC so that PF and gratuity liability stays low. The Code on Wages 2019, effective in a phased roll-out, defines "wages" to include Basic plus DA plus retaining allowance, and explicitly provides that this shall not be less than 50 percent of the total remuneration. A 40-percent Basic structure is therefore inconsistent with the Code. This workbook enforces the 50-percent floor via a MAX formula at the Basic cell so it is impossible to draft a non-compliant structure, regardless of what Basic-percent the user tries to key in.
Waterfall allocation & exact reconciliation
The second common problem is over-allocation — inputting allowances that, combined, exceed the available CTC budget after Basic, HRA, retirals and Medical insurance are provided for. Earlier versions of salary-structure templates simply produced negative Special Allowance or mis-reported Take-Home. The waterfall approach in this workbook fixes that: each allowance takes the minimum of (a) user input, (b) statutory cap under Rule 2BB / Rule 3(7), and (c) remaining budget. The priority order is Variable → CEA → CHA → Meal → Gift → LTA → Special. Whatever is left becomes Special Allowance, which is floored at zero. The result — every single combination of user inputs reconciles to CTC exactly, with a warning banner flagging the fact that allowances were auto-capped.
FY 2026-27 amended limits (Rules 2026)
The Income-tax Rules 2026, notified to take effect 1 April 2026, substantially revised four long-dormant perquisite limits: Children Education Allowance from ₹100/month to ₹3,000/month per child (max two children — ₹72,000 p.a.); Children Hostel Allowance from ₹300/month to ₹9,000/month per child (₹2,16,000 p.a.); Meal Coupon value per meal from ₹50 to ₹200 (₹1,05,600 p.a.); and Gift Voucher threshold from ₹5,000 to ₹15,000 p.a. Crucially, Meal and Gift exemptions are now available under both Old and New Regime per the Rule 3(7) amendment — while CEA, CHA, and LTA remain Old-regime-only. The workbook applies each of these correctly.
HRA metro expansion
Sec 10(13A) read with Rule 2A historically defined metro cities as the four Presidency cities — Delhi, Mumbai, Chennai, and Kolkata — entitled to 50 percent of Basic as HRA exemption. A 2026 amendment extended the list to eight — adding Bengaluru, Pune, Hyderabad, and Ahmedabad. The workbook’s city dropdown reflects this expansion; HRA exemption at 50 percent is computed for any of the eight metros, 40 percent for all other locations.
Who this is for
HR teams drafting offer letters and salary slabs; payroll managers running the year-end salary review exercise; Chartered Accountants advising executive-level clients on CTC structuring; and individual salaried professionals validating that the structure offered by their employer is tax-optimal and Code-compliant.
Frequently asked questions
My employer offers Basic at 40% of CTC. Is that legal?
Under the Code on Wages 2019 (once the relevant provisions are operational), Basic & DA combined must be at least 50 percent of total remuneration. Many employers are still transitioning — a 40 percent structure is inconsistent with the letter of the Code but is not yet being prosecuted widely. Best practice is to comply now. Note that any lower Basic reduces PF and gratuity accrual, which may not be in the employee’s long-term interest either.
Can I run this for a fresher at ₹3 lakh CTC?
Yes — the waterfall handles low CTC cases correctly. Basic floors at ₹1.5 lakh (50%), HRA at ₹75,000 (metro), retirals at ~₹25,000, and the remaining budget (~₹50,000) cascades through Variable → CEA → CHA → Meal → Gift → LTA → Special. All reconciles to ₹3 lakh.
Does the tax comparison include surcharge and marginal relief?
Yes. The Tax Optimisation sheet applies surcharge at the 10/15/25/37 percent bands, caps New Regime surcharge at 25 percent per Finance Act 2023, and adds 4 percent H&E Cess on top. Marginal relief is applied wherever total income is just above a slab threshold.
How do I use this to negotiate a raise?
Model your target CTC (e.g. ₹25 lakh) in the CTC Breakup sheet. Compare the Take-Home (before income tax) against your current structure. The gap between the two is what you are negotiating for.