Using Black-Scholes and Newton Raphson to Calculate Volatility

1. In this Model Builder we will use option and stock data to calculate the instruments' volatility. We should start by creating a new workbook and naming it MB4.3_User.xls. On the first sheet we should set up a number of labels in the following cells:

   B3: “Inputs: Option & Stock Data” B8: “Today's Date”

   B4: “Variable” B9: “Option Expiry Date:”

   B5: “Stock Price” B10: “Vega Threshold Guess”

   B6: “Strike Price” B11: “Call Price:”

   B7: “Risk-Free Interest Rate” C4: “Value”

2. The values for each of these inputs could vary depending on the case at hand, but for now enter the following values in the corresponding cells:

   C5: 12 C9: 10/16/2010

   C6: 4 C10: .4

   C7: 5.00% C11: 9

   C8: 7/6/2010

3. To assist readers who may be wondering how the inputs in step 2 were derived, we listed out common calculation methods on the sheet. While they are not required for any functionality, you may want to enter them now. The following are only text guides in cells for reference:

   D4: “Common Calculation Method” D8: “Current Date”

   D5: “Current Value in Market” D9: “Give in Option Contract”

   D6: “Given in Option Contract” D11: “Current Value in Market, Mid”

   D7: “Appropriate T-bill/T-bond rate”

4. A very simple initial calculation that we must do is to determine the option expiration as a fraction of a year. This can be done by entering the formula, =(C9-C8)/365 in C15. We can provide labels by entering “Time to Option Expiry” in B15 and “(Expiry Date-Current Date)/(Days in Year)” in D15.
5. There are a number of names we need to create that are very important since they will interact with the VBA code for this section. Create the following names for the corresponding cells:

   C5: stockPrice C11: callPrice

   C6: strike C15: maturity

   C7: riskFreeRate C16: CallPriceSolution

   C10: Threshold C17: ImpVol

6. The calculations involved to get to the implied volatility should be done in VBA. Open the VBE and insert a new module in this project. Rename this module BlackScholeSolution.
7. Creating three separate functions will make calculation in the subroutine easier. Enter the following code to get the standard D1 calculation from the Black-Scholes equation:

   Function computeD1(ByVal vol As Double) As Double
       computeD1 = (Log(stockPrice / strike) + (riskFreeRate
   + 0.5 * (vol ^ 2)) * maturity) / (vol * Sqr(maturity))
   End Function}]]>

8. The second function is a normal distribution function:

   Function myNormDistPDF(ByVal x As Double)
       myNormDistPDF = Exp(-x ^ 2 / 2) / Sqr(2 * WorksheetFunction.Pi)
   End Function}]]>

9. The final function is an initial guess at the volatility. As with all iterative procedures, a question of major concern is “Where do I start my guess?” Sometimes there is a logical starting location, which is true if we are iterating to solve for the asset price (Merton), but solving for the volatility is a bit trickier since it really can be anything. At this point you might be wondering, “Why is this issue a big deal and why is it so important?” To get a better understanding, let us first consider the function that we are minimizing (equation 4.48):

   

   If we plot this function, we will get a curve that will look like the curve shown in Figure 4.8. You will notice that the function asymptotically approaches +/– 2 where the slope in that region, our vega, will be essentially zero. If our starting point happens to lie within this region, our iterative solver will fail to converge since dividing by zero will produce infinity. Therefore, before the start of each solve, we must do an initial scan to make sure we are not starting within this region.

10. We will go through the code to this function in two sections. The first covers the necessary variables and initial values. Enter the following code in the BlackScholeSolution module:

    Function volatilityInitialGuess() As Double
        Dim threshold As Double
        Dim i As Integer

    Dim d1 As Double
        Dim vol As Double
        Dim vega As Double
        Dim vegaConstTerm As Double
    
        ' threshold on vega because we want the derivative, ie slope,
         relative to sigma
        threshold = Range(“Threshold”).Value
        vol = 0.02
        vegaConstTerm = stockPrice * Sqr(maturity)

    FIGURE 4.8 The basic shape of the function in equation 4.46. This curve was generated using fake data and is plotted only to show the general behavior of such a function. The actual values of the volatility and data used are not important. What is important is to note that function asymptotically approaches +/– 2.

    

