Excel formulas for payroll taxes

Excel formulas for payroll taxes DEFAULT

Excel Payroll Formulas

2. Deductions.

Deductions like income tax or health benefits can be calculated by subtracting their percentage from gross earnings. For example, if you have gross earnings of $550 in column A1, and the income tax that must be deducted is 12%, you can enter =(A1)*0.12 in the cell that you would like the deduction to appear in. You can do the same for any other deductions and then add them together to calculate total deductions.

To find net pay, subtract what is in the cell for total deductions from the cell with gross pay. For example, =(A1) - (B4).

3. Paid time off.

If your employees are entitled to paid time off, then you likely have a system for accruing vacation time for every day or hour worked. If it is accrued hourly, you first need to divide the total number of vacation hours that they are entitled to in a year by the number of working days in the year.

For example, if an employee works 30 hours per week and there are 365 working days in the year, divide 30 by 365. 30/365=0.08. This means that for every day worked, the employee accrues 0.08 days off. With 0.08 as your multiplier, you can enter the days worked in the pay period and multiply them by 0.08. The result is how much PTO has been accrued in the pay period.

Sours: https://www.thesmbguide.com/excel-payroll-formulas

Running payroll is a lot of hassle. If you are just starting out and your small business has less than five people, then you can use excel for it. MS Excel is one of the most loved applications. It has a multitude of use cases that can be carried out using its formulae and functions.

In this blog:

What is payroll in Excel?

Your payroll process can be carried out using several options. There’s manual payroll, payroll using MS Excel, and specialized payroll software. The process of payroll includes:

  • Collecting employee data
  • Evaluating total hours worked
  • Computing pay and tax deductibles
  • Generating and distributing payslips
  • Recording and tracking payroll costs

Excel is considered a great tool for payroll management as it is easy to use and versatile. Additionally, it has several functions that allow for simple calculations within seconds. Its simple layout also allows for visual representations of the payroll data.

Why use Excel for your payroll process?

Let’s look at a few reasons why Excel is widely used for different payroll activities.

1. Collection and Tracking of Employee Data

Excel as a software can be used for way too many things than we can list down. As a result, it can help businesses right from the first step in payroll. It is used for storing and managing employee details. Furthermore, this data can be stored alphabetically, by particular dates, and by other methods. This provides you with a lot of control over how you enter, manage, and look at data. Since it is easy to manipulate the data in Excel, you can add, remove, and edit it whenever you want. This becomes an increasingly necessary advantage to avoid making mistakes in recording payroll information.

2. Visual Presentation

Most people think that Excel can only be used to make elaborate spreadsheets. However, it is so much more than that. One of the biggest pros of doing payroll in Excel is its ability to be visually appealing. Let’s face it when you’re dealing with figures and calculations, who wouldn’t like it to look less dull?

Excel can take any data and turn it into charts, diagrams, or graphs. This is great for presentations and easy understanding of complicated payroll information. Not only that, when you visualize data in this manner, you can also draw parallels between parameters. This adds to a better cognizance of what your payroll numbers stand at and employee pay scales.

3. High Accuracy

Dealing with payroll figures can be a complex process. That is a no-brainer. Moreover, the bigger your company gets, the higher the number of transactions get. It can get truly difficult to not only track but also analyze these on a regular basis.

..and then comes payroll in Excel to the rescue. As I said, data manipulation is quite simple with this tool. Even formulas can be used as inputs for spreadsheets. What this does is it makes it effortless to insert numbers into specific locations on the spreadsheet itself. In addition, Excel will perform the necessary calculations automatically. As a result, you will end up putting absolutely no effort in obtaining payroll results. So not only are you saving tons of your valuable business time but also reducing the work you put in for this monthly task.

9 steps to carry out payroll in Excel

It is clear that Excel is a utilitarian application. But how do you really execute payroll in Excel? Here are the 9 key steps:

1. Launch MS Excel

Yes, we’re starting with the very basics. To open a new spreadsheet, launch MS Excel from the applications. This will open a blank Excel file for you to start on.

2. Save Files

The majority of the people believe in saving their file after completing work on it. While it is okay to do this for most file types, do not take the risk when it comes to payroll information. Do you want to spend hours creating a comprehensive sheet only to lose it at the very last minute? No.

Before you begin working on your payroll in Excel, save the file on a preferred location. Name the file suitably and store it at a place that you can have easy access to.

3. Setting up payroll parameters

The first step now is to create columns with payroll particulars. Consequently, this will ensure the data is stored systematically and values are entered accordingly in the parameters.

  • Employee Name: Contains your employee name
  • Hourly pay: The per hour pay rate
  • Total Number of Hours Worked: Total hours worked by an employee.
  • Hourly Overtime: Overtime rate per hour
  • Total Number of Overtime Hours: Number of employee overtime
  • Gross Salary: Payable amount without deductions
  • Income Tax: Tax payable on Gross Salary
  • Other Deductibles: Deductibles other than Income Tax such as EPF, PT, TDS, etc.
  • Net Pay: In-hand salary for the employee

4. Data Entry

After you decide on the parameters that are relevant to your business, it is time to make data entries. Thus, add the column-wise details without adding any formula.

5. Gross Pay Calculation

Calculating gross salary is the first step before making any further calculations. Furthermore, it is calculated as the sum of the product of Hourly Pay, Total Number of Hours Worked and Hourly Overtime, Total Number of Overtime Hours. Here’s a simpler representation:

