Help calculate optimal capital structure using excel

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