Task

Part A $$ The basics

$1.$ Use Excel functions to compute monthly summary statistics for the six stocks $-$ mean return, sample standard deviation, sample variance, and correlations $($VBA not required$).$

$2.$ Write your own VBA function to compute the variance$-$covariance matrix for the six stocks. The function needs to be able to handle matrices of different size. Note: If you are unable to complete the VBA function, generate the sample variance$-$covariance matrix using Covariance in Data Analysis so you can complete the other tasks. Full marks are earned if your program does not rely on calling Excel worksheet functions like COVAR from VBA.

$3.$ Generate an efficient frontier allowing for short sales. You will need to use matrix multiplications to compute portfolio risk and return.

$4.$ Assume the risk$-$free interest rate is $0\mathrm{.}2\%($return per month$).$ Identify the global minimum variance portfolio and the tangent portfolio based on the given risk$-$free rate on the efficient frontier. Draw the efficient frontier and Capital Market Line.

$5.$ Extend Questions $3$ & $4$ not allowing for short selling. You need to use Excel Solver for this task. VBA is not required. Note: Optimal portfolios are those with the highest Sharpe ratio given the risk$-$free rate.

$6.$ Extend Questions $3,4$ & $5$ by using considerable VBA programming $($calling Solver from VBA is a suitable solution$)$ to automate the process.

Part B $$ Simulating feasible portfolios

$1.$ Write a VBA subroutine to generate at least $500$ feasible portfolios. In your subroutine:

a$.$ Generate six random numbers

b$.$ Assign the jth random number divided by the sum of the six random numbers as the weight for stock j$.$

$2.$ Plot the simulated portfolios as a scatter graph in the risk$-$return diagram $($together with efficient frontier previously generated in Part A$).$ Both the minimum variance and highest Sharpe ratio portfolios should be highlighted on your graph.

$3.$ The user has a choice for basically an unlimited number of simulations. Improve the efficiency and speed of your model is crucial, and we intend to test this feature with more than $500$ portfolios.

Part C $$ User interface and layout

$1.$ Design a friendly user interface with Excel form controls and allow users to choose:

a$.$ Different level of interest rates. Note: This will affect the Capital Market Line and the tangent portfolio.

b$.$ The user is able to choose whether to allow short selling.

c$.$ All inputs and outputs are clearly labelled and shown on the same Excel worksheet and the model is well structured without excess options.

$2.$ Formulas and subroutines are linked so the model can handle changes in data $($assuming replacing one stock with another with the same number of observations$).$

$3.$ Think about other $($additional$)$ features you want to add and improve your portfolio model.

Part D $$ Comments and structure

$1.$ Provide instructions and highlight features $($other than those required$)$ of your model. Write your comments in the textbox $($insert$->$textbox$).$

$2.$ Add comments to your VBA code, especially on any special techniques employed.

$3.$ Properly formatting and indenting your code. To indent code, simply press the TAB key. Press the tab key again to add a second code indention. Generally, you want to add one indentation for each code block such as IF statements and Loops.

Stock$1$ Stock$2$ Stock$3$ Stock$4$ Stock$5$ Stock$6$

$37\mathrm{.}485\mathrm{.}32120\mathrm{.}5504\mathrm{.}1835\mathrm{.}47$

$37\mathrm{.}075\mathrm{.}48124\mathrm{.}5644\mathrm{.}2937\mathrm{.}5$

$32\mathrm{.}765\mathrm{.}21125604\mathrm{.}3635\mathrm{.}82$

$32\mathrm{.}75\mathrm{.}4125\mathrm{.}5804\mathrm{.}6137\mathrm{.}56$

$34\mathrm{.}885\mathrm{.}18138\mathrm{.}568\mathrm{.}55\mathrm{.}1935\mathrm{.}99$

$31\mathrm{.}374\mathrm{.}2514570535\mathrm{.}01$

$34\mathrm{.}644\mathrm{.}52156865\mathrm{.}1637\mathrm{.}59$

$35\mathrm{.}754\mathrm{.}74153\mathrm{.}5965\mathrm{.}4238\mathrm{.}18$

$35\mathrm{.}744\mathrm{.}61156\mathrm{.}51005\mathrm{.}6538\mathrm{.}31$

$37\mathrm{.}664\mathrm{.}741651015\mathrm{.}4238\mathrm{.}82$

