Scribd
Upload
78 views10 pages

Practice-Markowitz Solved

The document examines the risk and return characteristics of a two asset portfolio with varying weights. It provides the expected return and risk of two individual assets, with asset 1 having an expected return of 15% and risk of 10%, and asset 2 having an expected return of 30% and risk of 15%. The correlation between the two assets is -0.5. Various portfolio combinations are shown with changing weights between the two assets, along with the resulting portfolio expected return and standard deviation.

Uploaded by

Sagar Kansal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
78 views10 pages

Practice-Markowitz Solved

The document examines the risk and return characteristics of a two asset portfolio with varying weights. It provides the expected return and risk of two individual assets, with asset 1 having an expected return of 15% and risk of 10%, and asset 2 having an expected return of 30% and risk of 15%. The correlation between the two assets is -0.5. Various portfolio combinations are shown with changing weights between the two assets, along with the resulting portfolio expected return and standard deviation.

Uploaded by

Sagar Kansal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 10

Examining Risk and Return Characteristics of a Two Asset Portfolio

Profile of Individual Assets

Asset
Expected
Return Risk
Two Asset Portfolio
1 15% 10% 35%
2 30% 15% 30%
25%
Correltion coefficient of returns between the assets = -0.5

Return %
20%
15%

Weights Portfolio 10%

Standard Expected 5%
Portfolio # Asset 1 Asset 2 Deviation Return 0%
4% 6% 8% 10% 12% 14% 16%
1 100% 0% 10.00% 15.00%
2 90% 10% 8.35% 16.50% Risk (Standard Deviation) %
3 80% 20% 7.00% 18.00%
4 70% 30% 6.14% 19.50%
5 60% 40% 6.00% 21.00%
6 50% 50% 6.61% 22.50%
7 40% 60% 7.81% 24.00%
8 30% 70% 9.37% 25.50%
9 20% 80% 11.14% 27.00%
10 10% 90% 13.03% 28.50%
11 0% 100% 15.00% 30.00% <=B25*$B$7+C25*$B$8

=SQRT(B25^2*$C$7^2+C25^2*$C$8^2+2*$F$10*B25*C25*$C$7*$C$8)
io

2% 14% 16%
n) %
Quarterly Return of Selected Funds during the Past Five Years

Fund # MF1 MF2 MF3 MF4 MF5 MF6 MF7 MF8


ICICI Pru Quantum DSP-BR Reliance ICICI Pru
SBI HDFC HDFC
Focused Long- Micro Cap Equity Exp&Othe
Quarter Emerging Balanced Prudence
Bluechip Term Fund - RP Oppor - r Services-
Busi (G) Fund (G) Fund (G)
Eqty (G) Equity (G) (G) RP (G) RP (G)
2015 Q1 2.90% 0.10% -13.70% -14.70% -6.40% -11.40% -1.00% -4.20%
2015 Q2 41.60% 44.30% 66.00% 66.20% 47.50% 49.10% 37.60% 47.00%
2015 Q3 19.20% 23.40% 30.50% 15.70% 27.40% 19.80% 14.10% 18.20%
2015 Q4 -0.20% -0.20% 14.10% -0.20% -0.20% 5.60% 8.50% -0.20%
2016 Q1 3.40% 1.90% 9.20% 4.60% 5.40% 1.80% 4.00% 4.10%
2016 Q2 2.90% 6.10% 9.40% 0.20% 5.00% 0.40% 6.70% 6.60%
2016 Q3 15.40% 16.20% 14.20% 19.10% 18.30% 14.30% 9.90% 12.50%
2016 Q4 1.20% 0.20% -4.40% 0.10% -2.60% -1.90% 0.60% -0.70%
2017 Q1 -2.60% -5.80% -14.70% -9.00% -7.30% -4.90% -3.10% -3.20%
2017 Q2 -1.00% -2.50% 1.50% 4.90% 2.60% -2.70% 4.60% 1.60%
2017 Q3 -9.40% -8.70% -5.20% -1.40% -8.90% -10.70% -5.10% -6.80%
2017 Q4 -2.40% -4.00% -13.50% -8.10% -8.50% -7.30% -6.60% -7.50%
2018 Q1 12.70% 17.10% 20.00% 14.60% 21.10% 17.80% 15.50% 16.00%
2018 Q2 -0.60% -1.90% 0.40% 3.10% 1.40% -3.20% -0.80% -1.10%
2018 Q3 8.10% 8.60% 9.60% 10.20% 10.70% 9.30% 6.20% 6.50%
2018 Q4 4.40% 3.80% 4.50% 13.00% 5.40% 5.20% 2.50% 5.10%
2019 Q1 -4.90% -3.90% -15.80% -12.50% -7.90% 1.90% -5.80% -8.60%
2019 Q2 1.80% -0.60% -0.70% -1.70% -3.10% 2.10% -0.30% -0.40%
2019 Q3 1.30% -1.10% -4.50% -8.40% -4.30% 17.00% -2.30% -7.30%
2019 Q4 10.00% 12.30% 24.90% 13.10% 19.50% 16.30% 15.40% 17.10%
Mean returns 5.19% 5.27% 6.59% 5.44% 5.76% 5.93% 5.03% 4.74%

