Financial Modeling with VBA

1
Financial Modelingwith VBA

• Presentation 17/2 2004
• Sjur Westgaard, NTNU

2
Todays schedule

• Tuesday 17/2 2004
• The Binominal Model
• Monte Carlo Simulation and Option Pricing

3
Before we start..A question from a student
yesterdayHow do set up a strategy if we believe
we are going back to historical vol levels in the
option prices? RecallCall 690 Mars 11 in
the Market (Implied vol13.26)Call 690 Mars
15.43 in our calculation (Implied
vol18.62)Strategy Expect vol to increase
but not sure of direction You have 26500 kroner
to spend Buy call and puts near at the money
(688.84) I.e. buy at 13/2 2004 690 Calls (cost
11) and 690 puts (Cost 15.50) Do 1000 Calls and
1000 Puts at a cost of 110001550026500
4
Buying 690 Mars OBX Calls and Puts
5

• Scenario Analysis (only nice scenarios)Implied
  vol increases to 19, market goes up from 688.84
  to 718.84 during 1 week (Use calculator with
  S718.84, K690, r2, T(33-7)/365,
  s19) Call payoff 100033.84933849 Put
  payoff 10004.0274027 Total payoff
  37876 Total profit 37876-2650011376 Implied
  vol increases to 19, market goes down from
  688.84 to 658.84 during 1 week (Use
  calculator with S658.84, K690, r2,
  T(33-7)/365, s19) Call payoff
  10003.5381754 Put payoff 100033.71533715 T
  otal payoff 35469 Total profit
  35469-265008969

6
Scenario Analysis

• In both scenarios you have financed a 2 week
  holiday on the Canary Islands (11376 or 8969
  kroner)
• What is market stays calm for the next week?

Implied vol stays at 13.26, market stays at
688.84 (Use calculator with S688.84, K690,
r2, T(33-7)/365, s13.26) Call payoff
10009.6379637 Put payoff 10009.8159815 Tot
al payoff 19452 Total profit 19452-26500-7048
7
The Binominal Model

• Most time analytical solutions like the BS
  Model cannot be found
• The Numerical Methods (an in particular the
  Binominal Model) are more flexible than
  analytical solutions and can be used to price a
  wide range of option contracts
• Created by Cox, Ross and Rubinstein (1979) and
  Rendleman and Bartter (1979)

8
The Binominal Model

• Cox, Ross and Rubinstein, 1979, Option Pricing
  A Simplified Approach, Journal of Financial
  Economics, 7, 229-263
• Rendleman Bartter, 1979, Two State Option
  Pricing, Journal of Finance, 34, 1093-1110
• These showed how to construct a recombining
  binominal tree that discretize the Geometric
  Brown motion.
• At the limit, a binominal tree (with a very
  large number of time steps) is equivalent to the
  continuous-time BS formula when pricing European
  options.

9
The Binominal Model

• Most interesting The Binominal Model handles
  the pricing of American options, where no
  closed-form solution exists, as well as several
  exotic options
• Few other numerical techniques can handle
  American Option in such a easy way done with the
  Binominal Model

10
The Binominal Model

• Recall lectures according to chapter 10,11about
  the Binominal Model
• To price European call options, the binominal
  model can be expressed like

11
The Binominal Model

• In the binominal model the price cab move up or
  down in any time period. If qu is the state price
  associated with an up move and qd is the state
  price associated with a down move, then we get
  this formula. U is the up move in the stock
  price, d is the down move.

• This is the Binominal coefficient (the number
  of up moves in n total moves)

12
The Binominal Model

• We can use Excels Combin(n,i) to give values
  for the Binominal Coefficients.
• Whenever we consider a finite approximation to
  the option-pricing formulas, we have to use an
  approximation to the up and down movements. We
  use the following translation

• This approximation guarantees that as ?t?0
  (i.e. as n??), the resulting distribution of
  stock returns approaches the lognormal
  distribution.

13
The Binominal Model

• Given this approximation, we are ready to define
  the VBA functions for the European Calls using
  The Binominal Option Pricing Model.
• For puts we can progress in a simular way (see
  mid term exercise)
• For American calls and puts the extension is
  also easy (see mid term exercise)

14
Public Function BinEurCall(S As Double, K As
Double, T As Double, _ r As
Double, v As Double, n As Integer) As Double
' Binominal European Call
Dim delta_t As Double Dim down As Double, up
As Double Dim x As Double Dim q_up As
Double, q_down As Double Dim index As
Integer ' Declaring all mid step
calculations delta_t T / n up
Exp(v Sqr(delta_t)) down Exp(-v
Sqr(delta_t)) x Exp(r delta_t) q_up
(x - down) / (x (up - down)) q_down 1 / x
- q_up BinEurCall 0 For index 0 To n
BinEurCall BinEurCall
Application.Combin(n, index) q_up index _
q_down (n - index)
Application.Max(S up index down _
(n - index) - K, 0) Next index End
Function ' Note the use of Application.Combin(n,
index)which gives value to the Binominal
Coefficient
15

• This is almost equal to the theoretical BS
  price of 15.43! (Keep in mind that we have only
  used 100 periods in the tree)

16

• Convergence BS Binominal Model with n steps

17
Monte Carlo Simulation

• Modeling Stock Prices
• Simulation in Excel
• VBA programs for MC simulation (One asset)

18
Modeling Stock Prices

• We can use any type of stochastic processes to
  model stock prices
• A continuous time, continuous variable process
  proves to be the most useful for the purposes of
  valuing derivative securities

19
An Process for Stock Prices

• We limit ourselves to processes where the natural
  logarithm of the underlying asset follows a
  geometric Brownian motion given by (discrete
  representation)
• Where ?S is a discrete change in S in the chosen
  time interval ?t, and ?t is a random number
  drawing from a standard normal distribution

20
Monte Carlo Simulation and Options

• Used where no closed form solution can be found
  (f.ex. arithmetic average options where only
  approximations can be found)
• Introduced by Boyle (1977)
• To see that it works, lets use MC Simulation on
  our OBX option

21
Monte Carlo Simulation and Options

• Recall the NHY option
• S688.84
• K390
• R2.0
• T33/365
• Vol18.62 (Historical)
• c15,44
• Do we get (roughly) the same result with MC
  Simulation?

22
Monte Carlo Simulation and Options

• We can sample random paths for the OBX index
  price by sampling values for e
• We chose Dt 1/365 (that is roughly daily
  movements), we get

23
Example Monte Carlo simulation of the ito prosess
in Excel

• Install analysis toolpack from Excel add ins
• Use Tools - Data analysis Random Numer
  Generator
• Use Normal Distribution with my1 and sigma 0
• Draw 33 random numbers in the time interval

24
Example monte carlo simulation of the Index
prosess in Excel

25
Example monte carlo simulation of the Index
prosess in Excel

26
Monte Carlo Simulation and Options

• We can sample 30000 random paths for the OBX
  stock price for 33 days ahead by 1 step (Not
  necessary with more than 1 step for European
  Options)
• At the end of the period, we price the option in
  each scenario (totally 30000) by
• We then put an equal weight (probability) on each
  scenario and calculate the option price as an
  average of all scenarios and discount this
  average back to todays value

27
Monte Carlo 30000 Simulations

• Not a good idea to implement directly in Excel
• Write a VBA code including the embedded function
• Application.NormInv(Rnd(),0,1))
• Application.Max

28
Public Function Max(X, y) Max
Application.Max(X, y) End Function
'// Monte Carlo plain vanilla European
option Public Function MonteCarlo(CallPutFlag As
String, S As Double, K As Double, T As Double, _
r As Double, v As Double, nSteps
As Integer, nSimulations As Integer) As Double
Dim dt As Double, St As Double
Dim Sum As Double, Drift As Double, vSqrdt As
Double Dim i As Integer, j As Integer, z As
Integer dt T / nSteps Drift (r -
v 2 / 2) dt vSqrdt v Sqr(dt) If
CallPutFlag "c" Then z 1 ElseIf
CallPutFlag "p" Then z -1 End
If For i 1 To nSimulations St S
For j 1 To nSteps St St
Exp(Drift vSqrdt Application.NormInv(Rnd(),
0, 1)) Next Sum Sum Max(z
(St - K), 0) Next MonteCarlo Exp(-r
T) (Sum / nSimulations) End Function

29

30
Monte Carlo Simulation and Options

• We get c15.42 (15.43 with BS) spending 4.17
  seconds on a Intel 2.4GHz computer with 1GB RAM
• Pros When everything fails and analytical
  solutions cant be found, MC does the job.
• Cons Computer intensive. For large calculations,
  VBA gets to slow. The MC Engine should be build
  in C instead.