Finance with Dr. John Elder
LE#2


Chapt 3 (Hedging) #12, 16, 17, 18, 19, 21, 24.
You can do #21 and #24 on a spreadsheet.
To access the statistical functions in Excel, you will have to load the Analyis Toolpack.

In old version of Excel:  Tools, Add-ins, Analysis Toolpack.  You may then need to exit and restart some versions of Excel.  To view the eligible statistical functions (such as correl, average and stdev), choose Insert, Function then select Statistical.

In new version of Excel: look up "Load the Analysis Toolpack" in the online help.  


Click to download a spreadsheet with three series.  These are
   1. monthly prices on a mutual fund
   2. monthly prices on the SP500 index.
   3. monthly returns on the T-bills.

For the first two series, calculate the monthly returns in a new column.
Find the average monthly return and the montly standard deviation.  Then annualize these values by multiplying the average monthly returns by 12, and the monthly standard deviations by sqrt(12).  
Calculate the correlation between each of the two series.
Calculate the excess return (relative to T-bills) on each series.
Estimate the CAPM regression (Data Analysis, Regression):  (Rp,t - Rf) = a + b*(Rm,t -Rf)+ et
What is the value of the regression coefficient?  This is an estimate of beta.
What is the R2?  How do you interpret the R2 and the coefficient, in terms of hedging?

Check Answers
12. on own
16. 3 contracts long
17. on own
 18. 26 contracts short.  That is N* = h* $P/$F contracts = 1.3*  1.5M/(50*1500) = 26 contracts short.  Note that we used the spot price (1500) to find the number of contracts rather than the futures price, which was not given.  We will always use the spot price to calculate the number of futures contracts.  It will give the best hedging performance when the contract maturity matches the hedging horizon.
21. 280 short; 120 long.
24. 139 contracts short



OTIS:  Buy about $350,000 of some well known mutual fund or ETF.    (Note that mutual fund database in OTIS is limited, so you might stick to big funds offered by, say, Fidelity or Vanguard.)   Now hedge the position with the appropriate position in S&P 500 index futures.  Record this transaction in your OTIS journals.