Chapter 7 Problem 12 a). Complete the spreadsheet below by estimating the project's annual after tax cash flow. b). What is the investment's net present value at a discount rate of 10 percent? c). What is the investment's internal rate of return? d). How does the internal rate of return change if the discount rate equals 20 percent? e). How does the internal rate of return change if the growth rate in EBIT is 8 percent instead of 3 percent? Facts and Assumptions Equipment initial cost $ Depreciable life yrs. 350,000 7 10 50 Expected life yrs. Salvage value $ Straight line depreciation EBIT in year 1 28,000 Tax rate 38% Growth rate in EBIT 3% Discount rate 10% Year 1 2 7 10 Initial cost 350,000 Annual depreciation 50,000 50,000 50.000 50,000 50,000 50,000 50,000 EBIT 28,000 28.840 29,705.2 30.596.356 31,514.247 32.459.674 33,433.464 34,436 468 35,469.562 36,533.649 Net present value @10% Internal rate of return Chapter 7 Problem 12 Suggested Answers a). Complete the spreadsheet below by estimating the project's annual after tax cash flow. See spreasheet below. b). What is the investment's net present value at a discount rate of 10 percent? c). What is the investment's internal rate of return? 0 d). How does the internal rate of return change if the discount rate equals 20 percent? It doesn't change. The IRR is independent of the discount rate. e). How does the internal rate of return change if the growth rate in EBIT is 8 percent instead of 3 percent? Facts and Assumptions Equipment initial cost $ Depreciable life yrs. Expected life yrs. Salvage value $ Straight line depreciation EBIT in year 1 Tax rate Growth rate in EBIT 12.8% Change the "Growth rate in EBIT assumption to 8% and observe the new IRR. 350,000 10 $0 Notes: One: Only enter answers into the "Green" Cells. Two: You MUST use the Excel Formulas, do NOT simply put a number in the answer cell. Three: A few Cells may have some guidance to help you with the answers. 28.000 38% 3% Discount rate 10% Year 1 2 3 4 5 6 7 8 9 10 Initial cost 350,000 Annual depreciation EBIT Taxes EBIT after tax After tax cash flow Check Figure Cell 830 350,000. 50,000 50,000 50,000 50,000 50,000 50,000 50,000 28.000 28,840 29,705.2 30,596.356 31,514.247 32,459.674 33,433.464 34,436.468 35,469.562 Net present value @10% Internal rate of return Check Figure Cell 83 $12,923| Check Figure Cell B3-11.0% You MUST use the Excel Formulas, do NOT simply put a number in the answer cell. 36,533.649 Check Figure Ce L30