Need help with part 3 only
Part 3 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 op=w.wT w subject to (11) w.rT = E(Rp) w1' = 1. Problem 2. Using the first and second portfolio, find out optimal weights that maximizing the portfolio standard deviation (Tangent Portfolio). E(Rp) WT max w Op (w. S.wT)1/2 subject to w.1' = 1. (12) Problem 3. Compute 99% - portfolio VaRs (i.e. percentage VaR) for the respective minimum variance portfolios and tangent portfolios and explain these values. Consolidate all excel spreadsheets from part1 in a single Excel file. Please make a cover page (name and ID) in Sheet1 in your EXCEL file. To submit this project electronically, use Blackboard. DO NOT send me it via email. Please submit your project in a single EXCEL file. ONLY an EXCEL FILE will be accepted. You have write up with your own words. Do not copy from others' work. In the last page, you have to show all your references for your project in the last sheet in your Excel. Part 1 Analysis of Economic Indicators Instruction: You have been retained by the SHB investment group to provide a fundamental analysis report to the president of the company, Seungho Baek. You are asked to examine the value of economic indicators as below. 1. TED spread: The TED spread is an indicator of perceived credit risk in the general economy. An increase in the TED spread is a sign that lenders believe the risk of default on interbank loans (also known as counterparty risk) is increasing. 2. Credit spread: A credit spread is the difference in yield between a U.S. T-bond and a debt security with the same maturity but of lesser quality. Widening credit spreads indicate growing concern about the ability of corporate (and other private) borrowers to service their debt while narrowing credit spreads indicate improving private creditworthiness. 3. Term spread: Term spreads, also known as interest rate spreads, represent the difference between the long-term interest rates and short-term interest rates on debt instruments such as bonds Widening term spreads indicate boom economy in the future whereas narrowing credit spreads indicate economic recession in the future. 4. Term Structure of Yields: The term structure of interest rates is the relationship between interest rates or bond yields and different terms or maturities. When graphed, the term structure of interest rates is known as a yield curve, and it plays a central role in an economy. 5. Cumulative returns of S&P market index: A cumulative return is the aggregate amount an investment has gained or lost over time, independent of the period of time involved. Upward trend of cumulative returns generally indicates bull stock market while downward trend indicates bear market. In order to generate these economic indicators, you need to use two data resources to collect economic variables such as Federal Reserve Economic Data (FRED) 2 and Yahoo! Finance. The interest rates necessary for computing spreads is located in FRED. You are able to obtain the historical stock index prices from Yahoo! Finance. To locate the historical s&p 500 index price, please follow the below steps. . Enter "GSPC in the search bar for Quote "Lookup". Note that the term structure reflects expectations of market participants about future changes in interest rates and their assessment of monetary policy conditions. The yield curve represents the changes in interests rates associated with a particular security based on length of time until maturity. Unlike other metrics, the yield curve is not produced by a single entity or government. Instead, it is set by measuring the feel of the market at the time, often referring to investor knowledge to help create the baseline. The direction of the yield curve is considered a solid indicator regarding the current direction of an economy. 2 https://fred.stlouisfed.org/ 1 . Click Historical Data tab. Set "Time Period" Jan. 2001 - Dec. 2019. Set "Frequency Monthly". Click Apply and click Download, You must write a report including your findings, all relevant information and computations, and provide evidence. For this project, you must download monthly historical data from Jan. 2001 to Dec. 2019. Problem 1. Investigate important economic risk events since the year of 2000 and summarize them. Problem 2. TED spread is defined as the difference between 3 month LIBOR (loan) rate - 3 month T-bill rate. Compute monthly TED spreads from Jan. 2000 to Dec. 2019 and plot the TED spreads over time. Problem 3. Let us define a credit spread as the difference between US coporate bond yields and 10 year Treasury rate. For this exercise, use Bank of America Merrill Lynch US Corporate AAA Effective Yield as a corporate bond rate and 10-Year Treasury Constant Maturity Rate as Treasury rate. Compute monthly credit spreads from Jan. 2000 to Dec. 2019 and plot the credit spreads. Problem 4. In practice, a term spread is defined as 10 year Treasury rate minus 1 year Treasury rate. Calculate monthly term spreads from Jan. 2000 to Dec. 2019 and plot the term spreads. Problem 5. Examine time series plots from problem 2, 3, and 4 and suggest your findings. Suggest your findings on how these indicators responded against systemic shocks that you answered in problem 1. Problem 6. Examine yield curve shapes using daily yield curve rates provided by U.S. department of treasury a) Report treasure yield rates (1 month, 3 month, 6 month, 1 year, 2 year, 3 year, 5 year, 7 year, 10 year, 20 year, 30 year) on Jan. 02, 2014, Jan. 02, 2015, Jan. 04, 2016, Jan. 03, 2017, Jan. 02, 2018, Jan. 02, 2019, and Jan. 02, 2020. b) Compute each term spreads for seven term structures. c) Plot seven yield curves that show the relation between yields and maturities. d) Provide your findings thoroughly from b) and c). Problem 7. Compute monthly S&P 500 index returns. To calculate monthly returns using S&500 index price, you need to use formula as R4 = PP-1 where t represents time (In our case, t refers to month; Pt represents the S&P index price at time t. For example, if you would like to compute a return on Feb., 2000, you may be able to specify an equation as RFeb,2000 = Pruh, 2000-P. an 2000 Problem 8. Using monthly returns that you obtained from the previous problem, compute monthly cu- mulative returns. To compute monthly cumulative returns, use the formula as CR4 = Rt + j=1 Rt-j where CR represents a cumulative return at time t. Problem 9. Plot monthly cumulative returns and examine how cumulative return series are affected by economic crisis. Also indicate bear or bull stages in the US stock market since the year of 2000. 3 TED spread =3 month loan rate - 3 month T-bill rate BofA Merrill Lynch US Corporate AAA Effective Yield - 10-Year Treasury Constant Maturity Rate 510-Year Treasury Constant Maturity Rate - 1-Year Treasury Constant Maturity Rate https://www.treasury.gov/resource center/data-chart-center/interest rates/pages/textview.aspx?data=yield 2 Part 2 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. 2019 from Yahoo! Finance and compute monthly stock returns from Jan.2010 to Dec. 2019.7 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." Epi = (on SAADA EL 20MAPLMSET JAAPL,MSFT = OMSFT,AAPL OMSFT = OMSFT,MSFT (1) obis Sp2 = ODIS,BA ODIS, AMZN OBA,DIS OBA BA,AMZN JAM ZN,DIS OAMZN.BA 'AMZN OLSA,DIS OTLSA,BA OTLSA,AM ZN ONFLX,DIS ONFLX.BA ONFLX.AM ZN ODIS,TLSA ODISNFLX OBATLSA OBA NFLX AMZN.TLSA CAMZN.NFLX olsa OTLSANFLX ONFLX TLSA OFLX (2) 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) = WAAPLAAPL + WMSFTPMSFT (3) op = w APLOAPL + WMSFTOMSFT + 2WAAPLWMSETO AAPL,MSFT (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) (5) WISDIS + WBATBA + WAMZNAMZN WTLSATILSA + WNFLXNFLX + + os wisobis +wBAOBA + wM ZNOMZN + WLSALSA + wmflxPLX (6) 2wDISWBAODIS,BA + 2WDI SWAM ZNODIS, AMZN + 2WDISUTLSAODISTLSA + 2WDISWNFLXODISNFLX + 2WBAWAMZNOBA,AM ZN + 2WBAWTLSAJBA,TLSA 2WBAWNFLXOBA,NFLX + 2WAMZN WTLSA AMZN.TLSA + 2WAM ZNWNFLXOAMZN.NFLIX 2wTLSAWNFLXOTLSANFLX * Re = PER where Ru represents a stock return at time t, Pe is a stock price at time t. BUse STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by oample = EX. ****, while the formula for standard deviation based on a population is written as opopulation = 2*, **** Use Data Analysis Toolpak to compute a covariance matrix. + 3 Problem 5. Using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and portfolio variances. 10 E(Rp) =WT (7) o =w. Swt (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. Problem 8. Compute 99%-VaR and ES for the first and second portfolio components and explain these values. VaR(99%) = E(Rp) - Z99% Op = E(Rp) - 0-(99%)op (9) (-(1 - 0.99) ES(99%) = E(Rp) - op- (10) 1- 0.99 where 6() is the normal density function, 0-1) is the inverse cumulative normal density function. Note for Excel: To compute Z99% (i..0-10.99) ), use NORM.S.INV (0.99) To compute 4(6-(1.0.99), use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01 10 Let w be a weight matrix that has N elements, w = (w1, 2, ..., Wr), and let r be a return matrix with N elements, 101,1 01,2 01 N 2,1 2.2 T2N r= (11,12,.....In). Let be a N by N covariance matrix, = : on, 1 ON 2 ONN -- .. ... Part 3 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 op=w.wT w subject to (11) w.rT = E(Rp) w1' = 1. Problem 2. Using the first and second portfolio, find out optimal weights that maximizing the portfolio standard deviation (Tangent Portfolio). E(Rp) WT max w Op (w. S.wT)1/2 subject to w.1' = 1. (12) Problem 3. Compute 99% - portfolio VaRs (i.e. percentage VaR) for the respective minimum variance portfolios and tangent portfolios and explain these values. Consolidate all excel spreadsheets from part1 in a single Excel file. Please make a cover page (name and ID) in Sheet1 in your EXCEL file. To submit this project electronically, use Blackboard. DO NOT send me it via email. Please submit your project in a single EXCEL file. ONLY an EXCEL FILE will be accepted. You have write up with your own words. Do not copy from others' work. In the last page, you have to show all your references for your project in the last sheet in your Excel. Part 1 Analysis of Economic Indicators Instruction: You have been retained by the SHB investment group to provide a fundamental analysis report to the president of the company, Seungho Baek. You are asked to examine the value of economic indicators as below. 1. TED spread: The TED spread is an indicator of perceived credit risk in the general economy. An increase in the TED spread is a sign that lenders believe the risk of default on interbank loans (also known as counterparty risk) is increasing. 2. Credit spread: A credit spread is the difference in yield between a U.S. T-bond and a debt security with the same maturity but of lesser quality. Widening credit spreads indicate growing concern about the ability of corporate (and other private) borrowers to service their debt while narrowing credit spreads indicate improving private creditworthiness. 3. Term spread: Term spreads, also known as interest rate spreads, represent the difference between the long-term interest rates and short-term interest rates on debt instruments such as bonds Widening term spreads indicate boom economy in the future whereas narrowing credit spreads indicate economic recession in the future. 4. Term Structure of Yields: The term structure of interest rates is the relationship between interest rates or bond yields and different terms or maturities. When graphed, the term structure of interest rates is known as a yield curve, and it plays a central role in an economy. 5. Cumulative returns of S&P market index: A cumulative return is the aggregate amount an investment has gained or lost over time, independent of the period of time involved. Upward trend of cumulative returns generally indicates bull stock market while downward trend indicates bear market. In order to generate these economic indicators, you need to use two data resources to collect economic variables such as Federal Reserve Economic Data (FRED) 2 and Yahoo! Finance. The interest rates necessary for computing spreads is located in FRED. You are able to obtain the historical stock index prices from Yahoo! Finance. To locate the historical s&p 500 index price, please follow the below steps. . Enter "GSPC in the search bar for Quote "Lookup". Note that the term structure reflects expectations of market participants about future changes in interest rates and their assessment of monetary policy conditions. The yield curve represents the changes in interests rates associated with a particular security based on length of time until maturity. Unlike other metrics, the yield curve is not produced by a single entity or government. Instead, it is set by measuring the feel of the market at the time, often referring to investor knowledge to help create the baseline. The direction of the yield curve is considered a solid indicator regarding the current direction of an economy. 2 https://fred.stlouisfed.org/ 1 . Click Historical Data tab. Set "Time Period" Jan. 2001 - Dec. 2019. Set "Frequency Monthly". Click Apply and click Download, You must write a report including your findings, all relevant information and computations, and provide evidence. For this project, you must download monthly historical data from Jan. 2001 to Dec. 2019. Problem 1. Investigate important economic risk events since the year of 2000 and summarize them. Problem 2. TED spread is defined as the difference between 3 month LIBOR (loan) rate - 3 month T-bill rate. Compute monthly TED spreads from Jan. 2000 to Dec. 2019 and plot the TED spreads over time. Problem 3. Let us define a credit spread as the difference between US coporate bond yields and 10 year Treasury rate. For this exercise, use Bank of America Merrill Lynch US Corporate AAA Effective Yield as a corporate bond rate and 10-Year Treasury Constant Maturity Rate as Treasury rate. Compute monthly credit spreads from Jan. 2000 to Dec. 2019 and plot the credit spreads. Problem 4. In practice, a term spread is defined as 10 year Treasury rate minus 1 year Treasury rate. Calculate monthly term spreads from Jan. 2000 to Dec. 2019 and plot the term spreads. Problem 5. Examine time series plots from problem 2, 3, and 4 and suggest your findings. Suggest your findings on how these indicators responded against systemic shocks that you answered in problem 1. Problem 6. Examine yield curve shapes using daily yield curve rates provided by U.S. department of treasury a) Report treasure yield rates (1 month, 3 month, 6 month, 1 year, 2 year, 3 year, 5 year, 7 year, 10 year, 20 year, 30 year) on Jan. 02, 2014, Jan. 02, 2015, Jan. 04, 2016, Jan. 03, 2017, Jan. 02, 2018, Jan. 02, 2019, and Jan. 02, 2020. b) Compute each term spreads for seven term structures. c) Plot seven yield curves that show the relation between yields and maturities. d) Provide your findings thoroughly from b) and c). Problem 7. Compute monthly S&P 500 index returns. To calculate monthly returns using S&500 index price, you need to use formula as R4 = PP-1 where t represents time (In our case, t refers to month; Pt represents the S&P index price at time t. For example, if you would like to compute a return on Feb., 2000, you may be able to specify an equation as RFeb,2000 = Pruh, 2000-P. an 2000 Problem 8. Using monthly returns that you obtained from the previous problem, compute monthly cu- mulative returns. To compute monthly cumulative returns, use the formula as CR4 = Rt + j=1 Rt-j where CR represents a cumulative return at time t. Problem 9. Plot monthly cumulative returns and examine how cumulative return series are affected by economic crisis. Also indicate bear or bull stages in the US stock market since the year of 2000. 3 TED spread =3 month loan rate - 3 month T-bill rate BofA Merrill Lynch US Corporate AAA Effective Yield - 10-Year Treasury Constant Maturity Rate 510-Year Treasury Constant Maturity Rate - 1-Year Treasury Constant Maturity Rate https://www.treasury.gov/resource center/data-chart-center/interest rates/pages/textview.aspx?data=yield 2 Part 2 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. 2019 from Yahoo! Finance and compute monthly stock returns from Jan.2010 to Dec. 2019.7 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." Epi = (on SAADA EL 20MAPLMSET JAAPL,MSFT = OMSFT,AAPL OMSFT = OMSFT,MSFT (1) obis Sp2 = ODIS,BA ODIS, AMZN OBA,DIS OBA BA,AMZN JAM ZN,DIS OAMZN.BA 'AMZN OLSA,DIS OTLSA,BA OTLSA,AM ZN ONFLX,DIS ONFLX.BA ONFLX.AM ZN ODIS,TLSA ODISNFLX OBATLSA OBA NFLX AMZN.TLSA CAMZN.NFLX olsa OTLSANFLX ONFLX TLSA OFLX (2) 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) = WAAPLAAPL + WMSFTPMSFT (3) op = w APLOAPL + WMSFTOMSFT + 2WAAPLWMSETO AAPL,MSFT (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) (5) WISDIS + WBATBA + WAMZNAMZN WTLSATILSA + WNFLXNFLX + + os wisobis +wBAOBA + wM ZNOMZN + WLSALSA + wmflxPLX (6) 2wDISWBAODIS,BA + 2WDI SWAM ZNODIS, AMZN + 2WDISUTLSAODISTLSA + 2WDISWNFLXODISNFLX + 2WBAWAMZNOBA,AM ZN + 2WBAWTLSAJBA,TLSA 2WBAWNFLXOBA,NFLX + 2WAMZN WTLSA AMZN.TLSA + 2WAM ZNWNFLXOAMZN.NFLIX 2wTLSAWNFLXOTLSANFLX * Re = PER where Ru represents a stock return at time t, Pe is a stock price at time t. BUse STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by oample = EX. ****, while the formula for standard deviation based on a population is written as opopulation = 2*, **** Use Data Analysis Toolpak to compute a covariance matrix. + 3 Problem 5. Using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and portfolio variances. 10 E(Rp) =WT (7) o =w. Swt (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. Problem 8. Compute 99%-VaR and ES for the first and second portfolio components and explain these values. VaR(99%) = E(Rp) - Z99% Op = E(Rp) - 0-(99%)op (9) (-(1 - 0.99) ES(99%) = E(Rp) - op- (10) 1- 0.99 where 6() is the normal density function, 0-1) is the inverse cumulative normal density function. Note for Excel: To compute Z99% (i..0-10.99) ), use NORM.S.INV (0.99) To compute 4(6-(1.0.99), use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01 10 Let w be a weight matrix that has N elements, w = (w1, 2, ..., Wr), and let r be a return matrix with N elements, 101,1 01,2 01 N 2,1 2.2 T2N r= (11,12,.....In). Let be a N by N covariance matrix, = : on, 1 ON 2 ONN
