xlf QandA series

Use Goal Seek to estimate Implied Volatility from multiple options

QUESTION

IS THERE A WAY TO ESTIMATE IMPLIED VOLATILITY ACROSS MULTIPLE OPTIONS USING GOAL SEEK WITHOUT SELECTING INDIVIDUAL INSTRUMENTS?


Answer icon Answer


The Goal Seek dialog box (figure 1) has limitations on the input parameters, with single cell references and a hard coded number for the "To value:" argument. This prevents the use of Goal Seek in an array formula environment.


xlf-goal-seek-parameters
Fig 1: Goal Seek has three input parameters. All are single cell references or a hard coded value.

The solution is to code Goal Seek in VBA.


Required skills:


Goal Seek example


The worksheet shown in figure 2 includes 10 simulated options (rows 7 to 11) and theoretical call option prices for the Black-Scholes model in row 13.


xlf-bs-multiple-options-implied-volatility-goal-seek
Fig 2: Multiple Options - Implied Volatility with Goal Seek [Scroll to view] Set Cell: GoalSeek property; To Value: Goal; and By changing cell: ChangingCell

VBA code to manipulate Goal Seek


GoalSeek is a method of the Range object.

The syntax for the GoalSeek method is:

VBA function / property Syntax
Range.GoalSeek (method) .GoalSeek(Goal, ChangingCell)

Returns TRUE if successful

Arguments Description
Goal CompulsoryThe target value you want returned in this cell
ChangingCell CompulsoryThe cell that should be changed to achieve the target value

Based on the IV with GS demo worksheet in figure 2, Range, Goal, and ChangingCell have range addresses of:


These values are assigned to variables in lines 14 to 16 of code 1.



Code 1: The implied volatility with Goal Seek macro 
Option Explicit

Sub IVwithGS()
' Implied volatility with Goal Seek

Dim SetCellArr As Range         ' << Formula row
Dim ToValueArr() As Variant     ' << Constants
Dim ByChangingCellArr As Range  ' << Volatility

Dim Count As Integer

    Worksheets("IV with GS demo").Activate

    Set SetCellArr = Range("E13:N13")
    ToValueArr = Range("E16:N16").Value
    Set ByChangingCellArr = Range("E10:N10")

    For Count = 1 To Range("E13:N13").Columns.Count
        Range(SetCellArr(1, Count).Address).GoalSeek _
            Goal:=ToValueArr(1, Count), _
            ChangingCell:=Range(ByChangingCellArr(1, Count).Address)
    Next Count

End Sub

The GoalSeek statement is in lines 19 to 21 of code 1.


Maintenance code


Code 2: Maintenance code to Reset the IV line to default value of 10% 

Sub ResetVols()

    Worksheets("IV with GS demo").Activate
    Range("E10:N10").Value = 0.1

End Sub

Code linking macros to the on-sheet controls


Code 3: ActiveX controls code 
' Module (Sheet): Sheet2
' Requires:
'   1. CommandButton, Name: cmdCalc
'   2. CommandButton, Name: cmdReset

Private Sub cmdCalc_Click()
    Call IVwithGS
End Sub

Private Sub cmdReset_Click()
    Call ResetVols
End Sub

Resources