Payroll Spreadsheet - Free Download
4.8,
4425
votes
Please vote for this template if it helps you.
1
SPREADSHEET ACCOUNTING I
P10 PAYROLL REGISTER (PR)
INTRODUCTION
This problem develops an automated and reusable payroll register that will calculate the
deductions and net pay (take-home pay) for various gross pay amounts. The excel IF
function is the feature that will be used to accommodate the different conditions when
calculating the Social Security taxes.
All of the formulas must be completed before any of the author designated formulas will
become active. Unprotect the worksheet if you like to see the author’s formulas. The
rest of the calculations will then fill out automatically.
Prepare the model in the following order:
• Insert Formulas 1-6
• Enter the cumulative gross pay to October 31 for Jones (Cell I13) beginning with
$85,000, then change to $105,000 and $125,000 later in the problem
EMPLOYEE PAY DEDUCTIONS
• Federal Income Tax Withholding
(1) Depends on total earnings, marital status, and the number of withholding
allowances claimed.
(2) Circular E, Employer’s Tax Guide, (IRS Publication 15) contains the withholding
tables for Federal Income, Social Security, and Medicare taxes.
(3) No need to use this publication for P10 since the FIT withholding amounts are
given in the problem.
• State Income Tax Withholding
(1) Some states have an income tax determined using withholding tables or a fixed
percent of the federal income tax withholding.
(2) The employees in P10 have no state income tax withholding.
• Federal Insurance Contributions Act (FICA) Taxes
(1) Social Security taxes: Both employees and employers pay Social Security taxes.
The 2011 employee tax rate is 4.2% and the 2011 employer tax rate is 6.2%. The
wage base limit is $106,800 for each the employee and employer, unchanged
from 2010.
(2) Medicare taxes: Both employees and employers pay 1.45% on all earnings with
no wage base limit.
