You have just received a positive response from Merrill Lynch on a recent graduate ‘Financial Analyst’ role you had appl
Posted: Thu Jul 14, 2022 2:16 pm
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.
PROBLEM SPECIFICATION
Consider a scenario where investor can select a number ofinvestments to be part of a financial portfolio. Your VBA programshould be able to process that information and create the initialExcel layout with either default values or values you enter throughprompts:
VBA program should be able to compute the V (variancecovariance) matrix values:
𝜎2 𝜎𝜎 𝑉=[ 1 1 2]
𝜎𝜎 𝜎2 212
VBA program should compute the capital weights for an n-securityportfolio inferred by the following Lagrange system:
2|Page
Security 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 of the size of theportfolio. Where the number of securities under consideration forthe portfolio equals n, the square coefficients matrix will haven+2 rows and n+2 columns.
The VBA program should solve for the system of capital weightsand Lagrange multipliers; optimal weights in a n-security portfolio- I used n=2 for illustration here, but n can be any value,preferable between 2 and 15. Use the target return of your choice,securities’ expected returns, standard deviations, andcovariances.
VBA program should compute the parameters α, β, γ:α = eTV−1e, β = eTV−1r, γ = rTV−1r
e: is a unit vector, refer to it as an n x 1 matrix forcomputation purposes. Subscript T indicates a transposed unitvector 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 unit vector (converted to an1 x n matrix).
These parameters are normally used to compute the globalminimum, diversified, and minimum variance portfolios: suchportfolio points are not required to be computed here.
You may start with any number of funds in the portfolio and forthe purpose of computing the return and risk of the portfolio, youwould need the capital weights. The capital weights are computedusing the Lagrange system. The sum of all capital weights should be1.00. The values should be stored in one-dimensional arrays x1(),x2(), ...., etc.
VBA program should compute the expected return, volatility,quadratic utility, and sharp ratio of
3|Page
the portfolio using the following corresponding expressions:
Portfolio expected rate of return:
Portfolio volatility:
Portfolio quadratic utility in expectation:
Sharp Ratio:
𝐸(𝑟𝑝) = xTr
where x and r are capital weight and fund returns vectors,respectively.
𝜎p =(xTVx)0.5
where xT is the transposed capital weights vector, V thevariance- covariance matrix, and x the capital weights vector.
𝐸(𝑢𝑝) = 𝐸(𝑟𝑝) − 1 𝐴𝜎2
𝑃
(*) A is the risk aversion coefficient
𝐸(𝑟𝑝) − 𝐸(𝑟𝑓) 𝜎𝑝
2
𝑆𝑃 =
For each weight set, program should compute portfolio expectedrate of return, 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.
VBA program should use a range of target portfolio returns from0% to 100% 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 a recorded macro that plots themean-variance efficient frontier
5|Page
and capital allocation line as shown below:
You should also code a choice to increase the number of assetsin portfolio by any number of assets. Your VBA program should havethe capabilities to insert the relevant rows and columns in themacro-enabled excel file to accommodate for the data of the newassets.
Your program should be organised in such a way that some of itis done by macros (subs) and the rest in dedicated user-definedfunctions of your choice, but not less than 4 user definedfunctions. Make good use of the VBA’s object model as well ascohesion and coupling programming principles. The finished productmust contain at least four well designed user-defined functions.There is only one program file you are expected to submit within amacro-enabled excel workbook file.
The program should output the capital weight values within 4decimal points and in a field with a reasonable character width, aswell as the portfolio expected return, volatility, utility, andsharp ratio within a reasonable character width and 4 decimalplaces. The values should be sorted from the lowest portfolioreturn to the highest. It should print out the maximum utility andsharp ratio, clearly indicating the corresponding portfolio withits return and volatility attributes. The input must be robustlyvalidated with error handling and the output should be properlyformatted.
You must produce a running program saved in a Microsoft ExcelMacro-Enabled Worksheet (.xlsm) file named “studentID.xlsm”. Youmay make use of your own recorded macros to plot the portfoliomean-variance efficient frontier, CML, and identify the marketportfolio, based on the program output data in the Macro enabledexcel file. This would allow you to check with literature that theshape of the graph is as describedin literature. You may wish tonote the maximum utility and sharp ratio in the graph. You do notneed to produce any written work on the theorized financialportfolio elements. Focus is on the programming skills.
You have flexibility on how you organise the code, and thelayout of data in Excel; both input and output should be in thesame macro enabled excel workbook file. However pay attention todetails and programming principles applied.
The program must run in order to be marked.
PROBLEM SPECIFICATION
Consider a scenario where investor can select a number ofinvestments to be part of a financial portfolio. Your VBA programshould be able to process that information and create the initialExcel layout with either default values or values you enter throughprompts:
VBA program should be able to compute the V (variancecovariance) matrix values:
𝜎2 𝜎𝜎 𝑉=[ 1 1 2]
𝜎𝜎 𝜎2 212
VBA program should compute the capital weights for an n-securityportfolio inferred by the following Lagrange system:
2|Page
Security 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 of the size of theportfolio. Where the number of securities under consideration forthe portfolio equals n, the square coefficients matrix will haven+2 rows and n+2 columns.
The VBA program should solve for the system of capital weightsand Lagrange multipliers; optimal weights in a n-security portfolio- I used n=2 for illustration here, but n can be any value,preferable between 2 and 15. Use the target return of your choice,securities’ expected returns, standard deviations, andcovariances.
VBA program should compute the parameters α, β, γ:α = eTV−1e, β = eTV−1r, γ = rTV−1r
e: is a unit vector, refer to it as an n x 1 matrix forcomputation purposes. Subscript T indicates a transposed unitvector 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 unit vector (converted to an1 x n matrix).
These parameters are normally used to compute the globalminimum, diversified, and minimum variance portfolios: suchportfolio points are not required to be computed here.
You may start with any number of funds in the portfolio and forthe purpose of computing the return and risk of the portfolio, youwould need the capital weights. The capital weights are computedusing the Lagrange system. The sum of all capital weights should be1.00. The values should be stored in one-dimensional arrays x1(),x2(), ...., etc.
VBA program should compute the expected return, volatility,quadratic utility, and sharp ratio of
3|Page
the portfolio using the following corresponding expressions:
Portfolio expected rate of return:
Portfolio volatility:
Portfolio quadratic utility in expectation:
Sharp Ratio:
𝐸(𝑟𝑝) = xTr
where x and r are capital weight and fund returns vectors,respectively.
𝜎p =(xTVx)0.5
where xT is the transposed capital weights vector, V thevariance- covariance matrix, and x the capital weights vector.
𝐸(𝑢𝑝) = 𝐸(𝑟𝑝) − 1 𝐴𝜎2
𝑃
(*) A is the risk aversion coefficient
𝐸(𝑟𝑝) − 𝐸(𝑟𝑓) 𝜎𝑝
2
𝑆𝑃 =
For each weight set, program should compute portfolio expectedrate of return, 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.
VBA program should use a range of target portfolio returns from0% to 100% 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 a recorded macro that plots themean-variance efficient frontier
5|Page
and capital allocation line as shown below:
You should also code a choice to increase the number of assetsin portfolio by any number of assets. Your VBA program should havethe capabilities to insert the relevant rows and columns in themacro-enabled excel file to accommodate for the data of the newassets.
Your program should be organised in such a way that some of itis done by macros (subs) and the rest in dedicated user-definedfunctions of your choice, but not less than 4 user definedfunctions. Make good use of the VBA’s object model as well ascohesion and coupling programming principles. The finished productmust contain at least four well designed user-defined functions.There is only one program file you are expected to submit within amacro-enabled excel workbook file.
The program should output the capital weight values within 4decimal points and in a field with a reasonable character width, aswell as the portfolio expected return, volatility, utility, andsharp ratio within a reasonable character width and 4 decimalplaces. The values should be sorted from the lowest portfolioreturn to the highest. It should print out the maximum utility andsharp ratio, clearly indicating the corresponding portfolio withits return and volatility attributes. The input must be robustlyvalidated with error handling and the output should be properlyformatted.
You must produce a running program saved in a Microsoft ExcelMacro-Enabled Worksheet (.xlsm) file named “studentID.xlsm”. Youmay make use of your own recorded macros to plot the portfoliomean-variance efficient frontier, CML, and identify the marketportfolio, based on the program output data in the Macro enabledexcel file. This would allow you to check with literature that theshape of the graph is as describedin literature. You may wish tonote the maximum utility and sharp ratio in the graph. You do notneed to produce any written work on the theorized financialportfolio elements. Focus is on the programming skills.
You have flexibility on how you organise the code, and thelayout of data in Excel; both input and output should be in thesame macro enabled excel workbook file. However pay attention todetails and programming principles applied.
The program must run in order to be marked.