(Hourly Pay* Total Number of Hours Worked) + (Hourly Overtime * Total Number of Overtime Hours)

This can be calculated easily using the SUM and PROD functions in Excel. Moreover, once you have calculated it for one employee, simply drag down on the subsequent cells for ‘Employee Name’ to automatically get values for all employees.

6. Calculate Income Tax

To calculate Income Tax, check how much percentage tax is paid by the employee on his/her gross salary. make sure that you always calculate Income Tax on Gross Pay. In India, there are different tax slabs corresponding to various gross salaries received. The lowest income tax slab is 5% while the highest tax slab for individuals is 30%. 

To calculate IT for different salaries, the formula is:

(-Income Tax percentage * Gross Salary)

For example, if it is 5%, the calculation will stand as -0.05 * taxable salary

7. Verify and Compute Deductions

This is a very important step as it relates to all tax deductibles applicable to employee salaries. These include Employee Provident Fund, Professional Tax, Public Provident Fund, Life/Health Insurance Premium, EMI on loans, etc. All investment declarations must be handled in a timely manner and proofs must be approved via workflow.

8. Formulate Net Salary

Net pay or salary is the actual amount that gets credited to the employee’s account. In addition, this is referred to as the in-hand salary and is the amount after all deductions are considered from gross salary.

So, to calculate Net Pay, the sum of Income Tax and Tax deductibles is subtracted from Gross Pay.

(Gross Pay – {Income Tax + Deductibles})

9. Evaluate all variables for payroll in Excel

Continue the above steps for every employee simply by dragging each cell downwards. Moreover, keep checking each value in every component. Lastly, add formatting to the cells and total all values at the end of the spreadsheet.

What next after payroll calculations in Excel?

Yay! You’ve successfully understood how to execute payroll in Excel. Now what? These are just various calculations. You can’t really send across this spreadsheet to employees because you don’t want everyone knowing each other’s salary. Neither can you print all of these, I mean you can, but its a lot of work and much more money. So let’s see how to build upon effective payroll practices following Excel calculations.

All of this is well and good, but how will you efficiently pay your employees? With a payroll software, that’s how. Payroll software does everything for you once you have the final data. Moreover, it automates all your payments and ensures employee and financial confidentiality. Furthermore, it is all done by keeping in mind the deductibles and compliances needed to work within.

  • Generating and Distributing Payslips

This is another important payroll task. You see, manual payslips are equally time-consuming as manual payroll. Not to mention they are also prone to high levels of inaccuracies and human errors on a large scale. So what’s the next best thing? An online payslip generator! With this software, all you have to do is enter values from your collated Excel data. You will receive an instant payslip within seconds along with your company logo. In addition, your employees can receive their salary slips on their mobiles as well as on WhatsApp.

  • Customizing Emails for Employees

When it comes to sending salary slip emails to employees, customization is the best way to go. Additionally, you can use software like MailMerge which seamlessly integrates with Excel. MailMerge is typically used to combine official forms and emails.

Payroll in excel

Payroll in Excel: Is it the best option?

Just hear me out. Excel is good for payroll, but is it great? Not really.

The payroll process requires a lot of attention to detail at every step. Moreover, data entering and human error are not mutually exclusive. So what really is the best way to execute payroll? PAYROLL SOFTWARE!

Payroll Automation

Automating your payroll process is truly one of the best business decisions you will make. In addition to the automated calculations, software like Asanify can also organize annual reports and make tasks efficient. 

Asanify payroll in excel

Easy Reporting

Just like HR reports are essential, so are payroll reports. With payroll software, you can create, edit, and store all your payroll reports. These include weekly, monthly and yearly reports of collated data. Simultaneously, you can also get more simplified reports which provide all the information related to gross salary, deductibles, and net pay in a single report. 

Data Confidentiality

Payroll software can also secure your data in a much more effective manner. At the same time, it allows for staff collaboration but ensures that data access is limited to certain parties only. 

Payroll Excel reports asanify

Payroll Compliance

Furthermore, we know how important a compliant payroll process is. Asanify provides comprehensive reports on Excel itself for efficient tax and compliance management. This helps you make reliable and accurate tax decisions.

Get started on a much more efficient payroll practice, there’s only so much Excel can do!

Summary

We’ve come to the end of this blog and I hope you have a clearer idea of how to execute payroll in Excel. We talked about 3 reasons you should use Excel for payroll and the various steps involved in that process. In conclusion, we looked at the actions to take after you complete calculating payroll variables on your Excel file.

FAQs

How to do payroll on Excel?

To carry out payroll on Excel, there are several steps right from collecting data to calculating payroll variables. Moreover, it can be a simple process if done correctly.

How do I calculate payroll time in Excel?

For calculating payroll time, the parameters required are the number of hours and minutes worked. Therefore, subtract the start time from the end time to obtain values for payroll time.

Can I create a payroll schedule on Excel?

Yes, Excel has templates that include options for Schedules. As a result, you can use business schedules with preferred formats and styles.

Tags: epfo,income tax,MS excel,professional tax,small business,SME,startup,tds

You Might Also Like

Sours: https://asanify.com/blog/payroll/how-to-do-payroll-in-excel-for-business/
  1. Baby shark lamp
  2. Pbs japanese cooking show
  3. Shooting star cartoon images
  4. Hopkins brake controller

How to Do Payroll in Excel in 7 Steps + Free Template

