Investment Planning: Asset Relocation 2

Let us take the 5 stocks as example for two methods of asset relocation.

First method:
The first method was shown in the previous blog titled "Investment Planning: Asset Relocation".
Scenario:
10-month-performances for five stocks were tabulated as a study case.
Stock i average return = AVERAGE(array stock i)
Stock i standard deviation = STDEV(array stock i)

Risk free rate = 2.5%/12 = 0.21% per month

Desired return = 2.5% per month

Sharpe Ratio = (Average Return - Risk Free)/Standard Deviation

Sharpe Ratio in lowest to highest order:
GenM,Haio,NTPM,Pbbank,LPI

Excel Solver Constraints:
Constraint#1: optimized weightage <=1 Constraint#2: desired return per month = 2.5% Constraint#3: sum of optimized weightage = 1 Current invested or value weight:
GenM : HaiO : NTPM : PbBank : LPI = 0.21 : 17.2 : 25.6 : 51.9 : 3.2

Excel Solver optimized weightage:
GenM : HaiO : NTPM : PbBank : LPI = 0.58 : 19.56 : 15.44 : 33.02 : 31.4

Reference of Relocation from Method 1 optimization:
Strategy 1: reduce GenM weightage or sell off all
Strategy 2: reduce high weightage of Pbbank and NTPM
Strategy 3: increase LPI weightage

Action recommended:
1. Stop buying GenM, Pbbank and NTPM. Extra cash is used to buy HaiO and LPI. Second Method:
This is the second method which takes into account of standard deviation.
Variance stock i = VARP(Array stock i)
Covariance stock i to j = COV(Array stock i, Array stock j)

Variance Term stock i = Stock i weightage*SUMPRODUCT(Array weightage, Array COV or/and VARP)

Portfolio variance = Summation of all variance term stock
Portfolio standard deviation = SQRT(profolio variance)

Excel Solver constraint:
Constraint#1: Optimized weightage >=0
Constraint#2: desired retrurn = 2.5% per month
Constraint#3: sum of optimized weightage = 1





Current invested or value weight:
GenM : HaiO : NTPM : PbBank : LPI = 0.21 : 17.2 : 25.6 : 51.9 : 3.2
Current portfolio return: 2.05%
Current portfolio standard deviation: 4.13%
Current portfolio Sharpe ratio = (2.05-0.21)/4.13 = 0.4455

Excel Solver optimized weightage:
GenM : HaiO : NTPM : PbBank : LPI = 7.39 : 3.1 : 15.35 : 31.78 : 42.38
Optimized portfolio return: 2.5%
Optimized portfolio standard deviation: 2.03%
Optimized portfolio Sharpe ratio = (2.5-0.21)/2.03 = 1.128

Conclusion:
Method 2 has a big different perspective in relating risk and return relationship. Our common sense that applied at Method 1 is beaten down by the statistical approaches.

Method 1: GenM(hold), HaiO(increase), NTPM(decrease), PbBank(decrease), LPI(increase)
Method 2: GenM(increase), HaiO(decrease). NTPM(decrease), PbBank(decrease), LPI(increase)

Thanks to the Markowitz,s CAPM for a better understanding on the risk and return relationship.