Cost of Capital Calculations Questions

Cost of Capital Calculations This is a group assignment! Please hand-in a single hardcopy of your completed analysis in class on Tuesday, April 15, 2003. Please record the names of all your group members on the first page of the assignment. Be prepared to discuss your solutions in class! In this assignment, you must estimate the cost of capital for the stock that your team has picked for your Analyst Project this semester. You should follow the example approach we covered in class. Recommended Steps: 1) Download the Excel Spreadsheet template from Class 13 (Class 13 - Risk Factors Spreadsheet) which contains monthly data from January 1990 to December 2002 on the following factors: x x x x (RM-Rf) - The monthly excess return on the market portfolio. Rsmb - The monthly "size" return on a portfolio that goes long in a group of small stocks and short in a group of large stocks. Rhml - The monthly "distress" return on a portfolio that goes long in a group of high B/M stocks (high financial distress) and short in a group of low B/M stocks (low financial distress). Rf - The monthly return to an investment in short term U.S. T-bills. 2) Go to the Wharton WRDS database and click on "Members Login". Then, login using the class username password. 3) Click on the CRSP link. Then click on the Stocks link on the left side of the webpage. This will link you to a page titled "CRSP Monthly Stock". 4) On the "CRSP Monthly Stock" page, you should proceed with the following: - Step One: Date Range Select monthly frequency and beginning date of Jan 1990 and ending date of Dec 2002. - Step Two: Search Search by ticker and select method 1 (company codes). Enter your company's ticker symbol (ie MSFT for Microsoft) on the company code line. - Step Three: Variables Select Holding Period Return. - Step Four: Output Select "comma-delimited text (*.csv)" under Output Format. - CLICK on the "Submit Request" button. 5) The data will take about 30 second to download. The following screen will then appear: Data Request Summary Data Request ID 081028073 Library/Data Set crsp/msf Frequency/Date Range mon/Jan 1990 - Dec 2002 Search Variable TICKER Input Codes 1 item(s) MSFT Conditional Statements n/a Output format/Compression csv/ Variables Selected RET Estimated output size n/a Your output is complete. Save the output in an Excel File. 6) Copy that stock return data (and matching date) for your stock from the spreadsheet to a blank column in the Excel Spreadsheet template listed in the first step (above). 7) Ensure that the dates of the monthly stock prices match the dates of the stock returns in the template spreadsheet! 8) Adjust the monthly stock return for each month for YOUR stock between January 1990 and December 2002 by multplying the value by 100: x Adjusted Monthly Stock Return = Monthly Stock Return * 100 9) Now you are ready to calculate the the CAPM BETA and the THREE-FACTOR BETAS for your company! Please answer the following 5 parts in your analysis: QUESTION A) Using standard regression analysis in Microsoft Excel, estimate the CAPM BETA for your company using monthly stock returns for the most recent 60 months (5 years). Attach the regression output for this estimate. Note: Your regression model should be of the form: Rstock = a0 + a1*Rf + BETA*(Rm-Rf) QUESTION B) Using standard regression analysis in Microsoft Excel, estimate the CAPM BETA for your company using monthly stock returns for the past 24 months (2 years). Attach the regression output for this estimate. QUESTION C) Calculate the predicted cost of capital using the BETA estimates from PART A) and B). Show your calculations. Is there a large difference using the 5 year (60 month) versus the 2 year (24 month) BETA? QUESTION D) Using standard regression analysis in Microsoft Excel, estimate the 3-factor BETA's for your company using monthly stock returns for the most recent 60 months (5 years). Attach the regression output for this estimate. Note: Your regression model should be of the form: Rstock = a0 + a1*Rf + BETA1*(Rm-Rf) + BETA2*Rsmb + BETA3*Rhml QUESTION E) Discuss each of the coefficient estimates that you estimated in QUESTION D): - What does the value of a0 mean or imply? - What does the value of BETA1 mean? Is the firm risky relative to the market? - What does the value of BETA2 (size BETA) tell us about your stock? Think about whether your stock is large or small relative to other stocks traded in the U.S. - What does the value of BETA2 (distress BETA) tell us about your stock? Think about whether your stock has a high or low B/M ratio relative to other stocks traded in the U.S.