Doing payroll in Excel is best for businesses that need to pay 10 or fewer employees and operate in states without complex labor and tax laws. With our free payroll Excel template, you can save time and process payroll efficiently because it contains pre-filled information like tax rates and overtime formulas.

If payroll is still taking too much time, or if you’ve outgrown a spreadsheet, consider using a small business payroll software like Gusto. It automatically files new hire paperwork, calculates and files payroll taxes, generates year-end W-2s and 1099s, and pays via direct deposit. It even helps employees choose and manage their benefits. Sign up for a 30-day free trial today.

Visit Gusto

How Doing Payroll in Excel Works

To do payroll in Excel, you need to create a standardized template that you can use from month to month. It should have tabs for each month, with links to formulas that calculate employee taxes, deductions, and pay. You also need a “Set Up” tab that your payroll calculations can pull standard information from, such as pay rate and benefits enrolled.

Keep the “Set Up” tab up-to-date so you can easily link to it each month you run payroll. This helps with automation and prevents double work.

We’ve created a payroll Excel template for you with 16 tabs—one for each month, plus additional tabs for general instructions, employer tax information, employee data setup, and year-end payroll information. After inputting employee information (such as names, pay rates, tax rates, and deductions), you just need to enter the actual hours worked, then the template will automatically populate the applicable payroll data.

Follow the steps below on how to use our free Excel payroll template when running your payroll. If you want to see the process in action, you can also watch our video tutorial on how to do payroll in Excel.

1. Review Payroll Excel Template and Edit for Your Business

Figuring out how to do payroll using an Excel template can take some time. First, you need to take a look at the template and evaluate your business needs. Here are some questions that can help you identify the information needed to finalize the template.

  • How many employees do you have?
  • What benefits payments and deductions do you need to withhold from their paychecks?
  • Is there anything missing from the template that you need to add?

Add or Delete Columns

You may need to add or delete columns to meet the needs of your small business. For example, if your business is located in a place that charges local income tax (like New York City), you need to reflect that in the payroll Excel template. You can do this by adding the required information in the “Set Up Employee Data” tab. This is where you should always start when making changes because all of the remaining payroll tabs pull their source data from here.

To add a column, choose the column letter (e.g., G, H, and I) you want your new column to be in front of. Right-click your mouse and then select “Insert.” Enter a header (in row 3) for your new column (for example, “Local NYC Tax”) and input the rates for applicable employees. Go to all the month tabs (from “January Payroll” to “December Payroll”), including the “Year-to-Date Payroll” tab, and enter a new column between columns N and Q (the tax columns). Label this “Local NYC Tax.”

When adding new columns to the month tabs, you must be consistent. If you add a “Local NYC Tax” column in column O on the “January Payroll” tab, you must add it in the same place on other month tabs. For the “Year-to-Date Payroll” tab, which contains the summary of the 12 months, you also need to add any new columns so that it correctly reflects details from the monthly payroll tabs.

To delete columns, highlight the column you want to remove, right-click your mouse, and then select “Delete.” The rules are the same if you’re deleting a column from the “Set Up Employee Data” tab. If you delete a column from one of the payroll tabs, including the “Year-to-Date Payroll” and the “Set Up” tabs, then you must delete it from the others.

For example, if you delete the “Social Security Tax Rate” in column G of the “Set Up Employee Data” tab, you need to remove the “Social Security Tax” column (column N) in all the 13 payroll tabs. If not, you’ll receive error messages—and your formulas won’t work.

Screenshot of Setting Up Employee Data Social Security or Medicare Tax RatesThis is the “Set Up Employee Data” tab. Don’t change the Social Security and Medicare tax rates in columns G and H, respectively, because they are fixed rates.
Screenshot of Adding a Column in Employee DataWhen adding a column, simply highlight the column you want a new one to be in front of and right-click.
Screenshot of Employee Data Automatic CalculationCheck how the cells with formulas automatically calculate totals once you enter data in columns A through I.

Change Column Names

When adding and deleting columns, consider whether or not you can simply change some of the existing column labels in the payroll Excel template. Aside from cutting down on errors, this will save you time from having to change multiple tabs.

To help, we added two columns (columns N and O) in the “Set Up Employee” tab and labeled them “Other Deduction.” Just double-click on the description and overwrite it with a label that more appropriately reflects your needs.

Note that we created the “Other Deduction” columns to be easily personalized, so you don’t have to change any other tabs to match. There’s only one “Other Deductions” column in the 13 payroll tabs, and the data that reflects here is the sum of columns N and O in the “Set Up Employee Data” tab. If you need to add more columns for deductions in the “Set Up” tab, follow the directions above for adding columns.

Screenshot of Employee Data Other Deduction ColumnsYou can use the “Other Deduction” columns in the “Set Up” tab to input miscellaneous deductions that are specific to your company.
Screenshot of Employee Data Other Deductions Setup TabAlthough there are two “Other Deduction” columns in the “Set Up“ tab, the monthly payroll tabs sum them in one column.

2. Set Up Payroll Information for Each Employee

Once you’re satisfied with the template layout, you can begin setting up your employee data. To complete the “Set Up” tab, you need the following information.

Employee Name

Enter your employees’ full names under column A in the “Set Up Employee” tab. Note that you need to re-enter their names on the monthly payroll tabs. When you do, ensure that it matches the name you entered in the “Set Up” tab. If there are differences in spelling and formatting, the monthly and annual payroll tabs will not be able to make automatic calculations.

