WFNAN 491 Computational Finance Using Excel and Mathematica Problem Set 1: Efficient Frontier from a choice of Common Stocks and a Default Risk Free Asset The calculator finds the efficient frontier from a group investments assuming the variances and covariances are constant and that the investments can't be sold short. 1 Refer to the spreadsheet titled Efficient Frontier. 2 The data that must be input to the program: a. Variance-Covariance matrix computed from the returns of each investment. In the example the this matrix is of 6 stocks. This matrix is named VarCov (see Insert/Name/Define) b. The mean returns at an annual rate from the data on investment returns. 3 The locations of the variables that must be changed using the Insert/Name/Define function are: Constant Means nsec Portfolio Mean Portfolio Sigma RESULTS theta Total VarCov weights x_1 x_2 x_3 x_4 x_5 x_6 x_7 x_8 x_9 x_10 x_11 x_12 x_13 x_14 x_15 etc For example, the location of VarCov is the range B6:G11. If you expand the number of investments to 15 from 6, the range of VarCov would be: B6:P20. These ranges are changed by using the Insert/Names/Define function by entering the new range for VarCov. 4 The program to compute the effcient frontier is executed by entering Ctrl-A. NOT Working The proportions for each of the constants (risk-free or borrowing and lending rates) is given in the RESULTS table. 5 To find the efficient asset allocation shares for any borrowing-lending rate, enter the borrowing-lending rate value as the constant. For the example this is cell c23. Enter the value in percent annual rate. Once these entries have been made, go to Data/Solver and run the Solver program. The results for the allocations associated with this rate will be the cells to the right of the x_i, starting in cell C38 in the example. 6 Chart 1 plots the columns Sigma and Mean giving the efficient frontier. Note: All return and constants variables are entered as percent. Your Problem 1. Select a portfolio of stocks, compute their average returns over a 5 year period using daily returns, find the means and covariance among these returns stated as an annual rate, and a default risk free rate. 2. Enter these data in the program. Remember the VarCov is entered as a table and referred to in computation as a table using Ctrl-Shift-Enter in matrix functions. 3. Change the risk free rate to see how your efficient stock allocations (x's) change and report these results for at least 5 risk free rates. 4. Graph the frontier using at least 5 risk free rates.