1~23 A B Problem 18-16 C D E F G H J K You are evaluating a project that requires an investment of $90 today and provides a single cash flow of $115 for sure one year from now. You decide to use 100% debt financing, that is, you will borrow $90. The risk-free rate is 5% and the tax rate is 25%. Assume that the investment is fully depreciated at the end of the year, so without leverage you would owe taxes on the difference between the project cash flow and the investment. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. Investment today Amount of debt Cash flow in 1 year Tax rate Risk-free rate $90.00 $90.00 $115.00 25% 5.00% a. Calculate the NPV of this investment opportunity using the APV method. 9 10 11 12 13 14 15 16 17 18 19 21 29 31 39 4567822EQEQLA222222222222033332=22 Tax basis at year end Taxes Owed FCF at year end VU Interest expense Interest tax shield PV of ITS VL NPV b. Using your answer to part (a), calculate the WACC of the project. Debt-to-value ratio WACC c. Verify that you get the same answer using the WACC method to calculate NPV. VL NPV d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. After-tax interest expense 41 FCFE NPV A B 45 Requirements 46 47 48 49 50 F 51 52 C 1. Start Excel completed. 2. D E F G H J To calculate the project's NPV using the APV method, you need to calculate the unlevered and levered values of the project. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 3. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 4. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt.). 5. In cell D21, by using cell references and the function PV, calculate the present value of the free cash flow (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the free cash flow from Step 4 in your calculations. 53 6. In cell D22, by using cell references, calculate the interest expense (1 pt.). 54 7. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 55 8. In cell D24, by using cell references and the function PV, calculate the present value of the interest tax shield (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the interest tax shield flow from Step 7 in your calculations. 56 57 9. In cell D25, by using cell references, calculate the project's levered value (1 pt.). 58 59 Note: Refer to the present values of the free cash flow and interest tax shield from Steps 5 and 8 in your calculations. 10. In cell D26, by using cell references, calculate the net present value using the APV method (1 pt.). 60 Note: Refer to the project's levered value from Step 10 in your calculations. 11. To calculate the WACC of the project using the answer from part (a), you will use the project-based WACC formula: r_wacc = r Unlevered - (Debt/Value) * Marginal tax rate *r Debt. In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). 61 62 63 64 12. In cell D31, by using cell references, calculate the WACC (1 pt.). 65 66 67 68 69 70 71 72 73 74 BR Note: Refer to the project's levered value from Step 10 in your calculations. 13. To verify that you get the same answer using the WACC method to calculate the NPV, first, calculate the project's levered value. In cell D35, by using cell references and the function PV, calculate the project's levered value (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the values of the free cash flow and WACC from Steps 4 and 12 in your calculations. 14. In cell D36, by using cell references, calculate the NPV (1 pt.). Note: Refer to the project's levered value from Step 13 in your calculations. 15. To show that the flow-to-equity approach also correctly gives the NPV of this investment opportunity, you need to calculate the free cash flow-to-equity after calculating the after-tax interest expense. In cell D40, by using cell references, calculate the after-tax interest expense (1 pt.). 16. In cell D41, by using cell references, calculate the free cash flow-to-equity (1 pt.). 17. To calculate the NPV of this investment opportunity, you need to calculate the present value of the free cash flow-to-equity. In cell D42, by using cell references and the function PV, calculate the NPV (1 pt.). Note: Refer to the value of the free cash flow-to-equity from Step 16 in your calculations. 18. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.