Don’t remove terminated and resigned employees from the “Set Up Employee” tab. If you delete employees midyear, any prior months reflected in their payroll will update to $0 and your year-to-date payroll expense amounts will change. When you have new hires, enter their details in the “Set Up” tab after the existing employees; information will be auto transferred to other tabs when you input their names.

**For employees who have resigned or been terminated, simply stop adding payroll data for them in the monthly payroll tabs. You can remove all inactive employees from the “Set Up” tab when you recreate the payroll Excel template for the next calendar year.

Straight-Time Hourly Rate

In column B of the “Set Up Employee Data” tab, enter the straight-time hourly rate (excluding overtime) for each hourly employee. Leave this cell blank for salaried employees.

Annual Salary

In column C of the same tab, input each salaried employees’ annual salary. For example, if your staff earns $50,000 a year, enter “$50,000.”

Number of Pay Periods in a Year

In column D, labeled “# of Pay Periods in Year,” enter the applicable payroll period based on how often you pay employees.

  • If biweekly, input “26” (computed as 52 weeks/two weeks per pay period = 26)
  • If weekly, input “52” (52 weeks in a year)
  • If semimonthly, input “24” (52 months/two pay period per month = 24)
  • If monthly, input “12” (12 months in a year)

Federal and State Income Tax Rates

Enter each employee’s federal and state income tax rates in columns E and F, respectively. To find the applicable rates, use the W-4 forms that you collected from employees when they joined your company. The tax rates depend on the number of allowances your employees entered (usually based on the number of dependents) and their filing status—like married, single, or head of household. Generally, the more allowances employees claim, the lower their tax rates.

Screenshot of Employee Data Columns A Through FColumns A through F in the “Set Up Employee Data” tab don’t have any formulas within the cells. Use your payroll records to ensure you enter accurate information.
Screenshot of Employee Data Year to Date PayrollThis is the “Year-to-date Payroll” tab. All data automatically populates within this tab, so you shouldn’t have to change any information within the cells.

Social Security and Medicare Tax Rates

Don’t make any changes to the Social Security and Medicare tax rates in columns G and H, respectively. These Federal Insurance Contributions Act (FICA) rates are pre-determined by the IRS. Keep in mind that Social Security maxes out if an employee earns $132,900 in wages before the end of the year. If you have employees earning more than that, you need to stop withholding the 6.2% tax once their YTD earnings exceed the said amount.

For example, if your employee meets the $132,900 earnings threshold for Social Security in April, delete all the formulas under “Social Security Tax” (column N) in payroll tabs May through December for the said employee. Note that because the Social Security tax rate is 6.2% of wages, the maximum amount any employee will pay annually is $8,239.80 ($132.900 maximum x 6.2% = $8,329.80).

To help check your employees’ total earnings, look at column M (labeled “Year-to-Date Gross Pay”) in the “Year-to-Date Payroll” tab. This column shows the employees’ YTD gross wages. Simply deduct all YTD non-taxable income (column I) from this amount to find the taxable gross income.

For Medicare, the tax rate is 1.45% on an employee’s first $200,000 in wages and an additional 0.9% if the earned wages exceed $200,000 in a calendar year. This increases the total Medicare tax to 2.35% for those individuals. If you have employees who reach this threshold, you need to change the formulas in the “Medicare Tax” column (column O) on all monthly payroll tabs following the month the employee reached $200,000 in gross pay.

To further clarify, look at the screenshot below, specifically the formula field. Delete the (gray) highlighted part of the formula and replace it with “2.35%.” Doing so will overwrite the link to the employee’s Medicare tax rate (which is 1.45%) on the “Set Up Employee Data” tab.

Screenshot of Employee Data Medicare Tax Formula

If your employee reaches the $200,000 wage threshold for Medicare, check and revise the formula in the “Medicare Tax” column on applicable monthly payroll tabs.

Note that the threshold for Social Security has increased for the past eight years, while the Medicare tax rate has been the same for the past six years. To ensure that you remain compliant, check for any tax rate changes at the beginning of each year and then update the payroll Excel template accordingly.

Benefits and Other Deductions

The template contains several columns for benefits you may offer employees such as health, dental, vision, and 401(k) plans. For each benefit that is applicable to your business, enter the employees’ premium per pay period in columns I through L of the “Set Up Employee Data” tab.

You can also use columns M, N, and O for garnishments and other employee deductions. After entering the benefits and deduction details on the “Set Up” tab, the template will automatically populate the appropriate fields when you input the employees’ names in column B of the monthly payroll tabs.

Paid-Time-Off (PTO) Calculations

Relying on your employees to keep track of their PTO is not a good strategy. To help you monitor this, we included a PTO calculation tool that’s optional to use.

If you decide to use the PTO calculation tool, check the last three columns in the “Set Up” tab that pertain to PTO. In column P (“Enter Annual PTO Hours”), input the total PTO hours that your employees are entitled to in a year.

You don’t need to fill column Q (“Auto Calculation-PTO Hours Taken”) since the template will automatically pull the total PTO hours each employee has used from the “Year-to-Date Payroll” tab. The same goes for column R (“Auto Calc-PTO Hours Remaining”) because it contains a formula that deducts the actual PTO taken from the employee’s annual entitlement.

