Instructions: In this assignment, follow the steps below and apply CAPM regression analysis (the Single Index Model) to examine stocks and give a conclusion if a stock is a good investment or not in the long run

Instructions:

In this assignment, follow the steps below and apply CAPM regression analysis (the Single Index Model) to examine stocks and give a conclusion if a stock is a good investment or not in the long run.

1. Pick three stocks (not including the one we discuss in lecture videos) you are interested in to analyze.

2. For each stock, apply the CAPM regression analysis in Excel:

?Collect historical price data from Yahoo! Finance for the stocks, for market return, and for risk-free return.

?You can examine the data of the stocks you pick in the past 6 months, 1 year, 2 years, 5 years...any period you like. Make sure your conclusion is consistent with your testing period.

?Use daily rate of return for analysis.

?Construct appropriate X and Y series.

?Do the CAPM regression using a scatter chart. Include the trendline and regression equation in the chart.

3. Write a brief report in a word file. Include the three charts you generate in Excel. Identify the Jensen’s alpha and historical beta for each stock. You do not need to calculate the future beta. Reach a conclusion for each stock based on your testing period, alpha and beta information, and briefly explain your conclusion:

?Is this stock a good investment?

?Why is it a good/bad investment?

?What is your strategy, long or short?

4. Submit both Excel file (with your detailed modeling) and Word file (with your conclusion/explanation for each stock.

I attached the lecture videos to help you, in the below link:

https://we.tl/t-iUkpKAGZTG

FIN4453 Financial Modeling and Analytics Xiaomin Guo, Ph.D., CFP® Assignment 3 (5 points) Instructions: In this assignment, follow the steps below and apply CAPM regression analysis (the Single Index Model) to examine stocks and give a conclusion if a stock is a good investment or not in the long run. 1. Pick three stocks (not including the one we discuss in lecture videos) you are interested in to analyze. 2. For each stock, apply the CAPM regression analysis in Excel: • • • • • Collect historical price data from Yahoo! Finance for the stocks, for market return, and for risk-free return. You can examine the data of the stocks you pick in the past 6 months, 1 year, 2 years, 5 years…any period you like. Make sure your conclusion is consistent with your testing period. Use daily rate of return for analysis. Construct appropriate X and Y series. Do the CAPM regression using a scatter chart. Include the trendline and regression equation in the chart. 3. Write a brief report in a word file. Include the three charts you generate in Excel. Identify the Jensen’s alpha and historical beta for each stock. You do not need to calculate the future beta. Reach a conclusion for each stock based on your testing period, alpha and beta information, and briefly explain your conclusion: • • • Is this stock a good investment? Why is it a good/bad investment? What is your strategy, long or short? 4. Submit both Excel file (with your detailed modeling) and Word file (with your conclusion/explanation for each stock) through Canvas by deadline. 1