Isom 5510 Data Analysis Group Project 14_3.docx

ISOM 5510 Data Analysis Group Project

Group number: 14

Hong Kong, November 21, 2018

Student #1 - Name: JI-YUNG YOO / ID: 20575673

Student #2 - Name: FAN MO / ID: 20538699

Student #3 - Name: Ameya Pandit/ ID: 20545379

Student #4 - Name: Yida Wang / ID: 20578053

Student #5 - Name: Marc Hanslin/ ID: 20541866

2|7

Report on Portfolio Construction I.

Strategy and Justification

A.

Portfolio is designed to optimize Sharpe Ratio As strategy for the selection and weighing of the stocks in our portfolio, we decided to use an efficient frontier approach and to look for the portfolio with the best Sharpe Ratio. The efficient frontier describes a set of portfolios that offers the highest expected returns for a certain level of risk or a minimum risk for a certain level of expected return. The Sharpe Ratio describes the excess return of a portfolio in relation to its risk, measured by its standard deviation.

B.

Sharpe Factor a Less Prejudiced Factor and Benchmarking Confirms Performance Our methodology measures how the stocks correlate or, in other words, how dependent their returns are on the same factors. The more independent they are, the larger is the effect of diversification. Since we only look at stocks from Hang Seng Index, we found a relatively high correlation. By selecting the portfolio with the highest Sharpe Ratio (and not with the lowest variance given a certain expected return or the highest expected return given a certain variance), we objectivized the selection of our portfolio and given us the best portfolio. Any alternative selection method would require to select the best portfolio for a specific investor, making assumptions either about a specific return an investor would expect or a specific risk an investor would be willing to accept. The benchmarking of the selected portfolio with the Hang Seng Index also showed a significant higher return over the last ten years (964% vs. 66.72%).

II.

Selected Portfolio is heavily diversified with a clear overweight of the best performing stocks 

Over 51% of the portfolio is allocated to the two best performing stocks (1109_HK and 0083_HK).



Another almost 30% is allocated to the 3 mid-performing stocks (0688_HK, 0388_HK and 0857_HK).



The remainder is allocated to the remaining five stocks.



No leverage was used for the portfolio and all funds have been invested in stocks (no cash).

Table 1

Average Monthly Return Weights 1044_HK 6.88% 0688_HK 9.21% 0883_HK 3.60% 0388_HK 10.11% 0857_HK 8.97% 1109_HK 26.08% 0386_HK 0.55% 0144_HK 6.38% 0083_HK 25.51% 0004_HK 2.73% Weighted Monthly Average Variation

1044_HK

0688_HK

0883_HK

0388_HK

0857_HK

1109_HK

0386_HK

0144_HK

0083_HK

0.85% 6.88% 0.85% 0.50% 0.26% 0.46% 0.30% 0.52% 0.37% 0.34% 0.43% 0.31%

1.61% 9.21% 0.50% 1.61% 0.48% 0.82% 0.65% 1.41% 0.63% 0.60% 0.78% 0.67%

0.96% 3.60% 0.26% 0.48% 0.96% 0.66% 0.72% 0.72% 0.57% 0.57% 0.57% 0.50%

1.52% 10.11% 0.46% 0.82% 0.66% 1.52% 0.75% 0.97% 0.69% 0.79% 1.03% 0.71%

1.21% 8.97% 0.30% 0.65% 0.72% 0.75% 1.21% 0.78% 0.89% 0.62% 0.64% 0.60%

2.13% 26.08% 0.52% 1.41% 0.72% 0.97% 0.78% 2.13% 0.83% 0.83% 0.77% 0.69%

1.15% 0.55% 0.37% 0.63% 0.57% 0.69% 0.89% 0.83% 1.15% 0.65% 0.58% 0.51%

0.99% 6.38% 0.34% 0.60% 0.57% 0.79% 0.62% 0.83% 0.65% 0.99% 0.73% 0.49%