Note that you have to separately track the actual PTO hours that each employee has taken for the applicable payroll period of every pay run. Input the data in column F (“PTO Hours Taken”) of the monthly payroll tabs and the “Year-to-Date Payroll” tab will sum up all PTO hours per employee.

Screenshot of Employee Data Paid Time of TrackingThere are columns for you to withhold premium deductions for common insurance benefits.
Screenshot of Employee Data Withhold Premium Deduction ColumnsThere’s a section for PTO tracking. It will update as you enter paycheck details each period.

3. Set Up Employer Payroll Tax Information

In addition to tracking withholdings from employee paychecks, you’re responsible for paying your share of payroll taxes. The “Employer Payroll Taxes” tab has cells you can use to enter your federal and state unemployment tax rates. Note that the 6% federal unemployment tax is only charged on the first $7,000 each employee earns (most state unemployment taxes follow the same logic). Be sure to review the information in the payroll tabs each month to help you calculate the tax amount due for each employee and the company.

You will also likely be responsible for workers’ compensation insurance payments. The rates and amounts can vary, depending on the work your employees do, claim history, and other factors. However, you should receive a monthly premium amount due from the insurance fund that creates your policy. Simply enter the monthly premium amounts in column G (“Enter Workers’ Comp Payable”).

Screenshot of Employee Data Employer Payroll Taxes

Use the “Employer Payroll Taxes” tab to help keep track of the taxes your business owes.

4. Enter Hours Worked and Other Income Details

After entering all of your employee and employer data into the payroll Excel template, you’re ready to calculate employee payments. Go to the appropriate monthly payroll tab—so if it’s January, go to the “January Payroll” tab. Enter the pay date in column A and the full name of each employee you are paying for the period in column B. Then, input the actual regular hours worked, overtime hours, PTO hours taken, and other income details in the applicable columns.

Remember, the name must be the same as the one you entered in column A of the “Set Up Employee Data” tab. If it doesn’t match, the pay rates, tax rates, and deductions will not populate in the monthly payroll tabs. If you’re concerned you’re not entering them correctly, copy and paste the names in.

Straight-Time and Overtime Hours

For accurate employee paychecks, enter the correct number of straight-time hours in column E and overtime hours in column G of the monthly payroll tabs. For straight-time hours, this is usually 40 hours or below—unless your business is located in California. For overtime, the template calculates pay at a rate of 1.5 times your regular pay rate using the number of hours you specify. However, this can be changed depending on the labor laws of the state where your business operates.

We encourage you to review your state laws on overtime rates. Federal law generally requires you to pay employees overtime for any hours worked over 40 in a consecutive seven-day period. However, California considers any hours worked over eight in a day to be overtime—regardless of whether you work 40 hours or less in a workweek.

Straight-Time vs Overtime Hourly Rate

The straight-time and overtime hourly rates listed in columns C (“Straight-Time Hourly Rate”) and D (“Overtime Hourly Rate”) of the monthly payroll tabs are automated using a formula. There’s also a note above both hourly rate columns that states not to change the contents of the cells because they will update automatically. So, whenever you enter your employees’ names under column B of the monthly payroll tabs, the straight-time hourly rates automatically carry over from the “Set Up Employee Data” tab.

Meanwhile, the overtime hourly rates in column D are calculated at 1.5 times the straight-time hourly rates in column C.

If your business operates in California, you may need to make adjustments because of its rule that states employees who work over 12 hours in one workday are entitled to double-time pay. So, any time worked between eight and 12 hours must be paid at a rate of 1.5 times and any hours over 12 must be paid at twice the straight-time rate. In this case, you have to change the formula in column D to reflect what you actually owe.

For example, if you need to pay double time for an employee who worked 16 hours in a day, you can overwrite the existing calculation with this formula. Note that the said formula is broken up into two parts: four hours to be paid at a rate of time and a half while the other four hours are considered double time:

= (4*(C4*1.5))+(4*(C4*2))

Taxable and Non-Taxable Income

If you decide to pay out an employee bonus, you must withhold payroll taxes similar to the way you do with regular wages. In this payroll Excel template, just enter the bonus amount in column H, and it will be included in the taxable gross income.

For reimbursements of expenses that employees incurred during business travel, note that these transactions are generally not taxable. This is because you are simply reimbursing the money that employees used to pay for business expenses; therefore, the employee didn’t actually receive any new income.

In this case, you don’t need to withhold payroll taxes. Instead, enter the amount you need to reimburse to the employee in column I of the applicable monthly payroll tab. We have formulas in place that will disregard this amount when calculating taxes to withhold and pay.

Screenshot Employee Data Example of a Monthly Payroll SheetThe monthly payroll tabs have specific columns where you can enter your employees’ names and actual hours worked.
Screenshot of Employee Data Overtime RateThe overtime rate in column D is based on the straight-time hourly rate you enter in column C with a formula that simply multiplies it by 1.5 (time and a half).
Screenshot of Employee Data Bonuses and Other Taxable IncomeBonuses and other taxable income should be entered in column H.

If learning how to do payroll in Excel seems too complex, try Gusto. You can pay your employees automatically and approve their hours worked online without having to update spreadsheets.

5. Review Automatic Payroll Calculations

After completing your initial setup and entering employee work hours, all payroll calculations should be visible. It’s a good idea to review them before paying employees so that you don’t have to void and reissue payments later. The accuracy of your payroll tax calculations also depends on how accurate your employee payroll information is. If you’re not careful, you can subject yourself to penalties. Here are the items you should review, including how the payroll Excel template computes each.

