Fianancial anaysisComputer Exercise I 1. Estimate the correlation (coefficient) between two assets...

Question

Fianancial anaysisComputer Exercise I 1. Estimate the correlation (coefficient) between two assets based on real data. Note that for our purpose, correlation means return correlation not correlation between prices of two assets. (a) Describe briefly what each of the following ETFs does: SPY, LQD, GSG, SDS, SSO, and SHV (b) Download monthly prices of these six ETFs over the period 08/01/2010−07/01/2018 from Yahoo or other sources you like. (c) Compute monthly returns for Aug. 2010, Sep. 2010, …, May 2018, and June 2018 using the adj. close prices, for each of the six ETFs. For example, to compute the return for Aug. 2010, you use the prices for 09/01/2010 and 08/01/2010. Note that while the monthly price series begins on 8/1/2010, the monthly return series begins on 9/1/2010. Note: Return = (Final Price – Initial Price)/Initial Price. (d) Estimate the following six correlation coefficients using Excel function CORREL: a. The correlation coefficients between SPY and each of the other five ETFs; b. The correlation coefficient between LQD and GSG. You may refer to Example 6.1 in Chapter 6 in the textbook (p. 151 in the 11th edition) on how to use excel function CORREL to compute the correlation. If necessary, you may refer to the following files posted on Canvas under “Projects” on how to get data from Yahoo and calculate returns using downloaded data: (i) “howToGetHistPrcFromYahoo.pdf” has instructions on how to download historical prices from Yahoo and then calculate returns using the prices. (ii) 	“example-correlation-beta-slope.xls” illustrates how to calculate monthly returns of XOM and S&P500 from historical prices and then estimate the correlation between the returns of XOM and S&P500 using function CORREL. Things to turn in (Please organize your work appropriately and label each tab clearly): (i) a brief description of each of the six ETFs; (ii) the monthly returns (not prices) of the six ETFs (it is better to place all six monthly return series in one spreadsheet) (iii) the estimates of the six correlation coefficients specified in part (d) of Question 1; and (iv) a brief discussion of whether your estimates of the six correlations are consistent with your intuition. E.g., should SPY and SSO be highly correlated? Is your estimate of their correlation consistent with your expectation? Page 1 2. Construct the efficient frontier of SPY and LQD using real data. (a) You need to estimate five input parameters in this case: the expected returns and standard deviations of SPY and LQD, and their correlation. Suppose you use monthly returns of the SPY and LQD over the period 09/01/2010−07/01/2018 – that are obtained in Question 1 – to estimate those five parameters. Note: The main reason for choosing this particular period (09/01/2010−07/01/2018) is that both SPY and LQD have a positive average return over the period, which makes it possible to estimate the expected return using the average return. (b) Estimate the average monthly return and standard deviation of the monthly returns for SPY. Then estimate the average monthly return and standard deviation of the monthly returns for LQD. Recall that the correlation between SPY and LQD is already estimated in Question 1. (c) Annualize the average returns and standard deviations of SPY and LQD obtained in part (b) using monthly data. You can use the following formulas to do that annualized avg. return = (1 + monthly avg return)^12 - 1; annualized std. dev. = monthly std. dev. * sqrt(12). See also “example-correlation-beta-slope.xls” on to do this. Correlation need not be annualized. (d) Assume that both the expected returns and standard deviations of SPY and LQD going forward are the same as their annualized average returns and standard deviations obtained in part (c), respectively. Tabulate the investment opportunity set of these two ETFs. Namely, select, say, 20 different portfolios (of SPY and LQD), with SPY’s weight ranging from -0.5 to 1.5 with an incremental of 0.1. Then calculate the expected return and standard deviation for each of the 20 portfolios. Spreadsheet 6.5 in Chapter 6 in the textbook (p.154 in the 11e) provides a similar example. (e) Plot the expected returns of 20 portfolios (the y-variable) vs. these portfolios’ standard deviations (the x-variable) to generate a curve (whose upper branch is the efficient frontier). If you want, you can use “example-efficientFrontier.xls” posted on Canvas as a template. Things to turn in (Please organize your work appropriately and label each tab clearly): (i) a brief description of SPY and LQD; (ii) the estimates of both monthly and annualized average returns and standard deviations of these two ETFs, and the estimate of the correlation between these two ETFs; (iii) the tabulated investment opportunity set (say, 20 different portfolios of SPY and LQD) and the corresponding portfolio expected returns and standard deviations; and (iv) a properly labeled graph of the investment opportunity set (the curve) – namely, labeling the x-axis and y-axis of your plot at least. 3. Consider an investor who plans to allocate $10,000 to SPY, LQD, and cash, using the asset allocation model introduced in our Fin406 class. Use the annualized average returns, annualized standard deviations, and the correlation estimated earlier in Question 2 (part C). The risk-free rate is assumed to be 2% for this question. (a) Determine the tangency portfolio using Eq. (6.10) in Chapter 6 in the textbook. (b) Consider three investors whose risk aversion coefficients (coefficient A) are 8.0, 12.0, and 16.0, respectively. Determine these three investors’ optimal complete portfolios. You can use “example-assetAllocator.xls” posted on Canvas as a template for this exercise. Things to turn in (Please organize your work appropriately and label each tab clearly): (i) the annualized average returns and annualized standard deviations of SPY and LQD; the correlation coefficient between SPY and LQD; and the risk-free interest; (ii) the portfolio weights of SPY and LQD in the tangency portfolio; (iii) the expected return and standard deviation of the tangency portfolio; and (iv) the three investors’ optimal complete portfolios (including both portfolio weights and dollar amounts invested on the three asset classes). 4. You are asked to estimate betas of the following four funds against the S&P 500 index in this question: the Vanguard Market Neutral Fund (ticker: VMNFX), the ProShares Ultra S&P500 ETF (SSO), Consumer Staples Select Sector SPDR ETF (ticker: XLP), and Consumer Discretionary Select Sector SPDR ETF (ticker: XLY). Intuitively, the ranking of these four funds by beta should look like this: VMNFX

Himanshu · Accepted Answer

Answer 1 a.) 
· SPY- The SPY ETF is one of the main common funds to monitor the Standard & Poor's 500 Index, which incorporates 500 U.S. large and mid-cap shares. These shares are chosen by a panel on the basis of market position, stability and business.
· LQD- iShares iBoxx $Investment Grade Corporate Bond ETF aims to measure the investment performance of an index made up of U.S. dollar-denominated investment grade mutual funds.
· GSG is a product portfolio monitoring the S&P GSCI edition, giving it a reasonably balanced correlation to the wide market for energy sources. Rather than keeping shares on the underlying goods, GSG retains only long-standing agreements on the GSCI itself, as well as often very large cash and T-Bill reserves.
· SDS offers a 1-day leveraged bet against the S&P 500 in a very flexible mechanism that is well matched with the strategic essence of the stock. Like other leveraged and inverse offerings, the Fund is structured to offer 2x inverse sensitivity to its index — the S&P 500—only for 1 day and 1 day.
· The ProShares Ultra S&P500 ETF SSO offers 2x diversified allocation to the market-cap-weighted index of 500 large and mid-cap US firms chosen by S&P. As a levered commodity, SSO is not a purchase-and-hold ETF; it is a short-term tactical tool. As several leveraged funds, it offers its 2x leverage just over a one-day time frame. For extended durations, yields will differ dramatically from the 2x goal returns in the headline.
· SHV offers U.S. insanely-short-term debt securities in an affordable and usable basket. The Fund owns assets with a maturity of 12 months or less. SHV monitors a large vanilla index which reflects very well the ultra-short-term U.S. stock market.
Answer 2
· SPY- The SPY ETF is one of the main common funds to monitor the Standard & Poor's 500 Index, which incorporates 500 U.S. large and mid-cap shares. These shares are chosen by a panel on the basis of market position, stability and business.
· LQD- iShares iBoxx $Investment Grade Corporate Bond ETF aims to measure the investment performance of an index made up of U.S. dollar-denominated investment grade mutual funds.
Answer 4
· VMNFX- Vanguard Market Neutral Fund The investment aims to have long-term capital gains while restricting sensitivity to overall market risk.

Computer Exercise I 1. Estimate the correlation (coefficient) between two assets based on real data. Note that for our purpose, correlation means return correlation not correlation between prices of...

Answer To: Computer Exercise I 1. Estimate the correlation (coefficient) between two assets based on real data....

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment