“Optimal complete portfolio and systematic risk of Australian firms: Impact of Global Financial Crisis (GFC)”

mlbbjoce
main_assignment_template_amended.xlsx
Home>Business & Finance homework help>Financial markets homework help>“Optimal complete portfolio and systematic risk of Australian firms: Impact of Global Financial Crisis (GFC)”

Main Computation

Expected Standard Risk Premium
Return/Mon Deviation
E(ri) si Ei=E(ri)-rf
Riskfree Rate (rf) 0.23% 0%
Risky Asset 1 2.10% 13% 1.87%
Risky Asset 2 1.30% 8% 1.07%
Correlation (1,2) 20%
Risk Aversion 2
Outputs Risky Asset Capital Utility Optimal
Proportion Opp Set Allocation Indifference Complete
in Risky (x-axis) Curve Line Curve Portfolio
Asset 1 or Standard Expected Expected Expected Expected
Opt Risky Deviation Ret / Mon Ret / Mon Ret / Mon Ret / Mon
Opp Set Curve -500.0% 72.7% -2.7%
Opp Set Curve -150.0% 25.0% 0.1%
Opp Set Curve -80.0% 16.0% 0.7%
Opp Set Curve -70.0% 14.8% 0.7%
Opp Set Curve -60.0% 13.6% 0.8%
Opp Set Curve -50.0% 12.5% 0.9%
Opp Set Curve -40.0% 11.4% 1.0%
Opp Set Curve -30.0% 10.4% 1.1%
Opp Set Curve -20.0% 9.4% 1.1%
Opp Set Curve -10.0% 8.6% 1.2%
Opp Set Curve 0.0% 8.0% 1.3%
Opp Set Curve 10.0% 7.6% 1.4%
Opp Set Curve 20.0% 7.4% 1.5%
Opp Set Curve 30.0% 7.4% 1.5%
Opp Set Curve 40.0% 7.8% 1.6%
Opp Set Curve 50.0% 8.3% 1.7%
Opp Set Curve 60.0% 9.0% 1.8%
Opp Set Curve 70.0% 9.9% 1.9%
Opp Set Curve 80.0% 10.8% 1.9%
Opp Set Curve 90.0% 11.9% 2.0%
Opp Set Curve 100.0% 13.0% 2.1%
Opp Set Curve 110.0% 14.2% 2.2%
Opp Set Curve 120.0% 15.4% 2.3%
Opp Set Curve 250.0% 32.3% 3.3%
Opp Set Curve 750.0% 100.9% 7.3%
Opt Risky Port 40.7% 7.8% 1.6%
Cap Alloc Line 0.0% 0.0% 0.2%
Cap Alloc Line 1000.0% 77.8% 14.2%
Indifference Curve 0.0% 0.63%
Indifference Curve 1.0% 0.65%
Indifference Curve 2.0% 0.71%
Indifference Curve 3.0% 0.81%
Indifference Curve 4.0% 0.95%
Indifference Curve 5.0% 1.13%
Indifference Curve 6.0% 1.35%
Indifference Curve 7.0% 1.61%
Indifference Curve 8.0% 1.91%
Indifference Curve 9.0% 2.25%
Indifference Curve 10.0% 2.63%
Indifference Curve 11.0% 3.05%
Indifference Curve 12.0% 3.51%
Indifference Curve 13.0% 4.01%
Indifference Curve 14.0% 4.55%
Indifference Curve 15.0% 5.13%
Opt Comp Port 4.5% 1.0%
Constant Utility Value 0.0063
Risk aversion Weight TP Weight RF Exp. Return Std. dev. Utility
2.0 0.5763 0.4237 0.0103 0.0448 0.0063

Sheet1

