Question

Share File Home Insert Draw Page Layout Formulas Data Review View Help O PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. e Enable Editing B3 : X Fox INCOME TAX CALCULATION -Nm INCOME TAX CALCULATION 7 Tax on Base 2- Develop pseudo codes to calculate the tax liability Pseudo Code to Calculate Tax Liability If income is <5,000 then 10 11 TAX TABLE: 1- Calculate the Tax on Base for the following tax table Income $0 $5,000 $5,000 - $15,000 $15,000 - $20,000 $20,000 $28,000 $28,000 $35,000 $35,000 $50,000 $50,000 else if income < 15,000 then Tax Rate 5% 10% 15% 20% 25% 30% 35% else if income < 20,000 then else if income < 28,000 then else if income < 35,000 then else if income < 50,000 then Individual 1 $120,000 Individual 2 $35,000 Individual 3 $2,500 Taxable Income: else 22 3- Using simple Excel formulas, determine the tax liability for each of the three individuals Tax Amount: 25 4- Compute each of the three individuals tax liability using Excel if logical statements Tax Amount:

1. Calculate the tax on base for the provided tax table.
2. Develop pseudo codes to calculate the tax liability for the provided tax table.
3. Using simple Excel formulas, determine the tax liability for each of the three individuals listed taxable amounts.
4. Compute each of the three individuals tax liability using Excel if logical statements

Answer 1

1) CALCULATION OF TAX ON BASE

income			Tax rate	tax on base
					$            0	-	$ 5,000	5%	$ 250
$        5,000	-	$ 15,000	10%	$ 1,000
$     15,000	-	$ 20,000	15%	$ 750
$     20,000	-	$ 28,000	20%	$ 1,600
$     28,000	-	$ 35,000	25%	$ 1,750
$     35,000	-	$ 50,000	30%	$ 4,500
	>=	$ 50,000	35%

2) CALCULATION OF TAX LIABILITY

Pseudo code to calculate tax liability

if income is <5000 then

income x 5%

if income is <15000 then

$250 + (income-5000) x 10%

if income is <20000 then

$1250 + (income-15000) x 15%

if income is <28000 then

$2000 + (income-20000) x 20%

if income is <35000 then

$3600 + (income-28000) x 25%

if income is <50000 then

$5350 + (income-35000) x 30%

else

$9850 + (income-50000) x 35%

3) CALCULATION OF TAX

income			Tax rate	tax amount
				individual 1	individual 2	individual 3
$               -	-	$                5,000	5%	$               250	$                        250	$                   125
$        5,000	-	$             15,000	10%	$           1,000	$                    1,000
$     15,000	-	$             20,000	15%	$               750	$                        750
$     20,000	-	$             28,000	20%	$           1,600	$                    1,600
$     28,000	-	$             35,000	25%	$           1,750	$                    1,750
$     35,000	-	$             50,000	30%	$           4,500
	>=	$             50,000	35%	$         24,500
			individual 1	individual 2	individual 3
taxable income			$                   1,20,000	$         35,000	$                    2,500
taxable liability			$                      34,350	$           5,350	$                        125

4) calculation of tax using IF condition

			individual 1	individual 2	individual 3
taxable income			$                   1,20,000	$         35,000	$                    2,500
taxable liability			$                      34,350	$           5,350	$                        125

put the formula in tax liability column:

=IF(D12>$C$9,9850 +(D12-C9)*0.35, IF(D12<$C$3,$D$3*D12, IF(D12<$C$4,250+(D12-5000)*$D$4, IF(D12<$C$5,1250+(D12-15000)*$D$5, IF(D12<$C$6,2000+(D12-20000)*$D$6, IF(D12>$C$7,3600+(D12-28000)*$D$7,5350+(D12-$C$7)*$D$8))))))

for the above formula following is the table in excel

B2	C	D
3	$                5,000	5%
4	$             15,000	10%
5	$             20,000	15%
6	$             28,000	20%
7	$             35,000	25%
8	$             50,000	30%
9	$             50,000	35%