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), Tesla(Ticker:TSLA), Netflix (Ticker:NFLX)). Now you are asked to compute two portfolio returns and risk measures. To do this, first download monthly stock prices from Dec.2009 to Dec. 2018 from Bloomberg and compute monthly stock returns from Jan.2010 to Dec. 2018.1 Problem 1. Problem 2. Compute the respective average, standard deviation, and covariance of monthly stock returns.2 Make two covariance matrices using two portfolio components. Note that you have to make a Problem 3. 2 NFLX,BA NFLX,AMZN NFLX,TLSA 2 NFLX,DIS NFLX 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(RP1)=wAAPLr AAPL +wMSFTr MSFT (3) 2 =w2 2 +w2 2 +2w w (4) completed form of a matrix as below.3 2 = AAPL,AAPL AAPL,MSFT = MSFT,AAPL 2 P1 = AAPL MSFT,AAPL = AAPL,MSFT MSFT = MSFT,MSFT (1) 2 DIS,BA DIS BA,DIS 2 BA = AMZN,DIS AMZN,BA P2 2 TLSA,BA TLSA,DIS DIS,AMZN DIS,TLSA DIS,NFLX BA,AMZN BA,TLSA BA,NFLX P1 AAPL AAPL MSFT MSFT AAPL MSFT AAPL,MSFT 1Rt = PtPt1 where Rt represents a stock return at time t, Pt is a stock price at time t. 2 AMZN,TLSA AMZN,NFLX (2) AMZN TLSA,AMZN 2 TLSA,,NFLX TLSA Pt 2Use STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by 2 = Sample N ( X X ) 2 2 N ( X X ) 2 i=1 N1 , while the formula for standard deviation based on a population is written as Population = i=1 N . 3Use Data Analysis Toolpak to compute a covariance matrix. 1 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. Problem 5. Problem 6. Problem 7. Problem 8. matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and E(RP ) = w rT (7) P2 =wwT (8) 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.) Using the table obtained from problem 6, Plot expected returns against portfolio risk (standard deviations) displaying efficient portfolios. Compute 99%-VaR and ES for the first and second portfolio components and explain these values. 2 P2 = + + + + E(RP2) = wDISr DIS +wBAr BA +wAMZNr AMZN + wTLSAr TLSA +wNFLXr NFLX w2 2 +w2 2 +w2 2 +w2 2 +w2 2 DIS DIS BA BA AMZN AMZN TLSA TLSA NFLX NFLX (5) (6) function. Note for Excel: 2wDISwBADIS,BA +2wDISwAMZNDIS,AMZN +2wDISwTLSADIS,TLSA 2wDISwNFLXDIS,NFLX +2wBAwAMZNBA,AMZN +2wBAwTLSABA,TLSA 2wBAwNFLXBA,NFLX +2wAMZNwTLSAAMZN,TLSA +2wAMZNwNFLXAMZN,NFLX 2wTLSAwNFLXTLSA,NFLX Using a portfolio variances.4 VaR(99%)=E(RP)Z99%P =E(RP)1(99%)P (9) ES(99%) = E(R ) (1(1 0.99) (10) P P 10.99 where () is the normal density function, 1() is the inverse cumulative normal density To compute Z99% (i.e.1(0.99) ), use NORM.S.INV (0.99) To compute (1(10.99), use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01 10.99 4Let w be a weight matrix that has N elements, w = (w1,w2,....,wn), and let r be a return matrix with N elements, 1,1 1,2 2,1 2,2 r = (r1,r2,....,rn). Let be a N by N covariance matrix, = . . ... ... 1,N 2,N . . . ... . N,1 N,2 . . . N,N 2 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). Problem 2. Using the first and second portfolio, find out optimal weights that maximizing the portfolio standard deviation (Tangent Portfolio). max E(RP ) = w rT w P (w wT)1/2 (12) subjectto w1 =1. min P2 =wwT w subjectto wrT =E(RP) w1 =1. (11) Problem 3. Compute 99% - portfolio VaRs (i.e. percentage VaR) for the respective minimum variance portfolios and tangent portfolios and explain these values. 3 Part 3 Portfolio Evaluations and Systemic Risk Instruction: From the previous parts, you have created three portfolios with two stocks (equal weighted portfolio, minimum-variance portfolio, tangent portfolio). Now you are about to evaluating the performance of three portfolios. Problem 1. Problem 2. Problem 3. Problem 4. Problem 5. Problem 6. Problem 7. Problem 8. Problem 9. Download monthly stock prices for the period from Dec. 2018 to Oct. 2019 using Bloomberg and generate monthly stock returns from Jan.2019 to Oct. 2019. Using the obtained weights (equal weights and two sets of optimal weights) from part 1 and part 2, compute three monthly portfolio returns for each portfolio (P1 and P2) over the period from Jan.2019 to Oct. 2019. Let denote EWP as an equal weighted portfolio, MVP as an minimum variance portfolio, and TP as a tangent portfolio. Compute the respective averages, and standard deviations for EWP, MVP, TP based P1. Compute the respective averages, and standard deviations for EWP, MVP, TP based P2. Compute the respective Sharpe Ratio (SR) for the six portfolios using the below equation. SR = E(RP ) (13) P Generate three cumulative returns and plot three cumulative returns based on P1. Generate three cumulative returns and plot three cumulative returns based on P2. Let us examine the validity of 99%-VaR and ES. (a) How many monthly equal weighted portfolio returns are beyond 99% VaR and ES from Problem 8 in Part 1. (b) How many monthly minimum variance portfolio returns are beyond 99% VaR and ES from Problem 3 in Part 2. (c) How many monthly tangent portfolio returns are beyond 99% VaR and ES from Problem 3 in Part 2. Interpret all the results from problem 3 to problem 7. Also suggest your findings and your opinion about each portfolio performance during the period between Jan. 2019 to Oct. 2019.