VBA Break-even volatility skew computation

Break-even Volatility skew

In a future article we will introduce a methodology to compute the rolling average correlation smile of an index for a given tenor (say, 3-Month, 1-Year). To do so, we are led to manipulate the break-even volatility skews of each component of this index, and the index itself. However, we need to take into account something “purer” than implied volatilities measured a priori on the options market. By definition such implied volatility is not valid in our study, nor historical volatility, as the latter wouldn’t necessarily be adequate to describe a full moneyness range.

To circumvent these limitations, we rely on “break-even volatility”, which can be seen as an exact realisation of the volatility smile.

Logic behind breakeven volatility

We calculate the N-business days volatility (n=90,180,270,360) for each asset involved (index and the components). Ex, if he are interested in computing the 90 days average correlation of an index, we need the 90 days break-even volatility skews of the index, its components. Therefore we need 90 days timeseries of the dividend adjusted index, and its components. The market data required here lies in index, stock close prices, and the net dividends distributed over the delta hedging period. Pretty basic data, but it becomes very quickly a big database (the amount of data is proportional to the index composition, ex: CAC40 vs S&P500).

To illustrate this methodology, for every date i over the study period, we roll a N-business days period containing adjusted stock prices (the end date of the periods is defined according to a modified following convention).

For every component j of the index, on date i, we are setting an axis of 41 strikes K_{j} of S_{ij}. K_{j} \in [0.8:1.2].

We use the following procedure to get this breakeven close-to-close volatility: we calculate the value of the option on t_{0} and the value of the close-to-close delta hedge from t_{0 } to t_{0+N}, and we compare this to the initial implied premium. If the premium level on t_{0} is too high compared to the total hedge value on t _{0+T}, then we cap the flat volatility at its current level, and we move to another iteration.

This is an iterative process that we solve using dichotomy (a VBA algorithm of the procedure is attached at the end of this article), as illustrated in the following figure:

break-even volatility

break-even volatility computation steps

Strike settlement

To set the volatility surface, we assume a sticky ATMF-moneyness framework. We suppose a range of possible volatilities from 5% to 200%.


Repo and risk free rates are assumed to be null. An alternative would be to consider a continuous long term flat rate, say 2%.

We consider European vanilla call options, and we price them in a Black& Scholes framework (K: strike in % of the forward).

Delta hedging and dividend

Computing break-even skews over 4 years is tantamount to replicating a delta hedged option position, and the various flows produced by the option detention, from the settlement date to the option expiry. In other words, for 3-Month Call, we:

– Use \sigma (historical 3-Month volatility) as a proxy for the breakeven volatility.

– Given the moneyness K%, we calculate the Call premium C(K%, \sigma, 3m)t_{0}.

