Question

IBT Tech Inc is considering a new 3-year investment project that requires an initial fixed asset investment of $4.49 million. The fixed asset will be depreciated straight-line to zero over its three-year life. The project is estimated to generate $2,950,000 in sales per year with additional costs of $952,000 per year. The tax rate is 30% and the required return (appropriate discount rate) is 16%. The fixed asset should have a market (or salvage) value of $595,000 at the end of the project. In addition, the project requires an initial investment in net working capital of $160,000 which will be fully recovered in the third year.

Please attach a file that clearly shows calculations for (A) the NPV of this investment, and (B) the marginal tax rate that would cause the firm to reverse decision based on NPV in part A (tax rate that yields NPV=0). Please highlight your final answer in red so I can find it easily!

Answer 1

Part A

The NPV of the investment is calculated below, The project free cash flow calculations are as shown below, the final project free cash flows are = PAT+Depriciation+CAPEX+Change in NOWC

Finally the NPV is calculated by using Excel's NPV function with MARR & given cash flows as inputs as

А E Н К Inputs Initial fixed asset Depriciation Rate 33.33% 44,90,000 Salvage value Annual revenues 29,50,000 5,95,000 Tax Rate (T) Annual cost 9,52,000 30% 4 1,60,000 16% 5 NOWC MARR 6 7 0 2 year $44,90,000.0 Fixed asset cost 8 additional NOWC $ 1,60,000.0 needed $ 29,50,000.0 $ 29,50,000.0 $ 29,50,000.0 $ 9,52,000.0 $ $ 14,96,666.7 $ 14,96,666.7 $ 14,96,666.7 Annual revenues 10 9,52,000.0 $ Annual cost 9,52,000.0 11 12 Depriciation Profit before tax Revenue-costs & deprcn.) 5,01,333.3 $ 5,01,333.3 $ 5,01,333.3 13 Profit after tax $ 3,50,933.3 $ 3,50,933.3 $ 3,50,933.3 (-PBT x(1-T)) 14 $ 18,47,600.0 $ 18,47,600.0 $ 18,47,600.0 PAT+depriciation 15 Terminal year salvage value 16 5,9 0.0 After Tax Salvage value ((Sv-Bv) x (1- T)) 4,16,500.0 17 NOWC recovery in terminal year 18. 1,60,000.0 $-46,50,000.0 $ 18,47,600.0 $ 18,47,600.0 $ 24,24,100.0 $-1,31,155.3 Project free cash flows 19 20 NPV

Formula view

A D E Н 1 Inputs -1/3 Initial fixed asset 2 4490000 Depriciation Rate Salvage value Annual revenues 2950000 595000 Annual cost 952000 Tax Rate (T) 4 0.3 5 NOWC 160000 MARR 0.16 6 7 2 year Fixed asset cost 8 --E2 additional NOWC needed --E5 -$E$3 -SE$3 -SE$3 Annual revenues 10 -$E$4 $E$4 =SE$4 Annual cost 11 -SE$2*$H$2 -SE$2*$H$2 =$E$2*$H$2 Depriciation 12 Profit before tax (Revenue-costs & 13 =F10-F11-F12 -G10-G11-G12 H10-H11-H12 deprcn.) =F13* (1-$H$4) H13*(1-$H$4) Profit after tax (=PBT x(1-T)) G13* (1-$H$4) 14 PAT +depriciation 15 =F14+F12 -G14+G12 H14+H12 16 -нз Terminal year salvage value 17 After Tax Salvage value ((Sv-Bv) x (1-T)) =H16*(1-H4) 18 NOWC recovery in terminal Project free cash flows E5 year 19 -G8+G9+G15+G17+G18 -E8+E9+E15+E17+E18 =F8+F9+F15+F17+F18 H8+H9+H15+H17+H18 NPV(H5,F19:H19) +E19 20 NPV

Part B.

For this part, we can use Excel's Goal seek function (Under Data tab-> What If analysis). Just enter the objective cell to be E20 (which holds the NPV formula) & in the 'To value' enter 0 & in 'By Changing Cell' enter H4 which holds the value of Tax rate (as per the given spreadsheet). Once these 3 values are entered press ok & Excel will automatically change the tax rate in its cell (i.e. K4 here) to the value. In this case the value of marginal tax rate comes out to be 21.3% as you can see in below screenshot :

AB D E F G Н K L М 1 Inputs Initial fixed asset Depriciation Rate 33.33% 2. 44,90,000 Salvage value Tax Rate (T) Annual revenues 5,95,000 21.30% 3 29,50,000 Annual cost 4 9,52,000 S Goal Seek X 16% 5 NOWC 1,60,000 MARR Set cell 6 SES20 7 0 1 2 year To value: |$ -44,90,000.0 Fixed asset cost 8 By changing cell: SH$4 additional Nowc |$ -1,60,000.0 ок Cancel needed $ 29,50,000.0 $ Annual revenues 29,50,000.0 $ 29,50,000.0 10 9,52,000.0 14,96,666.7 $ 14,96,666.7 Annual cost $ 9,52,000.0 $ 9,52,000.0 $ 11 $14,96,666.7 $ Depriciation 12 Profit before tax $ 5,01,333.3 $ 5,01,333.3 $ 13 5,01,333.3 (=Revenue-costs & 3,94,561.0 $ 3,94,561.0 $ Profit after tax 3,94,561.0 14 $ 18,91,227.6 $ PAT+depriciation 18,91,227.6 $ 18,91,227.6 15 Terminal year 16 salvage value After Tax Salvage value ((Sv-Bv) x (1 $ 5,95,000.0 17 $ T)) NOWC recovery in 4,68,278.8 18 terminal year 1,60,000.0 $ -46,50,000.0 $ 18,91,227.6 $ Project free cash flows 18,91,227.6 $ 25,19,506.4 19 $ 20 NPV