Part 1 Portfolio Return and Risk Instruction: You are a portfolio investment analyst at Goldman Sachs. Your investment unit manages two equity portfolios - one portfolio, named P1, consists of two stock assets (Apple (AAPL) and Microsoft (Ticker: MSFT)) and the other portfolio, named P2, consists of five stocks (Disney (Ticker:DIS), Boeing(Ticker:BA), Amazon (Ticker:AMZN), United Arline(Ticker:UAL), Netflix (Ticker:NFLX)). Now you are asked to com- pute two portfolio returns and risk measures. To do this, first download monthly stock prices from Dec.2010 to Dec. 2020 from yahoo! finance and compute monthly stock returns from Jan.2010 to Dec. 2020. Problem 1. Compute the respective average, standard deviation, and covariance of monthly stock returns. Problem 2. Make two covariance matrices using two portfolio components. Note that you have to make a completed form of a matrix as below.3 p2 = = = OMSFT,AAPL = AAPL, MSFT ODIS OBA, DIS AMZN,DIS OLSA, DIS ONFLX,DIS ODIS,BA O'BA AMZN, BA JUAL, BA ONFLX, BA AAPL, MSFT = OMSFT, AAPL . MSFT = OMSFT,MSFT ODIS, AMZN JBA, AMZN OAMZN JUAL, AMZN ONFLX.AMZN (1) ODIS,UAL ODIS, NFLX OBA,UAL OBA,NFLX OAMZN,UAL OAMZN,NFLX (2) OUAL JUAL,,NFLX ONFLX,UAL ONFLX Problem 3. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and portfolio variance for the first portfolio using the below equations. E(RP) = WAAPLTAAPL + WMSFTTMSFT (X-X) N (3) o = WAPLO APL + WMSFTOMSFT+2WAAPLWMSFTO AAPL,MSFT Pt-1 R = Pt=Pt= P-P-1 where Re represents a stock return at time t, Pt is a stock price at time t. 2Use STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by Sample (X-X), while the formula for standard deviation based on a population is written as Population = =1 3Use Data Analysis Toolpak to compute covariance matrix. (4) Problem 4. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and portfolio variance for the second portfolio using the below equations. E(RP) WDISTDIS + WBATBA+WAMZNTAMZN + WTLSATUAL + WNFLXTNFLX = (5) 0/ WHIS DIS + WBABA+WAMZNO MZN + wvaloval + wvFLXONFLX + 2WDISWBAODIS,BA +2WDISWAMZNODIS, AMZN +2WDISWUALO DISUAL + 2WDISWNFLXODIS,NFLX + 2WBAWAMZNOBA, AMZN +2WBAWUALOBA,UAL + 2WBAWNFLXOBA,NFLX +2WAMZN WUALO AMZN,UAL +2WAMZN WNFLXOAMZN, NFLX + 2WU ALWNFLXOUAL,NFLX Problem 5. Using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and portfolio variances.4 E(Rp) = w r (7) =w..wT (8) Problem 6. With the first portfolio, create a table that shows the benefit of diversification using Data Table in Excel. (Note that the table shows portfolio returns and portfolio standard deviation with respect to scenarios of weights on AAPL.) Problem 7. Using the table obtained from problem 6, Plot expected returns against portfolio risk (standard deviations) displaying efficient portfolios. (6) Problem 8. Compute 99%-VaR and ES for the first and second portfolio components and explain these values. E(Rp) (99%)p (9) VaR(99%) = E(Rp) - Z99% Op = ES(99%) = E(Rp) - op (10) where p() is the normal density function, -() is the inverse cumulative normal density function. (-(10.99) 1 - 0.99 Note for Excel: To compute Z99% (i.e.-(0.99)), use NORM.S.INV (0.99) To compute ($- (1-0.99) 1-0.99 use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01 7 4Let w be a weight matrix that has N elements, w = (w, w2, ..., wn), and let r be a return matrix with N elements, 1, N 02, N r = (, 12,..., n). Let be a N by N covariance matrix, = 01.1 01.2 02,1 2,2 : N,2 ON,1 ON.N. Part 2 Minimum Variance and Tangent Portfolios Instruction: You have two customers that have different tastes of risk. The first customer is a risk averter who find the way of minimizing investment risk. The other customer is a kind of person who taking affordable risk that makes a risk adjusted return. Your boss asks you to find optimal weights of investments satisfying their risk-preferences. Problem 1. Using the first portfolio, find out optimal weights that minimizes the portfolio standard devi- ation (Minimum Variance Portfolio). min W subject to max W 6 = w . .wT w.r = E(Rp) w.1' = 1. Problem 2. Using the first and second portfolio, find out optimal weights that maximizing the portfolio standard deviation (Tangent Portfolio). E(Rp) op subject to w.1' = 1. (11) w.rT (w. .wT)1/2 (12) Problem 3. Compute 99% - portfolio VaRs (i.e. percentage VaR) for the respective minimum variance portfolios and tangent portfolios and explain these values. Part 1 Portfolio Return and Risk Instruction: You are a portfolio investment analyst at Goldman Sachs. Your investment unit manages two equity portfolios - one portfolio, named P1, consists of two stock assets (Apple (AAPL) and Microsoft (Ticker: MSFT)) and the other portfolio, named P2, consists of five stocks (Disney (Ticker:DIS), Boeing(Ticker:BA), Amazon (Ticker:AMZN), United Arline(Ticker:UAL), Netflix (Ticker:NFLX)). Now you are asked to com- pute two portfolio returns and risk measures. To do this, first download monthly stock prices from Dec.2010 to Dec. 2020 from yahoo! finance and compute monthly stock returns from Jan.2010 to Dec. 2020. Problem 1. Compute the respective average, standard deviation, and covariance of monthly stock returns. Problem 2. Make two covariance matrices using two portfolio components. Note that you have to make a completed form of a matrix as below.3 p2 = = = OMSFT,AAPL = AAPL, MSFT ODIS OBA, DIS AMZN,DIS OLSA, DIS ONFLX,DIS ODIS,BA O'BA AMZN, BA JUAL, BA ONFLX, BA AAPL, MSFT = OMSFT, AAPL . MSFT = OMSFT,MSFT ODIS, AMZN JBA, AMZN OAMZN JUAL, AMZN ONFLX.AMZN (1) ODIS,UAL ODIS, NFLX OBA,UAL OBA,NFLX OAMZN,UAL OAMZN,NFLX (2) OUAL JUAL,,NFLX ONFLX,UAL ONFLX Problem 3. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and portfolio variance for the first portfolio using the below equations. E(RP) = WAAPLTAAPL + WMSFTTMSFT (X-X) N (3) o = WAPLO APL + WMSFTOMSFT+2WAAPLWMSFTO AAPL,MSFT Pt-1 R = Pt=Pt= P-P-1 where Re represents a stock return at time t, Pt is a stock price at time t. 2Use STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by Sample (X-X), while the formula for standard deviation based on a population is written as Population = =1 3Use Data Analysis Toolpak to compute covariance matrix. (4) Problem 4. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and portfolio variance for the second portfolio using the below equations. E(RP) WDISTDIS + WBATBA+WAMZNTAMZN + WTLSATUAL + WNFLXTNFLX = (5) 0/ WHIS DIS + WBABA+WAMZNO MZN + wvaloval + wvFLXONFLX + 2WDISWBAODIS,BA +2WDISWAMZNODIS, AMZN +2WDISWUALO DISUAL + 2WDISWNFLXODIS,NFLX + 2WBAWAMZNOBA, AMZN +2WBAWUALOBA,UAL + 2WBAWNFLXOBA,NFLX +2WAMZN WUALO AMZN,UAL +2WAMZN WNFLXOAMZN, NFLX + 2WU ALWNFLXOUAL,NFLX Problem 5. Using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and portfolio variances.4 E(Rp) = w r (7) =w..wT (8) Problem 6. With the first portfolio, create a table that shows the benefit of diversification using Data Table in Excel. (Note that the table shows portfolio returns and portfolio standard deviation with respect to scenarios of weights on AAPL.) Problem 7. Using the table obtained from problem 6, Plot expected returns against portfolio risk (standard deviations) displaying efficient portfolios. (6) Problem 8. Compute 99%-VaR and ES for the first and second portfolio components and explain these values. E(Rp) (99%)p (9) VaR(99%) = E(Rp) - Z99% Op = ES(99%) = E(Rp) - op (10) where p() is the normal density function, -() is the inverse cumulative normal density function. (-(10.99) 1 - 0.99 Note for Excel: To compute Z99% (i.e.-(0.99)), use NORM.S.INV (0.99) To compute ($- (1-0.99) 1-0.99 use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01 7 4Let w be a weight matrix that has N elements, w = (w, w2, ..., wn), and let r be a return matrix with N elements, 1, N 02, N r = (, 12,..., n). Let be a N by N covariance matrix, = 01.1 01.2 02,1 2,2 : N,2 ON,1 ON.N. Part 2 Minimum Variance and Tangent Portfolios Instruction: You have two customers that have different tastes of risk. The first customer is a risk averter who find the way of minimizing investment risk. The other customer is a kind of person who taking affordable risk that makes a risk adjusted return. Your boss asks you to find optimal weights of investments satisfying their risk-preferences. Problem 1. Using the first portfolio, find out optimal weights that minimizes the portfolio standard devi- ation (Minimum Variance Portfolio). min W subject to max W 6 = w . .wT w.r = E(Rp) w.1' = 1. Problem 2. Using the first and second portfolio, find out optimal weights that maximizing the portfolio standard deviation (Tangent Portfolio). E(Rp) op subject to w.1' = 1. (11) w.rT (w. .wT)1/2 (12) Problem 3. Compute 99% - portfolio VaRs (i.e. percentage VaR) for the respective minimum variance portfolios and tangent portfolios and explain these values