Statistical and Financial Functions used in Derivatives Excel has a number of statistical and financial functions that are often used in derivatives applications. This project is practice in identifying and using some of those functions.
- Optimal Hedge Ratio: Commodity Futures
This example uses the statistical properties of data to derive an optimal hedge ratio. In this case, it is using soybean price data.
Statistical and Financial Functions used in Derivatives
Suppose we have the following data:
Spot Price Change (S)Futures Price Change (F)
0.500.56
0.610.63
-0.22-0.12
-0.35-0.44
-0.51-0.56
-0.41-0.46
Using the correlation (insert function; statistical; ìCORRELî) function of Excel, calculate the correlation coefficient between changes in S and changes in F.
Using the standard deviation function (insert; function; statistical; ìSTDEVAî), calculate the standard deviations for changes in S and changes in F.
Statistical and Financial Functions used in Derivatives
Using these numbers, compute the optimal hedge ratio using formula 3.1 on page 60. OHR =? ss/sf
Plot the data in Excel and find the slope between changes in S and changes in F with the SLOPE function in Excel. Put the change in S on the y-axis and the change in F on the x-axis (like figure 3.2).
Is the slope number the same as the optimal hedge ratio calculated earlier?
- Optimal Hedge Ratio: Stock Index Futures Using Stock Beta
This example uses historical stock price data on a company and the market to compute the stockís beta, which is an optimal hedge ratio for a stock index hedge.
On ACE you will find an Excel spreadsheet with two columns of 2014 closing prices: the S&P 500 and YUM Brands, the parent company of the KFC, Taco Bell, and Pizza Hut brands. The data for the company and the S&P 500 was taken from the Yahoo! Finance web site (finance.yahoo.com).
Statistical and Financial Functions used in Derivatives
Find the percentage changes in each price series by the following formula: (current price-list period price)/last period price. Create a new series for the S&P and YUM using this formula.
Using the SLOPE function in Excel, find the slope of a regression line with changes in the S&P on the X-axis and changes in YUM on the Y-axis. This is the stockís beta coefficient. Plot the data and compute the slope that way.
Now compute the beta coefficient using the formulas beta = ?ym sy / sm. Use Excel functions VARA for S&P variance, STDEVA for the two standard deviations, and CORREL for the correlation coefficient between the market and YUM. It should match what you found in number 2, just like in the soybean example.
We can write this or a similar paper for you! Simply fill the order form!