We apply the following notations in the formulas:

  • P&L stands for the close to close delta rebalancing, entailed by the long gamma position
  • P^{'} stands for the implied premium
  • day t
  • T maturity date
  • t^{adj} \frac{T-t}{365.25}

The initial Call premium on t_{0} is:

(1)   \begin{equation*} Call{t_{0}}^{'}=(S_{t_{0}}- \sum_{i=0}^{t}{Div_{i}})N(\frac{\ln (\frac{1}{K^{\%}})+\frac{\sigma ^{2}}{2}(T-t)}{\sigma \sqrt{T-t}})-K^{\%}N(\frac{\ln (\frac{1}{K^{\%}})-\frac{\sigma ^{2}}{2}(T-t)}{\sigma \sqrt{T-t}}) \end{equation*}

The Spot value S_{0} is adjusted of any future dividend occurring
during the hedging period.

– At any date t between t_{0} and T we compute the Call delta:

(2)   \begin{equation*} Delta_{t0}^{Call}=N(\frac{\ln (\frac{1}{K^{\%}})+\frac{\sigma ^{2}}{2}t_{0}^{adj}}{\sigma \sqrt{t_{0}^{adj}}}) \end{equation*}

(3)   \begin{equation*} Delta_{T}^{Call}=N(\frac{\ln (\frac{1}{K^{\%}})+\frac{\sigma ^{2}}{2}T^{adj}}{\sigma \sqrt{T^{adj}}}) \end{equation*}

We have to take into account any dividend payment during the hedge period. For most single stocks such events happen once a year. However when we impact the index with these dividend payments they can occur several times on a same hedging period, as illustrated in the following chart:

CAC40 Index Adjusted compared to Total return CAC40 Index

CAC40 Index Adjusted compared
to Total return CAC40 Index

Below is the formula for adjusting the Call delta accordingly:

(4)   \begin{equation*} Delta_{t}^{Call}=N(d_{1}) \end{equation*}


(5)   \begin{equation*} d_{1t}=\frac{\ln (\frac{S_{t}-\sum_{i=0}^{t}{Div_{i}}}{K^{\%}(S_{0}-\sum_{i=0}^{t}{Div_{i}})}+0.5\sigma^{2}(\frac{T-t}{365.25}))}{\sigma \sqrt{\frac{T-t}{365.25}}} \end{equation*}

The daily P&L is based on the delta variation \Delta_{DeltaCall}:

(6)   \begin{equation*} \Delta _{t}(Delta^{Call})=Delta_{t}-Delta_{t-1} \end{equation*}

The daily delta hedge is equal to:

(7)   \begin{equation*} DeltaHedge_{t}=Spot_{t} \Delta _{t}(Delta^{Call})=Spot_{t}(Delta_{t}-Delta_{t-1}) \end{equation*}

The Delta Hedge is also impacted with dividend payments. Consequently:

(8)   \begin{equation*} DeltaHedge_{t}=Spot_{t} \Delta _{t}(Delta^{Call})- 1_{t=exdate}(Div_{t}-Delta_{t-1}) \end{equation*}

The profit accumulated over the T days period is such that:

(9)   \begin{equation*} Profit=\sum_{t=1}^{T}{S_{t}\lbrack N(\frac{\ln (\frac{1}{K^{\%}})+\frac{\sigma ^{2}}{2}t^{adj}}{\sigma \sqrt{t^{adj}}})-N(\frac{\ln (\frac{1}{K^{\%}})+\frac{\sigma ^{2}}{2}(t-1)^{adj}}{\sigma \sqrt{(t-1)^{adj}}})\rbrack } \end{equation*}

At the end of the period, the profit of the option is equal to its intrinsic value, ie:

(10)   \begin{equation*} PNL_{T}^{Call}=(S_{T}-K^{\%}S_{0}) \end{equation*}

We are looking for \sigma such that:

(11)   \begin{equation*} \vert \frac{Call_{t0}(\sigma ,K^{\%},T)-TotalHedge(t_{0},T)}{Call_{t0}(\sigma ,K^{\%},T)}\vert \le 1.10^{-8}Call_{t0}(\sigma ,K^{\%},T) \end{equation*}

With Total Hedge:

(12)   \begin{equation*} TotalHedge(t_{0},T)=\sum_{t=t_{0}}^{T}{(Delta Hedge_{t})}+PNL_{T}^{Call} \end{equation*}

Several iterations of the Total Hedge calculation are performed, according to a dichotomy procedure.


(13)   \begin{equation*} \frac{Call_{t0}(\sigma ,K^{\%},T)-TotalHedge(t_{0},T)}{Call_{t0}(\sigma ,K^{\%},T)} > 1.10^{-8}Call_{t0}(\sigma ,K^{\%},T) \end{equation*}

then we consider that \sigma is too high, and we define \sigma as the upper boundary of the dichotomy. Conversely, if

(14)   \begin{equation*} \frac{Call_{t0}(\sigma ,K^{\%},T)-TotalHedge(t_{0},T)}{Call_{t0}(\sigma ,K^{\%},T)} < 1.10^{-8}Call_{t0}(\sigma ,K^{\%},T) \end{equation*}

then \sigma is too low, and \sigma becomes the lower boundary of the dichotomy.

Iterations of the algorithm are performed until \sigma = \sigma_{R } and:

(15)   \begin{equation*} \vert \frac{Call_{t0}(\sigma ,K^{\%},T)-TotalHedge(t_{0},T)}{Call_{t0}(\sigma ,K^{\%},T)}\vert \le 1.10^{-8}Call_{t0}(\sigma _{BE},K^{\%},T) \end{equation*}

The bandwith for \sigma is limited to [5%; 200%].

Considerations about the algorithm

A VBA/Excel sheet is attached at the end of this article. VBA is not ideal with regards to performances, given the amount of iterations. C++ or C# would be more adapted (maybe for another article 🙂 ).

A few manual calculations of breakeven volatility on index and its components tend to show that historical volatility \sigma is most of the time close to breakeven volatility \sigma_{BE}. Consequently we can use \sigma as a proxy to determine the upper and lower boundaries of the dichotomy procedure. There are two possibilities when proceeding to the
calculation of the successive breakeven-volatility per strike. One can either compute the volatilities starting from the 80% moneyness, until reaching the 120% moneyness, or consider that the 100% is the more reliable, and
therefore work out the ATM Vol, and compute the left and right in a second step.

It is convenient to split the algorithm into several functions. A first one to price the Call C(K%,T) on t_{0}. A second function calculates the delta hedge during the period considered, and returns the total hedge, etc.

At each step of the numerical procedure, we evaluate the ratio

(16)   \begin{equation*} \frac{C(K^{\%},T,\sigma )_{0}- PL(K^{\%},T,\sigma )_{T}}{C(K^{\%},T,\sigma )_{0}} \end{equation*}

The latter should be less than 0.0001 * C(K^{\%},T,\sigma )_{0} , before we can conclude that \sigma_{BE} = \sigma.

In practice, once we have gathered historical timeseries and dividends for the 51 components of the CAC40 universe from Jan 2005 to Aug 2009, we perform a batch that calls the RVolSmileForIndex() VBA function available in the annexes of this article.

For instance, the next figure is an outlook of the calculations for 1 month of break-even volatility at the beginning of 2015 for a stock over the study period.

Break-even volatility surface for Arcelor-Mittal (MTP) for 31/01/2015 to 23/02/2015

Break-even volatility surface of a CAC40 stock from 31/01/2015 to 23/02/2015

It is actually more relevant to talk about break-even volatility profiles, instead of smiles or skews. Indeed these profiles can vary significantly through time, and exhibit several types of slope, declinations in their convexity. And also it is noticeable that most of them reveal some concavity with regards to ITM and OTM forward moneyness.

The VBA code:



← Previous post

Next post →

1 Comment

  1. Aravind Menon

    Could you teach me how to use your spreadsheet? I am trying to make sense of it and don’t understand it/

Leave a Reply

Your email address will not be published. Required fields are marked *