Reference no: EM133493642

Assignment: Security Analysis Project- Financial Management Complete Financial Paper

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. 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:

Instructions for Downloading Stock Data

1. In the box type in the stock symbol (for example General Electric's is GE and the S&P 500 index's is ^GSPC).

2. Once you find the stock, choose Historical Data. Click on monthly button as the data Frequency and enter the date range as instructed below.

3. The first page of data should now be visible on the screen. Use the Download Data link to download data and save the data to a file.

4. Open Excel and then open the file you saved.

5. At this point you should have seven columns including: Date, Open, High, Low, Close, Adj Close, and Volume for each month.

6. If you can't download S&P 500 index directly, copy and paste the data in the Excel file.

Task

Question I. First download into Excel the monthly data of two 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. When you create the new worksheet, make sure all dates are matched)

Question II. 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? Convert your answers to percentages. (Hint: translate the monthly average return into an Annual Rate by multiplying 12.)

Question III. 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?

Question IV. 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?

Question V. 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?

Question VI. 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.

Question VII. 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 (buy or sell)? Explain your answer.