Advanced Excel® PC Workshop  Option Pricing and Risk Assessment
Duration: 2 days
 Analytical and Numerical Option Models
 Option Risk Assessment
 Volatility Analysis
 Delta Hedging and Risk Analysis
 Volatility Trading and Hedging
 Monte Carlo Simulation
 Pricing Exotic Options
The objective of this workshop is to give you a good understanding of and handson 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 BlackScholes model (stock options), the GarmanKohlhagen model
(currency options), the Black 76 model (interest rate options), the CRR model and various equilibrium
and noarbitrage 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 “valueatrisk” and “extreme VaR” can be derived from these
distributions.
Finally, we explain and demonstrate how “exotics” such as Asian, lookback, 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 BlackScholes model
 The GarmanKohlhagen 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, HullWhite)  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 lognormal 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 PathDependent Options
 Barrier options
 Asian options
 Lookbacks, ladders and ratchet options

Pricing Discrete PayOff 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
