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 E

Constructing an ATM Curve in Excel

Within this practical, three methods of constructing an ATM curve are developed. First, an ATM curve is constructed using interpolation between market tenors. Then, an ATM curve is constructed using a parameterized model. Finally, weights are added to a simple ATM curve to demonstrate how ATM curves are maintained by traders in practice. These steps mirror the material developed in Chapter 11.

Task A: Constructing an ATM Curve Using Interpolation

When constructing an ATM curve based on market tenors, the expiry date for each market tenor must first be calculated using functions developed in Practical D. The ATM implied volatility is then manually inputted at each tenor. For the purposes of testing, a simple upward-sloping ATM curve can be used initially:

bappeuf001

Using these inputs, a VBA function can interpolate to give the ATM volatility for any date. This function references the expiry dates and ATM volatilities at market tenors using named cells, with linear interpolation used to generate ATM volatility for expiry dates between tenors:

Function getATMVol(QueryDate As Long) As Double Dim Count As Long Dim TimeLow As Double, TimeHigh As Double Dim VolLow As Double, VolHigh As Double 'Find the relevant Expiry date row (requires the Expiry dates to be ordered) Count = 1 While Range("ExpiryDateRef").Offset(Count, 0) < QueryDate And Range("ExpiryDateRef").Offset(Count, ...

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