Salary per Period

Salaries in column J of the monthly payroll tabs are automatically pulled from the “Set Up Employee Data” tab. Once the template has the employee’s annual salary and your total number of pay periods in the year, the column will automatically reflect the employee’s salary for the period. In calculating the per period salary amount, note that the cells in column J have formulas that simply divide the annual salary by the number of annual pay periods you entered on the “Set Up Employee Data” tab.

Straight-Time Pay

Similar to column J, the “Straight-Time Pay” column (K) of the monthly payroll tabs also contains automated formulas. It has two parts: the number of straight-time hours worked multiplied by the straight-time hourly rate and the number of PTO hours taken multiplied by the straight-time hourly rate. This helps ensure that you’re paying employees all of the money they’re due, especially PTO hours.

Overtime Pay

Overtime pay is simple in that it’s calculated using the number of overtime hours you entered in column G and the hourly overtime rate in column D of the monthly payroll tabs. If you have overtime hours listed in column G, there should be a corresponding dollar amount in column L.

Gross Pay

Gross pay (column M) is the total income an employee is due before deductions and taxes. In this payroll Excel template, it’s calculated by adding columns J through L, plus columns H and I, of the monthly payroll tabs. The said columns contain the employees’ per period salary, straight-time pay, overtime pay, bonuses and commissions, non-taxable income, and reimbursements.

FICA Taxes

FICA taxes are labeled as “Social Security Tax” (column N) and “Medicare Tax” (column O) of the monthly payroll tabs. These columns are linked to the “Set Up Employee Data” tab, automatically pulling the applicable tax rates from there. The cells then multiply the rates by the employee’s gross pay minus any nontaxable income (such as reimbursements).

Income Tax Withholdings

As an employer, you are responsible for withholding federal income taxes from your employees’ paychecks. You might also have to withhold additional amounts—depending on the state. In the monthly payroll tabs, columns P and Q (labeled “Federal Income Tax” and “State Income Tax,” respectively) calculate taxes based on the tax rate information you provided in the “Set Up” tab.

Benefits and Other Deductions

Benefits are reflected in column R on each of the monthly payroll tabs, which sums all premiums showing in columns I through L on the “Set Up Employee Data” tab. The amounts should be fixed from one pay period to the next, but you can update the amounts in the “Set Up” tab if they ever change. If you do happen to change the benefit deductions for a particular employee, you need to overwrite the benefit deduction formulas for the prior months first and then overwrite it with the actual dollar amounts.

For example, if you need to change an employee’s health insurance deduction from $100 to $200 starting in the month of June, go to each prior month’s payroll tab (January through May) and delete the formula in the health insurance column for that particular worker. Overwrite with $100 and then go to the “Set Up” tab to update the employee’s health insurance deduction to $200.

