Advanced Excel® PC Workshop - Option Pricing and Risk Assessment

The objective of this workshop is to give you a good understanding of and hands-on experience with advanced option valuation and risk assessment using financial modelling and simulation techniques in Excel™ and Visual Basic for Applications™.

We start with a review of the analytical and numerical models that are used for option pricing in practice. Models include the standard Black-Scholes model (stock options), the Garman-Kohlhagen model (currency options), the Black 76 model (interest rate options), the CRR model and various equilibrium and no-arbitrage term structure models for valuing interest rate option. The participants will implement selected models in Excel/VBA and use the models for practical option valuation and risk assessment.

Further, we explain and demonstrate how volatility including “volatility smiles” can be analyzed and modelled in Excel and how option “Greeks” and other analytics can be used in practical trading and hedging applications.

We also explain and demonstrate how to implement and use a Monte Carlo simulation toolkit. We explain how random numbers are generated and how stochastic differential equations for various processes can be simulated in Excel/VBA. We also explain and demonstrate how to sample from multivariate distributions using the “Cholesky decomposition”, how return distributions of complex portfolios can be estimated and how risk measures such as “value-at-risk” and “extreme VaR” can be derived from these distributions.

Finally, we explain and demonstrate how “exotics” such as Asian, look-back, barrier, basket and rainbow option can be priced, and how “Greeks” such as delta, gamma, vega etc. can be estimated using Monte Carlo simulation.

 

Day One

09.00 - 09.15 Welcome and Introduction

09.15 - 12.00 Option Pricing Models

• Analytical Option Pricing Models
  • The Black-Scholes model
  • The Garman-Kohlhagen model
  • The Black 76 model
  • Pricing with analytical models
  • Risk Analysis: Delta, Gamma, Vega and the other “Greeks”
• Workshop:
      Participants Implement Analytical Models and Use Analytical option Pricing Models for pricing and Risk Analysis of Currency Option.
• Numerical Option Pricing Models
  • The CRR Model
  • Term structure Models (CIR, BDT, Hull-White) - overview
• Workshop:
      Participants implement, calibrate and test a CRR model for pricing and risk analysis of American stock options.

12.00 - 13.00 Lunch

13.00 - 16.30 Volatility Analysis

• Volatility Glossary and the Role Volatility in Option Pricing
• Option Volatility Framework
  • Volatility Smiles, Skews and Surfaces
  • Modelling and Forecasting Volatility
• Forward Volatility and Forward Starting Options
• Workshop:
      Computing Volatility. Participants Analyse Data Sample and Construct Volatility Curves.

Using Option Sensitivities in Trading and Risk Management

• Overview of Option Risk Assessment (Greeks)
• Delta Hedging and Risk Analysis
• Volatility Trading and Hedging (Gamma and Vega)
• Workshop:
      Volatility Trading and Hedging Applications in Excel

Day Two

09.00 - 09.15 Recap

09.15 - 12.00 Monte Carlo Toolkit

• Generating Random Numbers
  • Random number generators – how they work
  • Testing the Excel/VB random number generator
• Statistical Distributions
  • Uniform, normal and log-normal distributions
  • Binomial distribution, Poisson distribution etc.
• Sampling Techniques
  • Generating normally distributed random numbers
  • Drawing form multivariate distributions
• Simulation Stochastic Differential Equations
• Estimating Return Distributions and calculating VaR and Extreme VaR
• Workshop:
      Participants program a “Monte Carlo” engine and test it by simulation SDE’s.

12.00 - 13.00 Lunch

13.00 - 16.00 Pricing and Risk Analysis of Exotic Options

• Types of Exotic Options
• Modelling Issues and Valuation Methods
• Pricing Path-Dependent Options
  • Barrier options
  • Asian options
  • Lookbacks, ladders and ratchet options
• Pricing Discrete Pay-Off Options
  • Digital options
  • Touch options
• Multivariate Options
  • Basket options
  • Rainbow options
• Workshop:
      Participants use MC Simulation for pricing and risk analysis of selected types of exotic options.

Evaluation and Termination of the Workshop