You are evaluating a project that requires an investment of $90 million today and provides a single cash flow of $115 million for sure one year from now. You decide to use 100% debt financing, that is, you will borrow $90 million. The risk-free rate is 5% and the tax rate is 40%. 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.

Need excel formulas in attachment

Problem 18-16 You are evaluating a project that requires an investment of $90 million today and provides a of $115 million for sure one year from now. You decide to use 100% debt financing, that is, $90 million. The risk-free rate is 5% and the tax rate is 40%. Assume that the investment is f the end of the year, so without leverage you would owe taxes on the difference between the and the investment. 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 opp Investment today (million) Amount of debt (million) Cash flow in 1 year (million) Tax rate Risk-free rate $90.00 $90.00 $115.00 40% 5.00% a. Calculate the NPV of this investment opportunity using the APV method. Tax basis at year end (million) Taxes owed (million) FCF at year end (million) VU (million) Interest expense (million) Interest tax shield (million) PV of ITS (million) VL (million) NPV (million) 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 (million) NPV (million) d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opp After-tax interest expense (million) FCFE (million) NPV (million) Requirements To calculate the project's NPV using the APV method, you need to calculate the unlev values of the project. 1. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 2. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 3. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt.). 4. To calculate the project's unlevered value, calculate the present value of the free cash function PV. In cell D21, by using the function PV and cell references, calculate the the free cash flow (1 pt.). 5. In cell D22, by using cell references, calculate the interest expense (1 pt.). 6. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 7. To calculate the present value of the interest tax shield use the function PV. In cell D2 function PV and cell references, calculate the present value of the interest tax shield ( 8. In cell D25, by using cell references, calculate the project's levered value (1 pt.). 9. In cell D26, by using cell references, calculate the net present value using the APV m 10. To calculate the WACC of the project using the answer from part (a), you will use the WACC formula: r_wacc = r_Unlevered - (Debt/Value) * Marginal tax rate * r_De In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). 11. In cell D31, by using cell references, calculate the WACC (1 pt.). 12. To verify that you get the same answer using the WACC method to calculate the NPV the project's levered value by using the function PV. In cell D35, by using the functio references, calculate the NPV (1 pt.). 13. In cell D36, by using cell references, calculate the NPV (1 pt.). 14. To show that the flow-to-equity approach also correctly gives the NPV of this investm you need to calculate the free cash flow-to-equity after calculating the after-tax intere D40, by using cell references, calculate the after-tax interest expense (1 pt.). 15. In cell D41, by using cell references, calculate the free cash flow-to-equity (1 pt.). 16. To calculate the NPV of this investment opportunity, you need to calculate the presen cash flow-to-equity using the function PV. In cell D42, by using the function PV and calculate the NPV (1 pt.). ion today and provides a single cash flow % debt financing, that is, you will borrow me that the investment is fully depreciated at e difference between the project cash flow APV method. oject. d to calculate NPV. V of this investment opportunity. APV method. oject. d to calculate NPV. V of this investment opportunity. eed to calculate the unlevered and levered at year end (1 pt.). d (1 pt.). flow at year end (1 pt.). ent value of the free cash flow using the references, calculate the present value of xpense (1 pt.). x shield (1 pt.). e function PV. In cell D24, by using the of the interest tax shield (1 pt.). evered value (1 pt.). t value using the APV method (1 pt.). part (a), you will use the project-based Marginal tax rate * r_Debt. alue ratio (1 pt.). pt.). hod to calculate the NPV, first, calculate D35, by using the function PV and cell t.). s the NPV of this investment opportunity, lating the after-tax interest expense. In cell expense (1 pt.). flow-to-equity (1 pt.). ed to calculate the present value of the free sing the function PV and cell references