G K L M N D this Week Net Pay Rate Tax Tax Tax $1,800 $3,500 $2.000 $4,500 $1,050 $2,000 $1,100 $1,200 $2,100 $3,600 $2,300 $1,500 $2,500 $4,000 $2,800 $3,200 Gross income (AGI) $1,570.00 $3,270.00 $1.770.00 $4,270.00 $820.00 $1,770.00 5870.00 $970.00 $1,870.00 $3,370.00 $2,070.00 $1,270.00 $2,270.00 $3,770.00 $2,570.00 $2,970.00 Employee Name Department YTU Earnings 3 4 Andrews, Bill Training $93,600 s Brown, Betty Admin $182,000 6 Carson, Catherine Training $104,000 7 Carlton, James Sales $234,000 8 Chase, Troy Training $46,800 9 Duwson, Jonathan Admin $104,000 10 Holland, Donald Training $57,200 11 Page, Lisa Sales $62,400 12 Pratt, Erik Sales $109,200 13 Spencer, Boyd Marketing $187,200 14 Tanner, Timothy Sales $119,600 15 Taylor, Hector Sales $78,000 16 Weber, Larry ADC $130,000 a7 White, Daniel Admin $208,000 18 Wiggins, Frank Training $145,600 19 Wilkins, Jesse Marketing $166,400 20 21 22 Standard Deduction from Gross Weekly Pay 33 Per fler ($12,000 / 521 $230.77 24 Note: As of 2018, there is no longer a standard os deduction for each dependent 26 27 Federal Tax Tables (for those paid weekly Bracket min Tax Rate AGI was at least Bracket on AG 29 50.00 ON 1 30 $73.00 10% 2 31 $260.00 3 32 $832.00 22% 4 33 $1,692.00 5 34 $3,164.00 32% 6 35 36 FICA tax rates (SSN and medicare) Up to Max 37 Tax Rate Pay Limit 38 Social Security Tax 6,20% $132.900 39 Medicare Tax 1.45% no limit 40 1. In 4, calculate William Andrew's Adjusted Gross Income (AGI) for this week by subtracting the standard deduction from his gross weekly pay 2. Copy this formula down the column to calculate the other employees' AGI. 3. in 4, VLOOKUP and the Federal Tax Rate table in cells A28 through c33 to calculate William Andrew's Federal tax rate, 4. Cooy this formula down the column to calculate the other employee's federal tax rate. 4. Copy this formula down the column to calculate the other employees' federal tax rate. 12% 24% 5. in 64. calculate William Andrew's federal tax Samount by multiplying his tax rate by his AGI 6 Copy this formula down the column to calculate the other employees' federal tax Samount 7. in 14, calculate William Andrew's social security tax Samount by multiplying his AI by the social security tax rate if his YTD pays to $132,900, his social security tax is $0. 8. Copy this formula down the column to calculate the other employees' social security tax Samount 9. In 14, calculate William Andrew's medicare tax Samount by multiplying his AGI by the medicare tax rate. 10. Copy this formula down the column to calculate the other employees' medicare tax Samount. 11. In 34, calculate William Andrew's net pay by subtracting his federal, social security, and medicare taxes from his gross pay. 12. Copy this formula down the column to calculate the other employees' net pay. utoSave Bu sv. ome Insert Draw Page Layout Formulas Act2 Formulasfunctions X Data Review Calibri (Body) View Developer 11 Tell me Paste B IU - A a. Au 15 Wrap Text ili II Percentage 1 x fx Merge Center 5 % 9 A B D E 1 1 Payroll Tawes Employee Name Department YTD Earnings Gross Pay for this Week Federal Tax Rate Federal Tax Soc Sec Tax Medicare TEX Net Pay Andrews, Bill Brown, Betty Carson, Catherine Carlton, James 8 Chase, Troy Dawson, Jonathan 0 Holland, Donald 1 Page, Lisa 2 Pratt, Erik 13 Spencer, Boyd 14 Tanner, Timothy 15 Taylor, Hector 16 Weber, Larry Training Admin Training Sales Training Admin Training Sales Sales Marketing Sales Sales ADC Admin $93,600 $182,000 $104,000 $234,000 $46,800 $104,000 $57,200 $62,400 $109,200 $187,200 $119,600 $78,000 $130,000 $208,000 $1,800 $3,500 $2,000 $4,500 $1,050 $2,000 $1,100 $1,200 $2,100 $3,600 $2,300 Adjusted Gross Income (AGI $1,570,00 $3,270.00 $1,770.00 $4,270.00 $820.00 $1.770.00 $870.00 $970.00 $1,870.00 $3,370.00 $2.070.00 $1.500 $2.500 $4,000 $2.800 $3.200 $1 270.00 $2,270.00 $1,770.00 $2.570.00 $2.970.00 17 White, Daniel 18 Wiggins, Frank Training $145,600 19 Wilkins, Jesse Marketing 5166,400 20 21 22 Standard Deduction from Gross Weekly Pay 23 Per filer ($12,000/52): $230.77 24 Note: As of 2018, there is no longer a standard 25 deduction for each dependent, 26 27 Federal Tax Tables (for those paid weekly) Bracket min Tax Rate Bracket 28 AGI was at least 1 ON $0.00 29 2 30 $73.00 10% 3 31 12 22 32 24% 5 32 1. In 14 calculate William Andrew's Adjusted Gross income for this weby weekly pay 2. Copy this formula down the column to calculate the employees'AL 3. in 4 use VLOOKUP and the Federal Tax Rate table in the calculate 4. Cooy this formula down the column to calculate the other one's federal taxa 4. Copy this formula down the column to calculate the other employees' federal tax rate on AGI 5. in 64. calculate William Andrew's federale Samarby muting tax rate by Copy this formule down the column to the other employees' federal som 7. in calculate William Andrew's curity tax Samouit by Al by the to $132,900, his social security tax is $0. 8. Copy this formula down the column to calculate the other employees' social security tax Samount 9. In 14, calculate William Andrew's medicare tax Samount by multiplying his AGI by the medicare tax rate. 10. Copy this formula down the column to calculate the other employees' medicare tax Samount. 11. In 34, calculate William Andrew's net pay by subtracting his federal, social security, and medicare taxes from his gross pay. 12. Copy this formula down the column to calculate the other employees' net pay. utoSave Bu sv. ome Insert Draw Page Layout Formulas Act2 Formulasfunctions X Data Review Calibri (Body) View Developer 11 Tell me Paste B IU - A a. Au 15 Wrap Text ili II Percentage 1 x fx Merge Center 5 % 9 A B D E 1 1 Payroll Tawes Employee Name Department YTD Earnings Gross Pay for this Week Federal Tax Rate Federal Tax Soc Sec Tax Medicare TEX Net Pay Andrews, Bill Brown, Betty Carson, Catherine Carlton, James 8 Chase, Troy Dawson, Jonathan 0 Holland, Donald 1 Page, Lisa 2 Pratt, Erik 13 Spencer, Boyd 14 Tanner, Timothy 15 Taylor, Hector 16 Weber, Larry Training Admin Training Sales Training Admin Training Sales Sales Marketing Sales Sales ADC Admin $93,600 $182,000 $104,000 $234,000 $46,800 $104,000 $57,200 $62,400 $109,200 $187,200 $119,600 $78,000 $130,000 $208,000 $1,800 $3,500 $2,000 $4,500 $1,050 $2,000 $1,100 $1,200 $2,100 $3,600 $2,300 Adjusted Gross Income (AGI $1,570,00 $3,270.00 $1,770.00 $4,270.00 $820.00 $1.770.00 $870.00 $970.00 $1,870.00 $3,370.00 $2.070.00 $1.500 $2.500 $4,000 $2.800 $3.200 $1 270.00 $2,270.00 $1,770.00 $2.570.00 $2.970.00 17 White, Daniel 18 Wiggins, Frank Training $145,600 19 Wilkins, Jesse Marketing 5166,400 20 21 22 Standard Deduction from Gross Weekly Pay 23 Per filer ($12,000/52): $230.77 24 Note: As of 2018, there is no longer a standard 25 deduction for each dependent, 26 27 Federal Tax Tables (for those paid weekly) Bracket min Tax Rate Bracket 28 AGI was at least 1 ON $0.00 29 2 30 $73.00 10% 3 31 12 22 32 24% 5 32 1. In 14 calculate William Andrew's Adjusted Gross income for this weby weekly pay 2. Copy this formula down the column to calculate the employees'AL 3. in 4 use VLOOKUP and the Federal Tax Rate table in the calculate 4. Cooy this formula down the column to calculate the other one's federal taxa 4. Copy this formula down the column to calculate the other employees' federal tax rate on AGI 5. in 64. calculate William Andrew's federale Samarby muting tax rate by Copy this formule down the column to the other employees' federal som 7. in calculate William Andrew's curity tax Samouit by Al by the