Excess Return of Selected Funds during the Past Five Years


Fund # MF1 MF2 MF3 MF4 MF5 MF6 MF7 MF8
ICICI Pru Quantum DSP-BR Reliance ICICI Pru
SBI HDFC HDFC
Focused Long- Micro Cap Equity Exp&Othe
Quarter Emerging Balanced Prudence
Bluechip Term Fund - RP Oppor - r Services-
Busi (G) Fund (G) Fund (G)
Eqty (G) Equity (G) (G) RP (G) RP (G)
2015 Q1 -2.29% -5.17% -20.29% -20.14% -12.16% -17.33% -6.03% -8.94%
2015 Q2 36.41% 39.04% 59.41% 60.76% 41.75% 43.18% 32.57% 42.27%
2015 Q3 14.01% 18.14% 23.91% 10.26% 21.65% 13.88% 9.07% 13.47%
2015 Q4 -5.39% -5.47% 7.51% -5.64% -5.96% -0.33% 3.47% -4.94%
2016 Q1 -1.79% -3.37% 2.61% -0.84% -0.36% -4.13% -1.03% -0.64%
2016 Q2 -2.29% 0.83% 2.81% -5.24% -0.76% -5.53% 1.67% 1.87%
2016 Q3 10.21% 10.94% 7.61% 13.66% 12.55% 8.38% 4.87% 7.77%
2016 Q4 -3.99% -5.07% -10.99% -5.34% -8.36% -7.83% -4.43% -5.44%
2017 Q1 -7.79% -11.07% -21.29% -14.44% -13.06% -10.83% -8.13% -7.94%
2017 Q2 -6.19% -7.77% -5.09% -0.54% -3.16% -8.63% -0.43% -3.14%
2017 Q3 -14.59% -13.97% -11.79% -6.84% -14.66% -16.63% -10.13% -11.54%
2017 Q4 -7.59% -9.27% -20.09% -13.54% -14.26% -13.23% -11.63% -12.24%
2018 Q1 7.51% 11.84% 13.41% 9.16% 15.35% 11.88% 10.47% 11.27%
2018 Q2 -5.79% -7.17% -6.19% -2.34% -4.36% -9.13% -5.83% -5.84%
2018 Q3 2.91% 3.34% 3.01% 4.76% 4.95% 3.38% 1.17% 1.77%
2018 Q4 -0.79% -1.47% -2.09% 7.56% -0.36% -0.73% -2.53% 0.36%
2019 Q1 -10.09% -9.17% -22.39% -17.94% -13.66% -4.03% -10.83% -13.34%
2019 Q2 -3.39% -5.87% -7.29% -7.14% -8.86% -3.83% -5.33% -5.14%
2019 Q3 -3.89% -6.37% -11.09% -13.84% -10.06% 11.08% -7.33% -12.04%
2019 Q4 4.81% 7.04% 18.31% 7.66% 13.75% 10.38% 10.37% 12.37%
Summary of Return and Risk

