Building the payroll sheet

Plan the payroll template before typing anything: the order of the columns, and which cells hold values and which hold formulas.

Put the constants in fixed cells at the top: days in the month (NODM), the DA rate, the HRA rates for supervisory and non-supervisory staff, the transport amounts and the PF rate. Formulas refer to them with absolute addresses, so they stay fixed when a formula is copied down for every employee. Each employee's own data uses relative addresses, which move row by row.

The formulas, column by column:

• Effective days = NODM − deduction days.
• BPE = BP × effective days / NODM.
• DA = BPE × DA rate.
• HRA: a nested IF on the employee type: 40% for Sup (supervisory), 30% for Nsup (non-supervisory), otherwise 0.
• TRA: a nested IF too: 1,000 for Sup, 500 for Nsup.

Then the totals:

• Gross salary = BPE + DA + HRA + TRA.
• PF = BPE × PF rate.
• Total deductions = PF + TDS + loan.
• Net salary = total earnings − total deductions.

Once the formulas are in, results appear the moment an employee's values are entered, and the same sheet works again next month.