lOMoARcPSD|18409049
Optimal portfolio
Financial Investment (Trường Đại học Kinh tế Thành phố Hồ Chí Minh)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
UEH UNIVERSITY
SCHOOL OF FINANCE
--------
FINANCIAL INVESTMENT SUBJECT
Lecturer of the subject : Trần Thị Hải Lý
Course name : Financial Investment
Subject code : 22C1FIN50504703
Members in group 3 : Phạm Quế Hân 31211025581
Lê Minh Thiện 31201027068
Nguyễn Thị Thu Thủy 31211023034
Lê Phúc Vinh 31211025702
Class : FNC03
Ho Chi Minh City, 11/2022
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
Table of % contribution of team members
Members % contribution
Phạm Quế Hân
Lê Minh Thiện
Nguyễn Thị Thu Thủy
Lê Phúc Vinh
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
CONTENT OF THE REPORT
CHAPTER 1: INTRODUCTION...........................................................................................2
1.1. Objective research:...................................................................................................2
1.2. Data:......................................................................................................................... 2
CHAPTER 2: INPUT CALCULATIONS...............................................................................3
2.1. Risk free rate:................................................................................................................3
2.2. Calculate return and portfolio standard deviation:.........................................................3
CHAPTER 3: RESULTS........................................................................................................5
3.1. Case of not allowing short selling:.................................................................................5
3.2. The case for short selling:..............................................................................................8
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
CHAPTER 1: INTRODUCTION
1.1. Objective research:
Which 5 stocks on HOSE, we can build an optimal risky portfolio, after that we
will show that the best attainable capital allocation line emerges when security
selection is introduced.
1.2. Data:
Using historical price data of 5 stocks NKG, SSI, BID, DIG, VCB from Octorber 1, 2019
to Octorber, 2022. The above data is taken from web cophieu68. The data on the risk-free
asset chosen by the team is a 10-year government bond. The yield on a 10-year
government bond is the risk-free return. Taken from the page:
1. [Link]
id=NKG&fbclid=IwAR1Im9N9OngowwJ0JQtGNWeZlANfStlRzsQSRkA8qQlI
_67lbJMUgaHD7TM
2. [Link]
dDocName=MOFUCM210828&_adf.ctrl-
state=olh9t3pt5_4&_afrLoop=9756575994790112#%40%3F_afrLoop
%3D9756575994790112%26dDocName%3DMOFUCM210828%26_adf.ctrl-
state%3Dzym902sze_4
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
CHAPTER 2: INPUT CALCULATIONS
2.1. Risk free rate:
With the data taken, the value of Rf = 3.00%
2.2 Calculate return and portfolio standard deviation:
2.1.1. TSSL:
With historical price data, the team calculates daily profit using the formula:
P t − P t −1
Rt =
Pt −1
Since we're going to be doing the calculation in terms of years, the team calculates
the annual return of each security by averaging the daily return by 250, assuming a year
of 250 trading days.
NKG SSI BID DIG VCB
Expected Return (daily) 0.21% 0.09% 0.04% 0.13% 0.04%
Expected Return (year)
51.59% 23.53% 10.91% 33.63% 8.92%
The return on the portfolio will be the weighted average of each security in
the portfolio. In this article, the author uses the SUMPRODUCT function to
calculate.
2.1.2. Standard deviation:
To calculate the portfolio standard deviation, we will first construct the
Variance Covariance Matrix table:
Each value in the table is calculated using the COVARIANCE.S command.
Covariance Matrix
Stock NKG SSI BID DIG VCB
NKG 0.001037 0.00045 0.00033 0.000401 0.00014
SSI 0.0004493 0.00084 0.00041 0.000438 0.00021
BID 0.000327 0.00041 0.00056 0.00028 0.00024
DIG 0.00040 0.00044 0.00028 0.001188 0.00014
VCB 0.0001394 0.00021 0.00024 0.000139 0.00033
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
We next calculate the portfolio standard deviation, the standard deviation equal to
the square root of the variance, with the portfolio variance calculated using a modified
formula using the [=MMULT] and [=TRANSPOSE] functions.
𝑉𝑎𝑟𝑝 = 250 ∗ 𝑀𝑀𝑈𝐿𝑇(𝑇𝑅𝐴𝑁𝑆𝑃𝑂𝑆𝐸(𝑤), 𝑀𝑀𝑈𝐿𝑇(𝐶𝑜𝑣, 𝑤))
2.1.3. Slope:
Slope-index was calculated by this formula:
E(rp) - rf
Slope =
σp
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
CHAPTER 3: RESULTS
3.1. Case of not allowing short selling:
a) Finding the Minimum Variance Portfolio
We will first identify the global minimum variance portfolio - starting point of the
efficient part of the frontier.
We use the Solver tool in Excel:
- The box "Set Object": is the cell containing the Variance value. The target is Min;
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%;
- Tick the box "Make unconstrained Variables Non-Negative" in case short selling is
not allowed.
The following results:
Result of global minimum variance portfolio
Risk free 3.00%
Erp 16.29%
Var 7.08%
Std 26.62%
Slope 0.4992
Min variance weighted
Stock Weight
NKG 10.91737%
SSI 1.41576%
BID 9.06123%
DIG 9.38974%
VCB 69.21590%
Total weight 100%
b) Charting the Efficient Frontier of Risky Portfolios
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
We determine the desired risk premiums (points on the efficient frontier) that we wish
to use to construct the graph of the efficient frontier:
We use the Solver tool in Excel:
- The box "Set Object": is the cell containing the Variance value. The target is Min;
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%; Input to the Solver a
constraint particularly which will be used to change the required risk premium based on the
global minimum variance point that we find before and thus generate different points along
the frontier.
- Tick the box "Make unconstrained Variables Non-Negative" in case short selling is
not allowed.
The following results:
Stock Weight
18.76 31.83 46.72 59.45 72.18
NKG 10.92% % 25.18% % 40.36% % 53.09% % 65.82% % 78.74% 100.00%
SSI 1.42% 1.69% 1.51% 0.21% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
BID 9.06% 3.38% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
11.17 14.03 16.76 19.05 21.35
DIG 9.39% % 12.61% % 15.61% % 17.91% % 20.20% % 21.26% 0.00%
65.00 53.93 36.52 21.49
VCB 69.22% % 60.71% % 44.03% % 29.01% % 13.98% 6.47% 0.00% 0.00%
20.00 26.00 33.00 39.00 45.00
Erp 16.29% % 23.00% % 30.00% % 36.00% % 42.00% % 47.78% 51.59%
10.79 14.08 18.41
Var 7.08% 7.26% 7.65% 8.29% 9.54% % 12.30% % 16.11% % 20.77% 25.92%
26.94 28.78 32.85 37.52 42.91
Std 26.62% % 27.65% % 30.89% % 35.07% % 40.14% % 45.58% 50.92%
Sharpe 0.50 0.63 0.72 0.80 0.87 0.91 0.94 0.96 0.97 0.98 0.98 0.95
c) Finding the Optimal Risky Portfolio on the Efficient Frontier
We look for the portfolio with the highest Sharpe ratio. That point on efficient
frontier portfolio that is tangent to the CAL and be also the optimal risk portfolio. To find
the proportion of stocks in the optimal risk portfolio:
We use the Solver tool in Excel:
- The box "Set Object": is the cell containing the Sharpe ratio value. The target is Max;
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
7
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%;
- Tick the box "Make unconstrained Variables Non-Negative" in case short selling is
not allowed.
The following results:
Rate of return of portfolio E(r p) 47.78%
Standard deviation of portfolio 𝜎
45.58%
𝑝
Slope
0.982380925
W NKG
78.74%
W SSI
0.00%
W BID
0.00%
W DIG
21.26%
W VCB
0.00%
The CAL is a straight line connecting the point where the risk-free rate is
represented to the point where the optimal risky portfolio is represented.
The efficient frontier and the CAL line are shown as follows:
60.00%
Non Short Sell
50.00%
40.00%
Expected Return
30.00%
20.00%
10.00%
0.00%
0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00%
Standard Devia琀椀on
Now that asset allocation is decided, we can find each investor’s optimal capital
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
8
allocation. An investor with a coefficient of risk aversion A = 3 would take a position
in portfolio P of:
E(rp) – rf 47,78% – 3.00%
y= = = 71,85%
𝐴 * σ2 3 * 0.45582
p
is the weight about the optimal risky portfolio.
So portfolio combine risky portfolio and risk free is:
Rate of return of portfolio E(𝑟C) 35.17%
Standard deviation of portfolio 𝜎C 32.75%
W rf 28.15%
W NKG 56.57%
W SSI 0.00%
W BID 0.00%
W DIG 15.28%
W VCB 0.00%
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
3.2. The case for short selling:
Similar to the case of not allowing short selling, to find the weight of stocks of
the Minimum Variance Portfolio and Optimal risk portfolio, we use the Solve tool in
Excel:
a) Finding the Minimum Variance Portfolio
We will first identify the global minimum variance portfolio - starting point of the
efficient part of the frontier.
We use the Solver tool in Excel:
- The box "Set Object": is the cell containing the Variance value. The target is Min;
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%;
- Do not tick the box "Make unconstrained Variables Non-Negative" in case short
selling is allowed.
The following results:
Min variance weighted
Stock Weight
NKG 10.92%
SSI 1.42%
BID 9.06%
DIG 9.39%
VCB 69.22%
Total weight 100.00%
b) Charting the Efficient Frontier of Risky Portfolios
We determine the desired risk premiums (points on the efficient frontier) that we wish
to use to construct the graph of the efficient frontier:
We use the Solver tool in Excel:
- The box "Set Object": is the cell containing the Variance value. The target is Min;
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%; Input to the Solver a
constraint particularly which will be used to change the required risk premium based on the
global minimum variance point that we find before and thus generate different points along
the frontier.
- Do not tick the box "Make unconstrained Variables Non-Negative" in case short
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
1
selling is allowed.
The following results:
Stock Weight
18.76
NKG 10.92% % 25.10% 31.43% 39.88% 46.21% 52.55% 58.89% 65.22% 71.56% 77.89% 101.45%
SSI 1.42% 1.69% 1.91% 2.13% 2.42% 2.64% 2.86% 3.08% 3.30% 3.52% 3.74% 4.56%
BID 9.06% 3.38% -1.21% -5.81% -11.93% -16.52% -21.11% -25.70% -30.30% -34.89% -39.48% -56.55%
11.17
DIG 9.39% % 12.61% 14.05% 15.96% 17.40% 18.84% 20.28% 21.72% 23.16% 24.59% 29.94%
65.00
VCB 69.22% % 61.60% 58.20% 53.66% 50.26% 46.86% 43.46% 40.06% 36.65% 33.25% 20.60%
20.00
Erp 16.29% % 23.00% 26.00% 30.00% 33.00% 36.00% 39.00% 42.00% 45.00% 48.00% 59.16%
Var 7.08% 7.26% 7.65% 8.26% 9.42% 10.56% 11.92% 13.50% 15.31% 17.34% 19.59% 29.94%
26.94
Std 26.62% % 27.65% 28.74% 30.70% 32.49% 34.52% 36.74% 39.13% 41.64% 44.27% 54.72%
Sharpe 0.50 0.63 0.72 0.80 0.88 0.92 0.96 0.98 1.00 1.01 1.02 1.03
c) Finding the Optimal Risky Portfolio on the Efficient Frontier
We look for the portfolio with the highest Sharpe ratio. That point on efficient
frontier portfolio that is tangent to the CAL and be also the optimal risk portfolio. To find
the proportion of stocks in the optimal risk portfolio:
- The box "Set Object": is the cell containing the Sharpe ratio value. The target is Max;
- The changed values are the weights of stocks in the portfolio;
- The condition is that the sum of the proportions is 100%;
- Do not tick the box "Make unconstrained Variables Non-Negative" in case short
selling is allowed.
The following results:
Rate of return of portfolio E(Rp) 59.16%
Standard deviation of portfolio 𝜎 p
54.72%
Slope
1.02622516
W NKG
101.45%
W SSI
4.56%
W BID
-56.55%
W DIG
29.94%
W VCB
20.60%
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
1
The CAL is a straight line connecting the point where the risk-free rate is represented
to the point where the optimal risk portfolio is represented.
The efficient frontier and the CAL line are shown as follows:
70.00%
Short Sell
60.00%
50.00%
40.00%
30.00%
20.00%
10.00%
0.00%
0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00%
Standard Devia琀椀on
Now that asset allocation is decided, we can find each investor’s optimal capital
allocation. An investor with a coefficient of risk aversion A = 3 would take a position in
portfolio P of:
E(rp) – rf 59,16% - 3.00%
y= = = 62.51%
2
𝐴*σ 2
3 * 0.5472
p
is the weight about the optimal risky portfolio.
So portfolio combine risky portfolio and risk free is:
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
38.10%
Rate of return of portfolio E(r c )
34.21%
Standard deviation of portfolio σ c
W rf 37.49%
W NKG
63.42%
W SSI
2.85%
W BID
-35.35%
W DIG
18.72%
W VCB
12.88%
THE END
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
lOMoARcPSD|18409049
Downloaded by Ngo TLinnh (kennguyenie.11@[Link])
