Hi. I am having trouble implementing the formulas in Excel and would like to see how it is done. Thank you.

PROF. DAVID SCHREINDORFER FIN 421 - SPRING 2016 Assignment 4 Due: Wednesday, 02.24.16 at 11:59 pm In this assignment you will backtest the risk parity strategy on a set of industry stock portfolios. Backtesting In Lecture 5, we saw that the risk parity strategy would have performed very favorably in allocating funds across major asset classes, including bonds, stocks and commodities over the 1983-2014 sample. In this assignment, you will investigate whether the strategy would have performed similarly well when applied to allocating funds across different U.S. stocks only. To implement the risk parity strategy, you will employ the tools we studied in Lecture 6. In particular, you will compute risk parity portfolio weights based on forecasts of each asset's volatility, i.e. you will rely on conditional volatility estimates that are allowed to change over time. Your task will therefore consist of two steps. In the first step, you will need to estimate and forecast the realized volatility for each asset in each month of the sample (as in Lecture 6). Based on these volatility forecasts, you then need to compute risk parity portfolio weights in step two (as in Lecture 5). On Blackboard, you will find an Excel file containing historical net returns for 48 industry portfolios over 1976-2015. Each portfolio consists of the value-weighted average of all publiclytraded U.S. stocks within a certain industry. Think of each industry portfolio as one asset you can invest in.1 The file contains both daily and monthly net returns for each industry portfolio. STEPS: To complete this assignment, you'll need to deal with large amounts of data. The easiest way to organize the computations is with a set of tables that are stored in separate worksheets. Here are the necessary tables: Table 1 On each day in the sample, compute each asset's realized variance over the past 21 trading days, i.e. compute 2 RVi,t = (ri,t20 )2 + (ri,t19 )2 + ... + (ri,t ), 1 The data is from Ken French's website, http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data library.html. While these portfolios are not traded directly, their constituents are known and one could therefore trade them by trading the underlying stocks. Alternatively, industries can be traded via various ETFs. We will backtest the risk parity strategy based on a set of stock portfolios rather than on individual stocks to avoid a number of data issues that arise when dealing with individual stocks (e.g. earnings announcements induce seasonalities in the volatilities of individual stocks, stocks are frequently added and deleted from the sample due to mergers, bankruptcies etc.). 1/3 denotes asset i's log return on day t (note: the spreadsheet contains net returns). where ri,t Hint: The easiest way to implement this is via cell references in a table that contains the dates in the rows and the assets (industries) in the columns. The function \"sumproduct\" may be helpful for computing the realized variance. Table 2 Create a second table that contains your realized volatility estimate (the square root of the realized variance you computed in the previous step) for all assets on the last day of each calendar Hint: An easy way to extract end-of-month values from the table of daily observations is a \"vlookup\" table. A separate pdf document on Blackboard details the necessary steps. Table 3 For each month t, use the most recent 60 monthly RV estimates to make a prediction for RVi,t+1 . To do so, estimate the regression q q RVi,s+1 = 0 + 1 RVi,s + s+1 s = t 59, ..., t 1. Then use the coefficient estimates to compute the fitted value from the last observation as your prediction for month t + 1: q d RVi,t+1 = b0 + b1 q RVi,t Hint: Use the \"intercept\" and \"slope\" functions in Excel to obtain coefficient estimates. This will allow you to estimate thousands of regressions without relying on Excel's data analysis toolpack, so that you can use cell references to replicate the formulas many times. We worked through an example of this for the S&P 500 in lecture 6. Table 4 The next table is simply to facilitate the computation of the risk parity portfolio weights. It should contain the inverse of the predicted monthly realized volatility values, qd i.e. 1/ RVi,t . Hint: These are the 1 -terms in the formula for risk parity portfolio weights. Table 5 The last table contains the risk parity weights, computed as wi,t 1 i,t = PN 1 , i = 1, ..., N, j=1 j,t where wi,t is the portfolio weight for industry i at the end of month t. Note that this weight applies to the portfolio return in month t + 1, i.e. you choose the portfolio at the end of the previous month and then observe its return over the current month. QUESTIONS: A Create a plot that shows the monthly realized volatility (from Table 2) along with the predicted monthly realized volatility (from Table 3) for the agriculture industry (ACRIC) for 1981-2015. B Report the average risk parity portfolio weights. 2/3 C Report the mean, standard deviation and Sharpe ratio of the monthly returns for the risk parity portfolio. To compute the Sharpe ratios, use a monthly risk-free rate of 0.37%, which equals the average return on 1-month T-bills over the sample. Next, report the same statistics for the equal-weighted (\"1/N \") portfolio. Hint: The return of the equalweighted portfolio equals the simple average over the 48 industry portfolio returns. D Suppose you would have invested $1 into each strategy at the end of December 1980 and rebalanced your portfolio monthly until 12/2015. How much would your investment be worth? Ignore transaction costs, taxes, etc. Construct a plot illustrating the evolution of the values of the two investments over time. E You should have found that the performance of the risk parity strategy relative to the 1/N strategy is much less impressive in this example than in the one we considered in Lecture 5. Speculate why this may be the case. (no more than 3 sentences) 3/3