Break-even volatility computation with VBA

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 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%.

Assumptions

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
handling

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

Below is the formula for adjusting the Call delta accordingly:

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

With

(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:

'**************************************************************************
'************************* MH BATTISTINI - J. MESSIAS *********************
'*************************     Breakeven Volatility    ********************
'***********************************  2012  *******************************
'**************************************************************************

Option Explicit

Const NbCol = 40
Const MaxIter = 102
Const MinVol = 0.06

Public Function RVolSmileForIndex(ByVal SpotArray As Range, _
                                    ByVal DivArray As Range, _
                                    ByVal Tenor As String, _
                                    ByVal AccuracyFactor As Double, _
                                    ByVal DisplayStrikeAxis As Boolean, _
                                    ByVal Transpose As Boolean)
'Main Function to calculate realized Volatility on a Double date
'Spot Array is a 2 columns array containing Date, Adjusted Close Price
Dim StrikeInPerc As Double, Strike0 As Double, epsilon As Double, HedgePremiumDiff As Double
Dim DivDate As Date, curdate As Date, Enddate As Date
Dim TimeToExpiry As Double, Volsup As Double, Volinf As Double
Dim RVol As Double, Premium1 As Double, Hedge As Double
Dim DivAmt As Variant, Divs As Variant
Dim Spots As Variant: Spots = SpotArray.value
Dim maxitem As Integer: maxitem = 0
Dim i As Integer, j As Integer, k As Integer, l As Integer, NbRow As Integer

If DisplayStrikeAxis Then NbRow = 1 Else NbRow = 0
ReDim Smile(0 To NbRow, 0 To NbCol)
On Error GoTo errhandler

'We define the 3M option expiry date with modified following convention
curdate = Spots(1, 1)

For i = 0 To 5
curdate = curdate + i
    If CalcEndDate(curdate, Spots, Tenor)(0, 0) <> 0 Then
        Enddate = CalcEndDate(curdate, Spots, Tenor)(0, 0)
        maxitem = CalcEndDate(curdate, Spots, Tenor)(0, 1)
        Exit For
    End If
Next i

ReDim AdjSpotArray(1 To maxitem, 1 To 2)
ReDim PriceArray(1 To maxitem - 1, 1 To 1)
Dim StDev As Single
TimeToExpiry = 0.0001 + (Spots(maxitem, 1) - Spots(1, 1)) / 365.25

For j = 1 To maxitem
    For k = 1 To 2
        AdjSpotArray(j, 1) = Spots(j, 1)
        AdjSpotArray(j, 2) = Spots(j, 2)
    Next k
Next j
For j = 1 To maxitem - 1
    PriceArray(j, 1) = Log(Spots(j + 1, 2) / Spots(j, 2))
Next j
StDev = TableStdDev(PriceArray) * 252 ^ 0.5
'We extract from the dividend array the eventual dividend falling in the 3M period
Divs = DivArray.value

Strike0 = 0.8
For i = 0 To NbCol
    StrikeInPerc = Strike0 + i / 100

    If i >= 1 Then
        Volsup = 2 * MaxInArray(Smile, DisplayStrikeAxis)
        Volinf = MinVol
    Else
        Volsup = 2 * StDev
        Volinf = MinVol
    End If
        For l = 1 To MaxIter
            RVol = (Volsup + Volinf) / 2
            Premium1 = Premium(AdjSpotArray, RVol, StrikeInPerc, Divs, TimeToExpiry)
            Hedge = HedgeCost(AdjSpotArray, RVol, StrikeInPerc, Divs)
            HedgePremiumDiff = (Premium1 - Hedge) / Premium1

            If Abs(HedgePremiumDiff) < AccuracyFactor Then
                Exit For
            Else
                If Abs(HedgePremiumDiff) > AccuracyFactor And (Premium1 >= 0 And Hedge >= 0) And Premium1 > Hedge Then
                    Volsup = max(RVol, 0)
                ElseIf Abs(HedgePremiumDiff) > AccuracyFactor And (Premium1 >= 0 And Hedge >= 0) And Premium1 < Hedge Then
                    Volinf = max(RVol, 0)
                ElseIf Premium1 >= 0 And Hedge <= 0 Then
                    Volsup = max(RVol, 0)
                End If
            End If
            'Debug.Print Volinf
            'Debug.Print HedgePremiumDiff
            'Debug.Print Volinf
        Next l
    If DisplayStrikeAxis Then
        Smile(0, i) = Round(StrikeInPerc, 2)
        Smile(1, i) = RVol
    Else
        Smile(0, i) = RVol
    End If
Next i
If Transpose Then
    RVolSmileForIndex = WorksheetFunction.Transpose(Smile)
Else
    RVolSmileForIndex = Smile
End If
Exit Function
errhandler:
Debug.Print "RVolSmileForIndex: " & Err.Description
Exit Function
End Function

Public Function HedgeCost(ByVal AdjSpotArray, _
                        ByVal Vol As Double, _
                        ByVal StrikePerc As Double, _
                        ByVal Divs) As Double
On Error GoTo errhandler
'AdjSpotArray is a one column array containing adj prices
Dim i As Integer, l As Integer, tempHedge As Double, deltadiv As Double
Dim maxitem As Integer: maxitem = UBound(AdjSpotArray)
ReDim temptable(1 To maxitem, 1 To 3)
Dim prevDelta As Double: prevDelta = 0
For i = 1 To maxitem
    temptable(i, 1) = DeltaHedge(AdjSpotArray(i, 1), AdjSpotArray(1, 1), AdjSpotArray(1, 2), _
                            AdjSpotArray(i, 2), Vol, StrikePerc, Divs, AdjSpotArray(maxitem, 1))
    If i = 1 Then
        temptable(i, 2) = 0
    Else
        temptable(i, 2) = temptable(i, 1) - prevDelta
    End If
    If i = maxitem Then
        temptable(i, 3) = -AdjSpotArray(i, 2) * temptable(i - 1, 1)
    ElseIf i = 1 Then
        temptable(i, 3) = AdjSpotArray(i, 2) * temptable(i, 1)
    Else
        temptable(i, 3) = AdjSpotArray(i, 2) * temptable(i, 2)
    End If
    prevDelta = temptable(i, 1)
    tempHedge = tempHedge + temptable(i, 3)
    For l = 1 To UBound(Divs)
        If AdjSpotArray(i, 1) = Divs(l, 1) And i >= 2 Then
            deltadiv = deltadiv - temptable(i - 1, 1) * Divs(l, 2)
        End If
    Next l
    'Debug.Print temptable(i, 3)
'    Debug.Print temptable(i, 2)
'    Debug.Print temptable(i, 3)
Next i
Dim FinalOptionPrice As Double: FinalOptionPrice = max(AdjSpotArray(maxitem, 2) - StrikePerc * (AdjSpotArray(1, 2) - _
            CalculateDivAmount2(AdjSpotArray(1, 1), AdjSpotArray(maxitem, 1), Divs)), 0)
HedgeCost = tempHedge + deltadiv + FinalOptionPrice

'Debug.Print "Sum Hedge =" & tempHedge
'Debug.Print "Sum DeltaDiv =" & deltadiv
'Debug.Print "Final OptionPrice =" & FinalOptionPrice
'Debug.Print "Hedge Cost =" & HedgeCost
Exit Function
errhandler:
Dim msgstring As String
Dim Response2
msgstring = "HedgeCost: " & i & Err.Description & " Continuer?"
Response2 = MsgBox(msgstring, vbOKCancel)
If Response2 <> 6 Then
    Exit Function
End If
End Function

Public Function DeltaHedge(ByVal Datei As Long, _
                            ByVal Date0 As Long, _
                            ByVal AdjPrice0 As Double, _
                            ByVal AdjPricei As Double, _
                            ByVal Vol As Double, _
                            ByVal StrikePercentage As Double, _
                            ByVal DivArray, _
                            ByVal D30EndDate As Long) As Variant
On Error GoTo errhandler
    Dim a As Double: a = AdjPricei - CalculateDivAmount2(Datei, D30EndDate, DivArray)
    Dim b As Double: b = (StrikePercentage * (AdjPrice0 - CalculateDivAmount2(Date0, D30EndDate, DivArray)))
    Dim c As Double: c = 0.5 * Vol * Vol * (0.0001 + (D30EndDate - Datei) / 365.25)
    Dim d As Double: d = (Vol * (0.0001 + ((D30EndDate - Datei) / 365.25)) ^ 0.5)
    Dim e As Double: e = a / b
    Dim d1 As Double: d1 = (Application.WorksheetFunction.Ln(e) + c) / d
    DeltaHedge = Application.WorksheetFunction.NormSDist(d1)
    Exit Function
errhandler:
    Debug.Print "Delta: " & Err.Description
    Exit Function
End Function

Public Function Premium(ByVal Spots, _
                        ByVal Vol As Double, _
                        ByVal StrikePercentage As Single, _
                        ByVal Divs, _
                        ByVal TimeToExpiry As Double) As Double
On Error GoTo errhandler
    Dim maxitem As Integer: maxitem = UBound(Spots)
    Dim Volsqrttime As Double: Volsqrttime = Vol * TimeToExpiry ^ 0.5
    Dim timeXvol As Double: timeXvol = Vol * TimeToExpiry
    Dim a As Double: a = (Application.WorksheetFunction.Ln(1 / StrikePercentage) + 0.5 * Vol * timeXvol) / Volsqrttime
    Dim b As Double: b = (Application.WorksheetFunction.Ln(1 / StrikePercentage) - 0.5 * Vol * timeXvol) / Volsqrttime
    Premium = (Spots(1, 2) - CalculateDivAmount2(Spots(1, 1), Spots(maxitem, 1), Divs)) * _
            (Application.WorksheetFunction.NormSDist(a) - StrikePercentage * Application.WorksheetFunction.NormSDist(b))
    Exit Function
errhandler:
    Debug.Print "Premium: " & Err.Description
End Function

Public Function DivInPeriod(ByVal SpotArray, ByVal DivArray) As Variant
On Error GoTo errhandler
    Dim i As Integer
    Dim temptable(1 To 1, 1 To 2)
    Dim maxitem As Integer: maxitem = UBound(SpotArray)
    Dim Date0 As Date: Date0 = SpotArray(1, 1)
    Dim dateend As Date: dateend = SpotArray(maxitem, 1)
    For i = 1 To UBound(DivArray)
        If DivArray(i, 1) >= Date0 And DivArray(i, 1) <= dateend Then
            temptable(1, 2) = DivArray(i, 2)
            temptable(1, 1) = DivArray(i, 1)
            DivInPeriod = temptable
            Exit Function
        End If
    Next i
    temptable(1, 2) = 0
    temptable(1, 1) = 0
    DivInPeriod = temptable
    Exit Function
errhandler:
    Debug.Print "DivInPeriod: " & Err.Description
End Function
Public Function IsDivInPeriod(ByVal SpotArray, ByVal DivArray) As Boolean
On Error GoTo errhandler
    Dim i As Integer
    Dim maxitem As Integer: maxitem = UBound(SpotArray)
    Dim temptable(1 To 1, 1 To 2)
    Dim Date0 As Date: Date0 = SpotArray(1, 1)
    Dim dateend As Date: dateend = SpotArray(maxitem, 1)
    For i = 1 To UBound(DivArray)
        If DivArray(i, 1) > Date0 And DivArray(i, 1) <= dateend Then
               IsDivInPeriod = True
            Exit Function
        End If
    Next i
    IsDivInPeriod = False
    Exit Function
errhandler:
    Debug.Print "IsDivInPeriod: " & Err.Description
End Function

Function max(ByVal a As Double, ByVal b As Double) As Double
If a > b Then max = a Else max = b
End Function

Function Min(ByVal a As Double, ByVal b As Double) As Double
If a > b Then Min = b Else Min = a
End Function

Public Function CalculateDivAmount(ByVal curdate As Date, _
                                    ByVal Enddate As Date, _
                                    DivArray As Range) As Double
Dim i As Integer, DivAmount As Double
Dim Divs As Variant: Divs = DivArray.value
For i = 1 To UBound(Divs)
    If DivArray(i, 1) > curdate And DivArray(i, 1) <= Enddate Then
        DivAmount = DivAmount + DivArray(i, 2)
    End If
Next i
CalculateDivAmount = DivAmount
End Function

Public Function CalculateDivAmount2(ByVal curdate As Date, _
                ByVal Enddate As Date, _
                ByVal DivArray) As Double
Dim Divs, i As Integer
Dim DivAmount As Double: DivAmount = 0
For i = 1 To UBound(DivArray)
    If DivArray(i, 1) > curdate And DivArray(i, 1) <= Enddate Then
        DivAmount = DivAmount + DivArray(i, 2)
    End If
Next i
CalculateDivAmount2 = DivAmount
End Function

Public Function MaxInArray(ByVal Smiles, ByVal DisplayStrikes As Boolean) As Double
Dim i As Integer
Dim max As Integer: max = UBound(Smiles, 2) - 1
If DisplayStrikes Then
    MaxInArray = Smiles(1, 0)
        For i = 0 To max
            If Not IsEmpty(Smiles(1, i + 1)) Then
                If Smiles(1, i + 1) > MaxInArray Then
                    MaxInArray = Smiles(1, i + 1)
                End If
            Else
                Exit For
            End If
        Next i
Else
    MaxInArray = Smiles(0, 0)
        For i = 0 To max
            If Not IsEmpty(Smiles(0, i + 1)) Then
                If Smiles(0, i + 1) > MaxInArray Then
                    MaxInArray = Smiles(0, i + 1)
                End If
            Else
                Exit For
            End If
        Next i
End If
End Function

Public Function CalcEndDate(ByVal curdate As Date, ByVal Spots, ByVal Tenor As String)
Dim tableresult
Dim maxitem As Integer, i As Integer
ReDim tableresult(0 To 0, 0 To 1)
Dim TenorCode As String: TenorCode = Right(Tenor, 1)
Dim NbTenor As Double: NbTenor = Left(Tenor, Len(Tenor) - 1)
Dim Enddate As Date: Enddate = DateTime.DateAdd(TenorCode, NbTenor, curdate)
If Weekday(Enddate, 2) = 6 Then
    Enddate = Enddate + 2
ElseIf Weekday(Enddate, 2) = 7 Then
    Enddate = Enddate + 1
End If
For i = 1 To UBound(Spots)
    If Enddate = Spots(i, 1) Then
        maxitem = i
        Exit For
    End If
Next i
If maxitem = 0 Then
        tableresult(0, 0) = 0
        tableresult(0, 1) = 0
        CalcEndDate = tableresult
    Exit Function
Else
    tableresult(0, 0) = Enddate
    tableresult(0, 1) = maxitem
    CalcEndDate = tableresult
End If

End Function

Function TableStdDev(inputarray As Variant) As Double
Dim s As Double, s2 As Double
Dim c As Integer, i As Integer

For i = LBound(inputarray) To UBound(inputarray)
    Dim v As Double: v = inputarray(i, 1)
    If IsNumeric(v) Then
        If Not (IsEmpty(v)) Then
            c = c + 1
            s = s + v
            s2 = s2 + v * v
        End If
     End If
Next

TableStdDev = Sqr((s2 - (s * s / c)) / c)

End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

'**************************************************************************

'************************* MH BATTISTINI - J. MESSIAS *********************

'************************* Breakeven Volatility ********************

'*********************************** 2012 *******************************

'**************************************************************************

Option Explicit

Const NbCol = 40

Const MaxIter = 102

Const MinVol = 0.06

Public Function RVolSmileForIndex(ByVal SpotArray As Range, _

ByVal DivArray As Range, _

ByVal Tenor As String, _

ByVal AccuracyFactor As Double, _

ByVal DisplayStrikeAxis As Boolean, _

ByVal Transpose As Boolean)

'Main Function to calculate realized Volatility on a Double date

'Spot Array is a 2 columns array containing Date, Adjusted Close Price

Dim StrikeInPerc As Double, Strike0 As Double, epsilon As Double, HedgePremiumDiff As Double

Dim DivDate As Date, curdate As Date, Enddate As Date

Dim TimeToExpiry As Double, Volsup As Double, Volinf As Double

Dim RVol As Double, Premium1 As Double, Hedge As Double

Dim DivAmt As Variant, Divs As Variant

Dim Spots As Variant: Spots = SpotArray.value

Dim maxitem As Integer: maxitem = 0

Dim i As Integer, j As Integer, k As Integer, l As Integer, NbRow As Integer

If DisplayStrikeAxis Then NbRow = 1 Else NbRow = 0

ReDim Smile(0 To NbRow, 0 To NbCol)

On Error GoTo errhandler

'We define the 3M option expiry date with modified following convention

curdate = Spots(1, 1)

For i = 0 To 5

curdate = curdate + i

If CalcEndDate(curdate, Spots, Tenor)(0, 0) <> 0 Then

Enddate = CalcEndDate(curdate, Spots, Tenor)(0, 0)

maxitem = CalcEndDate(curdate, Spots, Tenor)(0, 1)

Exit For

End If

Next i

ReDim AdjSpotArray(1 To maxitem, 1 To 2)

ReDim PriceArray(1 To maxitem - 1, 1 To 1)

Dim StDev As Single

TimeToExpiry = 0.0001 + (Spots(maxitem, 1) - Spots(1, 1)) / 365.25

For j = 1 To maxitem

For k = 1 To 2

AdjSpotArray(j, 1) = Spots(j, 1)

AdjSpotArray(j, 2) = Spots(j, 2)

Next k

Next j

For j = 1 To maxitem - 1

PriceArray(j, 1) = Log(Spots(j + 1, 2) / Spots(j, 2))

Next j

StDev = TableStdDev(PriceArray) * 252 ^ 0.5

'We extract from the dividend array the eventual dividend falling in the 3M period

Divs = DivArray.value

Strike0 = 0.8

For i = 0 To NbCol

StrikeInPerc = Strike0 + i / 100

If i >= 1 Then

Volsup = 2 * MaxInArray(Smile, DisplayStrikeAxis)

Volinf = MinVol

Else

Volsup = 2 * StDev

Volinf = MinVol

End If

For l = 1 To MaxIter

RVol = (Volsup + Volinf) / 2

Premium1 = Premium(AdjSpotArray, RVol, StrikeInPerc, Divs, TimeToExpiry)

Hedge = HedgeCost(AdjSpotArray, RVol, StrikeInPerc, Divs)

HedgePremiumDiff = (Premium1 - Hedge) / Premium1

If Abs(HedgePremiumDiff) < AccuracyFactor Then

Exit For

Else

If Abs(HedgePremiumDiff) > AccuracyFactor And (Premium1 >= 0 And Hedge >= 0) And Premium1 > Hedge Then

Volsup = max(RVol, 0)

ElseIf Abs(HedgePremiumDiff) > AccuracyFactor And (Premium1 >= 0 And Hedge >= 0) And Premium1 < Hedge Then

Volinf = max(RVol, 0)

ElseIf Premium1 >= 0 And Hedge <= 0 Then

Volsup = max(RVol, 0)

End If

'Debug.Print Volinf

'Debug.Print HedgePremiumDiff

'Debug.Print Volinf

Next l

If DisplayStrikeAxis Then

Smile(0, i) = Round(StrikeInPerc, 2)

Smile(1, i) = RVol

Else

Smile(0, i) = RVol

End If

Next i

If Transpose Then

RVolSmileForIndex = WorksheetFunction.Transpose(Smile)

Else

RVolSmileForIndex = Smile

End If

Exit Function

errhandler:

Debug.Print "RVolSmileForIndex: " & Err.Description

Exit Function

End Function

Public Function HedgeCost(ByVal AdjSpotArray, _

ByVal Vol As Double, _

ByVal StrikePerc As Double, _

ByVal Divs) As Double

On Error GoTo errhandler

'AdjSpotArray is a one column array containing adj prices

Dim i As Integer, l As Integer, tempHedge As Double, deltadiv As Double

Dim maxitem As Integer: maxitem = UBound(AdjSpotArray)

ReDim temptable(1 To maxitem, 1 To 3)

Dim prevDelta As Double: prevDelta = 0

For i = 1 To maxitem

temptable(i, 1) = DeltaHedge(AdjSpotArray(i, 1), AdjSpotArray(1, 1), AdjSpotArray(1, 2), _

AdjSpotArray(i, 2), Vol, StrikePerc, Divs, AdjSpotArray(maxitem, 1))

If i = 1 Then

temptable(i, 2) = 0

Else

temptable(i, 2) = temptable(i, 1) - prevDelta

End If

If i = maxitem Then

temptable(i, 3) = -AdjSpotArray(i, 2) * temptable(i - 1, 1)

ElseIf i = 1 Then

temptable(i, 3) = AdjSpotArray(i, 2) * temptable(i, 1)

Else

temptable(i, 3) = AdjSpotArray(i, 2) * temptable(i, 2)

End If

prevDelta = temptable(i, 1)

tempHedge = tempHedge + temptable(i, 3)

For l = 1 To UBound(Divs)

If AdjSpotArray(i, 1) = Divs(l, 1) And i >= 2 Then

deltadiv = deltadiv - temptable(i - 1, 1) * Divs(l, 2)

End If

Next l

'Debug.Print temptable(i, 3)

' Debug.Print temptable(i, 2)

' Debug.Print temptable(i, 3)

Next i

Dim FinalOptionPrice As Double: FinalOptionPrice = max(AdjSpotArray(maxitem, 2) - StrikePerc * (AdjSpotArray(1, 2) - _

CalculateDivAmount2(AdjSpotArray(1, 1), AdjSpotArray(maxitem, 1), Divs)), 0)

HedgeCost = tempHedge + deltadiv + FinalOptionPrice

'Debug.Print "Sum Hedge =" & tempHedge

'Debug.Print "Sum DeltaDiv =" & deltadiv

'Debug.Print "Final OptionPrice =" & FinalOptionPrice

'Debug.Print "Hedge Cost =" & HedgeCost

Exit Function

errhandler:

Dim msgstring As String

Dim Response2

msgstring = "HedgeCost: " & i & Err.Description & " Continuer?"

Response2 = MsgBox(msgstring, vbOKCancel)

If Response2 <> 6 Then

Exit Function

End If

End Function

Public Function DeltaHedge(ByVal Datei As Long, _

ByVal Date0 As Long, _

ByVal AdjPrice0 As Double, _

ByVal AdjPricei As Double, _

ByVal Vol As Double, _

ByVal StrikePercentage As Double, _

ByVal DivArray, _

ByVal D30EndDate As Long) As Variant

On Error GoTo errhandler

Dim a As Double: a = AdjPricei - CalculateDivAmount2(Datei, D30EndDate, DivArray)

Dim b As Double: b = (StrikePercentage * (AdjPrice0 - CalculateDivAmount2(Date0, D30EndDate, DivArray)))

Dim c As Double: c = 0.5 * Vol * Vol * (0.0001 + (D30EndDate - Datei) / 365.25)

Dim d As Double: d = (Vol * (0.0001 + ((D30EndDate - Datei) / 365.25)) ^ 0.5)

Dim e As Double: e = a / b

Dim d1 As Double: d1 = (Application.WorksheetFunction.Ln(e) + c) / d

DeltaHedge = Application.WorksheetFunction.NormSDist(d1)

Exit Function

errhandler:

Debug.Print "Delta: " & Err.Description

Exit Function

End Function

Public Function Premium(ByVal Spots, _

ByVal Vol As Double, _

ByVal StrikePercentage As Single, _

ByVal Divs, _

ByVal TimeToExpiry As Double) As Double

On Error GoTo errhandler

Dim maxitem As Integer: maxitem = UBound(Spots)

Dim Volsqrttime As Double: Volsqrttime = Vol * TimeToExpiry ^ 0.5

Dim timeXvol As Double: timeXvol = Vol * TimeToExpiry

Dim a As Double: a = (Application.WorksheetFunction.Ln(1 / StrikePercentage) + 0.5 * Vol * timeXvol) / Volsqrttime

Dim b As Double: b = (Application.WorksheetFunction.Ln(1 / StrikePercentage) - 0.5 * Vol * timeXvol) / Volsqrttime

Premium = (Spots(1, 2) - CalculateDivAmount2(Spots(1, 1), Spots(maxitem, 1), Divs)) * _

(Application.WorksheetFunction.NormSDist(a) - StrikePercentage * Application.WorksheetFunction.NormSDist(b))

Exit Function

errhandler:

Debug.Print "Premium: " & Err.Description

End Function

Public Function DivInPeriod(ByVal SpotArray, ByVal DivArray) As Variant

On Error GoTo errhandler

Dim i As Integer

Dim temptable(1 To 1, 1 To 2)

Dim maxitem As Integer: maxitem = UBound(SpotArray)

Dim Date0 As Date: Date0 = SpotArray(1, 1)

Dim dateend As Date: dateend = SpotArray(maxitem, 1)

For i = 1 To UBound(DivArray)

If DivArray(i, 1) >= Date0 And DivArray(i, 1) <= dateend Then

temptable(1, 2) = DivArray(i, 2)

temptable(1, 1) = DivArray(i, 1)

DivInPeriod = temptable

Exit Function

End If

Next i

temptable(1, 2) = 0

temptable(1, 1) = 0

DivInPeriod = temptable

Exit Function

errhandler:

Debug.Print "DivInPeriod: " & Err.Description

End Function

Public Function IsDivInPeriod(ByVal SpotArray, ByVal DivArray) As Boolean

On Error GoTo errhandler

Dim i As Integer

Dim maxitem As Integer: maxitem = UBound(SpotArray)

Dim temptable(1 To 1, 1 To 2)

Dim Date0 As Date: Date0 = SpotArray(1, 1)

Dim dateend As Date: dateend = SpotArray(maxitem, 1)

For i = 1 To UBound(DivArray)

If DivArray(i, 1) > Date0 And DivArray(i, 1) <= dateend Then

IsDivInPeriod = True

Exit Function

End If

Next i

IsDivInPeriod = False

Exit Function

errhandler:

Debug.Print "IsDivInPeriod: " & Err.Description

End Function

Function max(ByVal a As Double, ByVal b As Double) As Double

If a > b Then max = a Else max = b

End Function

Function Min(ByVal a As Double, ByVal b As Double) As Double

If a > b Then Min = b Else Min = a

End Function

Public Function CalculateDivAmount(ByVal curdate As Date, _

ByVal Enddate As Date, _

DivArray As Range) As Double

Dim i As Integer, DivAmount As Double

Dim Divs As Variant: Divs = DivArray.value

For i = 1 To UBound(Divs)

If DivArray(i, 1) > curdate And DivArray(i, 1) <= Enddate Then

DivAmount = DivAmount + DivArray(i, 2)

End If

Next i

CalculateDivAmount = DivAmount

End Function

Public Function CalculateDivAmount2(ByVal curdate As Date, _

ByVal Enddate As Date, _

ByVal DivArray) As Double

Dim Divs, i As Integer

Dim DivAmount As Double: DivAmount = 0

For i = 1 To UBound(DivArray)

If DivArray(i, 1) > curdate And DivArray(i, 1) <= Enddate Then

DivAmount = DivAmount + DivArray(i, 2)

End If

Next i

CalculateDivAmount2 = DivAmount

End Function

Public Function MaxInArray(ByVal Smiles, ByVal DisplayStrikes As Boolean) As Double

Dim i As Integer

Dim max As Integer: max = UBound(Smiles, 2) - 1

If DisplayStrikes Then

MaxInArray = Smiles(1, 0)

For i = 0 To max

If Not IsEmpty(Smiles(1, i + 1)) Then

If Smiles(1, i + 1) > MaxInArray Then

MaxInArray = Smiles(1, i + 1)

End If

Else

Exit For

End If

Next i

Else

MaxInArray = Smiles(0, 0)

For i = 0 To max

If Not IsEmpty(Smiles(0, i + 1)) Then

If Smiles(0, i + 1) > MaxInArray Then

MaxInArray = Smiles(0, i + 1)

End If

Else

Exit For

End If

Next i

End If

End Function

Public Function CalcEndDate(ByVal curdate As Date, ByVal Spots, ByVal Tenor As String)

Dim tableresult

Dim maxitem As Integer, i As Integer

ReDim tableresult(0 To 0, 0 To 1)

Dim TenorCode As String: TenorCode = Right(Tenor, 1)

Dim NbTenor As Double: NbTenor = Left(Tenor, Len(Tenor) - 1)

Dim Enddate As Date: Enddate = DateTime.DateAdd(TenorCode, NbTenor, curdate)

If Weekday(Enddate, 2) = 6 Then

Enddate = Enddate + 2

ElseIf Weekday(Enddate, 2) = 7 Then

Enddate = Enddate + 1

End If

For i = 1 To UBound(Spots)

If Enddate = Spots(i, 1) Then

maxitem = i

Exit For

End If

Next i

If maxitem = 0 Then

tableresult(0, 0) = 0

tableresult(0, 1) = 0

CalcEndDate = tableresult

Exit Function

Else

tableresult(0, 0) = Enddate

tableresult(0, 1) = maxitem

CalcEndDate = tableresult

End If

End Function

Function TableStdDev(inputarray As Variant) As Double

Dim s As Double, s2 As Double

Dim c As Integer, i As Integer

For i = LBound(inputarray) To UBound(inputarray)

Dim v As Double: v = inputarray(i, 1)

If IsNumeric(v) Then

If Not (IsEmpty(v)) Then

c = c + 1

s = s + v

s2 = s2 + v * v

End If

TableStdDev = Sqr((s2 - (s * s / c)) / c)

End Function

Break-even volatility

The smile of Thales

A journey into Finance and Computation

Recent posts

Archives

VBA Break-even volatility skew computation

Break-even Volatility skew

Logic behind breakeven volatility

Strike settlement

Assumptions

Delta hedging and dividend
handling

Considerations about the algorithm

1 Comment

Aravind Menon

Leave a Reply Cancel reply

The smile of Thales

A journey into Finance and Computation

Recent posts

Archives

VBA Break-even volatility skew computation

Break-even Volatility skew

Logic behind breakeven volatility

Strike settlement

Assumptions

Delta hedging and dividend handling

Considerations about the algorithm

1 Comment

Aravind Menon

Leave a Reply Cancel reply

Delta hedging and dividend
handling