MF9 MF10 Fund # MF1


ICICI Pru
Birla Sun
R* Shares Focused
Life GSec Fund Name
Golld ETF Bluechip
- LTF (G)
Eqty (G)
7.40% 10.40% Average Return per Quarter 5.19%
4.70% -4.10% Annual Return 22.43%
1.80% 7.70% Variance per Quarter 1.15%
1.70% -0.20% Annual Variance of Returns 4.61%
1.20% -2.80% Srandard Deviation of Returns 21.47%
5.70% 14.20% Q stdev 10.74%
1.40% 1.50% Annual stdev 21.47%
1.10% 7.30% Average
1.20% 1.30% Fund Return
1.10% 4.90% ICICI Pru Focused Bluechip Eqty (G) 5.19%
1.40% 19.20% Quantum Long-Term Equity (G) 5.27%
3.20% 2.00% DSP-BR Micro Cap Fund - RP (G) 6.59%
2.00% 3.80% SBI Emerging Busi (G) 5.44%
3.40% 3.50% Reliance Equity Oppor - RP (G) 5.76%
2.00% 5.20% ICICI Pru Exp&Other Services-RP (G) 5.93%
2.40% -2.30% HDFC Balanced Fund (G) 5.03%
2.60% -2.80% HDFC Prudence Fund (G) 4.74%
5.20% -15.60% Birla Sun Life GSec - LTF (G) 2.21%
-6.60% 16.00% R* Shares Golld ETF 3.08%
1.30% -7.60%
2.21% 3.08% Total weights

Return
MF9 MF10 Variance

Birla Sun
R* Shares
Life GSec
Golld ETF
- LTF (G)
Standard Deviation
5.19% 7.32% Sharpe Ratio
2.49% -7.18%
-0.41% 4.62%
-0.51% -3.28%
-1.01% -5.88%
3.49% 11.12%
-0.81% -1.58%
-1.11% 4.22%
-1.01% -1.78%
-1.11% 1.82%
-0.81% 16.12%
0.99% -1.08%
-0.21% 0.72%
1.19% 0.42%
-0.21% 2.12%
0.19% -5.38%
0.39% -5.88%
2.99% -18.68%
-8.81% 12.92%
-0.91% -10.68%

Final Portfolio

Annual
Asset Return
ICICI Pru Focused Bluechip Eqty (G) 22.43%
Quantum Long-Term Equity (G) 22.78%
DSP-BR Micro Cap Fund - RP (G) 29.08%
SBI Emerging Busi (G) 23.60%
Reliance Equity Oppor - RP (G) 25.08%
ICICI Pru Exp&Other Services-RP (G) 25.89%
HDFC Balanced Fund (G) 21.69%
HDFC Prudence Fund (G) 20.33%
Birla Sun Life GSec - LTF (G) 9.14%
R* Shares Golld ETF 12.90%
Risk Free Asset 8.00%
Total
MF2 MF3 MF4 MF5 MF6 MF7 MF8 MF9 MF10
Quantum DSP-BR Reliance ICICI Pru
SBI HDFC HDFC Birla Sun
Long- Micro Cap Equity Exp&Othe R* Shares
Emerging Balanced Prudence Life GSec
Term Fund - RP Oppor - r Services- Golld ETF
Busi (G) Fund (G) Fund (G) - LTF (G)
Equity (G) (G) RP (G) RP (G)
5.27% 6.59% 5.44% 5.76% 5.93% 5.03% 4.74% 2.21% 3.08%
22.78% 29.08% 23.60% 25.08% 25.89% 21.69% 20.33% 9.14% 12.90%
1.47% 3.51% 2.85% 2.03% 1.84% 1.00% 1.59% 0.07% 0.64%
5.89% 14.06% 11.42% 8.11% 7.35% 4.00% 6.37% 0.28% 2.56%
24.26% 37.49% 33.79% 28.48% 27.11% 20.01% 25.25% 5.32% 16.01%
12.13% 18.75% 16.90% 14.24% 13.56% 10.00% 12.62% 2.66% 8.01%
24.26% 37.49% 33.79% 28.48% 27.11% 20.01% 25.25% 5.32% 16.01%

