Advanced Excel™ Workshop 2

Monte Carlo Simulations, Value-at-Risk and Option Pricing

Agenda Program
divider graphic
Prague, NH Hotel Prague
divider graphic
Monte Carlo Simulation in Finance
Random Number Generation
Cholesky Decomposition
Stochastic Differential Equations
Variance Reduction Techniques
Pricing Exotic and Hybrid Options
Measuring Value at Risk
Stress Testing
The objective of this advanced-level course is to give the participants hands-on experience with the use of advanced simulation techniques in finance. We start with an introduction to the Monte Carlo method and we give an overview of the widespread use of Monte Carlo methods in securities and derivatives pricing and in risk management. We then give an in-depth explanation of the Monte Carlo method, enumerating its fundamental building blocks. Participants will work their way through the generation of pseudo-random numbers including numbers drawn from arbitrary probability distributions, discrete as well as continuous. Participants will also learn and try how the "Cholesky decomposition" technique can be used when sampling from multivariate distributions, when assets are correlated. We use lattice-pricing to price and risk assess exotic options such as Asian, barrier and lookback options using various stochastic processes, including Black-Scholes as a benchmark. Further, we show how to construct discrete versions of widely used Stochastic Differential Equations. Participants will use these to simulate trajectories of assets and to measure the Value at Risk of a portfolio of securities, estimate the potential exposure of market driven instruments etc., and to perform "stress testing". Finally, we present a number of variance reduction techniques for use with Monte Carlo Simulation, including the use of antithetic variables, control variate and importance sampling methods. The effect of these techniques on computational accuracy and/or performance will be evaluated. Throughout the course the participants will be given the opportunity to work on exercises, gaining hands-on experience with some of the Monte Carlo methods (Excel™ and Visual Basic™).
The course will be highly practical and hands-on. Participants are required to bring a notebook with MS Excel. Participants will use models and exercises to outline and develop the techniques and methods.

Program of the seminar: Advanced Excel™ Workshop 2

The seminar timetable follows Central European Time (CET).

09.00 - 09.15 Welcome and Introduction

09.15 - 12.00 Monte Carlo Simulation in Finance

  • Applications of Monte Carlo Simulation in Finance
  • Couple of Examples of What You Can Do
  • Introductory Exercise

Implementing the Monte Carlo Toolkit

  • Statistical Distributions
  • Generating Normally Distributed Random Numbers in Visual Basic
  • Drawing from Multivariate Distributions
  • Programming Stochastic Differential Equations in Visual Basic
  • Workshop: Participants Program Sampling Routines and Simulate Basic SDEs in Visual Basic

12.00 - 13.00 Lunch

13.00 - 16.30 Pricing Options Using Monte Carlo Simulation

  • Overview of Option Pricing Models
  • Pricing Standard European Options
  • Pricing "Path Dependent" Options
    • Barrier options
    • Lookback options
    • Asian options
  • Pricing other Exotic Options
    • Digital options and "range floaters"
    • Basket and compound options
    • Chooser and rainbow options
  • Greeks in Monte Carlo
  • Workshop: Participants Program a Generalized Routine in VB for Valuation of Standard and Exotic Options

Day Two

09.00 - 12.00 Calculating "Value-at-Risk" Using Monte Carlo Simulation

  • VaR for Single Asset Portfolios
    • Formulating the price process
    • Discretezising the price process
    • Constructing the P&L Histogram
    • Inferring the VaR
  • Workshop: Participants Program Routine to Generate Full Distribution and Calculate VaR for Single Asset
  • VaR for Multiple Asset Portfolios
    • When prices are independent
    • When prices are perfectly correlated
    • When prices are imperfectly correlated
    • Cholesky decomposition
    • Constructing the P&L histogram
    • Inferring the VaR
  • Workshop: Participants Program Routine to Generate Full Distribution and Calculate VaR for Asset Portfolio

12.00 - 13.00 Lunch

13.00 - 16.00 Calculating "Value-at-Risk" for Option Portfolios

  • Building a "Simulation within the Simulation"
  • Constructing the Pay-off Distribution and Inferring the VaR (market Risk + Counterparty Risk)
  • Workshop: Participants Construct Pay-off Distribution for Option Portfolio and Infer VaR

Making Monte Carlo Simulation More Efficient

  • Problems with Conventional MCS
  • Variance Reduction Techniques
  • Quasi-Monte Carlo Approaches
  • Scrambled Nets Approach
  • Scenario Simulation � an Alternative Approach
  • Workshop: Participants "Tune" their MC Applications

Evaluation and Termination of the Workshop

Training catalogue in PDF
arrow-up icon