Question

Do in Excel.

Southwest Products Company is considering a   project to invest. The initial investment is $15 million and the life of the project is 7 years. Its revenue per year is $10 million and cost of goods sold is 50% of revenue. The project will be depreciated by using the 7-year MACRS depreciation rate. The company’s tax rate 35% and cost of capital is 12%. Calculate payback period, discounted payback period, NPV, IRR, PI and MIRR for the project.

Answer 1

K11 : X r fi =IRR(K2:K9) A B C D E F Depreci ation Tax@35 % Costs PBT 1 Year Revenue 2 0 3 1 $ 10.00 2 $ 12.00 3 $ 14.40 4 $ 17.28 7 5 $ 20.74 8 6 $ 20.74 7 $ 20.74 10 $ 5.00 $ 6.96 $ 8.35 $ 10.02 $12.03 $12.03 $12.03 (1.59 $ G H J K Cash flows Net Deprec from Total cash income iation operations Investment flows I $ (15.00) $ (15.00) $ 1.86 $ 2.14 $ 4.00 4.00 $ 0.89 $3.67 $ 4.56 4.56 $ 2.23 $ 2.62 $ 4.85 $ 4.85 $ 3.50 $ 1.87 $ 5.37 5.37 $ 4.79 $ 1.34 $ 6.13 6.13 $ 4.79 | $ 1.34 $ 6.13 $ 6.13 $ 4.79 $ 1.34 $ 6.13 $ 0.23 $ 6.36 $ 2.14 $ 3.67 $ 2.62 $ 1.87 $ 1.34 $ 1.34 $ 1.34 L M N O Simple Cumulative cumulative discounted PVIF@12% Present value cash flows | cash flows 1.000 $ (15.00) $ (15.00) $ (15.00) 0.893 $ 3.57 $ (11.00) $ (11.43) 0.797 $ 3.64 $ (6.44) $ (7.79) 0.712 $ 3.45 $ (4.34) 0.636 $ 3.41 $ 3.78 $ (0.93) 0.567 $ 3.48 $ 9.91 $ 2.55 0.507 $ 3.11 $ 16.04 $ 5.66 0.452 $ 2.88 $ 22.41 $ 8.54 $2.86 $ 1.00 $1.37 $ 0.48 $3.42 $ 1.20 $5.38 $ 1.88 $7.37 | $ 2.58 $7.37 | $ 2.58 $7.37 $ 2.58 IRR 26.80% NPV 8.54 Asset cost Less: Depreciation charged $ 15.00 $ 14.33 MIRR 19.45% WDV of machine at time of sale Sale value of machine Profit/(Loss) on sale =MIRR(K2:K9,12%, 12%) $ 0.67 $ - $ (0.67) $ (0.23) $ 0.23 Profitability index 1.57 Tax@35% After tax sale value = 1+8.54/15 Payback period 3.30 Years =3+1.59/5.37 Discounted Payback period 4.27 Years =4+0.93/3.48

*Please rate thumbs up