Copy Of Optimal Portfolio Assignment Solution Strudwick

PORTFOLIO OPTIMISATION ASSIGNM Inputs & Visual Adjustments:

Outputs:

Allow Short Sales FALSE Display Efficient Frontier with SS FALSE Display Efficient Frontier without SS FALSE View Simulation FALSE 100 % to be Shorted Number of Portfolios

300

50%

10% 9% 8%

5%

400 510

4% 3% 2% 0%

35% 65% -35%

60%

11%

6%

4.00% 5.10% 4.06%

70%

12%

7%

Recalculate Asset Allocation: Risk-Free Rate Required Return Std Deviation

Visualisation of Efficient Frontier

0%

1000%

Risk Free

Short Sales # of Rand

Primary Axis

0.00% 12.40% 35%

4.00% 7.36% 65%

Risky Asset

800% 600% 400% 200% 0%

0%

Borrowing

TRUE 300 100 10 10 50 25 300 50 500 75 1000 100

Calculation of the Random Portfolios CHH FER UBIX Rand # 0.03 0.24 0.82 Prop 1.2% 9.3% 32.4% Portfolio Details Mean (r) VARP STDEVP

4.35% 0.37% 6.11%

60%

1200%

40%

10%

40%

Individual Stock Information

Risk Free Risky AssetBorrowing 30% 35% 65% 0% 20%

20%

Beta Mean Return Std Dev % of with SS % of Without SS

IN 0.39 15.3%

LN 0.9 35.6%

S 0.16 6.3%

2.539 2.539

80%

100%

Storage for the 300 random portfolios StdDev Mean (r)

Min (r): Max (r):

Means Std Devs 0.00% 0.00% 0.00% 0.00%

ON ASSIGNMENT

Created Created by by X X

Tangent Return Std Dev

5.68% 6.20%

Minimum Variance Return #N/A Std Dev 0.00% Sharpe Ratio Tangent Portfolio Minimum Variance

80%

100%

120%

1200%

Graph Options:

1000% 800% 600% 400% 200% 0% CHH

CHH 0.36 3.46% 8.52% 59.8% 10.0%

FER 0.18 3.17% 5.74% -15.9% 19.1%

UBIX 1.12 8.85% 10.28% 79.9% 42.7%

Secondary Axis

60%

0.27 #N/A

Primary Axis Mean Return Secondary Axis Beta

IN 0.37 3.21% 9.09% 8.0% 13.9%

LN 0.25 1.02% 9.04% -77.3% 4.5%

S 0.90 4.69% 11.49% 45.5% 9.9%

Stock Return Information Date CHH 2-May-91 14.91% 4-Jun-91 -0.54% 1-Jul-91 -11.96% 1-Aug-91 10.03% 2-Sep-91 -6.40% 1-Oct-91 11.80% 1-Nov-91 13.33% 2-Dec-91 8.33% 6-Jan-92 11.78% 3-Feb-92 -4.17% 3-Mar-92 4.35% 1-Apr-92 -5.42% 1-May-92 10.13% 2-Jun-92 11.20% 1-Jul-92 -3.96% 3-Aug-92 1.86% 1-Sep-92 -0.75% 2-Oct-92 -5.30% 2-Nov-92 -8.00% 1-Dec-92 20.43% 5-Jan-93 1.43% 1-Feb-93 -2.53% 1-Mar-93 8.89%

