Calculations for the Optimal Capital Structure (OCS) by using the Hamada equation. need to find the new unlevered beta and the new cost of equity from CAPM and then have to calculate the new WACC , provide excel sheet

 

Input Data
Tax Rate	16%	5-Year Effective Tax Rate
Debt	110,087,000,000	From Annual Report (Millions)
Number of Common Shares	15,943,425,000	From Annual Report
Market Price per share	152.64	From Market Watch Website
Total Market Value of Equity	2,433,604,392,000
Total Market Value	2,543,691,392,000
Debt Proportion	4.33%
Equity Proportion	95.67%
Cost of Capital
Beta (b)	1.293	Beta From 5 Years Data
Risk-Free Rate	3.88%	Data From 30-Year Treasury Data (Resource center, n.d.).
Market Risk Premium	5.60%	Data From the Statista.com website
Cost of Equity (CAPM) CAPM=Rf+Beta*Rm	11.12%
Cost of Equity (Dividend Growth Model)
2023 Dividends	0.92	From Yahoo Finance
Share Price(17th Feb 2023)	152.64	From Market Watch Website
Cost of Equity (Dividend Growth Model)
Dividends	0.92
Price	152.64
Growth	11.12%
Rate of Return	11.73%
Cost of Debt
Interest expense	2,800,000,000
Total Debt	110,087,000,000
Cost of Debt	2.54%
WACC	Proportion	Costs
Debt	4.33%	9.38%	0.41%
Equity	95.67%	2.54%	2.43%
WACC			2.84%

Hamada's Equation: bL = bU*[1 + (1 - T) *D/E] bU = bL/ [1 + (1 - T) *D/E] bU = 1.0777 bU is unlevered beta T is Tax Rate D/E is Debt to Equity Ratio bL is levered beta

Calculating CAPM using the new beta to compute the Cost of Equity, assuming same rates as in the previous WACC RE=RF+ (RMRF)=RF+ RPM

new RE= new beta = new WACC = (Cost of Equity * Weight of equity) + (Cost of debt * weight of debt) * (1-Tax Rate)