must show work in excel

Ch 18 - Capital Budgeting Case Data Given: Debt/Equity of project T = Levered equity Debt Issue $ IR = PMT 40% ; debt amt = 40% of 18.5m 28% 15% 9.57 m retiring at the end of year 3. 8% $3.19 m YEAR 0 1 2 3 UCF -18.5 m 5.79 m 9.59 m 8.89 m Calculate Value of the Project. What would happen if there is a flotation cost? Hints: 1. APV = NPV (UCF) at Ro + NPV (Financing at Ro) 2. RS = RO +(B/S)[RO - RB)(1 - TC) 3. NPV (debt financing) - Proceeds - Aftertax PV(Interest Payments) PV(Principal Payments) 4. Each year, an equal principal payment will be made, which will reduce the interest accrued during the year. 5. Given a known level (AMT) of debt, debt cash flows should be discounted at the pretax cost of debt, 6. Use APV if the project's level of debt is known over the life of the project. Use WACC or FTE if project uses firm's target D/V ratio over project's life. Use ?? because the level of debt for the project is known throughout the life of the project APV = NPV (UCF) at Ro + NPV (Financing at RB) 2. Find PV (debt financing) NPV (debt financing) = Proceeds - Aftertax PV (Interest Payments) - PV(Principal Payments) - Interest PMT=8% of loan balance PV at? a. Aftertax PV(Interest Payments) YEAR AT=(1-T) 0 1 2 3 a $ $ $ PV of Aftertax PV(Interest Payments) Repayment of Principal PV at? b. PV(Principal Payments) YEAR 0 1 2 3 PV(Principal Payments) C. NPV (debt financing) = Proceeds - Aftertax PV(Interest Payments) - PV(Principal Payments) NPV (debt financing) - $9. m Ch 18 - Capital Budgeting Case Data Given: Debt/Equity of project T = Levered equity Debt Issue $ IR = PMT 40% ; debt amt = 40% of 18.5m 28% 15% 9.57 m retiring at the end of year 3. 8% $3.19 m YEAR 0 1 2 3 UCF -18.5 m 5.79 m 9.59 m 8.89 m Calculate Value of the Project. What would happen if there is a flotation cost? Hints: 1. APV = NPV (UCF) at Ro + NPV (Financing at Ro) 2. RS = RO +(B/S)[RO - RB)(1 - TC) 3. NPV (debt financing) - Proceeds - Aftertax PV(Interest Payments) PV(Principal Payments) 4. Each year, an equal principal payment will be made, which will reduce the interest accrued during the year. 5. Given a known level (AMT) of debt, debt cash flows should be discounted at the pretax cost of debt, 6. Use APV if the project's level of debt is known over the life of the project. Use WACC or FTE if project uses firm's target D/V ratio over project's life. Use ?? because the level of debt for the project is known throughout the life of the project APV = NPV (UCF) at Ro + NPV (Financing at RB) 2. Find PV (debt financing) NPV (debt financing) = Proceeds - Aftertax PV (Interest Payments) - PV(Principal Payments) - Interest PMT=8% of loan balance PV at? a. Aftertax PV(Interest Payments) YEAR AT=(1-T) 0 1 2 3 a $ $ $ PV of Aftertax PV(Interest Payments) Repayment of Principal PV at? b. PV(Principal Payments) YEAR 0 1 2 3 PV(Principal Payments) C. NPV (debt financing) = Proceeds - Aftertax PV(Interest Payments) - PV(Principal Payments) NPV (debt financing) - $9. m