Write VBA code

4. In the lab, we have created the VBA function, myConstantCorr, to calculate the constant correlation variance-covariance matrix. This function takes two parameters: a matrix of historical returns and the average correlation. Let us get rid of the second parameter and write a new VBA function, ConstantRhoHistthat only takes one parameter: the matrix of historical returns. The average correlation among all stocks should be calculated inside this function(include all correlations less than 100%). Use ConstantRhoHistand the return data in Tab Q4to construct the constant correlation var-cov matrix. Use “Option Base 1” to finish this question. (Hint: you can reuse any functions we derived in the lab. 5. Using the functions ConstantRhoHist from step 4 (which computes the constant correlation var-covar matrix) and the functions SIM and Var Covar (which compute the single index model and sample var-covar matrices used in prior labs), create a new function named Shrinkage(ASSET_RETURNS, CHOICE1,CHOICE2,LAMBDA,[MKT_RET]). This function creates the Shrinkage variance-covariance matrix seen in Topic2 that gives weight LAMBDA to the var-covar matrix specified in CHOICE1 and weight (1-LAMBDA) to the var-covar matrix specified in CHOICE2. The choices specified in the input parameters CHOICE1and CHOICE2can be “sample”, “SIM”, or “constant-corr” to indicate which variance-covariance matrix to choose [login to view URL] ASSET_RETURNSis a matrix of asset [login to view URL], the input MKT_RET is an optional parameter that provides the series of historical returns of the stock market portfolio that is needed if computing the SIM var-covar matrix. Use the Shrinkagefunction and the asset and market return data in Tab Q5to estimate the shrinkage variance-covariance matrices that result from the following choices: a)CHOICE1=“sample” , CHOICE2=“SIM” , LAMBDA=0.25 b)CHOICE1=“sample” , CHOICE2=“constant-corr” , LAMBDA=0.60 c)CHOICE1=“SIM” , CHOICE2=“constant-corr” , LAMBDA=0.75 Lastly, when calling upon your function make sure to only add the optional parameter as an input to your function if needed for the computation, otherwise leave it blank.