Minimum Variance Portfolio and Tangent Portfolio Construction
FINC 3330 Pro ject 3 Seungho Baek Due: Wednesday, May 10, 2017, 06:30 P.M.
Consolidate all excel spreadsheets from part1 in a single Excel le. Please make a cover page (name and ID) in Sheet1 in your EXCEL le. To submit this pro ject electronically, use Blackboard. DO NOT send me it via email. Please submit your pro ject in a single EXCEL le. ONLY an EXCEL FILE will be accepted.
You have write up with your own words. Do not copy from others' work. In the last page, you have to show all your references for your pro ject in the last sheet in your Excel.
Part 1 Minimum Variance Portfolio and Tangent Portfolio Construction Instruction: You have been retained by the SHB Brooklyn investment, LLC to provide a quantitative analysis report to the CEO of the company, Seungho Baek. Today (on Jan. 04, 2016), you are asked to construct three types of portfolio (1) Equal-Weighted Portfolio; 2) Minimum Variance Portfolio; 3) Tangent Portfolio) and evaluate them. Three stock portfolios have the same constituents that are listed as below.
1. Stock Portfolio 1) Colfax Corp (Ticker: CFX) 2) Alleghency Technology, Inc. (Ticker: ATI) 3) Plantronics, Inc. (Ticker: PLT) 4) Ritchie Bros Auctioneers (Ticker: RBA) 5) Apple (Ticker: APPLE) 6) Microsoft Corporation (Ticker:MSFT) To evaluate these portfolios and nd optimal weights for the minimum portfolio risk or for maximum Sharpe ratio, you need to use historical stock price using Yahoo! Finance for your convenience. Download monthly stock prices for eight companies. The data period is between Jan. 2013 and December. 2016. Compute monthly stock returns (ignoring dividend), r i;t = ( P i;t P i;t 1 P i;t 1 ) where r i;t represents a simple return at time t for asset i, P i;t represents a stock price at time tfor asset i. 1 Divide monthly return data into two datasets, in-sample and out-of-sample. The in-sample-data set include return data from Feb. 2013 to Dec. 2015 whereas the out-of-sample data includes return data from Jan.2016 to Dec.2016.
Problem 1. Now you attempt to construct these portfolios. Using the in-sample-data, answer the below problems.
1) Compute average monthly returns and monthly standard deviations for each company with a series of return data from Feb. 2013 and Dec. 2015 . 2 2) Compute the annualized returns using the EAR formula and annual standard deviations 3 .
3) Generate a covariance matrix for the portfolio with the same period of data. 41 When to compute a log return, use an adjust close price.
2 In Excel, use AVERAGE for averages and STDEV.P for standard deviations 3 Use p 12 to compute an annualized standard deviation, which means Y ear = M onth p 12 from point 1 4 In Excel, you can get it easily using DATA ANALYSIS TOOLPAK.
1 4) Compute an equal weighted portfolio (EWP) return and a portfolio variance using the be- low equations. (Recommendation: Use MMULT in Excel.) First, the portfolio return is given by rp = n X i =1 w i r i (1) where r p refers to a portfolio return; w iis an weight assigned to company i; r i represent an average monthly stock return for company i. In this example, all the weights are 0.167, which means w 1 = w 2 = w 3 = w 4 = w 5 = w 6 = 0 :167. 5 Second, the portfolio variance is written as 2 p = n X i =1 w 2 i 2 i + 2 X 1
2 p = w w 0 (4) where represents a 6 by 6 matrix, 0 B B B B B B B B @ 11 12 13 14 15 16 21 22 23 24 25 26 31 32 33 34 35 36 41 42 43 44 45 46 51 52 53 54 55 56 61 62 63 64 65 66 1 C C C C C C C C A Problem 2. In order to get risk free rates, go and visit Economic Research Federal Reserve Bank of St.
Louis 8 and download monthly 3-Month Treasury Bill: Secondary Market Rate for the period between January 2011 and December 2016. Divide the sample into in-sample and out-of- sample. With in-sample data, please a time series graph and explain the trend of time series line over time.
Problem 3. Using the risk free rate in the in-sample data, compute monthly BEYs and plot the time series of BEYs. Also, provide the average and standard deviation of BEYs.
Problem 4. With the BEY on Dec. 2015, compute the Sharpe Ratios for individual stocks using annualized returns and standard deviations that you calculated from problem 1-2. 9 Explain which stock is outperforming and least performing among others. 5 You can easily recognize that this is a formula of arithmetic average.
6 To calculate optimal weights, use Solver in Excel.
7 If you would like to know how to compute portfolio returns and variances using Excel, see the attachment.
8 https :==research:stlouisf ed:org=f red 2=release ?rid = 18 9 S R =r ann;i rf BE Y ;Dec: 2015 ann;i where r ann;i represents an annualized return (EAR) for stock i, ann;i represents an annual- ized standard deviation for stock i, and rf BE Y ;Dec: 2015represents a BEY on Dec. 2015.
2 Problem 5.
In problem 1, you have constructed the equal weighted portfolios. Now let us use optimal weights that create a minimum variance portfolio (MVP) using the formula as expressed in below.
@ 2 p @ w i= 0 (5) such that w i> 0 and P 4 i =1 w i= 1. Solve the equation to get an optimal weight matrix denoted by b w = ( ^ w 1; ^ w 2; ^ w 3; ^ w 4).
That is, Minimizewi 2 p Sub ject to 0 < w i< 1; i = 1 ; : : : ; 6:
6 X i =1 w i= 1 :
Compute each portfolio return and variance using this optimized weights and compare them.
Note. The portfolio return based on those optimized weights is obtained by the formula as ^ r p = n X i =1 ^ w i r i (6) or ^ r p = ^wR 0 (7) The portfolio risk is computed by ^ 2 p = n X i =1 ^ w 2 i 2 i + 2 X 1
@ S R @ w i= 0 (10) such that w i> 0 and P 4 i =1 w i= 1. Solve the equation to get an optimal weight matrix denoted by b w = ( ^ w 1; ^ w 2; ^ w 3; ^ w 4).
That is, Maximizewi S R =E (r p ) rf 2 p Sub ject to 0 < w i< 1; i = 1 ; : : : ; 6:
6 X i =1 w i= 1 :
3 Compute each portfolio return and variance using this optimized weights and compare them.
Problem 7. Compare three portfolio returns (EWP, MVP, TP) and variances and explain which portfolio construction method is the most attractive.
Part 2 Backtesting Instruction: Your CEO, Seungho Baek, would like to have you check whether the best performing port- folio approach really work in out-of-sample period. To handle his request, you are now considering to run backtesting in order to compare all the construction method and evaluate them.
Problem 1. Compute respective monthly portfolio returns (portfolio returns from equal weights, optimal weights for MVP, optimal weights for TP) using the out-of-sample data. To compute individual monthly portfolio returns, use the below equation.
^ r p;t = n X i =1 ^ w ir i;t (11) where ^ r p;t is a portfolio return at time t, r i;t is a stock return for stock iat time t.
Problem 2. Compute each average portfolio return and standard deviation using the out-of-sample date.
Problem 3. With the BEY on Dec. 2016, compute the Sharpe Ratios for individual stocks using annualized returns and standard deviations that you calculated from problem 2. 10 Problem 4. Examine whether your chosen portfolio with the highest Sharpe ratio from the in-sample period is outperforming in the out-of-sample period as well.
Problem 5. Plot the cumulative portfolio returns for the respective portfolios in a gure and describe the gure.
Problem 6. Provide your ndings and suggest your opinion about portfolio strategy for your SHB Brook- lyn,LLC. (Note. You must show evidence about your suggestion to make your CEO under- stand.) 10 S R =r ann;i rf BE Y ;Dec: 2016 ann;i where r ann;i represents an annualized return (EAR) for stock i, ann;i represents an annual- ized standard deviation for stock i, and rf BE Y ;Dec: 2016represents a BEY on Dec. 2016.
4
