eatimate the most current weighted cost of capital for Harley Davidson. please help me complete this problem in excel and please show formulas. thank you!!

Transcribed Image Text:

Problem 2: Estimate the most current Weighted Average Cost of Capital of Harley Davidson Inc (ticker: HOG; CUSIP: 412822). Use CAPM to estimate the cost of equity. Assume that the capital structure (the market debt-to-equity ratio) observed as of the most recent annual financial statements remains in place forever for the company. Also assume that the market risk premium is 5% and that the marginal tax rate is 21%. Recipe: a) Calculate the cost of debt using most recent yields from a representative set of Harley Davidson public bonds. b) Calculate the market value of Harley Davidson debt using book value of debt adjusted by the price to face value multiple of a set of public bonds of Harley Davidson. c) The marginal tax rate is provided. d) Calculate the market value of equity using number of shares outstanding from the most recent financial report and historical stock price as of that date from NYSE, NASDAQ or Yahoo. e) Calculate the cost of equity using CAPM. For that, you are provided the market risk premium. Use current yield on the 10-year Treasury bond as the risk-free rate, and calculate the beta of Harley Davidson stock using 5 years (that is, 60 months) of monthly stock returns and value- weighted (VW) CRSP portfolio as the market portfolio. Data on stock returns are provided in the Excel file obtained from the CRSP database (column G contains the monthly returns of Harley Davidson stock, column I contains monthly returns of the value-weighted CRSP portfolio of stocks, column K contains monthly returns of the S&P 500 portfolio of stocks). f) Combine it all to calculate Weighted Average Cost of Capital (WACC) for Harley Davidson. Suggested sources of the data: 1. SEC's Edgar database-for financial statements and annual or quarterly report (10-K or 10-Q) 2. Stock returns from CRSP database-to calculate the beta. 3. FINRA's TRACE database-for public bond yields and prices 4. US Treasury's web-site-for T-bond rates 5. Yahoo's finance web-site, NYSE.com or NASDAQ.com-for the stock price around the date of the most recent financial report. To get to Problem 2: Estimate the most current Weighted Average Cost of Capital of Harley Davidson Inc (ticker: HOG; CUSIP: 412822). Use CAPM to estimate the cost of equity. Assume that the capital structure (the market debt-to-equity ratio) observed as of the most recent annual financial statements remains in place forever for the company. Also assume that the market risk premium is 5% and that the marginal tax rate is 21%. Recipe: a) Calculate the cost of debt using most recent yields from a representative set of Harley Davidson public bonds. b) Calculate the market value of Harley Davidson debt using book value of debt adjusted by the price to face value multiple of a set of public bonds of Harley Davidson. c) The marginal tax rate is provided. d) Calculate the market value of equity using number of shares outstanding from the most recent financial report and historical stock price as of that date from NYSE, NASDAQ or Yahoo. e) Calculate the cost of equity using CAPM. For that, you are provided the market risk premium. Use current yield on the 10-year Treasury bond as the risk-free rate, and calculate the beta of Harley Davidson stock using 5 years (that is, 60 months) of monthly stock returns and value- weighted (VW) CRSP portfolio as the market portfolio. Data on stock returns are provided in the Excel file obtained from the CRSP database (column G contains the monthly returns of Harley Davidson stock, column I contains monthly returns of the value-weighted CRSP portfolio of stocks, column K contains monthly returns of the S&P 500 portfolio of stocks). f) Combine it all to calculate Weighted Average Cost of Capital (WACC) for Harley Davidson. Suggested sources of the data: 1. SEC's Edgar database-for financial statements and annual or quarterly report (10-K or 10-Q) 2. Stock returns from CRSP database-to calculate the beta. 3. FINRA's TRACE database-for public bond yields and prices 4. US Treasury's web-site-for T-bond rates 5. Yahoo's finance web-site, NYSE.com or NASDAQ.com-for the stock price around the date of the most recent financial report. To get to