FE445 Investment Analysis and Portfolio Management Project Assignment
Go to https://finance.yahoo.com and download the monthly closing stock prices of Tesla Motors, Inc. (Ticker: TSLA), Amazon.com, Inc. (AMZN) and the S&P 500 index (ˆGSPC) from June 1, 2011 to June 30, 2016. The way to do this is: search for the ticker symbols and then click on "Historical Data". Choose the period and frequency (monthly) of the data. Click "Download Data", and open in Excel. Make sure the prices are in chronological order. Use adjusted close price to calculate returns, otherwise you will miss the part of the holding period returns due to reinvesting dividends. You should calculate monthly percentage returns for 5 × 12 = 60 months from July 2011 to June 2016. E.g. the one month HPR for July 2011 is calculated as:
HPRJul2011 = (PAdg.Close,Jul2011 - PAdj.Close,Jun2011/PAdj.Close,Jun2011)
1) What is the arithmetic average excess return and standard deviation of the returns for each stock? Remember, you should use excess returns to calculate standard deviation. The risk-free rate (1 month T-bill) is available in an Excel file on QuestromTools under "Assignments". Note that the T-bill rate is given in the file as a monthly return.
Important notice: You should get 4.56% for Tesla, 2.44% for Amazon as the arithmetic mean excess return. Use these numbers as the expected excess return. If you get a different number, you probably have the wrong series and/or calculated the mean incorrectly. Only proceed with the rest of the exercise once you have matched the above two numbers.
2) What is the covariance and correlation of excess returns between Tesla and Amazon? From here on assume that the monthly return on the risk-free asset is the return in June 2016.
3) Calculate the portfolios consisting of Tesla and Amazon with weights on Tesla ranging from -100% to +200% in increments of 5%, i.e. w = -1, -0.95, -0.9, · · · , 1.9, 1.95, 2. You should have 61 portfolios. Calculate the expected return, expected excess return, and standard deviation (σ) for all of them. What is the expected return and standard deviation of the portfolio with 50% Tesla and 50% Amazon?
Use the portfolios calculated in Question 3 to answer Questions 4-7.
4) Using the standard deviation and expected return for each alternative investment portfolio, draw a graph with the investment opportunity set for the two stocks: plot the expected return (on the y-axis) as a function of the standard deviation (on the x-axis) for the above 61 portfolios using the "scatter" chart in Excel.
5) Which of the above portfolios is the minimum variance portfolio? What are the weights and what is the standard deviation? Just choose from the above portfolios, no need to find the exact minimum variance portfolio weights.
6) Calculate the reward to variability ratio for each portfolio. Which is the mean-variance efficient portfolio (optimal or tangency portfolio) among the 61 portfolios? In order to answer Questions 7-9, use the tangency portfolio identified in Question 6 as the risky portfolio.
7) Calculate the expected return and standard deviation for the complete portfolios using weights on the tangency portfolio ranging from 0% to 200% in increments of 5%. You should have 41 complete portfolios. Plot the CAL (Capital Allocation Line) and identify the slope.
In Questions 8 and 9, assume that you are investing $100,000.
8) Assume you want a monthly standard deviation of 10%, what is the most efficient way to achieve this if you are creating a portfolio with Tesla, Amazon and the risk-free asset? Calculate the portfolio weights exactly. Specify the dollar amounts invested in each asset.
9) Calculate the utility over the above 41 complete portfolios on the CAL for two investors with mean-variance utility, one with risk aversion of A=2 and another with A=4. Which of the above complete portfolios is their optimal portfolio allocation (in dollar terms)?
In Questions 10-12, use the S&P 500 index as the market return.
10) Graph a scatterplot of the 60 excess monthly stock returns vs. the excess market returns for both stocks separately. Add the regression line (trendline) to the plot. The x-axis should be the excess market return, the y axis the excess return on the given stock.
11) Run a regression to find the alpha and beta of Tesla and Amazon. How precise are your results? You can use "Data Analysis Regression", Add-In Tool in Excel. If you use Excel for MAC 2011 or Mac 2008, you may want to install third-party Data Analysis tools, such as http:
//www.analystsoft.com/en/products/statplusmacle/. You may use any statistics software, such as Matlab, SAS, R, etc.
12) Using the above estimate of beta what is the expected monthly return on the two stocks according to the CAPM (i.e. alpha zero) if you expect the market return (the S&P 500 index) to be 0.6% a month (approximately 7.2% per year)? Why are these different from the numbers you got in Question 1?
13) Using the expected return from Question 12, re-evaluate Question 6: what is your optimal risky portfolio now? Why do you think it changed the way it did? (Note: do not worry about getting low Sharpe ratios here, these are monthly values and need to be multiplied by SQRT(12) to get annualized values.)
Attachment:- Data.rar