FIE 400 E – Investments

Notes:
? Turn in your solutions via Itslearning.
? Use Excel file template PS1_template – You should complete all grey cells in that file in order to answer all the questions in this assignment.
? Work in groups: 3-5 members
? Turn in one solution per group.
Data:
? Data is provided in file PS1_template, sheet “WRDS-raw data”
o The sheet has monthly returns for 20 U.S. companies + US market
o I recommend that you “copy” that data into sheet “Data”.
? Consider a constant monthly interest rate of 0.1%
Markowitz portfolio selection
Using the data provided, perform the following steps:
1. Compute monthly average returns, standard deviations and a covariance matrix. Draw a scatter plot comparing average returns with standard deviations. (Note: do not forget that you are estimating the standard deviations on a sample, not the population. The use of the wrong estimator can introduce a bias).
? Sheet “Inputs”
? Sheet “Plot Returns-Risk”
2. Determine the minimum-variance (MV) portfolio. To be specific, specify the expected return, standard deviation and Sharpe ratio of the MV portfolio. Also, report the weights of each stock within the MV portfolio. Note that you do not have formulas in the lecture notes to determine the weights of each stock in the case you have more than two securities. An easy way to solve this is to use Excel:
? Construct a table like the following:
Security
Expected Return
St. Deviation
Weights
Stock 1
xxx
yyy
zzz
Stock 2
xxx
yyy
zzz
…
xxx

FIE 400 E
yyy
zzz

FIE 400 E Assignement
? Give arbitrary weights to each stock, but remember that the sum of the weights has to be equal to 1.
? Given the arbitrary weights, determine the expected return, the variance and the Sharpe ratio of the portfolio. Note that the variance of the portfolio is given by:
????2=S????2????2+SS???????????????????????
Do not forget the covariance terms! – Youtube has some clips showing how to solve this problem:
? http://www.youtube.com/results?search_query=markowitz+portfolio+optimization+excel
? https://www.youtube.com/watch?v=tntOCGkgt98 – its relevance for the project is highly debatable
? Use Excel Solver (in Norwegian: problemløseren; you might have to go to Excel add-ins to install it). “Ask” Excel to obtain the minimum standard deviation of the portfolio by changing the weights of the stocks, under the restriction that the weights must sum to 1.
? Sheet “Min Variance Portfolio”
3. Plot the Efficient frontier without a risk-free rate. As discussed in class, start with the portfolio with lowest standard deviation, gradually increase the risk and compute the max Expected Return that can be attained for that level of risk.
? Sheet “Plot Efficient Frontier”
4. Determine the Tangent Portfolio not allowing for short-selling. Specify the expected return, standard deviation and Sharpe ratio of the Tangent portfolio. Also, report the weights of each stock within the portfolio.
? Sheet “Best Portfolio No Short Selling”
5. Determine the Tangent Portfolio allowing for short-selling, but the weight on a specific stock has to be between -20% and 20%. Specify the expected return, standard deviation and Sharpe ratio of the Tangent portfolio. Also, report the weights of each stock within the portfolio.
? Sheet “ Best Portfolio, <20%”
6. Determine the Tangent Portfolio allowing for short-selling. Specify the expected return, standard deviation and Sharpe ratio of the Tangent portfolio. Also, report the weights of each stock within the portfolio.
? Sheet “Best Portfolio”
7. Plot in the same graph the Capital Allocation Lines for each of the 3 Tangent Portfolios.
? Sheet “CALs”
8. Determine how two investor with risk aversion coefficients of 2 and 4 (consider that the utility function ??=??(??)-0,5????2 is appropriate) should allocate 1,000,000NOK among a risk-free asset and the 20 stocks used, under each of the 3 Tangent Portfolios.
? Sheet “Allocation”
Single Index Model
Using the data provided, perform the following steps:
1. For each stock provided, estimate firm-specific risk, alphas and betas. This is achieved by estimating the following regression for each stock:
????(??)-????(??)=????+????[????(??)-????(??)]+????(??)
? Sheet “SIM-AUX”
? Sheet “SIM-Inputs”
In Excel, you can easily do this by clicking Data – Data Analysis – Regression. If you do not see Data Analysis you have to:
? Click File and then click Options.
? Click Add-Ins, and then in the Manage box, select Excel Add-ins.
? Click Go.
? In the Add-Ins available box, select the Analysis ToolPack check box, and then click OK.
2. Using the 10-step recipe, determine:
? the weights of each stock within the active portfolio; the alpha of the active portfolio; and the residual variance of the active portfolio.
? the weight of the active portfolio and the market.
? the expected return, standard deviation and Sharpe ratio of the optimal portfolio.
? Sheet “SIM-Results”
3. Repeat the previous step, but instead of using the 10-step, maximize the Sharpe ratio using Excel solver (see slide 31, lecture 4).
? Sheet “SIM-Results”

FIE 400 E