1.68% 25.51% 0.43% 0.78% 0.57% 1.03% 0.64% 0.77% 0.58% 0.73% 1.68% 0.96%

0.03%

0.09%

0.02%

0.10%

0.06%

0.31%

0004_HK 1.02% 2.73% 0.31% 0.67% 0.50% 0.71% 0.60% 0.69% 0.51% 0.49% 0.96% 1.02%

0.00% 0.05% 0.25% 0.02% Weighted Average Monthly Portfolio Return

= 100%

= 0.93% 1.58%

Selected Portfolio (Return 19.00% / Risk 33.47%)

Table 2

Efficiency Frontier 20.00% 19.00% 18.00%

Return

17.00% 16.00% 15.00% 14.00% 13.00% 12.00% 19.00%

21.00%

23.00%

25.00%

27.00%

29.00%

31.00%

33.00%

35.00%

Risk

The graph in table 2 shows a number of theoretical portfolios in terms of their return and their risk based on the dataset used. The selected portfolio lies on the tangent from the risk-free rate (the 10year US Treasure Yield) and the efficient frontier of the possible portfolios. As shown in table 3 below, the Sharpe Ratio of the selected portfolio is 0.4472. Table 3

Excess Return RISK

Portfolio Hang Seng Index monthly Yearly Monthly Yearly 1.2% 14.97% 0.3% 3% 9.7% 33% 6.2% 22%

SHARPE

0.13

III.

Methodology

A.

Selection of stocks

0.45

0.05

0.16

We used the monthly returns of the selected stocks provided in the excel sheet “StockReturn.xlsx” of the last ten years and one month (i.e. July 31, 2001 to July 31, 2011) and added the monthly returns of the Hang Seng index for the same period. Understanding that usually a high level of diversification can be achieved with a portfolio of six to ten different stocks, we selected ten stocks based on the highest long-run multi-period gross return. 𝐿𝑜𝑛𝑔 − 𝑅𝑢𝑛 𝑀𝑢𝑙𝑡𝑖 − 𝑃𝑒𝑟𝑖𝑜𝑑 𝐺𝑟𝑜𝑠𝑠 𝑅𝑒𝑡𝑢𝑟𝑛 = 𝐸𝑥𝑝𝑒𝑐𝑡𝑒𝑑 𝑆𝑖𝑛𝑔𝑙𝑒 𝑃𝑒𝑟𝑖𝑜𝑑 𝐺𝑟𝑜𝑠𝑠 𝑅𝑒𝑡𝑢𝑟𝑛 −

𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒 2

5|7

B.

Weighing of stocks

1.

Covariance Table

In order to weigh the ten stocks we selected, we first made a covariance table with all our ten stocks using the following formula: 𝑛

𝑠𝑥𝑦

1 = ∑(𝑥𝑖 − 𝑥̅ )(𝑦𝑖 − 𝑦̅) 𝑛−1 𝑖=1

Then, we extended the covariance table with a weight-factor which reflects our stock selection. The sum of the weighted averages for the covariances of each stock multiplied by the weight of that stock gives us the variance for the whole portfolio since:

We calculated the return of the selected portfolio by multiplying the weighing-factor with the monthly average return for each stock that we had calculated initially when selecting the ten stocks for our portfolio. 𝐸(𝑍) = 𝑝𝐸(𝑋) + (1 − 𝑝)𝐸(𝑌) As we will see further below, we will use the average annual standard deviation of the returns of the portfolios in order to select the best portfolio. The covariance table, however, only gave us the monthly variance. In order to get the monthly standard deviation, we had to take the square root of the variance since: 𝑆𝑡𝑑𝐷𝑒𝑣 = √𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒 To translate the standard deviation of the monthly returns to the standard deviation of the annual returns, it has to be multiplied by √12. 2.

Production of a Set of Random Portfolios

Having now prepared the basic formulas for our model, we used Excel to produce 10001 random portfolios. Since we did not intend to use leverage and do intended to invest all our funds in stock, all weighing factors sum up to 100%.