Other deductions should be handled the same way as benefits. Note that the “Other Deductions” column (column S) in the monthly payroll tabs consists of garnishments and other deductions detailed in the “Set Up” tab. On the other hand, the “Total Deductions” (column (column T) is the sum of all taxes withheld, premiums collected for benefits, and other deductions.

Net Pay

The “Net Pay” column (column U) contains the amount you need to pay to employees either through check, direct deposit, or pay card. This amount is calculated automatically by subtracting the employee’s total deductions (column T) from their gross pay (column M).

Screenshot of Employee Data Cells with N/ACells with “N/A” in them will continue to reflect that until sufficient employee and/or paycheck details are entered for it to complete the calculation.
Screenshot of Employee Data Net PayColumn U shows the net pay amounts of employees.

Want to know more about how this payroll Excel template computes salaries and deductions? Watch this video tutorial on calculating employee pay.

6. Pay Your Employees

After you have reviewed your payroll taxes, deductions, and wage calculations for the pay period, you’re ready to pay your employees. To do this, you can choose to either print checks online, deposit money to employee pay cards, or work with your bank to process direct deposits.

7. Review Year-to-Date Payroll Information

The last tab, titled “Year-to-Date Payroll,” in our payroll Excel template lists all of your employee payroll details for the year. It’s linked to the monthly payroll tabs, so all employee payroll expenses should be reflected. If you decide to provide pay stubs—some states require you to do so—this tab will give you the YTD information you need to fill them in.

Bottom Line

If you have 10 or fewer employees and live in a state that doesn’t heavily regulate labor and payroll law, learning how to do payroll in Excel could be good for your business. Aside from paying employees, you can keep track of the payroll taxes you owe and maintain payroll records electronically.

However, if you’ve outgrown spreadsheets and want a payroll system you can easily personalize to fit your business needs, consider Gusto. It offers full-service payroll with automated taxes and forms starting at $45, plus you can set it to run payroll on autopilot.

Learn more in our Gusto review

Sours: https://fitsmallbusiness.com/how-to-do-payroll-in-excel/

How to Calculate Percent Tax in a Payroll in Excel

By Bonnie Conrad

Calculating your payroll taxes accurately saves your company the expense of penalties from the Internal Revenue Service.

As a business owner, it is your responsibility to compute the paychecks for your employees and make sure the IRS receives the prescribed payroll taxes from each worker. If you run a large business, it may make sense to outsource your payroll functions. But many small firms handle payroll in-house using a spreadsheet program like Microsoft Excel.

Launch Microsoft Excel. Open a new spreadsheet and save it to your hard drive or network share. Give the spreadsheet a descriptive name, like Company Payroll or Payroll Calculations.

Create columns for the employee information you will be entering. Set up columns for first name, last name, Social Security number and gross pay amount.

Set up columns for each payroll tax your company is required to report to the IRS. Include columns for Social Security taxes and Medicare taxes. Use separate column for the employer and employee shares of each tax.

Enter the pertinent information for each employee in your company. Enter the employee name and Social Security number, followed by the amount of their gross pay for the period.

Create a calculation for each payroll tax, based on the tax percentage rate and the employee's gross pay. For instance, the employer portion of the payroll tax is 6.2 percent, so the calculation would multiply the employee's gross pay by 6.2 percent. So if the gross pay was in cell D3, the formula would read "=D3*6.2%."

Enter the rest of your calculations into the other columns you have set up in the Excel spreadsheet. For instance, the Medicare tax for both employer and employee is currently 1.45 percent (as of 2011), so the formula would read "=D3*1.45%", assuming cell D3 contains the employee's gross pay.

Highlight the first set of formulas you have created. Hold the "Ctrl" key down and press "C" to copy those formulas to the clipboard. Then highlight the remaining rows, hold the "Ctrl" key down and press "V" to paste those formulas to the remaining rows.

References

Writer Bio

Based in Pennsylvania, Bonnie Conrad has been working as a professional freelance writer since 2003. Her work can be seen on Credit Factor, Constant Content and a number of other websites. Conrad also works full-time as a computer technician and loves to write about a number of technician topics. She studied computer technology and business administration at Harrisburg Area Community College.

Sours: https://smallbusiness.chron.com/calculate-percent-tax-payroll-excel-13742.html

Taxes payroll excel for formulas

How to calculate income tax in Excel?

In some regions, the income tax will be taken out from your incomes (salary) by your company accountants, while in some regions it requires to calculate and declare the income tax by yourself. How to figure out your income tax? There are several solutions in Excel.


Calculate income tax in Excel

Supposing you have got the tax table in the Range A5:C12 as below screenshot shown, and your income is placed in the Cell C1. Now you can calculate your income tax as follows:

1. Add a Differential column right to the tax table. In the Cell D6 type 10%, in the Cell D7 type the formula =C7-C6, and then drag the AutoFill Handle to the whole Differential column. See screenshot:

2. Add an Amount column right to the new tax table. In the Cell E6 type the formula =$C$1-A6 ($C$1 is the cell with your income), and then drag the AutoFill Handle to the whole Amount column. See screenshot:

3. Add a Tax column right to the new tax table. In the Cell F6 type the formula =E6*D6, and then drag the AutoFill Handle until negative results appear. See screenshot:

4. Click into the cell you will place the income tax at, and sum all positive numbers in the Tax column with the formula =SUM(F6:F8). See screenshot:

So far you have figured out the income tax of the specified income already.

Quickly match the taxable income with tax rate by an amazing tool

In many countries and regions, the income tax rate varies depend on your income. Here, I will introduce the Lookup between Two Values feature to help you quickly match the tax rate and cumulative tax with one or multiple incomes with an Excel formula. Full Feature Free Trial 30-day!

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Calculate income tax with SUMPRODUCT function in Excel

Actually, you can apply the SUMPRODUCT function to quickly figure out the income tax for a certain income in Excel. Please do as follows.

1. In the tax table, right click the first data row and select Insert from the context menu to add a blank row. See screenshot:

2. Select the cell you will place the calculated result at, enter the formula =SUMPRODUCT(C6:C12-C5:C11,C1-A6:A12,N(C1>A6:A12)), and press the Enter key. See screenshot:

Note: In the above formula, C6:C12-C5:C11 figures out the differential rates, C1 is the specified income, C1-A6:A12 calculates the amount to each differential rate, and you can change them as you need.


Calculate income tax with Vlookup function in Excel

Frequently, you can get the tax table with cumulative tax for each tax bracket. In this condition, you can apply the Vlookup function to calculate the income tax for a certain income in Excel.

Select the cell you will place the calculated result at, enter the formula =VLOOKUP(C1,A5:D12,4,TRUE)+(C1-VLOOKUP(C1,A5:D12,1,TRUE))*VLOOKUP(C1,A5:D12,3,TRUE) into it, and press the Enter key. See screenshot:

Note: In above formula, C1 is the specified income, A5:D12 is the tax table, and you can change them as you need.


Related articles:

How to calculate sales tax in Excel?


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More...Free Download...Purchase... 


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Sours: https://www.extendoffice.com/documents/excel/5082-excel-calculate-income-tax.html
How to Prepare Employee Payroll Sheet on MS Excel in Amharic

Payroll in Excel

Payroll in Excel

Payroll in Excel (Table of Contents)

Introduction to Payroll in Excel

  • While most organisations turn their heads towards software like Tally or ADP for payroll, or they give a contract to some outsourcing companies to provide them with the payroll services without any fuss. Several reasons are there for this approach. However, the core of those is that it is very time-consuming to do all the manual work for payroll and have it set. Apart from that, it consumes manpower engaged as well. With all the ease it makes for them to be considered, some businesses prefer to do payroll work on their own (in-house) and manage it accordingly.
  • Having already said it is a time, cost and manpower consuming task, it provides you total control over your employee’s payroll as well as of each penny you are spending on them at the same time. In order to do payroll manually, you need something (definitely a tool) that is powerful, versatile and easy to handle at the same time. With all these requirements, you can easily guess the tool. Yes, you might have guessed it right! Microsoft Excel. The range of simple formulae and its simple layout makes excel stand out for those who are doing payroll manually for their employee. In this article, we are going to see how we can create a payroll manually from scratch.

Example of Creating a Payroll in Excel

Payroll in Excel is very simple and easy. Let’s understand how to create the Payroll in Excel with a few steps.

Step 1: Open a new blank excel spreadsheet. Go to Search Box. Type “Excel” and double click on the match found under the search box to open a blank excel file.

payroll in excel 1-1

Step 2: Save the file on the location you want your payroll to be saved so that it does not get lost, and you will always have it with you.

payroll in excel 1-2

Step 3: In this newly created file where all your employee payroll information would be stored, create some column with names that can hold the values for certain parameters/variables. Enter the column names in the following hierarchy.

  • Employee Name (column A): Contains your employee name.
  • Pay/Hour (column B): Contains per hour pay rate to the employee without any currency symbol.
  • Total Hours Worked (column C): Contains total hours worked by an employee in a day.
  • Overtime/Hour (column D): Overtime rate per hour without any currency symbol.
  • Total Overtime Hours (Column E): Number of hours employees overtime in a day.
  • Gross Pay (column F): Payable amount to the employee without any deductibles.
  • Income Tax (column G): Tax payable on Gross Pay.
  • Other Deductibles (If Any) (column H): Deductibles other than Income Tax.
  • Net Pay (column I): Payment, the employee, will receive in hand after all the deductions.

Step 4: Add the details column-wise like Employee Name in column A, the number of hours worked and hourly paying rate, etc. I will say input the fields with no formula (From column A to column E). See the screenshot below for a better understanding.

payroll in excel 1-3

In this example, if you can see, the Total Hours Worked and Total Overtime Hours are considered on a monthly basis (because we pay the employee on a monthly basis, right?). Therefore 160 means total hours worked during the month. The same is the case with total hours overtimed. Also, the Pay/Hour and Overtime/Hour are in USD.

Step 5: Formulate Gross Pay. Gross Pay is nothing but the sum of the product of Pay/Hour; Total Worked Hours and Overtime/Hour, Total Overtime Hours. (Pay/Hour * Total Hours Worked) + (Overtime/Hour * Total Overtime Hours). The payroll sheet can be formulated under cell F4 as =(B2*C2)+(D2*E2). It’s a simple formula anyway. However, you can see the screenshot below for a better understanding.

payroll in excel 1-4

After using the formula, the answer is shown below.

payroll in excel 1-5

Drag the same formula cell F3 to cell F6.

payroll in excel 1-6

Step 6: In order to calculate the Income Tax, you need to check how much percentage of tax your employee pays on the total gross pay. Income Tax is always calculated on Gross Pay. In this case, we will consider 15% of Income-tax on all the Gross Pay. The formula for Income Tax, therefore, becomes as – 0.15 * Gross Pay.

payroll in excel 1-7

Which in terms of excel payroll sheet can be formulated under cell G2 as =0.15*F2 (Column F contains Gross Pay amount).

payroll in excel 1-8

Drag the same formula in cell G3 to G6.

payroll in excel 1-9

Step 7: You have to mention other deductibles, if any, for a particular employee. These deductibles may contain the premium of health/life insurance, professional taxes, EMI amount if any loan is taken from an organization, etc. add these amount values under column H. If there is no other deductible for a particular employee, you can set the value under column H for that employee to zero.

step -7

Step 8: Now, finally, we come towards Net Pay. Net Pay is nothing but the amount that gets credited into your employee’s bank account after all the deductions from Gross Pay. Therefore, in this case, we will deduct (subtract) Income Tax (column G) and Other Deductibles (Column H), which can be formulated under cell I2 as =F2-(G2+H2). Here, Income Tax and Other Deductibles are summed up and then subtracted from Gross Pay. See the screenshot below for a better understanding.

step -8

Drag the same formula in cell I3 to cell I6.

step 8-1

This is how we create the payroll under excel to manage things on our own.

Step 9: Add all the employee names working for you in this payroll one by one and set their total worked hours, overtimed hours, deductibles and charges accordingly. For Gross Pay, Income Tax and Net Pay, just drag the 4th cell of respective columns to have the details formulated. Also, add some formatting to the cells and add the total at the end of the sheet. The final Payroll should look like this.

step 9

After using the SUM Formula, the answer is shown below.

final payroll

Find the Sum Total below.

Sum Total

Things to Remember About Payroll in Excel

  • There are more advanced tools available to have your payroll done. However, creating your own in excel gives you a kind of security because you are monitoring your own payroll there.
  • There might be some more columns added like health insurance premium, life cover premium, etc. However, this is a simple layout.

Recommended Articles

This is a guide to Payroll in Excel. Here we discuss How to Create a Payroll in Excel along with a practical example and downloadable excel template. You can also go through our other suggested articles –

  1. MAX Formula in Excel
  2. ROUND Formula in Excel
  3. NPV Formula in Excel
  4. PPMT Function in Excel
Sours: https://www.educba.com/payroll-in-excel/

Now discussing:

Seryozhka bared his fangs again, but put the plate on the table. We ate in silence. Lena threw frightened glances at Seryozhka, apparently at any second ready to jump off the spot and run away, and her son looked.



4645 4646 4647 4648 4649