In this assignment you'll solve the portfolio choice problem in an investment universe with five risky and one risk-free asset. Each problem below forms a part in this overarching exercise. Go to the Questions-sheet in your excel file and start solving the problems now. Throughout, you may assume that there are 252 trading days in a year. A. Your first step is to find the statistical properties of your assets. In cells C4 to G4 enter functions that estimate the expected daily return of all assets. Hint: The appropriate estimator for the mean of a distribution is the sample mean: µˆ = 1 N P N i=1 xi . Time series of historic returns are available in the Daily returns sheet. The excel-function AVERAGE is useful for such calculations. B. In cells C5 to G5 enter functions that annualize your daily estimates of the expected returns, i.e. express them on an annual basis. Hint: You are trying to find the return you would have if you got your estimated daily return every trading day of the year, i.e. 252 days in a row. C. In cells C6 to G6 enter functions that estimate the (annual) excess return of all assets using your values in cells C5 to G5. Hint: To annualize excess returns you must take the difference between the annualized returns, not annualize their difference. D. In cells C7 to G7 enter functions that estimate the daily variance of returns for all assets. Hint: Remember that you are estimating the variance from a random sample rather than from the entire population. The appropriate estimator for the variance of a distribution from sample data is: σˆ 2 X = 1 N−1 P N i=1 (xi − µˆ) 2 Time series of historic returns are available in the Daily returns sheet. The excel-function VAR is useful for such calculations. E. In cells C8 to G8 enter functions that annualize your estimates of the daily variance of returns for all assets. You may assume that daily returns are independent. That is a standard assumption that is appropriate in an efficient market, which we will discuss in a coming lecture. It turns out 4that this calculation is cumbersome. A standard (and reasonable) approximation for the annualized variance is to multiply the daily variance with the number of trading days, i.e. 252. F. In cells C9 to G9 enter functions for the annualized standard deviation of returns. From here on we will only be concerned with the annualized variables. G. In cells C10 to G10 enter functions for the annual Sharpe-ratio for all stocks. H. Having estimated the statistical properties of the assets you can invest in, it is now time to combine them into some interesting portfolios. You'll do this in the "Portfolio lab", i.e. the area B13:H25. The top half of this area already contains covariance matrix for a portfolio specified by the weight in cells C22 to G22. By changing the cells, you'll can consider different portfolios. Although there are five risky assets, it is enough to specify the portfolio weights in the first four, as the fifth weight will be implicit. Cell G22 already contains a formula that makes sure that your weights will always sum to one, and you don't have to change that. Enter functions in cells C23 to C26 that calculate the properties of the portfolio defined by the weights in cells C22 to G22. I. Find the efficient frontier by finding portfolio weights that minimize the portfolio variance for each of the given portfolio returns in cells B31 to B36. Use the Solver and the portfolio lab to solve the six associated constrained minimization problems. Copy each solution to the corresponding cells in columns C to G. Also make sure to copy the resulting standard deviation to the corresponding cell in column H (and take care to copy the value as opposed to the formula). The efficient frontier will automatically plot in the graph. J. Now use the Solver and the portfolio lab to find the optimal risky portfolio, P ∗ , in this market. Copy the optimal portfolio weights to cells C41 to G41 and the associated portfolio properties to cells C42 to C45 K. It is finally time to find the optimal complete portfolio. Assuming that you have preferences represented by the utility function U = E(r)− 1 2Aσ2 and a risk aversion parameter A = 2, enter a function in cell C52 that calculates the fraction of your wealth that you should invest in the optimal 5risky portfolio. Enter the resulting portfolio properties in cells C53 and C54, and enter you resulting utility level in cell C55. Note that the utility function used here assumes that you express E(r) and σ in decimal form, e.g. 0.2 for 20%. L. Find the optimal complete portfolio again, but use a numerical solution method this time. That is, specify appropriate formulas in cells D53 to D55 and use the Solver to find the optimal value in cell D52.