Constructing Volatility Surfaces in EXCEL

About the Course

Volatility surface plots are a construct of the latter across moneyness (strike prices) and maturity (time to expiry). Unlike implied volatilities which are determined by backing out volatility from the Black Scholes option price equation applied to at the money options, local volatilities use implied volatilities and a one factor Black Scholes model to drive local volatility values across the surface. Calibrating volatility surfaces is one of the tweaks market practitioners use to sidestep the constant volatility assumption present in the Black Scholes equation for pricing European options.

This course begins by explaining the difference and pros and cons of various volatility estimates, from trailing volatilities, to implied volatilities to local volatilities. It then looks at the benefits of purchasing deep out of the money options, and how a change in volatility impacts the value of these options. The next lesson delves deeper into the differences between implied and local volatilities. A step-by-step guide on how to build surfaces for local volatilities for options on stock prices using Dupire’s formula in EXCEL is illustrated. Forward implied volatilities using the methodology described by Taleb are also derived.

Finally, the course extends the calculation to determine local volatility surfaces for options on commodity futures contracts. The resulting surface is plotted against moneyness (as given by strike/futures price) and maturity as well as against futures price and maturity for a given strike. Results are compared with implied and forward implied volatilities.

The course consists of:

• A 40 page PDF guide that shows how to build a volatility surface step by step in EXCEL using Dupire's formula.
• A 20 page PDF guide that shows how to calculate the volatility surface in EXCEL for options of futures contracts.
• An EXCEL spreadsheet that is used as a simple teaching template by the PDF tutorial above. The Excel sheet shows the implementation of Dupire's formula as well as the resultant volatility surface. The sheet also shows Taleb's implementation of implied forward volatility using term structure of volatility concepts.
• An EXCEL spreadsheet that shows the calculation of local volatility surfaces for options on Crude Oil WTI commodity futures contracts. The sheet also shows the calculation of the implied forward volatility and the comparison of local volatilities with implied & forward implied volatilities.

Below is the table of contents from the 40-page PDF guide.

Learning Objectives

After taking this course you will be able to:

• Distinguish between trailing, implied and local volatilities
• Calculate implied volatility
• Calculate local volatility using Dupire’s formula
• Understand the advantages of purchasing a deep out of the money option
• Construct a volatility surface of implied volatilities in EXCEL
• Construct a volatility surface of local volatilities in EXCEL for options on stock price
• Calculate a term structure of forward implied volatilities in EXCEL
• Construct a local volatility surface in EXCEL for options on commodity futures contracts

Prerequisites

Familiarity with the Black Scholes equation, derivative products and pricing models for pricing European options and EXCEL.

Target Audience

This advanced practitioner course is aimed at professionals who deal with pricing, valuation and risk issues related to derivative transactions.