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
efresh your statistical and Excel skills, because the exercise will require you to perform an OLS
egression 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 XXXXXXXXXXtimeframe as an interesting period historically with several
instances when VIX was very elevated, even exceeding 60%
iefly 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
epresent 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 XXXXXXXXXXto 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
eturn 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
oad 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.
) 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
atio determined in a) above and with a starting value of a $225 million stock portfolio at the
eginning 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 XXXXXXXXXXin 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
iefly 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 refe
ed 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
egression 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
egression 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.)
Date SP500FutRet
1/2/20 0.935%
1/3/20 -0.757%
1/6/20 0.381%
1/7/20 -0.281%
1/8/20 0.533%
1/9/20 0.678%
1/10/20 -0.288%
1/13/20 0.688%
1/14/20 -0.152%
1/15/20 0.226%
1/16/20 0.832%
1/17/20 0.311%
1/21/20 -0.196%
1/22/20 0.012%
1/23/20 0.115%
1/24/20 -0.889%
1/27/20 -1.603%
1/28/20 1.048%
1/29/20 -0.083%
1/30/20 0.325%
1/31/20 -1.816%
2/3/20 0.743%
2/4/20 1.524%
2/5/20 1.155%
2/6/20 0.336%
2/7/20 -0.533%
2/10/20 0.747%
2/11/20 0.173%
2/12/20 0.644%
2/13/20 -0.107%
2/14/20 0.160%
2/18/20 -0.258%
2/19/20 0.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/20 4.331%
3/3/20 -2.863%
3/4/20 4.203%
3/5/20 -3.324%
3/6/20 -1.653%
3/9/20 -7.809%
3/10/20 5.174%
3/11/20 -4.875%
3/12/20 -9.568%
3/13/20 8.549%
3/16/20 -10.942%
3/17/20 5.399%
3/18/20 -5.063%
3/19/20 0.212%
3/20/20 -4.309%
3/23/20 -2.557%
3/24/20 9.060%
3/25/20 1.497%
3/26/20 5.839%
3/27/20 -2.979%
3/30/20 3.248%
3/31/20 -1.491%
4/1/20 -4.500%
4/2/20 2.308%
4/3/20 -1.445%
4/6/20 6.717%
4/7/20 0.102%
4/8/20 3.357%
4/9/20 1.522%
4/13/20 -0.913%
4/14/20 2.949%
4/15/20 -2.125%
4/16/20 0.482%
4/17/20 2.702%
4/20/20 -1.762%
4/21/20 -3.036%
4/22/20 2.219%
4/23/20 -0.007%
4/24/20 1.394%
4/27/20 1.442%
4/28/20 -0.460%
4/29/20 2.618%
4/30/20 -0.931%
5/1/20 -2.647%
5/4/20 0.276%
5/5/20 0.924%
5/6/20 -0.678%
5/7/20 1.207%
5/8/20 1.655%
5/11/20 0.021%
5/12/20 -1.993%
5/13/20 -1.769%
5/14/20 1.197%
5/15/20 0.460%
5/18/20 3.046%
5/19/20 -1.027%
5/20/20 1.699%
5/21/20 -0.690%
5/22/20 0.190%
5/26/20 1.232%
5/27/20 1.488%
5/28/20 -0.184%
5/29/20 0.446%
6/1/20 0.404%
6/2/20 0.828%
6/3/20 1.331%
6/4/20 -0.263%
6/5/20 2.563%
6/8/20 1.209%
6/9/20 -0.746%
6/10/20 -0.558%
6/11/20 -5.765%
6/12/20 1.198%
6/15/20 0.934%
6/16/20 1.925%
6/17/20 -0.415%
6/18/20 0.038%
6/19/20 -0.572%
6/22/20 0.642%
6/23/20 0.460%
6/24/20 -2.551%
6/25/20 1.072%
6/26/20 -2.375%
6/29/20 1.470%
6/30/20 1.281%
7/1/20 0.700%
7/2/20 0.551%
7/6/20 1.544%
7/7/20 -1.031%
7/8/20 0.765%
7/9/20 -0.569%
7/10/20 1.021%
7/13/20 -0.866%
7/14/20 1.296%
7/15/20 0.919%
7/16/20 -0.329%
7/17/20 0.290%
7/20/20 0.808%
7/21/20 0.213%
7/22/20 0.569%
7/23/20 -1.193%
7/24/20 -0.644%
7/27/20 0.729%
7/28/20 -0.634%
7/29/20 1.230%
7/30/20 -0.357%
7/31/20 0.790%
8/3/20 0.695%
8/4/20 0.386%
8/5/20 0.621%
8/6/20 0.668%
8/7/20 0.072%
8/10/20 0.299%
8/11/20 -0.825%
8/12/20 1.394%
8/13/20 -0.181%
8/14/20 0.003%
8/17/20 0.318%
8/18/20 0.216%
8/19/20 -0.416%
8/20/20 0.311%
8/21/20 0.355%
8/24/20 1.013%
8/25/20 0.350%
8/26/20 1.003%
8/27/20 0.219%
8/28/20 0.646%
8/31/20 -0.362%
9/1/20 0.942%
9/2/20 1.446%
9/3/20 -3.441%
9/4/20 -0.816%
9/8/20 -2.732%
9/9/20 1.975%
9/10/20 -1.736%
9/11/20 0.051%
9/14/20 1.317%
9/15/20 0.505%
9/16/20 -0.397%
9/17/20 -0.880%
9/18/20 -1.151%
9/21/20 -1.113%
9/22/20 1.018%
9/23/20 -2.319%
9/24/20 0.267%
9/25/20 1.617%
9/28/20 1.661%
9/29/20 -0.545%
9/30/20 0.758%
10/1/20 0.642%
10/2/20 -0.949%
10/5/20 1.773%
10/6/20 -1.422%
10/7/20 1.741%
10/8/20 0.886%
10/9/20 0.893%
10/12/20 1.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/20 0.401%
10/21/20 -0.189%
10/22/20 0.549%
10/23/20 0.340%
10/26/20 -1.848%
10/27/20 -0.345%
10/28/20 -3.418%
10/29/20 1.016%
10/30/20 -1.042%
11/2/20 1.121%
11/3/20 1.766%
11/4/20 2.235%
11/5/20 1.950%
11/6/20 -0.023%
11/9/20 1.257%
11/10/20 -0.147%
11/11/20 0.743%
11/12/20 -0.970%
11/13/20 1.384%
11/16/20 1.248%
11/17/20 -0.538%
11/18/20 -1.203%
11/19/20 0.421%
11/20/20 -0.685%
11/23/20 0.599%
11/24/20 1.611%
11/25/20 -0.154%
11/27/20 0.278%
11/30/20 -0.443%
12/1/20 1.094%
12/2/20 0.210%
12/3/20 -0.027%
12/4/20 0.862%
12/7/20 -0.205%
12/8/20 0.293%
12/9/20 -0.897%
12/10/20 -0.033%
12/11/20 -0.117%
12/14/20 -0.448%
12/15/20 1.352%
12/16/20 0.157%
12/17/20 0.559%
12/18/20 -0.399%
12/21/20 -0.358%
12/22/20 -0.169%
12/23/20 0.090%
12/24/20 0.389%
12/28/20 0.859%
12/29/20 -0.191%
12/30/20 0.143%
12/31/20 0.508%
1/4/21 -1.361%
1/5/21 0.689%
1/6/21 0.598%
1/7/21 1.486%
1/8/21 0.570%
1/11/21 -0.674%
1/12/21 0.021%
1/13/21 0.269%
1/14/21 -0.350%
1/15/21 -0.729%
1/19/21 0.785%
1/20/21 1.384%
1/21/21 0.091%
1/22/21 -0.354%
1/25/21 0.394%
1/26/21 -0.156%
1/27/21 -2.444%
1/28/21 0.860%
1/29/21 -2.002%
2/1/21 1.665%
2/2/21 1.414%
2/3/21 0.079%
2/4/21 1.137%
2/5/21 0.394%
2/8/21 0.722%
2/9/21 -0.067%
2/10/21 -0.044%
2/11/21 0.162%
2/12/21 0.494%
2/16/21 -0.087%
2/17/21 0.023%
2/18/21 -0.426%
2/19/21 -0.177%
2/22/21 -0.769%
2/23/21 0.121%
2/24/21 1.102%
2/25/21 -2.410%
2/26/21 -0.515%
3/1/21 2.424%
3/2/21 -0.780%
3/3/21 -1.325%
3/4/21 -1.237%
3/5/21 1.840%
3/8/21 -0.498%
3/9/21 1.428%
3/10/21 0.622%
3/11/21 1.014%
3/12/21 0.135%
3/15/21 0.596%
3/16/21 -0.126%
3/17/21 0.341%
3/18/21 -1.455%
3/19/21 -0.185%
3/22/21 0.798%
3/23/21 -0.787%
3/24/21 -0