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