help
Equity-linked Securities: 1 FIN 403 – Individual Course Hedging Project – Fall 2022 Hedging a Stock Portfolio with S&P 500 Futures Contracts For this project, you will gain understanding of how a financial institution could use futures contracts to hedge against general market-level equity risk for their stock portfolio holdings, over a given period of time. You will also gain familiarity with futures contracts. Finally, you will refresh your statistical and Excel skills, because the exercise will require you to perform an OLS regression in Excel in order to estimate the hedge ratio. For the purposes of this assignment, we will ignore the discreteness in your hedge position, based on the size of futures contracts. Rather, we will assume that you can take a position in any dollar amount for your futures position. This project will evaluate the 2020-2021 timeframe as an interesting period historically with several instances when VIX was very elevated, even exceeding 60% briefly in March 2020 and the VIX exceeded 30 a couple times in 2021. Due Date: The due date is by 11:59 pm on Wednesday, November 30; files submitted to the Blackboard Assignment. See page 3 for details on the deliverables for this project. After this due date, we will discuss the project answers in our final day of class, to close the loop on this project. Hedging a Stock Portfolio with S&P 500 Futures Contracts This project will feature the posted “SP500_FuturesData” Excel spreadsheet. This spreadsheet contains daily returns that represent returns you would have approximately earned from holding a position in S&P500 futures contracts over 1/2/2020 to 12/31/2021 (Filename: SP500FutRet2020_21.xlsx). January 2, 2020 is the first trading day of that year.1 1. For our hypothetical stock holdings, we will use the ETF on the Russell MidCap Index to represent a hypothetical mid-cap stock portfolio that an institution might hold. See additional information on the Russell MidCap index on the last page of these instructions). To illustrate the concept of hedge mismatching (in terms of your operational holdings not aligning exactly with the underlying of the futures contract), we will use this mid-cap index ETF. Go to Yahoo Finance and select the price history time-period from 12/31/2019 to 01/01/2022 (in the dropdown menu Yahoo choice) for the ticker IWR (which is the ETF for the Russell 2000) and then download the data to an Excel spreadsheet. Then, use the adjusted closing price (Adj Close) to calculate the daily returns for this ETF stock fund for each trading day over 2020-2021 to match with the daily returns of the S&P futures (that were given to you).2 It is recommended to compute and work with percentage 1 Note that the “futures return” is really the percentage change in the price of the futures contract; rather than an actual return because there is no initial outlay when you enter into a future contract. 2 With this download and calculation, you should end up with 505 daily returns, dating from 1/2/2020 to 12/30/2021. 2 return units (rather than decimal), for ease of review.3 Then, merge the stock-fund returns and the S&P 500 Futures returns together, by date, in a single worksheet for statistical evaluation.4 2. To evaluate whether the S&P 500 futures contract appears to useful as a good hedge against equity-market risk for your stock fund, graph the S&P 500 futures return (as the x-variable) against the IWR stock-fund return (as the y-variable) using a scatter graph from Excel, with an included linear regression line over the 2020 evaluation period only (so do not graph all your data, but just that over 2020. Include this graph as an appendix in your deliverables for this assignment. 3. Next, assume that you are a hedge fund and you have a broad mid-cap oriented stock portfolio (represented by the IWR fund) that you might periodically wish to hedge against general stock market risk. For the purposes of the hedge exercise, assume that the beginning value of your stock portfolio is $225 million. In this exercise, you will evaluate how a stock hedge would have performed over 2021, based on a hedge ratio estimated with 2020 daily data. Specifically: a) In terms of the timing for this hedge, pretend that it is the end of 2020 and you just received the stock returns for your portfolio over 2020 (as calculated from the Yahoo IWR prices). Then, to determine the hedge ratio for your hedge going forward, regress the daily percentage returns of the S&P 500 futures contracts (as your explanatory x variable) against the daily IWR returns of your stock fund (as the dependent y variable) over 2020 only. Include a copy of this Excel regression as an appendix in your deliverables for this assignment. b) From your regression above over the 2020 period, what proportion of the variance of your stock- fund returns are you able to explain based on the S&P 500 futures returns? Based on your estimated Beta, what should be your dollar position (exposure) in the futures market to appropriately hedge your stock portfolio, going forward?5 c) Next, assume that you are going to set up your hedge at the beginning of 2021, using the hedge ratio determined in a) above and with a starting value of a $225 million stock portfolio at the beginning of each trading day over 2021, where you assume that your portfolio returns are those of the IWR ETF.6 Given these return outcomes over 2021, use Excel to determine: Without the hedge, what would have been the standard deviation of your daily dollar gain/loss (or change in the value of your portfolio in dollars) ? With the hedge, what would have been the standard 3 You can either format the cells in Excel to “%”, or multiply the decimal returns by 100, so that the number displayed in Excel is a percentage return. 4 Recommend using Excel’s VLookUp function to merge the two return series. 5 For this application: _(in dollars) (in dollars)*f S futures spotN N β= − where the beta is from a regression of the futures return as the x-variable versus the spot return as the y-variable. This is an application of the minimum variance hedge ratio discussed on pages 355-360 in the Chance textbook, as we derived in class. 6 Thus, these instructions assume that your daily holdings are consistently about $225 million, but if this hedge was actually put into place there would be day-to-day fluctuations in the fund’s value. 3 deviation of your daily dollar gain/loss? Without the hedge, what would have been your greatest daily dollar loss over this period? With the hedge, what would have been your greatest daily dollar loss over this period? Include a copy of this Excel spreadsheet where you did these calculations as a separate file for your deliverables for this assignment (Hint: Do not forget to review the example spreadsheet from the in-class demonstration.) Deliverables: Your report should include: 1) Header Information: Project title, class, & date. 2) Name.; 3) A summary written report, in the spirt of an Executive Summary, that concisely summarizes the problem assignment and your work and findings. The summary report should briefly address the embedded questions in the above instructions. (See http://en.wikipedia.org/wiki/Executive_summary for a review of Executive Summaries). 4) The requested spreadsheet, regression output, and graph that are asked for in the instructions should be included as appendices that are referred to in the written report. See the demonstration file as an example format for your spreadsheet. 5) Report Due: Due date is by 11:59 pm by Wednesday, November 30. Your deliverables should be submitted through the Blackboard assignment. For the graph and regression output; you can paste the required parts into MS Word and submit as a Word file (or .pdf file) that contains the Executive Summary and the answers for the project, with the graph and regression output as appendices to the Executive Summary. But, don’t forget to also submit your supporting spreadsheet as a second file. Make sure to name the files so for clarity. (For example: FIN403_Project2_Hedge_Jones.doc and FIN403_Project2_Hedge_Jones.xlsx) ____________________________ About the Russell Midcap Index, from Investopedia: The Russell Midcap Index is a market capitalization-weighted index comprised of 800 publicly traded U.S. companies with market caps of between $2 and $10 billion. The 800 companies in the Russell Midcap Index are the 800 smallest of the 1,000 companies that comprise Russell 1000 Index. http://en.wikipedia.org/wiki/Executive_summary FIN 403 – Individual Course Hedging Project – Fall 2022 Hedging a Stock Portfolio with S&P 500 Futures Contracts Data Notes: These returns are "daily percentage returns" that approximate those from holding a position in a near-term S&P 500 futures contracts. (So, these are "percentage returns", not prices. For the project, you will have to calculate comparable percentage returns from the adjusting closing prices of the IWR ETF.) DateSP500FutRet 1/2/200.935% 1/3/20-0.757% 1/6/200.381% 1/7/20-0.281% 1/8/200.533% 1/9/200.678% 1/10/20-0.288% 1/13/200.688% 1/14/20-0.152% 1/15/200.226% 1/16/200.832% 1/17/200.311% 1/21/20-0.196% 1/22/200.012% 1/23/200.115% 1/24/20-0.889% 1/27/20-1.603% 1/28/201.048% 1/29/20-0.083% 1/30/200.325% 1/31/20-1.816% 2/3/200.743% 2/4/201.524% 2/5/201.155% 2/6/200.336% 2/7/20-0.533% 2/10/200.747% 2/11/200.173% 2/12/200.644% 2/13/20-0.107% 2/14/200.160% 2/18/20-0.258% 2/19/200.478% 2/20/20-0.411% 2/21/20-1.030% 2/24/20-3.317% 2/25/20-3.030% 2/26/20-0.368% 2/27/20-4.491% 2/28/20-0.420% 3/2/204.331% 3/3/20-2.863% 3/4/204.203% 3/5/20-3.324% 3/6/20-1.653% 3/9/20-7.809% 3/10/205.174% 3/11/20-4.875% 3/12/20-9.568% 3/13/208.549% 3/16/20-10.942% 3/17/205.399% 3/18/20-5.063% 3/19/200.212% 3/20/20-4.309% 3/23/20-2.557% 3/24/209.060% 3/25/201.497% 3/26/205.839% 3/27/20-2.979% 3/30/203.248% 3/31/20-1.491% 4/1/20-4.500% 4/2/202.308% 4/3/20-1.445% 4/6/206.717% 4/7/200.102% 4/8/203.357% 4/9/201.522% 4/13/20-0.913% 4/14/202.949% 4/15/20-2.125% 4/16/200.482% 4/17/202.702% 4/20/20-1.762% 4/21/20-3.036% 4/22/202.219% 4/23/20-0.007% 4/24/201.394% 4/27/201.442% 4/28/20-0.460% 4/29/202.618% 4/30/20-0.931% 5/1/20-2.647% 5/4/200.276% 5/5/200.924% 5/6/20-0.678% 5/7/201.207% 5/8/201.655% 5/11/200.021% 5/12/20-1.993% 5/13/20-1.769% 5/14/201.197% 5/15/200.460% 5/18/203.046% 5/19/20-1.027% 5/20/201.699% 5/21/20-0.690% 5/22/200.190% 5/26/201.232% 5/27/201.488% 5/28/20-0.184% 5/29/200.446% 6/1/200.404% 6/2/200.828% 6/3/201.331% 6/4/20-0.263% 6/5/202.563% 6/8/201.209% 6/9/20-0.746% 6/10/20-0.558% 6/11/20-5.765% 6/12/201.198% 6/15/200.934% 6/16/201.925% 6/17/20-0.415% 6/18/200.038% 6/19/20-0.572% 6/22/200.642% 6/23/200.460% 6/24/20-2.551% 6/25/201.072% 6/26/20-2.375% 6/29/201.470% 6/30/201.281% 7/1/200.700% 7/2/200.551% 7/6/201.544% 7/7/20-1.031% 7/8/200.765% 7/9/20-0.569% 7/10/201.021% 7/13/20-0.866% 7/14/201.296% 7/15/200.919% 7/16/20-0.329% 7/17/200.290% 7/20/200.808% 7/21/200.213% 7/22/200.569% 7/23/20-1.193% 7/24/20-0.644% 7/27/200.729% 7/28/20-0.634% 7/29/201.230% 7/30/20-0.357% 7/31/200.790% 8/3/200.695% 8/4/200.386% 8/5/200.621% 8/6/200.668% 8/7/200.072% 8/10/200.299% 8/11/20-0.825% 8/12/201.394% 8/13/20-0.181% 8/14/200.003% 8/17/200.318% 8/18/200.216% 8/19/20-0.416% 8/20/200.311% 8/21/200.355% 8/24/201.013% 8/25/200.350% 8/26/201.003% 8/27/200.219% 8/28/200.646% 8/31/20-0.362% 9/1/200.942% 9/2/201.446% 9/3/20-3.441% 9/4/20-0.816% 9/8/20-2.732% 9/9/201.975% 9/10/20-1.736% 9/11/200.051% 9/14/201.317% 9/15/200.505% 9/16/20-0.397% 9/17/20-0.880% 9/18/20-1.151% 9/21/20-1.113% 9/22/201.018% 9/23/20-2.319% 9/24/200.267% 9/25/201.617% 9/28/201.661% 9/29/20-0.545% 9/30/200.758% 10/1/200.642% 10/2/20-0.949% 10/5/201.773% 10/6/20-1.422% 10/7/201.741% 10/8/200.886% 10/9/200.893% 10/12/201.609% 10/13/20-0.653% 10/14/20-0.628% 10/15/20-0.124% 10/16/20-0.060% 10/19/20-1.520% 10/20/200.401% 10/21/20-0.189% 10/22/200.549% 10/23/200.340% 10/26/20-1.848% 10/27/20-0.345% 10/28/20-3.418% 10/29/201.016% 10/30/20-1.042% 11/2/201.121% 11/3/201.766% 11/4/202.235% 11/5/201.950% 11/6/20-0.023% 11/9/201.257% 11/10/20-0.147% 11/11/200.743% 11/12/20-0.970% 11/13/201.384% 11/16/201.248% 11/17/20-0.538% 11/18/20-1.203% 11/19/200.421% 11/20/20-0.685% 11/23/200.599% 11/24/201.611% 11/25/20-0.154% 11/27/200.278% 11/30/20-0.443% 12/1/201.094% 12/2/200.210% 12/3/20-0.027% 12/4/200.862% 12/7/20-0.205% 12/8/200.293% 12/9/20-0.897% 12/10/20-0.033% 12/11/20-0.117% 12/14/20-0.448% 12/15/201.352% 12/16/200.157% 12/17/200.559% 12/18/20-0.399% 12/21/20-0.358% 12/22/20-0.169% 12/23/200.090% 12/24/200.389% 12/28/200.859% 12/29/20-0.191% 12/30/200.143% 12/31/200.508% 1/4/21-1.361% 1/5/210.689% 1/6/210.598% 1/7/211.486% 1/8/210.570% 1/11/21-0.674% 1/12/210.021% 1/13/210.269% 1/14/21-0.350% 1/15/21-0.729% 1/19/210.785% 1/20/211.384% 1/21/210.091% 1/22/21-0.354% 1/25/210.394% 1/26/21-0.156% 1/27/21-2.444% 1/28/210.860% 1/29/21-2.002% 2/1/211.665% 2/2/211.414% 2/3/210.079% 2/4/211.137% 2/5/210.394% 2/8/210.722% 2/9/21-0.067% 2/10/21-0.044% 2/11/210.162% 2/12/210.494% 2/16/21-0.087% 2/17/210.023% 2/18/21-0.426% 2/19/21-0.177% 2/22/21-0.769% 2/23/210.121% 2/24/211.102% 2/25/21-2.410% 2/26/21-0.515% 3/1/212.424% 3/2/21-0.780% 3/3/21-1.325% 3/4/21-1.237% 3/5/211.840% 3/8/21-0.498% 3/9/211.428% 3/10/210.622% 3/11/211.014% 3/12/210.135% 3/15/210.596% 3/16/21-0.126% 3/17/210.341% 3/18/21-1.455% 3/19/21-0.185% 3/22/210.798% 3/23/21-0.787% 3/24/21-0