How to do payroll in excel

how to do payroll in excel

Create Payroll list for small companies by Microsoft Office Excel 2013

Having all information about every month employees’ payroll is a big deal for every employer who has a small business as they may not afford buying a payroll software. Why don’t use Microsoft Office Excel 2013 as you can create one time and use as many time as you need. Just enough to enter some basic data and all others will be automatically calculated.

In order to create a Payroll list

• First of all open new sheet in Microsoft Office Excel 2013 by choosing New in File tab

• Insert a table from Insert Tab and choose Table from tables group in this example we insert a table with 12 columns

• Each row will contain pay information for every employee

Let’s see what each column contains and how we calculate them

1. First Column contain Name of employee which work for that month in that company.

2. Second Column contains per hour wage for that employee this columns as you know cannot be calculated.

3. Total work hours shows the how many hours the employee works. This time will not contain over time hours.

4. Over time rate shows wage of employee for every over time hours. This rate will be calculated by this formula 3/2*[@[Pay rate]] so this rate will be calculated regarding per hour wage which show in this excel in pay rate column .In order to enter formula to this column click the cell under overtime and insert “=” sign then enter 3/2 and use “*” sign then click on Pay rate .This will select pay rate as reference cell.

5. As you may guess Total over time hours shows how many hours the employee works as over time

6. Gross pay shows income of employee without any deduction this columns will be calculated by this formula =[@[Pay rate ]]*[@[Total work hours]]+[@[Overtime Pay rate]]*[@[Total Overtime Hours]].This formula is created by calculate pay rate multiple to total work hours add to calculation of overtime pay rate multiple to total overtime hours

7. From this column we should calculate the deductions so first of them is federal income tax as we create this payroll for USA this column will be calculated by this formula 12.7%*[@[Gross pay]]. You should calculate 12.7 percent of gross pay.

8. Social security tax is calculated by = 6.2 %*[@[Gross pay]] formula. This column will calculate 6.2 percent of Gross pay

9. Medicare tax which calculate for coverage of insurance will be calculated by this formula =1.45 %*[@[Gross pay]]. This column will calculate 1.45 percent of gross pay.

10. State income tax which vary base on which state are you located is calculated here by this formula = 1.9%*[@[Gross pay]].You should calculate 1.9 percent of gross pay.

11. Deduction columns shows sum of all deductions which should subtract from gross pay of any employees

12. Net pay shows exact income of every employee which calculated by subtract deduction from gross pay

By creating this list you need just enter name of every employees and the number of hours that he works as regular or over time do not forget to enter pay rate which may vary by every employees and all others item will be calculated automatically without any efforts.


Category: Bank

Similar articles: