Reference no: EM132397052

Instructions

Part I. Application of Modern Portfolio Theory using Stock and Bond ETFs

(1) Use the worksheet "Part I Data" that contains the monthly dividend-adjusted closing price on the Russell 3000 Stock Index ETF (IWV) and Barclays Aggregate Bond Index ETF (AGG) from January 2004 to September 2019. IWV is the regular ETF that tracks the total return on the Russell 3000 Stock Index, while AGG is the regular ETF that tracks the Barclays Aggregate Bond Index. Consider IWV as the total stock market ETF and AGG as the total bond market ETF. Calculate the monthly total returns (rt= Pt/Pt-1-1) on IWV and AGG.

(2) Calculate the mean (Excel function =average, as an estimate for expected return), standard deviation (Excel function =stdev), and Sharpe ratio [=(Expected return-rf)/Std. Dev. of Return] for the returns on each of these two ETFs. Calculate the correlation between the returns on these two ETFs. Please note that the average Short-term risk free rate (rf) from January 2004 to September 2019 is 1.32%.

Which ETF (stock market or bond market) performs better from an expected return perspective? Which ETF (stock market or bond market) has better total risk-adjusted performance?

You can use "Part I worksheet" for (3)-(6) of Part I. Consider the IWV as Risky Asset #1 and the AGG as the Risky Asset #2.

(3) Find the expected return, standard deviation and Sharpe ratio of all possible combinations of the Stock ETF and the Bond ETF, as the weight in the IWV varies from 100% to 0% (and, therefore, the weight on the AGG varies from 0% to 100%). Use increments of 5%. (Reference: see the expected return and std dev formulas on slide 37, and Sharpe ratio formula on slide 16)

(4) What would be the weights for each of the two risky assets in the minimum variance portfolio? What are the expected return, standard deviation, and Sharpe Ratio of this minimum variance portfolio (MVP)? (Reference: see the minimum variance formula on slide 39)

(5) What would be the weight for each of the two risky assets in the optimal risky portfolio (i.e., the risky portfolio with highest Sharpe ratio)? What are the expected return, standard deviation, and Sharpe ratio of this optimal risky portfolio (ORP)? (Reference: see the optimal risky portfolio formula for w1 on slide 47)

(6) Suppose an investor places 85% in the optimal risky portfolio and 15% in the risk-free T-bill to form a complete portfolio C. What are the expected return, standard deviation and Sharpe ratio of this complete portfolio? Calculate the overall asset allocation of this complete portfolio in Risky Asset #1 (IWV), Risky Asset #2 (AGG) and the risk free asset. (Reference: see the expected return and std dev formulas on slides 18 and 19 for the complete portfolio)

Part II. Regular and Leveraged ETFs

The "Part II Daily Data", "Part II Monthly Data", "Part II Annual Data" worksheets contain the daily, monthly, and annual price, NAV and dividend-adjusted price data on the regular and leveraged ETFs that track the 100%, 200%, 300%, -100%, -200% and -300% of the daily return on the NASDAQ 100 Index (NDX), and the Price Index and Dividend-adjusted Price Index on NDX. The sample period is a five-year window from October 2014 to September 2019. The frequency of the data includes daily, monthly and annual observations.

The QQQ is a regular ETF tracking 100% of the total return on the NASDAQ 100 Index (NDX), while the QLD, TQQQ, PSQ, QID, and SQQQ are leveraged exchange-traded funds (LETFs) seeking to deliver a daily target multiple of 200%, 300%, -100%, -200%, and -300% of the NDX total return, respectively.

Use "Part II Monthly Data" for all questions of Part II. "Part II Daily Data" and "Part II Annual Data" are only needed for question (5) of Part II.

(1) Use the dividend-adjusted monthly closing price on the ETFs to compute the monthly total returns of the QQQ (rQQQ,t= PQQQ,t/ PQQQ,t-1-1), QLD (rQLD), TQQQ (rTQQQ), PSQ (rPSQ), QID (rQID), and SQQQ (rSQQQ), respectively; compute the monthly excess returns (monthly total returns minus the risk free rate rf ) of the NASDAQ 100 Index (NDX) and the six ETFs. Use the NDX as the benchmark market index.

According to data provided by the Federal Reserve Board, the average annual yield of 10-year Treasury Notes during the sample period was 2.85% (as proxy for long-term risk free rate).

Regress the QQQ stock excess returns on the market excess returns. To get the intercept of the regression, alpha ?, use the =INTERCEPT(Range for the stock excess returns, Range for the market excess returns) function in Excel; for the slope coefficient of the regression, beta ?, use the =SLOPE(Range for the stock excess returns, Range for the market excess returns) function; for the explanatory power the regression, R2, use the =RSQ(Range for the stock excess returns, Range for the market excess returns). Repeat the same procedure to compute the ?'s and ?'s for the QLD, TQQQ, PSQ, QID, and SQQQ stocks.

(2) In Excel, use Data?Data Analysis?Regression to run the regression of each ETF's excess return on the NDX excess return. Complete Table 4 using estimates from the regression outputs.

• Check the consistency of regression estimates (alpha, beta, R-squared) in Table 4 with the alpha, beta, and R-squared in Table 3.
• Examine the p-value of the alpha and beta estimates to see if the alphas and betas are statistically significant.
• Compare each ETF's estimated beta coefficient with its target multiple.
• Discuss the sign and significance of the alphas and whether they make sense to you.

(3) Construct an X-Y scatter plot of the QQQ stock excess returns vs. the market excess returns. Place the market excess return on the X-axis and QQQ stock's excess return on the Y-axis. Click on one of the scatter data points in your X-Y graph, then click on the right mouse to pull down the "Chart" menu, and select "Add Trendline." Click on the "linear" picture, check the "Display equation on Chart", and then click "OK." Excel does the rest. This trendline is the Security Characteristic Line (SCL) of the QQQ stock. Repeat the same procedure to generate the SCLs for the QLD, TQQQ, PSQ, QID, and SQQQ stocks.

(4) Calculate the ETF Premium/Discount (PD) as PD=(PRICE- NAV)/ NAV. Please note that the Price used in the calculation should be the closing price without adjustment for dividend payment. Compute the mean, median and standard deviation of the monthly PD for each of the six ETFs in the NDX ETF family. Based on these statistics, discuss whether the market is efficient in pricing these ETFs.

(5) Calculate the Return Deviation (RD) of an LETF as:
RD=Total return on the LETF - (LETF Target Multiple × Total return on the underlying index). Compute the mean and standard deviation of RD for each of the six ETFs in the NDX ETF family using daily, monthly and annual data.

Using the return deviation statistics from Table 6, provide clear answers to the two questions below with detailed justifications:

• If your client is interested in getting inverse triple exposure to the NASDAQ 100 index for a one-year investment horizon, would you recommend SQQQ to your client? Why or why not?
• If your client is interested in getting inverse triple exposure to the NASDAQ 100 index for just one trading day, would you recommend SQQQ to your client? Why or why not?

Attachment:- Modern Portfolio Theory.rar