The goals:

1
Lectures 3 Monte Carlo method in finance Lab

• The goals
• create a VBA package/framework to price exotic
  options via Monte Carlo method and its
  modifications (i.e. variance reduction
  techniques).
• create an excel file to be used as a front end
  for pricing options.

2
Lecture 3 MC Lab library design

• Library design
• Create separate modules, containing functions
  with similar goals (reading input, writing
  output, core Monte Carlo functions etc.).
• Isolate contract dependent code in dedicated
  modules (e.g. one for plain vanilla, one for
  asian option etc.).
• Make the code easily extensible

3
Lecture 3 MC Lab Excel as a front end (I).

• define sheets to manage Monte Carlo parameters
  (input)
• scenario numbers (N)
• flag for activate antithetic sampling or control
  variates
• define sheets to store market data (input)
• Yield curves
• Equity data (current stock price, dividends,
  volatility)

4
Lecture 3 MC Lab Excel as a front end (II).

• define sheets to store some general contract
  descriptions (input)
• Fixing dates
• define sheets to store specific contract
  descriptions (input) and option price (output).
  This sheet is strictly related to option typology
  
• e.g. for plain vanilla option
• Strike price
• Option sub-type (e.g. call or put)
• Option price

5
Lecture 3 MC Lab Excel as a front end (III).

• define sheets to store equity path evolution
  (output)
• printing the N paths generated by Monte Carlo
  (for debug only).
• printing the pay-off for each path.

6
Lecture 3 MC Lab VBA library (I).

• Structures to manage
• Fixing future fixing dates
• Yield curve market data (rates for different
  maturities),
• Equity market data price, dividend yield and
  volatility.
• Function to read from excel data to initialize
  the structures defined above.

7
Lecture 3 MC Lab VBA library (II).

• Core functions
• Path generation
• Compute option price (using pay-off function
  see next slide)
• Output functions
• Printing path and pay-off
• Printing statistics

8
Lecture 3 MC Lab VBA library (III).

• Structure and functions related to contract
  typology (e.g. plain vanilla, asian, reverse
  cliquet)
• Structure containing specific contract
  characteristics
• Function to read (initialize) structure from
  excel sheet
• Function to write outputs e.g. the option price
• Function for pay-off computation along a specific
  path (it depends from contract typology!)
• Remember the Compute_price function must be
  customized in order to treat a new type of option
  (select case)

9
Lecture 3 MC Lab VBA library organization (I).

• For each of the above points we define a specific
  bas file
• Module_cons constants definitions.
• Module_struct containing the structures
  definition
• Module_read_struct to read main structures
• Module_core_functions the core Monte Carlo
  functions (path generation and MC option
  pricing).
• Module_output_functions to manage the output
• Module_util utility functions

10
Lecture 3 MC Lab VBA library organization
(II).

• Module_pv containing structure and functions to
  manage plain vanilla options
• Module_asian containing structure and functions
  to manage asian options
• Module_reverse_cliquet containing structure and
  functions to manage reverse cliquet options

11
Lecture 3 MC Lab Exercises

• Ex. 1 verify that Monte Carlo error scales with
  N (the scenarios number) as 1/sqrt(N).
  Suggestion compute option prices with different
  values of N and plot the data in a log-log graph.
• Ex. 2 within VBA library, implement the pay-off
  calculation for a reverse cliquet option and
  compute, via Monte Carlo simulation, its value.
• Suggestion take the following parameters values
• H 4
• L 0
• M12, i.e. twelve fixing dates (with delta_t
  1/12 year)