11. The next part loops 500 times to determine the initial volatility guess. Enter the following code after the code where we left off in step 10:

    For i = 1 To 500
            d1 = computeD1(vol)
            vega = vegaConstTerm * myNormDistPDF(d1)
    
            If (vega >= threshold) Then
                Exit For
            End If
    
            vol = vol + 0.02
        Next i
    
        volatilityInitialGuess = vol
    End Function

12. Now we are ready for the main subroutine. This subroutine will take in values from the sheet, which is stored in VBA variables. The first part of the code focuses on drawing in the information required:

    Option Explicit
    
    Dim stockPrice, strike As Double
    Dim riskFreeRate, maturity As Double
    Dim callPrice As Double
    Dim vegaConstTerm As Double
    
    Sub BlackScholesSolve()
        'Error Term is known as epsilon in most texts
        Dim errorTerm As Double
        Dim impVol As Double
        Dim d1, d2 As Double
        Dim calcPrice As Double
        Dim vega As Double
        Dim counter As Integer
        Dim vegaConstTerm As Double
    
        'Take inputs from spreadsheet
        stockPrice = Range(“stockPrice”).Value
        strike = Range(“strike”).Value
        riskFreeRate = Range(“riskFreeRate”).Value
        maturity = Range(“maturity”).Value
        callPrice = Range(“callPrice”).Value

13. There are a few logical constraints that we should set up and provide information for users of the subroutine. The first of these constraints is the strike price being above the current stock price. If this were the case the strike is already achieved at the current stock price! To avoid this enter the following code after where we left off in step 12:

    If (strike >= stockPrice) Then
        MsgBox (“Strike cannot be greater than or equal to the stock
        price”)
        Exit Sub
    End If

14. The other constraint is if the call price is less than the stock price minus the strike price. We can avoid this by picking up where we left off in step 13 and entering the following code:

    If (callPrice < stockPrice-strike) Then
        MsgBox (“Call price cannot be less than stockPrice minus
        strike”)
        Exit Sub
    End If

15. We are now very close to converging on a solution, but first should initialize the values prior to the loop. This is assisted by drawing on the initial volatility guess from step 8 and 9. Enter the following code after the area we left off in step 14:

    'Initialize variables before start of loop
    calcPrice = 0
    impVol = volatilityInitialGuess()
    errorTerm = 0.0001
    counter = 0
    vegaConstTerm = stockPrice * Sqr(maturity)

16. We can now use Newton-Raphson to solve for the volatility. Enter the following code after the area we left off in step 15:

    'Use Newton-Raphson Method to Converge on Correct Value
    On Error GoTo ErrorHandler
    While counter < 100 And Abs(callPrice-calcPrice) > errorTerm
        d1 = (Log(stockPrice / strike) + (riskFreeRate + 0.5 *
    (impVol ^ 2)) * maturity) / (impVol * Sqr(maturity))
        d2 = d1-(impVol * Sqr(maturity))
        'N'(d1) = normdist(d1, 0, 1, false) but this is faster
        vega = vegaConstTerm * myNormDistPDF(d1)
    
        calcPrice = stockPrice * Application.NormSDist(d1)-strike
    * Exp(-riskFreeRate * maturity) * Application.NormSDist(d2)
    
        impVol = impVol-(calcPrice-callPrice) / vega
        counter = counter + 1
    Wend

17. There are two final steps to complete the process. The first is to export the answer to the sheet. The second is to create an error handler that simply tells the user when an error occurs and the subroutine fails. This will help users troubleshoot any possible problems. Enter the final pieces of code after the code that was created in step 16:

    'insert values onto spreadsheet
    Range(“ImpVol”) = impVol
    Range(“CallPriceSolution”) = calcPrice
    Exit Sub
    
    ErrorHandler:
        MsgBox (“Converge failed. Try another initial guess threshold”)
    End Sub

One final step that we might want to consider, but that is not absolutely necessary, is to create a button to run the subroutine from the sheet.