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

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