Automate Payroll Tax Calculations with Python and Pandas

1. Introduction

In a Python community, a member asked how to calculate pre‑tax salary, individual income tax and cumulative amounts from a net salary of 12,000 RMB using pandas.

2. Implementation

The original code attempted to compute tax brackets but contained logical errors. A corrected version defines tax brackets, calculates cumulative taxable income, applies the appropriate tax rate, and derives pre‑tax salary, social security, and housing fund contributions.

def calculate_pre_tax(df, cum):
    kou_cs = 5000
    tax_brackets = [
        (34920, 0.03, 0),
        (132120, 0.10, 2520),
        (256920, 0.20, 16920),
        (346920, 0.25, 31920),
        (514920, 0.30, 52920),
        (709920, 0.35, 85920)
    ]
    df['累计不含税应纳税所得额'] = (df['税后工资'] - df['扣除数']).cumsum()
    for threshold, rate, deduction in tax_brackets:
        if df['累计不含税应纳税所得额'] <= threshold:
            df['累计应纳税所得税含税'] = (df['累计不含税应纳税所得额'] - deduction) / rate
    df['累计预缴个税'] = df['累计不含税应纳税所得额'] - df['累计应纳税所得税含税']
    df['本期预缴个税'] = df['累计预缴个税'].diff().fillna(df['累计预缴个税'])
    df['社保公积金个人部分'] = cum
    df['税前应发工资'] = df['税后工资'] + df['本期预缴个税'] + df['社保公积金个人部分']
    return df

cum = 2000
path = r'C:\Users\Administrator\Desktop'
file_name = '推算.xlsx'
df = pd.read_excel(os.path.join(path, file_name))
print(calculate_pre_tax(df, cum))

A helper function calculate_tax is used to compute tax for a single row, and the final DataFrame is printed.

3. Conclusion

The example demonstrates how to use pandas to automate payroll tax calculations, providing a reusable script for similar financial data processing tasks.