Generating a Probability Density Function from Option Prices in Excel

This practical introduces a method of generating a probability density function from a volatility smile by numerically differentiating vanilla option prices twice with respect to the strike. The code reuses volatility smile functions developed in Practical F and vanilla options pricing functions developed in Practical C. Probability density functions are explored in detail within Chapter 13.

First, volatility smile inputs and market data must be defined within the Excel sheet. Then a range of delta values is established, from 0.1% to 99.9% in tight steps of 0.1%:

The implied volatility and strike must be calculated for each delta value. Due to the amount of data on the sheet it is better to use a VBA subroutine to calculate the values and place them on the sheet surface. The MalzSmileVol and StrikeFromPutDelta functions from Practical F can being used. Note that StrikeFromPutDelta takes a negative put delta value as input:

Sub populateSmileStrikesAndVols()
    Dim InputPutDelta As Double
    Dim ImpliedVol As Double
    Dim DeltaCount As Long
    DeltaCount = 1
    While Range("VolatilitySmileRef").Offset(DeltaCount, 0) <> ""
        InputPutDelta = Range("VolatilitySmileRef").Offset(DeltaCount, 0)
        ImpliedVol = MalzSmileVol(Range("ATM"), Range("RR25d"),        Range("Fly25d"), InputPutDelta)
        Range("VolatilitySmileRef").Offset(DeltaCount, 1) = ImpliedVol
        Range("VolatilitySmileRef").Offset(DeltaCount, 2) =        StrikeFromPutDelta(Range("Spot"), -InputPutDelta,        Range("rCCY1"), Range("rCCY2"), Range("T"), ImpliedVol)
        DeltaCount = DeltaCount + 1
    Wend
End Sub

Next, in a new column, define equally spaced strikes for calculating the probability density function (pdf):

A VBA function is used to calculate the implied volatility and equivalent option price at each strike level. It is okay to use linear interpolation to generate the implied volatility since delta has small increments. The OptionPrice function from Practical C is reused:

Sub populatePDFImpliedVolsAndPrices()
    Dim PDFStrikeCount As Long, SmileDeltaCount As Long
    Dim LowStrike As Double, HighStrike As Double
    Dim LowVol As Double, HighVol As Double
    Dim InputStrike As Double, ImpliedVol As Double
    PDFStrikeCount = 1
    While Range("StrikeRef").Offset(PDFStrikeCount, 0) <> ""
        InputStrike = Range("StrikeRef").Offset(PDFStrikeCount, 0)
        ImpliedVol = -1
        SmileDeltaCount = 1
        While Range("VolatilitySmileRef").Offset(SmileDeltaCount + 1, 0)        <> ""
            LowVol = Range("VolatilitySmileRef").Offset(SmileDeltaCount, 1)
            HighVol = Range("VolatilitySmileRef").Offset(SmileDeltaCount            + 1, 1)
            LowStrike = Range("VolatilitySmileRef")            .Offset(SmileDeltaCount, 2)
            HighStrike = Range("VolatilitySmileRef")            .Offset(SmileDeltaCount + 1, 2)
            'Linear Interpolation to get Implied Vol
            If (InputStrike > LowStrike) And (InputStrike            < HighStrike) Then
                ImpliedVol = LowVol + (HighVol - LowVol) * (InputStrike -                 LowStrike) / (HighStrike - LowStrike)
            End If
            SmileDeltaCount = SmileDeltaCount + 1
        Wend
        Range("StrikeRef").Offset(PDFStrikeCount, 1) = ImpliedVol
        Range("StrikeRef").Offset(PDFStrikeCount, 2) =        OptionPrice(False, Range("Spot"), InputStrike, Range("T"),        Range("rCCY1"), Range("rCCY2"), ImpliedVol)
        PDFStrikeCount = PDFStrikeCount + 1
    Wend
End Sub

The probability density can now be calculated by finding the second derivative of price with respect to strike on the sheet:

Note that the probability density (second derivative of option value with respect to strike) takes a similar shape to that of gamma (second derivative of option value with respect to spot).

Probability density functions can then be compared by copying the output values. When making changes to the volatility smile or market data inputs, remember to rerun both VBA subroutines in order to correctly set up the calculation.

Flat volatility smile versus long wings volatility smile:

1. 

   Flat volatility smile versus long topside risk reversal volatility smile:

2. 

Finally, as discussed in Chapter 13, the area under the probability density function should equal one since it represents a probability mass. This can be checked by multiplying the average pdf between strikes by the change in strike at each strike level and summing the total. This indicates how accurate the output is. If the strike spacings are tight enough, the total probability value should be between 0.99 and 1.01.