Pin Me

Free Downloadable Payroll Deductions Worksheet

written by: ciel s cantoria•edited by: Linda Richter•updated: 5/18/2011

Small business entrepreneurs using a non-accountable payroll format and the Wage Bracket Tables for their employees' withholding taxes will find an easy-to-use payroll deductions worksheet in Bright Hub's Media Gallery. This article provides the guidelines for using this free Excel payroll template.

  • slide 1 of 6

    Payroll Worksheet in Non-Accountable Format Using the Wage Bracket Method

    payroll deduction worksheet.xls image 

    Find a free downloadable payroll deductions worksheet in Bright Hub’s Media Gallery. We recommend it for small businesses or for those whose payroll system adheres to the following IRS methods:

    Non-Accountable – Under this payroll system, supplemental wages like meals and transportation allowances form part of the employees' gross salaries, to which the employer does not require the presentation of receipts. As wages, they are taxable and therefore subject to federal tax deductions on income.

    Wage Bracket Method – Withholding taxes for this payroll system make use of the simplified Wage Bracket Table. The corresponding amounts of taxes to be withheld are found by referring to the number of tax allowances being claimed by the employee, and opposite the gross wages bracket.

  • slide 2 of 6

    Additional Information about the Downloadable Payroll Deduction Worksheet

    Since the withholding tax deductions in our example make use of the monthly Wage Bracket Table, the payroll master should refer to the applicable payroll period (daily, weekly, bi-weekly, semi-monthly or monthly) for the appropriate type of withholding tax table. See IRS Publication 15 Employer's Tax Guide for 2011.

    Computations for the FICA taxes are likewise based on new tax rates, which are mandated for implementation on all wages earned beginning Jan 01, 2011:

      (a) Social Security Rate for 2011- The new rate is 4.2% replacing the previous year’s rate of 6.20%. However, take note that the employer’s contributions to the employee’s social security fund will still be computed at 6.20 %; hence the employee’s total social security contributions for the month would be 10.40%. The wage base limit remains unchanged at $106,800.

      (b) Medicare Rate 2011- The new Medicare rate for both employees and employers is 1.45%, which brings the employee’s Medicare contribution to a total of 2.90% every month. There is no wage base ceiling for Medicare tax computations.

    • slide 3 of 6

      How to Use the Payroll Deductions Template

      Column A, C, and D – Use the formula bar to replace or modify the information according to your payroll data. However, take note that Cells C27 and C28 should not be modified unless there are new FICA tax rates.

      Column E – Indicate if the employee is Single (S) or Married (M). You will use this as reference for determining the Wage Table Bracket to be used.

      Column F Gross Salary –Type your employees’ respective gross salaries under this column; include the taxable supplemental benefits in order to determine and compute their taxes as if they are part of the regular wages.

      Column G -- No. of Withholding Tax Allowances – When filling in the cells in this column, refer to the employee’s updated W4 to determine the number of tax allowances being claimed by the employee.

    • slide 4 of 6

      How to Use the Payroll Deductions Template (continuation)

      image of withholding tax table 

      Column H – Withholding Tax on Income -- Manually input the corresponding withholding tax by referring to the withholding tax amount opposite the gross wages and under the number of tax allowances claimed, as tabularized in the appropriate Wage Bracket Table.

      The example provided in the downloadable payroll worksheet template is for a single employee claiming one (1) tax allowance; hence the correct amount of income tax withheld is $345. (Click on the image on your left to view a larger screenshot image of the Monthly Wage Bracket Table for single employees).

      Column I FICA SSS Tax – Under this column, the employee’s social security tax is automatically computed for each cell, once you input the employee’s gross salary.

      Column J FICA Medicare Tax -- The employee’s Medicare tax is automatically computed for each cell under this column, similar to that of the SS tax.

      Column K State Tax – This column is for mandatory state withholding tax, if any. The state tax example in our payroll deduction worksheet is based on Colorado’s Occupational Privilege Tax. It requires a $2 tax deduction per month from all individuals deriving gross earnings of $250 or more while working within the city limits of Aurora, CO. To check if your state is currently imposing a similar tax on gross income earned by your employees, visit the directory of state and local tax contents provided by Payroll .com.

      Column L – Other Deductions – These are post-tax deductions, usually those deductions taken from the employee’s salary as payment for retirement fund contributions, union dues, uniforms, additional insurance costs, repayments on advances or loans, and the like, which form part of the employee’s wages and therefore are still subject to tax.

      Use the Payroll Deduction form submitted by your employee, which serves as your authorization and reference guide. For more information about post tax deductions, readers may refer to a separate article entitled “Which Payroll Deductions are Taxable?"

      Column M – Total Deductions – The cells under this column are automatically generated by the effect of the autosum function for the cell inputs under Columns H, I, J K and L. In case you need to add more deductions to the payroll template, be sure to insert the new column before the Total Deductions. That way, the amounts of the new deductions will be included among the inputs that are automatically calculated.

      Column N – Net Pay- The amount under this column is automatically generated by the formula integrated in each cell, wherein Gross Salary (Column F) is reduced by the Total Deductions (Column M).

    • slide 5 of 6

      Adding More Columns for Additional Payroll Deductions

      For the benefit of those who are not too familiar with Excel applications, you can add more deductions to the downloadable payroll deductions worksheet by:

      • Moving the cell cursor on column K or L but before M or the Total Deductions Columns; codes for column placements will change after extra columns have been added. Hence, it would be best to refer to the title headings for subsequent additions.
      • Right click on the mouse;
      • Choose “insert" from the menu;
      • Choose “entire column" after clicking on “insert".
      • Name the new column using the formula bar, and,
      • Save.
    • slide 6 of 6

      Reference Materials and Image Credit Section:

      References:

      • Publication 15 - Main Content --http://www.irs.gov/publications/p15/ar02.html#en_US_2011_publink1000202337

      Image Credits:

      • IRS Publication 15 Employer's Tax Guide for 2011- Monthly Wage Bracket Table ---http://www.irs.gov/pub/irs-pdf/p15.pdf
      • Payroll deductions worksheet created for this article by author cscantoria.