Use an IF function with nested VLOOKUP functions to calculate the Federal Income Tax Rate.The federal tax rate is a function of the adjusted income and the employee's marital status.Reference the tax tables in the Model Assumptions and the marital status in the Employee Information section to construct a formula $($set of nested formulas$)$ to calculate the tax rate.

Pay Rate $30.00 Marital Status Married # regular hours in Pay Period # of paychecks per year 80 26 # of Allowances 2 "Single" Tax Rate Income Retirement Savings % 5% Payroll Tax information Health Insurance (every paycheck) $125.00 Medicare Rate 1.45% Rate $0 10% $725 15% Federal Tax Rate Tables "Married" Tax Rate Income Rate $0 10% $1,450 15% Flexible Spending (Annual) $1,500.00 Social Security Rate 4.2% $2,945 25% $5,891 25% $7,137 28% $11,891 28% Paycheck Summary Income Tax Information $14,887 33% $18,120 33% Gross Pay $2,625.00 Allowance Deduction $146 $32,362 35% $32,362 35% Total Deductions $313.94 State Tax Rate 5% Adjusted Income $2,311.06 Total Taxes $0.00 Net Pay $2,311.06 Paycheck Calculations Income Hours Worked Pay Rate Regular Pay Overtime Pay Total Gross Pay 85 $30.00 $2,400.00 $225.00 $2,625.00 |Deductions (Items not subject to income tax) Insurance Flexible Spending Retirement Total Deductions Adjusted Income Taxes Federal Income Tax Rate Federal Income Tax Adjustment for Allowances Net Federal Income Tax State Income Tax Medicare Tax Social Security Tax Total Taxes $125.00 $57.69 $131.25 $313.94 $2,311.06