Risky Asset 1 Risky Asset 2
Month Barrick IBM Treasury Bills
Dec 2009 -7.75% 3.60% 1.13786 0.158
Nov 2009 19.37% 5.22% 1.20227 0.148
Oct 2009 -5.20% 0.84% 1.28278 0.148
Sep 2009 9.22% 1.32% 1.12176 0.178
Aug 2009 -0.57% 0.57% 0.94464 0.178
Jul 2009 4.02% 12.94% 0.97148 0.202
Jun 2009 -11.90% -1.75% 1.07346 0.311
May 2009 31.55% 3.51% 1.01442 0.316
Apr 2009 -10.24% 6.52% 0.97616 0.38
Mar 2009 7.35% 5.28% 0.99319 0.602
Feb 2009 -19.45% 0.96% 0.93643 0.587
Jan 2009 1.96% 8.90% 1.0366 0.612
Dec 2008 24.81% 3.14% 0.94445 0.671
Nov 2008 30.43% -11.69% 0.89839 0.706
Oct 2008 -38.11% -20.51% 1.01356 0.706
Sep 2008 5.79% -3.92% 0.95021 0.805
Aug 2008 -17.99% -4.49% 0.92141 0.795
Jul 2008 -6.92% 7.97% 0.85231 0.735
Jun 2008 12.93% -8.42% 0.72274 0.665
May 2008 4.84% 7.65% 0.57301 0.575
Apr 2008 -11.12% 4.83% 0.4895 0.525
Mar 2008 -16.36% 1.12% 0.56436 0.565
Feb 2008 0.99% 6.68% 0.49814 0.465
Jan 2008 22.33% -0.92% 0.48375 0.44
Dec 2007 3.80% 2.78% 0.60468 0.53
Nov 2007 -7.86% -9.08% 0.71986 0.475
Oct 2007 9.56% -1.43% 0.66227 0.39
Sep 2007 23.86% 0.95% 0.58165 0.34
Aug 2007 -1.16% 5.82% 0.54709 0.28
Jul 2007 13.18% 5.13% 0.61332 0.345
Jun 2007 -0.21% -1.27% 0.57013 0.31
May 2007 4.16% 4.69% 0.61908 0.3
Apr 2007 -1.54% 8.43% 0.70258 0.35
Mar 2007 -4.42% 1.42% 0.64211 0.36
Feb 2007 0.84% -5.96% 0.61332 0.425
Jan 2007 -3.52% 2.06% 0.65651 0.435
Dec 2006 -2.35% 5.69% 0.6853 0.51
Nov 2006 1.77% -0.12% 0.65075 0.435
Oct 2006 0.91% 12.68% 0.66515 0.405
Sep 2006 -8.24% 1.20% 0.54997 0.44
Aug 2006 8.70% 4.99% 0.56149 0.46
Jul 2006 4.05% 0.77% 0.53845 0.435
Jun 2006 -3.30% -3.85% 0.49814 0.445
May 2006 0.79% -2.60% 0.47799 0.405
Apr 2006 11.89% -0.16% 0.65939 0.415
Mar 2006 -0.48% 2.78% 0.77169 0.435
Feb 2006 -13.00% -1.06% 0.75729 0.44
Jan 2006 12.88% -1.09% 0.79184 0.425
Dec 2005 4.74% -7.54% 0.55573 0.64
Nov 2005 5.82% 8.82% 0.61895 0.69
Oct 2005 -13.08% 2.07% 0.63348 0.605
Sep 2005 10.79% -0.50% 0.61024 0.48
Aug 2005 7.02% -3.16% 0.63058 0.555
Jul 2005 -2.12% 12.48% 0.64511 0.485
Jun 2005 8.87% -1.79% 0.68288 0.51
May 2005 3.49% -0.82% 0.65092 0.54
Apr 2005 -6.84% -16.42% 0.56955 0.575
Mar 2005 -3.78% -1.30% 0.47366 0.58
Feb 2005 13.91% -0.71% 0.38648 0.505
Jan 2005 -9.74% -5.23% 0.31732 0.48
Dec 2004 -1.50% 4.61% 0.31732 0.48
Nov 2004 9.73% 5.20%
Oct 2004 6.99% 4.68%
Sep 2004 5.20% 1.24%
Aug 2004 4.60% -2.53%
Jul 2004 -3.19% -1.23%
Jun 2004 -4.45% -0.50%
May 2004 8.06% 0.68%
Apr 2004 -19.13% -4.00%
Mar 2004 16.28% -4.83%
Feb 2004 3.81% -2.59%
Jan 2004 -13.25% 7.07%
Dec 2003 1.66% 2.36%
Nov 2003 15.31% 1.36%
Oct 2003 3.40% 1.30%
Sep 2003 -6.83% 7.71%
Aug 2003 18.05% 1.13%
Jul 2003 -4.36% -1.52%
Jun 2003 2.40% -6.29%
May 2003 17.66% 3.89%
Apr 2003 -3.92% 8.25%
Mar 2003 -3.47% 0.62%
Feb 2003 -2.01% -0.13%
Jan 2003 6.75% 0.90%
Dec 2002 5.79% -10.84%
Nov 2002 -2.65% 10.30%
Oct 2002 -3.09% 35.38%
Sep 2002 -3.24% -22.65%
Aug 2002 4.90% 7.29%
Jul 2002 -19.33% -2.22%
Jun 2002 -12.89% -10.50%
May 2002 9.17% -3.77%
Apr 2002 8.14% -19.46%
Mar 2002 2.83% 5.99%
Feb 2002 5.56% -8.93%
Jan 2002 7.21% -10.81%
Dec 2001 5.42% 4.65%
Nov 2001 -2.25% 7.09%
Oct 2001 -10.14% 17.83%
Sep 2001 8.30% -8.23%
Aug 2001 7.59% -4.87%
Jul 2001 -1.72% -6.89%
Jun 2001 -8.18% 1.07%
May 2001 1.03% -2.78%
Apr 2001 15.05% 19.71%
Mar 2001 -11.79% -3.72%
Feb 2001 4.72% -10.69%
Jan 2001 -5.56% 31.76%
Dec 2000 9.20% -9.09%
Nov 2000 12.97% -4.94%
Oct 2000 -12.30% -12.44%
Sep 2000 -4.31% -14.77%
Aug 2000 0.00% 17.51%
Jul 2000 -12.37% 2.62%
Jun 2000 0.34% 2.04%
May 2000 8.46% -3.58%
Apr 2000 7.17% -5.51%
Mar 2000 -3.83% 14.84%
Feb 2000 -0.38% -8.36%
Jan 2000 -7.42% 4.06%
Average Return
Standard Deviation