Weights
20.00%
0.00%
0.00%
0.00%
0.00%
20.00%
20.00%
0.00%
20.00%
20.00%

100.00% < =SUM(R15:R24)

4.29% < =MMULT(TRANSPOSE(R15:R24),Q15:Q24)


0.41% < =MMULT(MMULT(TRANSPOSE(R15:R24),AD6:AM15),R15:R24)

6.41% < =SQRT(S29) 5% 78% 22%


0.3567615 < =(S28-0.02)/S30 (assuming risk free rate is 8%)

To maximise Sharpe Ratio, Solver can be used.


The market portfolio has following characteristics
Expected Return = 4.29%
Standard Deviation = 6.41%
Weights
Market Final
Portfolio Portfolio
20.00% 15.60%
0.00% 0.00%
0.00% 0.00%
0.00% 0.00%
0.00% 0.00%
20.00% 15.60%
20.00% 15.60%
0.00% 0.00%
20.00% 15.60%
20.00% 15.60%
22.00%
100.00%
Variance-Covariance Matrix

ICICI Pru Quantum


Focused Long-
Formula in previous column
Bluechip Term
Eqty (G) Equity (G)
< =AVERAGE(M6:M25) ICICI Pru Focused Bluechip Eqty (G) 1.15% 1.29%
< =(1+AA6)^4-1 Quantum Long-Term Equity (G) 1.29% 1.47%
< =VARP(M6:M25) DSP-BR Micro Cap Fund - RP (G) 1.83% 2.12%
< =4*AA8 SBI Emerging Busi (G) 1.66% 1.87%
< =SQRT(AA9) Reliance Equity Oppor - RP (G) 1.46% 1.69%
ICICI Pru Exp&Other Services-RP (G) 1.32% 1.50%
HDFC Balanced Fund (G) 1.00% 1.15%
HDFC Prudence Fund (G) 1.30% 1.48%
Birla Sun Life GSec - LTF (G) 0.05% 0.05%
R* Shares Golld ETF -0.22% -0.20%
DSP-BR Reliance ICICI Pru
SBI HDFC HDFC Birla Sun
Micro Cap Equity Exp&Othe R* Shares
Emerging Balanced Prudence Life GSec
Fund - RP Oppor - r Services- Golld ETF
Busi (G) Fund (G) Fund (G) - LTF (G)
(G) RP (G) RP (G)
1.83% 1.66% 1.46% 1.32% 1.00% 1.30% 0.05% -0.22%
2.12% 1.87% 1.69% 1.50% 1.15% 1.48% 0.05% -0.20%
3.51% 2.96% 2.56% 2.26% 1.83% 2.28% 0.05% -0.36%
2.96% 2.85% 2.23% 1.96% 1.55% 2.03% 0.06% -0.37%
2.56% 2.23% 2.03% 1.73% 1.37% 1.76% 0.04% -0.27%
2.26% 1.96% 1.73% 1.84% 1.20% 1.50% -0.06% -0.28%
1.83% 1.55% 1.37% 1.20% 1.00% 1.23% 0.04% -0.20%
2.28% 2.03% 1.76% 1.50% 1.23% 1.59% 0.07% -0.29%
0.05% 0.06% 0.04% -0.06% 0.04% 0.07% 0.07% -0.06%
-0.36% -0.37% -0.27% -0.28% -0.20% -0.29% -0.06% 0.64%

You might also like