Answer Happy

You have just received a positive response from Merrill Lynch ona recent graduate ‘Financial Analyst’ role you had applied for andyou are invited to a job interview. The job specification includes,among other things, knowledge of VBA at introductory level, butwith the prospect of further training within the organisation. Ifsuccessful you will work in a recently re-organisedasset pricingdepartment and if you were to be successful, you’d work as part ofa team dealing with financial analysis and asset pricing that alsoinvolves computational work. Your prospective employer has prepareda trial test for you as part of the interview, entirely based onintroductory VBA programming. You’d be expected to be tested onprogramming skills in VBA, where a specific task is given that mustbe coded within a limited amount of time to a problemspecification. You are expected to code the solution withuser-defined functions, but no object orientation, or graphicaluser interface(GUI). Make use of good VBA-style comments, variabledeclaration, initialization, data processing, control flowstatements, function cohesion, and coupling. You arealso expectedto demonstrate that you understand both the syntax, functiondesign, and have good and timely programming skills in VBA. PROBLEMSPECIFICATION Consider a scenario where investor can select anumber of investments to be part of a financial portfolio. Your VBAprogram should be able to process that information and create theinitial Excel layout with either default values or values you enterthrough prompts: VBA program should be able to compute the V(variance covariance) matrix values: 𝜎2 𝜎𝜎 𝑉=[ 1 1 2] 𝜎𝜎 𝜎2 212 VBAprogram should compute the capital weights for an n-securityportfolio inferred by the following Lagrange system: 2|PageSecurity standard deviation, covariance, return rate, capitalweight, and Lagrange multipliers are denotes by i, ij, E(Ri), xi,, and v, respectively. This system may be applied regardless ofthe size of the portfolio. Where the number of securities underconsideration for the portfolio equals n, the square coefficientsmatrix will have n+2 rows and n+2 columns. The VBA program shouldsolve for the system of capital weights and Lagrange multipliers;optimal weights in a n-security portfolio - I used n=2 forillustration here, but n can be any value, preferable between 2 and15. Use the target return of your choice, securities’ expectedreturns, standard deviations, and covariances. VBA program shouldcompute the parameters α, β, γ: α = eTV−1e, β = eTV−1r, γ = rTV−1re: is a unit vector, refer to it as an n x 1 matrix for computationpurposes. Subscript T indicates a transposed unit vector i.e.converted to an 1 x n matrix. V: is the variance covariance matrixr: rates of return vector. Consider it an n x 1 matrix forcomputation purposes. ), subscript T indicates a transposed unitvector (converted to an 1 x n matrix). These parameters arenormally used to compute the global minimum, diversified, andminimum variance portfolios: such portfolio points are not requiredto be computed here. You may start with any number of funds in theportfolio and for the purpose of computing the return and risk ofthe portfolio, you would need the capital weights. The capitalweights are computed using the Lagrange system. The sum of allcapital weights should be 1.00. The values should be stored inone-dimensional arrays x1(), x2(), ...., etc. VBA program shouldcompute the expected return, volatility, quadratic utility, andsharp ratio of 3|Page the portfolio using the followingcorresponding expressions: Portfolio expected rate of return:Portfolio volatility: Portfolio quadratic utility in expectation:Sharp Ratio: 𝐸(𝑟𝑝) = xTr where x and r are capital weight and fundreturns vectors, respectively. 𝜎p =(xTVx)0.5 where xT is thetransposed capital weights vector, V the variance- covariancematrix, and x the capital weights vector. 𝐸(𝑢𝑝) = 𝐸(𝑟𝑝) − 1 𝐴𝜎2 𝑃(*) A is the risk aversion coefficient 𝐸(𝑟𝑝) − 𝐸(𝑟𝑓) 𝜎𝑝 2 𝑆𝑃 = Foreach weight set, program should compute portfolio expected rate ofreturn, volatility, quadratic utility, and Sharp ratio. Thecomputed portfolio returns and volatilities should be storedinarrays rp(), and vp(), respectively. Program should then find theportfolio with the highest utility and highest sharp ratio. VBAprogram should use a range of target portfolio returns from 0% to100% and the Lagrange system to compute the capital weightsolutions. Portfolio risk should be computed using the Markowitzformula given above. 4|Page VBA program should also include arecorded macro that plots the mean-variance efficient frontier5|Page and capital allocation line as shown below: You should alsocode a choice to increase the number of assets in portfolio by anynumber of assets. Your VBA program should have the capabilities toinsert the relevant rows and columns in the macro-enabled excelfile to accommodate for the data of the new assets. Your programshould be organised in such a way that some of it is done by macros(subs) and the rest in dedicated user-defined functions of yourchoice, but not less than 4 user defined functions. Make good useof the VBA’s object model as well as cohesion and couplingprogramming principles. The finished product must contain at leastfour well designed user-defined functions. There is only oneprogram file you are expected to submit within a macro-enabledexcel workbook file. The program should output the capital weightvalues within 4 decimal points and in a field with a reasonablecharacter width, as well as the portfolio expected return,volatility, utility, and sharp ratio within a reasonable characterwidth and 4 decimal places. The values should be sorted from thelowest portfolio return to the highest. It should print out themaximum utility and sharp ratio, clearly indicating thecorresponding portfolio with its return and volatility attributes.The input must be robustly validated with error handling and theoutput should be properly formatted. You must produce a runningprogram saved in a Microsoft Excel Macro-Enabled Worksheet (.xlsm)file named “studentID.xlsm”. You may make use of your own recordedmacros to plot the portfolio mean-variance efficient frontier, CML,and identify the market portfolio, based on the program output datain the Macro enabled excel file. This would allow you to check withliterature that the shape of the graph is as describedinliterature. You may wish to note the maximum utility and sharpratio in the graph. You do not need to produce any written work onthe theorized financial portfolio elements. Focus is on theprogramming skills. You have flexibility on how you organise thecode, and the layout of data in Excel; both input and output shouldbe in the same macro enabled excel workbook file. However payattention to details and programming principles applied. Theprogram must run in order to be marked.