Chart 1

Risky Opportunity Set, Capital Allocation Line, and Indifference Curve

Risk Free

0 2.3E-3 Risky Asset 1

0.13 2.1000000000000001E-2 Risky Asset 2

0.08 1.2999999999999999E-2 Complete Portfolio

4.4841306731903098E-2 1.0342971157698472E-2 Optimum Portfolio

7.7808710077419324E-2 1.6256176939981118E-2 Portfolio Frontier 0.72670489196096655 0.24984995497297974 0.15987995496621835 0.14773828210724529 0.13592056503708333 0.12451907484397723 0.11365913953571882 0.10351135203445079 9.4305885288246918E-2 8.6344658201882932E-2 7.9999999999999946E-2 7.5679587736720635E-2 7.3740084079149254E-2 7.4368003872633287E-2 7.7500967735893461E-2 8.2855295545909438E-2 9.0035548535009224E-2 9.8643803657401621E-2 0.10834020491027328 0.11885873968707561 0.13 0.14161708936424308 0.15360208331920502 0.3231485726411305 1.0090713552569015 -2.700000000000001E-2 1.0000000000000009E-3 6.5999999999999982E-3 7.3999999999999986E-3 8.199999999999999E-3 8.9999999999999993E-3 9.7999999999999962E-3 1.0599999999999998E-2 1.1400000000000007E-2 1.2200000000000009E-2 1.3000000000000008E-2 1.3800000000000007E-2 1.46E-2 1.5399999999999999E-2 1.6199999999999999E-2 1.7000000000000001E-2 1.78E-2 1.8599999999999998E-2 1.9400000000000001E-2 2.0199999999999999E-2 2.1000000000000001E-2 2.18E-2 2.2600000000000002E-2 3.3000000000000002E-2 7.3000000000000009E-2 CML 0 0.77808710077419319 2.3E-3 0.14186176939981118 Indifference Curve 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 6.3214855788492359E-3 6.5214855788492355E-3 7.1214855788492362E-3 8.1214855788492354E-3 9.5214855788492356E-3 1.1321485578849237E-2 1.3521485578849236E-2 1.6121485578849237E-2 1.9121485578849236E-2 2.2521485578849233E-2 2.6321485578849238E-2 3.0521485578849233E-2 3.5121485578849233E-2 4.0121485578849238E-2 4.552148557884924E-2 5.1321485578849232E-2 The Risky Asset 1 and 2

0 0.13 0.08 2.3E-3 2.1000000000000001E-2 1.2999999999999999E-2

Standard Deviation (s)

Expected Return / Month