How to Manually Calculate Payroll Wages and Taxes in Excel
Mar 1, 2011 Updated 2 months ago
There are three main ways of completing payroll for any business: hiring an outside payroll provider like ADP, using an inhouse software like Quickbooks Pro, or using a spreadsheet and/or calculator. The costs associated with each drop significantly going from the first to the last.
Payroll providers like ADP are best for those large corporations who pay thousands of employees. Using them might be cheaper than hiring an office full of payroll specialists. Small to mid-size companies might be best served using an in-house software. But what about the micro companies with only a few employees? While it's still feasible to use the payroll module of Quickbooks Pro, it might be better to manually compute wages and tax for just a few employees.
ADP, Quickbooks or Excel Spreadsheets
That is not as scary as it sounds. The IRS provides a simple chart to follow for those who wish to manually compute their payroll. This chart can be found on the IRS.gov website. The best part about the chart is that an Excel formula can be used to automatically calculate taxes without the bookkeeper ever having to use a calculator. For those not savvy with Excel, the booklet also contains the breakdown on what is deducted for all tax filers by total dollar amount.
Since that part of the booklet is self explanatory, only the actual chart (found on page 36) will be discussed in this article. First and foremost, let's assume that every bookkeeper reading this already knows how to multiply total hours worked by the hourly wage to get the gross wages. Next, the amount of exemptions must be deducted before federal taxes are computed. These amounts are also in the IRS booklet on page 35.
Exemptions Are Subtracted from Gross Pay Before Calculating Tax
Let's say that an employee is married and claims 2 exemptions, and he is paid weekly. His withholding allowance is $71.15 for each exemption, which makes his total allowance $142.30. If his gross earnings for that week are $500.00, his taxable earnings are $357.70 (500.00 - 142.30).
Table One on the tax withholding chart specifies how much of his $357.70 is taxable:
- Up to $152 = 0
- $152 to $479 = 10% of excess
- $479 to $1479 = $32.70 plus 15% over $479
Since his taxable earnings fall between $152 and $479, then his tax rate is 10% of the amount that is over $152, which is $205.70 (357.70 - 152.00). So his total federal tax is $20.57. What is the Excel formula, you ask? It would be as follows:
=IF(A1<152,0,IF(A1<=479,(A1-152)*0.1,IF(A1<=1479,(A1-479)*0.15+32.7,"need more equation")))
Formulas Make Excel a Valuable Payroll Tool
"Need more equation" was entered as a reminder that the entire formula is not there. The rest of the chain could easily be completed following the same steps. The A1 from the formula specifies the cell on the spreadsheet where the gross wages reside, so it would change depending on where that number is inputted.
Social security (or FICA) is easier to compute. While it is normally 7.65% for each the employee and the employer, taxpayers are receiving a one year FICA tax break in 2011 of 2%. So the gross taxable earnings would be multiplied by 5.65% to get the FICA tax. In this case it would be $28.25 (500 X .0565).
Health Insurance Deductions Are Pre-Tax Deductions
If there are any other discretionary deductions for the employee, such as insurance, they would be deducted as well. Please note that if the cost of health insurance is deducted, that would take away from taxable earnings before computing Federal and FICA tax.
Assuming there are no other deductions other than Federal and FICA for this employee, his net check would be in the amount of $451.18 (500 - 20.57 - 28.25). If using Excel to compute payroll, the entire check could be calculated in the spreadsheet, where only the hours worked would need to be entered. As seen in this example, it really is quite easy to manually calculate an employee's payroll.
If the employer would like to keep track of payroll in an ongoing basis, a multi-page spreadsheet can be created that will give accumulating totals. There is also a payroll calculator template which can be downloaded directly from Microsoft. While this template makes it easier to format the spreadsheet, the formula created above would still be needed to calculate federal taxes. Excel is a very versatile software, and calculating payroll is only one of its many other functions.Source: suite.io