Print Register
Facebook Twitter

Advanced Excel™ Workshop 2 - Monte Carlo Simulations, Value-at-Risk and Option Pricing

Duration: 2 days
  • 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™).

Day One

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