O'Reilly logo

FX Derivatives Trader School by Giles Jewitt

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Practical G

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

bappguf001

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required