Question

Fatima Corporation has the following information pertaining to the purchase of a new piece of equipment: Cash revenues less cash expenses $40,000 per year Cost of equipment Salvage value at the end of the year Increase in working capital requirements $70,000 $7,000 $30,000 Tax rate Life 30 percent 6 years Cost of capital is 11 percent. Required (use excel): a. Calculate the following assuming straight-line depreciation: i. Calculate the after-tax net income for each of the six years. ii. Calculate the after-tax cash flows for each of the six years. iii. Calculate the after-tax payback period. iv. Calculate the accrual accounting rate of return on original investment for each of the six years. v. Calculate the net present value (NPV). vi. Calculate the internal rate of return (IRR). b. Calculate the following assuming that depreciation expense is $18,000, $15,000, $12,000, S9,000, $6,000 and $3,000 for years 1 through 6, respectively: i. Calculate the after-tax cash flows for each of the six years. ii. Calculate the after-tax payback period. iii. Calculate the net present value (NPV). iv. Calculate the internal rate of return (IRR).

Answer 1

X for a А в 1 2 Cash revenues less cash expenses depreciation income before tax tax expense after-tax net income depreciation after-tax cash flows cost of equipment working capital introduced working capital released Salvage value net cash flows present value factor @ 11% present value of net cash flows year 0 year 1 year 2 year 3 year 4 year 5 year 6 $ 40,000 $40,000 $ 40,000 $ 40,000 $ 40,000 $40,000 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 29,500 $ 29,500 $ 29,500 $ 29,500 $ 29,500 $ 29,500 $8,850 $8,850 $8,850 $8,850 $8,850 $8,850 $ 20,650 $ 20,650 $ 20,650 $ 20,650 $ 20,650 $ 20,650 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 10,500 $ 31,150 $ 31,150 $ 31,150 $ 31,150 $ 31,150 $ 31,150 -$ 70,000 -$ 30,000 $ 30,000 $ 7,000 $ 100,000 $ 31,150 $ 31,150 $ 31,150 $ 31,150 $ 31,150 $ 68,150 0.90090 0.81162 0.73119 0.65873 0.59345 0.53464 $ 100,000.00 $ 28,063.06 $ 25,282.04 $ 22,776.61 $ 20,519.47 $ 18,486.01 $ 36,435.77 after tax payback period = $100,000 / $31,150 = 3.21 years year 1 51.63% year 2 51.63% year 3 51.63% year4 51.63% year 5 51.63% year 6 51.63% accrual accounting rate of return 5 6 net present value (NPV) internal rate of return (IRR) $ 51,562.97 13.19%

1 x ✓ fx b к M N O P Q R S year 0 year 1 year 2 year 3 year 4 year 5 year 6 Cash revenues less cash expenses $ 40,000 $40,000 $40,000 $40,000 $40,000 $40,000 depreciation $ 18,000 $ 15,000 $ 12,000 $9,000 $6,000 $3,000 income before tax $ 22,000 $25,000 $ 28,000 $ 31,000 $ 34,000 $ 37,000 tax expense $6,600 $ 7,500 $ 8,400 $ 9,300 $ 10,200 $ 11,100 after-tax net income $ 15,400 $ 17,500 $ 19,600 $ 21,700 $ 23,800 $ 25,900 depreciation $ 18,000 $ 15,000 $ 12,000 $9,000 $6,000 $3,000 after-tax cash flows $ 33,400 $ 32,500 $ 31,600 $30,700 $ 29,800 $ 28,900 cost of equipment -$ 70,000 working capital introduced $ 30,000 working capital released $ 30,000 Salvage value $ 7,000 net cash flows $ 100,000 $33,400 $ 32,500 $ 31,600 $ 30,700 $ 29,800 $ 65,900 present value factor @ 11% 1 0.90090 0.81162 0.73119 0.65873 0.59345 0.53464 present value of net cash flows |-$ 100,000.00 $ 30,090.09 $ 26,377.73 $ 23,105.65 $ 20,223.04 $ 17,684.85 $ 35,232.83 year 1 cumulative after-tax cash flows $ 33,400 after tax payback period = 3 + ($2,500 / $30,700) = year 2 $ 65,900 year 3 $ 97,500 3.08 year 4 $ 128,200 years year 5 $ 158,000 year 6 $186,900 net present value (NPV) internal rate of return (IRR) $ 52,714.19 13.81% 4

