Help calculate optimal capital structure using excel Nominal cost of debt After tax % of cost of debt (re) Example used in the online lecture: % of debt D/E ratio Risk free rate 5% 0% 10% equity 100% 0 90% 11.1% market risk premium 6% currnt (unlevered) Beta 20% 80% tax rate 30% na na 7,00% 4.20% 25.0% 7.25% 4.35% 42.9% 8.00% 4.80% 60% 66.7% 8.75% 5.25% 50% 100.0% 9.75% 5.85% 150.0% 11.00% 6.60% 70% LT growth rate 40% 50% relever Beta CAPM 60% 40% Indicated value 1.00 40% 1% Bu x [1+ (1-T) (new D/E)] RMRP X B FCF + (WACC-8) Cost of equity Projected FCF WACC 1.00 11.0% 11.00% 100,000 1.07 11,4% 10.68% 100,000 1.15 11.9% 10.39% 100,000 1.26 12.5% 10.22% 100,000 1.40 13,4% 10.14% 100,000 1.60 14,6% 10.23% 100,000 1.90 16,4% 10.52% 100,000 Levered Beta (B) Firm value 909,091 936,330 962,464 978,474 986,193 977,995 950,570 Help calculate optimal capital structure using excel Nominal cost of debt After tax % of cost of debt (re) Example used in the online lecture: % of debt D/E ratio Risk free rate 5% 0% 10% equity 100% 0 90% 11.1% market risk premium 6% currnt (unlevered) Beta 20% 80% tax rate 30% na na 7,00% 4.20% 25.0% 7.25% 4.35% 42.9% 8.00% 4.80% 60% 66.7% 8.75% 5.25% 50% 100.0% 9.75% 5.85% 150.0% 11.00% 6.60% 70% LT growth rate 40% 50% relever Beta CAPM 60% 40% Indicated value 1.00 40% 1% Bu x [1+ (1-T) (new D/E)] RMRP X B FCF + (WACC-8) Cost of equity Projected FCF WACC 1.00 11.0% 11.00% 100,000 1.07 11,4% 10.68% 100,000 1.15 11.9% 10.39% 100,000 1.26 12.5% 10.22% 100,000 1.40 13,4% 10.14% 100,000 1.60 14,6% 10.23% 100,000 1.90 16,4% 10.52% 100,000 Levered Beta (B) Firm value 909,091 936,330 962,464 978,474 986,193 977,995 950,570