1

The largest number supported by excel.

6|7

3.

Ranking of Portfolio by the Highest Sharpe Ratio

As a last step, we ranked the 1000 random portfolios in order of their Sharpe Ratio, beginning with the highest. The formula for the Sharpe Ratio is: 𝑆ℎ𝑎𝑟𝑝𝑒 𝑅𝑎𝑡𝑖𝑜 =

𝑅𝑥 − 𝑅𝑓 𝑆𝑡𝑑 𝐷𝑒𝑣 𝑅𝑥

Rx = Expected portfolio return Rf = Risk free rate of return StdDev Rx = Standard deviation of portfolio return / volatility The portfolio with the highest Sharpe Ratio has to be considered the best under our strategy, which was in our case 0.4472. A Sharpe Ratio of 1 or higher is considered as acceptable to good, a Sharpe Ratio of 2 or higher is very good.2

IV.

Evaluation and Benchmarking We use Hang Seng Index as the benchmark to evaluate the performance of our selected portfolio. Back testing analysis is conducted to assess the viability of a portfolio by discovering how it would play out with historical data. We use ten-years monthly return rate data of HS Index from 07/31/2001 to 07/31/2011 and calculate the monthly return rate for our portfolio using the formula: 𝑛

𝑅𝑝𝑜𝑟𝑓𝑜𝑙𝑖𝑜 = ∑ 𝑅𝑖 × 𝑊𝑖 𝑖=0

Ri = expected monthly return of Stock i Wi = weight of stock i in the portfolio Assuming that equally invest $100 million in our portfolio and HS index, we got the back testing result as shown in table 4. By calculating the rate of return and CAGR using the formula: Rate of Return =

𝐸𝑛𝑑𝑖𝑛𝑔 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑖𝑛𝑣𝑒𝑠𝑡𝑚𝑒𝑛𝑡 − 𝐵𝑒𝑔𝑖𝑛𝑛𝑖𝑛𝑔 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑖𝑛𝑣𝑒𝑠𝑡𝑚𝑒𝑛𝑡 % 𝐵𝑒𝑔𝑖𝑛𝑛𝑖𝑛𝑔 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑖𝑛𝑣𝑒𝑠𝑡𝑚𝑒𝑛𝑡 CAGR = (

1 𝐸𝑛𝑑𝑖𝑛𝑔 𝑣𝑎𝑙𝑢𝑒 )# 𝑜𝑓 𝑦𝑒𝑎𝑟𝑠 − 1 𝐵𝑒𝑔𝑖𝑛𝑛𝑖𝑛 𝑣𝑎𝑙𝑢𝑒

The rate of return of our portfolio is 964%, and CAGR is 26.7%, comparing to HS index is 66.72% and 5.24% respectively.

2

Using solver to find the portfolio with the highest possible Sharpe Ratio, we know that a portfolio of a Sharpe Ratio of 0.4716 (average annual return = 18.76% and average standard deviation = 40%), if roughly 93% of the funds would have been allocated to two stocks only.

07/31/2001 11/30/2001 03/31/2002 07/31/2002 11/30/2002 03/31/2003 07/31/2003 11/30/2003 03/31/2004 07/31/2004 11/30/2004 03/31/2005 07/31/2005 11/30/2005 03/31/2006 07/31/2006 11/30/2006 03/31/2007 07/31/2007 11/30/2007 03/31/2008 07/31/2008 11/30/2008 03/31/2009 07/31/2009 11/30/2009 03/31/2010 07/31/2010 11/30/2010 03/31/2011 07/31/2011

million$

7|7

Though simple rate of return and CAGR don’t take into account the effect of the time value of money and inflation, we still can come out a conclusion that our portfolio is more attractive and profitable than HS index.

Table4

Portfolio Back Testing

1400.00

1200.00

1000.00

800.00

600.00

400.00

200.00

0.00

HS Index Portfolio