$37\mathrm{.}394\mathrm{.}66163\mathrm{.}51205\mathrm{.}3537\mathrm{.}4$

$37\mathrm{.}994\mathrm{.}391671285\mathrm{.}338\mathrm{.}9$

$36\mathrm{.}574\mathrm{.}27181\mathrm{.}51435\mathrm{.}0337\mathrm{.}38$

$38\mathrm{.}384\mathrm{.}831801324\mathrm{.}937\mathrm{.}95$

$36\mathrm{.}474\mathrm{.}98179\mathrm{.}51144\mathrm{.}7539\mathrm{.}02$

$37\mathrm{.}755\mathrm{.}051661105\mathrm{.}4240\mathrm{.}8$

$37\mathrm{.}015\mathrm{.}291751155\mathrm{.}4142\mathrm{.}17$

$35\mathrm{.}95\mathrm{.}3176\mathrm{.}5122\mathrm{.}55\mathrm{.}4841\mathrm{.}07$

$38\mathrm{.}685\mathrm{.}491881355\mathrm{.}5142\mathrm{.}52$

$36\mathrm{.}675\mathrm{.}88179\mathrm{.}51225\mathrm{.}7142\mathrm{.}7$

$33\mathrm{.}875\mathrm{.}461811095\mathrm{.}6840\mathrm{.}6$

$33\mathrm{.}965\mathrm{.}85176\mathrm{.}51025\mathrm{.}9240\mathrm{.}13$

$30\mathrm{.}925\mathrm{.}64183\mathrm{.}51056\mathrm{.}2535\mathrm{.}75$

$29\mathrm{.}375\mathrm{.}51821136\mathrm{.}9438\mathrm{.}01$

$29\mathrm{.}265\mathrm{.}77185123\mathrm{.}58\mathrm{.}0834\mathrm{.}28$

$33\mathrm{.}656\mathrm{.}71771288\mathrm{.}2335\mathrm{.}18$

$31\mathrm{.}036\mathrm{.}44178\mathrm{.}5129\mathrm{.}59\mathrm{.}334\mathrm{.}52$

$31\mathrm{.}976\mathrm{.}44177\mathrm{.}51278\mathrm{.}0735\mathrm{.}05$

$29\mathrm{.}596\mathrm{.}661891267\mathrm{.}736\mathrm{.}63$

$27\mathrm{.}056\mathrm{.}021771277\mathrm{.}2734\mathrm{.}23$

$26\mathrm{.}456\mathrm{.}61188\mathrm{.}5136835\mathrm{.}65$

$25\mathrm{.}185\mathrm{.}95192\mathrm{.}51267\mathrm{.}3132\mathrm{.}26$

$22\mathrm{.}225\mathrm{.}561861257\mathrm{.}2728\mathrm{.}93$

$23\mathrm{.}025\mathrm{.}731851398\mathrm{.}0429\mathrm{.}61$

$18\mathrm{.}095\mathrm{.}81187\mathrm{.}51168\mathrm{.}3330\mathrm{.}11$

$17\mathrm{.}865\mathrm{.}831901107\mathrm{.}4428\mathrm{.}72$

$15\mathrm{.}355\mathrm{.}371781078\mathrm{.}0527\mathrm{.}95$

$15\mathrm{.}575\mathrm{.}321661008\mathrm{.}0925\mathrm{.}35$

$16\mathrm{.}865\mathrm{.}79168\mathrm{.}51007\mathrm{.}4325\mathrm{.}96$

$20\mathrm{.}685\mathrm{.}88171101\mathrm{.}57\mathrm{.}4628\mathrm{.}34$

$19\mathrm{.}085\mathrm{.}64163\mathrm{.}5103\mathrm{.}58\mathrm{.}1227\mathrm{.}4$

$18\mathrm{.}655\mathrm{.}16162101\mathrm{.}58\mathrm{.}326\mathrm{.}84$

$19\mathrm{.}525\mathrm{.}81161105\mathrm{.}59\mathrm{.}226\mathrm{.}53$

$20\mathrm{.}435\mathrm{.}26150110\mathrm{.}58\mathrm{.}9728\mathrm{.}63$

$22\mathrm{.}385\mathrm{.}28150\mathrm{.}51128\mathrm{.}4828\mathrm{.}62$

$23\mathrm{.}074\mathrm{.}571541247\mathrm{.}8428\mathrm{.}37$