for formulas and calculations, refer to the image below -

X for a year 0 Cash revenues less cash expenses depreciation income before tax tax expense after-tax net income depreciation 2. after-tax cash flows cost of equipment -70000 working capital introduced -30000 working capital released Salvage value net cash flows =SUM(09:013) present value factor @ 11% =1 present value of net cash flows =D14*D15 year 1 year 2 40000 40000 --(70000-7000)/6 =$E$4 =E3+E4 =F3+F4 --E5*0.3 --F5*0.3 =E5+E6 =F5+F6 --E4 --F4 =E7+E8 =F7+F8 year 3 40000 =$E$4 =G3+G4 --G5*0.3 =G5+G6 --G4 =G7+G8 year 4 40000 =$E$4 =H3+H4 --H5*0.3 =H5+H6 --H4 =H7+H8 year 5 40000 =$E$4 =13+14 --15*0.3 1=15+16 =-14 =17+18 year 6 40000 =$E$4 =J3+14 --J5*0.3 =J5+J6 =-14 =J7+J8 =SUM(E9:E13) =D15/1.11 =E14*E15 30000 7000 ESUM(F9:F13) =SUM(G9:G13) ISUM(H9:H13) I=SUM(19:113) I=SUM(19:J13) =E15/1.11 =F15/1.11 =G15/1.11 =H15/1.11 =115/1.11 =F14*F15 =G14*G15 =H14*H15 =114*115 =J14*J15 after tax payback period = $100,000 / $31,150 = =100000/31150 years year 1 =E7/E3 year 2 =F7/F3 year 3 =G7/G3 year 4 =H7/H3 year 5 =17/13 year 6 =J7/13 accrual accounting rate of return 5 6 net present value (NPV) internal rate of return (IRR) =SUM(D16:J16) =IRR(D16:J16,10%)

N O P Q R year 0 Cash revenues less cash expenses depreciation income before tax tax expense after-tax net income depreciation after-tax cash flows cost of equipment |-70000 working capital introduced -30000 working capital released Salvage value net cash flows =SUM(NO:N13) present value factor @ 11% present value of net cash flows =N14*N15 year 1 40000 - 18000 =O3+04 --05*0.3 =O5+06 --04 =07+08 year 2 40000 - 15000 =P3+P4 --P5*0.3 =P5+P6 --P4 =P7+P8 year 3 40000 - 12000 =Q3+Q4 --Q5*0.3 =Q5+Q6 =-Q4 =Q7+Q8 year 4 40000 -9000 =R3+R4 =-R5*0.3 =R5+R6 --R4 =R7+R8 year 5 40000 -6000 =S3+S4 =-55*0.3 =S5+S6 --S4 =S7+S8 year 6 40000 -3000 =T3+T4 --T5*0.3 =T5+T6 --T4 =T7+T8 =SUM(09:013) =SUM(P9:P13) =SUM(Q9:013) =N15/1.11 =O15/1.11 =P15/1.11 =014*015 =P14*P15 =Q14*015 30000 7000 =SUM(R9:R13) =SUM(59:513) =SUM(T9:T13) =Q15/1.11 =R15/1.11 1=S15/1.11 =R14*R15 =S14*S15 =T14*T15 year 2 year 1 cumulative after-tax cash flows =09 after tax payback period = 3 +($2,500 / $30,700) = year 3 =P19+Q9 =3+(2500/30700) year 4 =Q19+R9 years year 5 =R19+59 =O19+P9 year 6 =S19+T9 net present value (NPV) internal rate of return (IRR) =SUM(N16:116) =IRR(N16:T16,10%) 4

In case you have any query, kindly ask in comments.