Finance homework

FE445 Fall 2016 Project 1 Yunjeen Kim

Boston University Questrom School of Business

FE445 Investment Analysis and Portfolio Management

Fall 2016

Project Assignment 1

• This assignment is due at 11:59 pm on Friday, October 14. Please submit electronically under “Assignments” on QuestromTools. Late submissions are subject to penalties.

• Please clearly indicate who is in the group. It is enough if one person from the group uploads the solution of the project on QuestromTools. You may not share any part of your Excel file

with other groups.

• Use at least two worksheets in a single Excel file: the first worksheet should summarize your responses (including graphs), the other worksheet(s) should include lengthy data and

additional calculations.

• Reference all cells, never copy-paste any numbers. Please do all calculations in Excel using functions (do not use a calculator). Ideally, if any data is changed in the file, all results should

automatically recalculate, though this will not work for the e.g. the Regression Add-In Tool.

Make sure you save the file in “.xls” or “.xlsx” format otherwise you might lose your formulas

and graphs!

• Please answer the questions briefly but clearly.

• The assignment is worth 65 points in total (5 points per question).

Go to http://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

1

FE445 Fall 2016 Project 1 Yunjeen Kim

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.

2

FE445 Fall 2016 Project 1 Yunjeen Kim

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

3