Beta Analysis

Beta Analysis

This project takes you through the basic calculation of beta using Excel in order to help demystify a company beta. You will mostly use the knowledge in Chapter 11 and 12. Along the way you will find how to download share price and index data which can then be manipulated in Excel. Finally, you will tie the concept of beta and the CAPM to stock valuation. You can get the data from the Yahoo Finance site: http://finance.yahoo.com. Once you arrive at this site you will find a search box in which you need to type in the stock symbol (if you are unsure of the stock symbol, just use the company name). To download financial data, you can follow the detailed instructions in the text box below:

1. First download into Excel the monthly data of two blue-chip stocks (you need to show the reasons why you choose these two stocks) and S&P 500 (^GSPC) for the five-year period from the Nov 1, 2014 to Nov 1, 2019 using the instructions outlined above. Use the adjusted closing price data (Adj Close) to calculate the monthly returns for two stocks and the S&P 500 index. You should have 60 monthly returns for each stock. The basic formula for calculating each month’s return is (current or latest month’s adj. close – previous or last month’s adj. close)/previous or last month’s adj. close. (Helpful Hint: I strongly urge creating a table of the adjusted closing prices for the two stocks and the S&P 500 on a separate worksheet and then do your calculations on this “fresh” worksheet.)
2. What are the average monthly returns for the two stocks and the S&P 500 index over this five-year period? What is the annualized average return for the two stocks and the S&P 500? Please convert your answers to percentages. (Hint: translate the monthly average return into an Annual Rate by multiplying 12.)
3. Calculate the standard deviation of monthly returns for the two stocks and the S&P 500 using Excel. What is the annualized standard deviation for the two stocks and the S&P 500 (Hint: convert the monthly standard deviation into annual standard deviation by multiplying the square root of 12, i.e., 3.4641.)? Among the two stocks and the S&P 500, which one has the lowest total risk? What is the 95% confidence interval of future returns for these two stocks using the annualized average return and annualized standard deviation?
4. Now calculate beta for the two stocks using Excel’s Slope function (=SLOPE(:,:)) to estimate the slope of the linear regression line, which is beta. The S&P 500 index is our market portfolio. Which one has the lowest systematic risk between the two stocks?
5. Now construct a portfolio that invest the same amount on each of the two stocks (i.e., the weight of each stock in the portfolio is 1/2). Calculate the annualized average return, annualized standard deviation, and beta of your portfolio. Any changes on the risk? What is the 95% confidence interval of future returns of the portfolio using the annualized average return and annualized standard deviation?
6. Now use the CAPM Equation to estimate the expected returns of each of the two stocks and the portfolio. Use 11.5% as the required annual market return and the average 3-month T-bill rate of 3.8% as the annual risk-free rate.
7. Compare your values in #6 with the stocks’ and portfolio annualized average returns. Which one is underpriced and which one is overpriced? What will you do for the two stocks and the portfolio? Explain your answer.

This assignment should include a full report (3-4 pages). The report should be typed in Word, double-spaced. You should also attach the Excel worksheets of the data analysis information.