Using EXCEL, Please answer all questions!!!

1. (Set up) Go to Yahoo Finance and Find the historical stock prices of Lockheed Martin corporation(LMT), Netflix, Inc.(NFLX), Microsoft Corporation (MSFT) and Tesla, Inc.(TLSA). Download 5Y monthly return for each of them.

a) Estimate betas for all 4 stocks using ^IRX as risk-free return proxy and SPY as market portfolio proxy.

b) Estimate correlation between all 4 stocks and construct correlation matrix(Use Excel function "CORREL").

c) Estimate volatility(standard deviation of returns) for all 4 stocks.

d) Apply CAPM ( using betas from a) ) to get expected returns given market risk premium in https://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/ctryprem.html (Links to an external site.) and risk-free return as most recent ^IRX.

e) Scatter-plot ( volatility, expected return) pairs. Comment on them.

2. (Portfolio Weight) For above 4 stocks, compute portfolio characteristics.

a) Find portfolio volatility and expected return for equal weighted portfolio

b) Find portfolio volatility and expected return for market-capitalization portfolio

c) Find portfolio weights for minimum variance portfolio

d) Find portfolio weights for maximum expected return portfolio without allowing short sales

e) Find portfolio weights for maximum expected return portfolio without allowing short sales while keeping portfolio volatility no more than 15%

f) Find portfolio weights for maximum return/volatility ratio without allowing short sales while keeping portfolio volatility no more than 15%

g) Find portfolio weights for maximum utility without allowing short sales when your risk tolerance is 50%.

utility = expected return of portfolio - variance of portfolio/risk tolerance

h) Scatter-plot ( volatility, expected return) pairs of a)-g). Comment on them. Are they better than individual stock? Why?