PLEASE SHOW ME EXCEL FORMULAS WHERE APPLICABLE TO FULLY GRASP CONCEPT.

Case #3: Building an efficient portfolio 1. Go to finance.yahoo.com and download monthly prices for S\&P500 and the following 10 securities: AMZN, GE, 10-Year-T-Bond, CAT, AA, DIS, JACK, QCOM, AAPL and SD, and in addition, the dates corresponding to these prices. Put them all into 1 spreadsheet. Use Adj Close data only (far right column). For consistency, pull historical data covering the following 60 month period: from 12/03/2007 to 11/1/2012 (these are your first and last monthly observation). At this step, your spreadsheet should contain 12 columns: DATE, S\&P price and 10 other securities' prices. 2. Convert prices into returns and delete price columns (use copy values to preserve returns, otherwise they'll disappear when you delete price columns). Keep only latest 60 monthly returns. Including your labels in 1st row, this will make your total dataset N=61. 3. Decompose the variance of each security and determine what proportion of the total risk of each is diversifiable. Recall that total variance = beta 2 Variance(market)+varianc(residuals). 4. Find a set of optimal portfolio weights among these assets and graph the frontier. a. Compute expected return (using CAPM) and standard deviation for each security. Assume risk free rate of 2% and market risk premium of 7%. b. Create variance-covariance matrix for the 10 securities in question. c. Create an array of weights (assign 0.1 or anything else for initial values). d. Create Expected portfolio return cell (=mmult(weights,array of expected returns of individual assets)). Make sure that matrix dimensions are in order - remember, you want to multiply 110 by 101 to get a scalar 1x1. Use =transpose where appropriate). Finally, evaluate the cell using Command Enter on a Mac and SHIFT CTRL ENTER on a PC. e. Create portfolio variance cell (=mmult(mmult(weights,var-covar matrix), weights). Here again, dimensions are crucial - first set of weights must be in 110 form, var-covar matrix is always square (10x10 in this case), so no problem, and last set of weights must be of 10x1 dimension (will need to use = transpose here). Finally, evaluate the cell using Command Enter on a Mac and SHIFT CTRL ENTER on a PC. f. Create cell constraint =sum(weights). We'll use this to make sure all weights sum up to 100% or 1. g. Go to solver and follow these steps: i. Set objective cell to your portfolio variance cell. ii. Set the value to be Min (minimized). iii. In "By changing cells" select your weights. iv. In "Subject to the Constraints" ADD a constraint that your cell where your constraint is computing =1. v. Make sure that Non-Negativity of solutions is checked OFF. vi. Hit SOLVE and observe what values your weights have been changed into. vii.Copy-paste VALUES into a separate table - the above weights pertain to minimum variance portfolio entry. viii.Go back to solver and ADD another constraint - now set the cell that is computing expected return to a fixed value, starting at 9%. Re-solve for the weights and copy/ paste them in the table of results. Case #3: Building an efficient portfolio ix. Re-do above step by changing constraint to capture different returns of the portfolio to up to and ABOVE exp return of the highest individual security, in 3% increments. So, have the following computed: min variance portfolio, 9%,12%,15%,18% and 21% return portfolios. x. Graph the frontier: portfolio standard deviation on the x-axis, portfolio expected return on the y-axis. xi. After you are done graphing the frontier (by following above steps), to the graph that contains the frontier, add expected return and stdev of each individual asset and see where they map relative to the frontier. Case #3: Building an efficient portfolio 1. Go to finance.yahoo.com and download monthly prices for S\&P500 and the following 10 securities: AMZN, GE, 10-Year-T-Bond, CAT, AA, DIS, JACK, QCOM, AAPL and SD, and in addition, the dates corresponding to these prices. Put them all into 1 spreadsheet. Use Adj Close data only (far right column). For consistency, pull historical data covering the following 60 month period: from 12/03/2007 to 11/1/2012 (these are your first and last monthly observation). At this step, your spreadsheet should contain 12 columns: DATE, S\&P price and 10 other securities' prices. 2. Convert prices into returns and delete price columns (use copy values to preserve returns, otherwise they'll disappear when you delete price columns). Keep only latest 60 monthly returns. Including your labels in 1st row, this will make your total dataset N=61. 3. Decompose the variance of each security and determine what proportion of the total risk of each is diversifiable. Recall that total variance = beta 2 Variance(market)+varianc(residuals). 4. Find a set of optimal portfolio weights among these assets and graph the frontier. a. Compute expected return (using CAPM) and standard deviation for each security. Assume risk free rate of 2% and market risk premium of 7%. b. Create variance-covariance matrix for the 10 securities in question. c. Create an array of weights (assign 0.1 or anything else for initial values). d. Create Expected portfolio return cell (=mmult(weights,array of expected returns of individual assets)). Make sure that matrix dimensions are in order - remember, you want to multiply 110 by 101 to get a scalar 1x1. Use =transpose where appropriate). Finally, evaluate the cell using Command Enter on a Mac and SHIFT CTRL ENTER on a PC. e. Create portfolio variance cell (=mmult(mmult(weights,var-covar matrix), weights). Here again, dimensions are crucial - first set of weights must be in 110 form, var-covar matrix is always square (10x10 in this case), so no problem, and last set of weights must be of 10x1 dimension (will need to use = transpose here). Finally, evaluate the cell using Command Enter on a Mac and SHIFT CTRL ENTER on a PC. f. Create cell constraint =sum(weights). We'll use this to make sure all weights sum up to 100% or 1. g. Go to solver and follow these steps: i. Set objective cell to your portfolio variance cell. ii. Set the value to be Min (minimized). iii. In "By changing cells" select your weights. iv. In "Subject to the Constraints" ADD a constraint that your cell where your constraint is computing =1. v. Make sure that Non-Negativity of solutions is checked OFF. vi. Hit SOLVE and observe what values your weights have been changed into. vii.Copy-paste VALUES into a separate table - the above weights pertain to minimum variance portfolio entry. viii.Go back to solver and ADD another constraint - now set the cell that is computing expected return to a fixed value, starting at 9%. Re-solve for the weights and copy/ paste them in the table of results. Case #3: Building an efficient portfolio ix. Re-do above step by changing constraint to capture different returns of the portfolio to up to and ABOVE exp return of the highest individual security, in 3% increments. So, have the following computed: min variance portfolio, 9%,12%,15%,18% and 21% return portfolios. x. Graph the frontier: portfolio standard deviation on the x-axis, portfolio expected return on the y-axis. xi. After you are done graphing the frontier (by following above steps), to the graph that contains the frontier, add expected return and stdev of each individual asset and see where they map relative to the frontier