Variance-Covariance Matrix (Crea CHH CHH 0.0073 FER 0.0025 UBIX 0.0016 IN 0.0046 LN 0.0050 S 0.0035 With Short Sales Z CHH 3.0929 FER 9.8416 UBIX 7.2125 IN -1.3873 LN -4.6723 S 1.6056 Transpose of X 0.2 0.63 Transpose of Y 0.24 0.61

Mean Var StdDev Cov (x,y) Corr (x,y)

X 0.0663 0.0042 0.0650 0 0.99

A Single Portfolio Calculation Proportion of X: p's Mean return: p's std dev: Gap 0.14 Prop of X Std Dev 7% 0% 7% 14% 7% 28% 7% 42% 7% 56% 7% 70% 7% 84% 7% 98% 7% 112% 6% 126% 6% 140% 6% 154% 6% 168% 6% 182% 6% 196% 6% 210% 6% 224% 6% 238% 6% 252% 6% 266% 6% 280% 5% 294% 5% 308% 5% 322% 5% 336% 5% 350% 5% 364% 5% 378% 5% 392% 5% 406% 5%

urn Information FER 12.75% 0.87% 0.86% 5.13% 4.07% 9.73% 5.07% -2.76% 6.38% -0.67% -0.40% 5.16% 10.39% 11.76% -2.11% 7.53% -5.00% 0.30% 1.06% 9.47% 7.69% -7.14% -7.21%

UBIX 2.94% 9.71% 4.17% 24.00% 14.92% 13.68% 10.35% 11.76% 22.37% -3.23% -3.33% -5.27% 18.52% 16.67% 5.36% 11.86% 24.24% -3.05% 10.52% 6.98% -2.72% 25.14% -12.05%

IN 19.62% -8.99% -11.60% 0.00% -3.07% 0.86% 19.54% 1.22% -3.61% -4.25% 3.13% 0.55% 20.51% 8.51% -3.92% 20.41% -0.85% -0.85% 4.00% 12.61% 0.00% -0.75% 0.75%

LN -2.42% 11.18% -4.37% 1.48% -10.82% 13.11% 15.07% -5.54% 10.07% -13.09% 9.09% 4.08% 11.17% 10.05% -6.64% 0.67% -5.74% 3.04% -15.91% 12.97% -2.59% -9.50% -1.93%

S 7.41% -3.38% -2.27% 4.19% -6.25% 23.81% 28.85% -7.49% 13.11% 1.45% 12.00% -6.12% 22.28% -2.41% -3.45% 5.95% 14.61% -5.88% 25.00% 1.66% -4.96% -12.17% 1.98%

Covariance Matrix (Created using VBA) FER UBIX IN 0.0025 0.0016 0.0046 0.0033 0.0007 0.0029 0.0007 0.0106 0.0009 0.0029 0.0009 0.0083 0.0027 0.0006 0.0029 0.0019 0.0026 0.0049

LN 0.0050 0.0027 0.0006 0.0029 0.0082 0.0035

S 0.0035 0.0019 0.0026 0.0049 0.0035 0.0132

0.01

4.0000

C X 0.1971 0.6271 0.4596 -0.0884 -0.2977 0.1023

Z 2.92 7.3 6.52 -1.38 -4.82 1.44

Y 0.24 0.61 0.54 -0.11 -0.4 0.12

0.46

-0.09

-0.3

0.1

0.54

-0.11

-0.4

0.12

Market 5.23% 4.65% 1.33% 16.45% 7.24% 16.24% 16.53% 5.08% 18.10% -1.80% 1.84% -5.17% 19.65% 10.24% 2.05% 10.28% 19.66% -3.81% 14.54% 5.66% -3.18% 11.80% -6.92%

PR 4.91% 5.61% -2.18% 9.16% 0.42% 11.77% 14.15% 1.39% 11.83% -6.37% 3.40% -0.14% 15.59% 11.34% -1.68% 8.29% 6.13% -0.44% -0.06% 10.02% -1.38% 3.19% -4.92%

A 8.77% 2.14% -3.64% 7.08% -0.38% 10.66% 14.20% 0.69% 9.02% -4.73% 3.35% 0.12% 15.40% 10.30% -2.33% 9.17% 3.41% -0.96% 1.28% 10.57% 0.16% -0.17% -3.21%

Means Means-C 3.46% 2.46% 3.17% 2.17% 8.85% 7.85% 3.21% 2.21% 1.02% 0.02% 4.69% 3.69% Without Short Sales A 0.0000 0.0000 1.0000 0.0000 0.0000 0.0000 100%

Transpose of A 0 0

1

Y 0.0738 0.0053 0.0730

Portfolio Calculation 0.3 7.15% 7.04% 15 Return 7% 7% 7% 7% 7% 7% 7% 7% 7% 7% 6% 6% 6% 6% 6% 6% 6% 6% 6% 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% 4% 4%

7.04%

0

0

0

A Mean 8.85% StdDev 10.28% Theta 0.76 'Without Short Sales' Table Created using VBA C Std Dev Mean -0.49 5.20% 4.41% -0.48 5.20% 4.41% -0.47 5.20% 4.42% -0.46 5.20% 4.42% -0.45 5.20% 4.43% -0.44 5.20% 4.43% -0.43 5.20% 4.44% -0.42 5.20% 4.44% -0.41 5.20% 4.44% -0.40 5.20% 4.45% -0.39 5.20% 4.45% -0.38 5.20% 4.46% -0.37 5.20% 4.47% -0.36 5.21% 4.47% -0.35 5.21% 4.48% -0.34 5.21% 4.48% -0.33 5.21% 4.49% -0.32 5.21% 4.50% -0.31 5.21% 4.51% -0.30 5.21% 4.52% -0.29 5.21% 4.52% -0.28 5.21% 4.53% -0.27 5.22% 4.54% -0.26 5.22% 4.55% -0.25 5.22% 4.56% -0.24 5.22% 4.56% -0.23 5.22% 4.57% -0.22 5.22% 4.58% -0.21 5.23% 4.59% -0.20 5.23% 4.61% -0.19 5.23% 4.62% -0.18 5.23% 4.63% -0.17 5.24% 4.65% -0.16 5.24% 4.66% -0.15 5.25% 4.68% -0.14 5.25% 4.70% -0.13 5.26% 4.72% -0.12 5.27% 4.75% -0.11 5.27% 4.78% -0.10 5.29% 4.81% -0.09 5.30% 4.85% -0.08 5.32% 4.89% -0.07 5.34% 4.94% -0.06 5.37% 5.01% -0.05 5.41% 5.08%

-0.04 -0.03 -0.02 -0.01 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.10

5.46% 5.53% 5.64% 5.82% 6.12% 6.67% 7.89% 10.03% 10.28% 10.28% 10.28% 10.28% 10.28% 10.28% 10.28%

5.17% 5.29% 5.45% 5.66% 5.97% 6.46% 7.36% 8.71% 8.85% 8.85% 8.85% 8.85% 8.85% 8.85% 8.85%

B 10.00% 1.89% -3.72% 9.33% 0.18% 12.55% 13.01% 2.53% 11.46% -3.14% 2.93% -1.54% 13.95% 10.45% -1.89% 6.71% 4.26% -2.51% 1.93% 11.64% 1.10% -0.24% -1.50%

Opt 10.23% 1.85% -3.73% 9.75% 0.29% 12.91% 12.79% 2.87% 11.91% -2.84% 2.85% -1.85% 13.68% 10.47% -1.81% 6.25% 4.42% -2.81% 2.06% 11.84% 1.27% -0.25% -1.18%

With SS Tangent Weights Return 0.221 0.008 0.618 0.020 0.502 0.044 -0.102 -0.003 -0.351 -0.004 0.111 0.005 1 Return 7.01% StdDev 6.89% 49.47% X 50.53% Y 0 0 0

Without SS Tangent A B 7.36% 28.44% 20.56% 26.35% 19.13% 18.69% 22.24% 5.85% 21.83% 8.41% 8.88% 12.27% 1.00 1.00 R 3.95% 4.32% S 5.84% 5.82% V 0.34% 0.34% Cov 0.33% Weight

-18.74%

118.74%

Table Created using VBA CHH FER 0.054 0.674 0.054 0.674 0.054 0.674 0.054 0.673 0.054 0.673 0.054 0.673 0.055 0.673 0.055 0.673 0.055 0.672 0.056 0.672 0.056 0.672 0.056 0.672 0.056 0.671 0.057 0.671 0.057 0.671 0.057 0.670 0.058 0.670 0.058 0.670 0.058 0.669 0.059 0.669 0.059 0.669 0.059 0.668 0.059 0.668 0.059 0.666 0.058 0.665 0.058 0.664 0.058 0.662 0.057 0.661 0.057 0.659 0.057 0.658 0.056 0.656 0.056 0.654 0.055 0.652 0.054 0.649 0.054 0.647 0.053 0.644 0.052 0.640 0.051 0.637 0.050 0.632 0.049 0.628 0.047 0.622 0.046 0.616 0.044 0.608 0.041 0.599 0.039 0.589

UBIX 0.209 0.210 0.210 0.211 0.211 0.212 0.212 0.213 0.213 0.214 0.215 0.215 0.216 0.217 0.217 0.218 0.219 0.220 0.221 0.222 0.223 0.224 0.225 0.226 0.228 0.229 0.231 0.233 0.235 0.237 0.239 0.241 0.244 0.247 0.250 0.254 0.258 0.262 0.267 0.273 0.279 0.287 0.296 0.307 0.320

Return Std Dev Sharpe:

4.39% 5.85% 0.07

IN 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

LN 0.016 0.016 0.015 0.015 0.014 0.014 0.013 0.013 0.012 0.011 0.010 0.010 0.009 0.008 0.007 0.006 0.005 0.004 0.003 0.002 0.001 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

S SHARPE 0.047 0.0792 0.047 0.0797 0.047 0.0805 0.047 0.0813 0.047 0.0820 0.047 0.0829 0.047 0.0837 0.047 0.0846 0.047 0.0855 0.047 0.0864 0.047 0.0874 0.047 0.0885 0.048 0.0895 0.048 0.0907 0.048 0.0919 0.048 0.0931 0.048 0.0945 0.048 0.0959 0.048 0.0974 0.048 0.0989 0.048 0.1006 0.049 0.1023 0.049 0.1035 0.049 0.1050 0.049 0.1065 0.049 0.1081 0.049 0.1098 0.049 0.1117 0.049 0.1137 0.049 0.1159 0.049 0.1182 0.049 0.1208 0.050 0.1236 0.050 0.1266 0.050 0.1300 0.050 0.1337 0.050 0.1379 0.050 0.1425 0.050 0.1477 0.051 0.1535 0.051 0.1602 0.051 0.1679 0.052 0.1768 0.052 0.1873 0.053 0.1998

0.47

0.035 0.031 0.025 0.017 0.005 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0.575 0.558 0.536 0.505 0.459 0.376 0.217 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0.336 0.357 0.384 0.422 0.477 0.563 0.722 0.966 1.000 1.000 1.000 1.000 1.000 1.000 1.000

0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0.053 0.054 0.055 0.057 0.059 0.060 0.061 0.034 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0.2148 0.2332 0.2561 0.2851 0.3224 0.3693 0.4263 0.4696 0.4717 0.4717 0.4717 0.4717 0.4717 0.4717 0.4717