Tax Calculation Worksheet (Nested IF Two)

1. In cell E13, retrieve the income limit from the appropriate tax table. Use a nested IF function with Vlookup inside the IF functions to retrieve the first column from the appropriate tax rate table. The formula is identical to the formula inside the Marginal Tax Rate cell (D13) except for retrieving the first column. Extend the formula to cells E14:E17. Format the cells as Currency with zero decimal digits. (7%)
2. In cell F13, retrieve the base tax from the appropriate tax table. Use a nested IF function with Vlookup inside the IF functions to retrieve the third column from the appropriate tax rate table. The formula is identical to the formula inside the Marginal Tax Rate cells except for retrieving the third column. Extend the formula to cells F14:F17. Format the cells as Currency with zero decimal digits. (7%)
3. In cell G13, compute the tax amount as the base tax amount plus the marginal tax rate times the marginal income (income greater than income limit). The marginal income is the income minus the income limit. Extend the formula to cells G14:G17. Format the cells as Currency with zero decimal digits. (6%)

Hint: Use the existing Vlookup function from Column D and change it a little for columns E and F.

D H A B Tax Table for Single S Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $510,301 37% $153,796 E F F G Tax Table for Head of Household HH Tax rate Base Tax $0 10% $0 $13,851 12% $1,385 $52,851 22% $6,065 $84,201 24% $12,962 $160, 701 32% $31,322 $204,001 35% $45,210 $510,301 37% $152,380 K Tax Table for Married Filing Jointly MF) Tax rate Base Tax $0 10% $0 $19,401 12% $1,904 $78,952 22% $9,086 $168,401 24% $28,765 $321,451 32% $65,497 $408,201 35% $93,257 $612,351 37% $164,709 M N P Tax Table for Married Filing Separately MFS Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $306,176 37% $82,355 Income Limit Base Tax Tax Amount 1 e Taxpayer Id Filing status 111223333 S 222334444 HH 5 222445555 MFS 5 333556666 MFJ - 444556666 S Income Marginal Tax Rate $165,899 32% $85,444 24% $401,550 37% $15,010 10% $160,726 32% 3 D H A B Tax Table for Single S Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $510,301 37% $153,796 E F F G Tax Table for Head of Household HH Tax rate Base Tax $0 10% $0 $13,851 12% $1,385 $52,851 22% $6,065 $84,201 24% $12,962 $160, 701 32% $31,322 $204,001 35% $45,210 $510,301 37% $152,380 K Tax Table for Married Filing Jointly MF) Tax rate Base Tax $0 10% $0 $19,401 12% $1,904 $78,952 22% $9,086 $168,401 24% $28,765 $321,451 32% $65,497 $408,201 35% $93,257 $612,351 37% $164,709 M N P Tax Table for Married Filing Separately MFS Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $306,176 37% $82,355 Income Limit Base Tax Tax Amount 1 e Taxpayer Id Filing status 111223333 S 222334444 HH 5 222445555 MFS 5 333556666 MFJ - 444556666 S Income Marginal Tax Rate $165,899 32% $85,444 24% $401,550 37% $15,010 10% $160,726 32% 3