Session20_datafile_barrier.xls
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Session20_datafile_barrier.xls as PDF for free.
More details
- Words: 2,193
- Pages: 22
A
B
C
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
E
F
G
H
PRICING A KNOCKOUT BARRIER OPTION
1
16
D
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
42 43 44 45 46 47
30
C
D
E
F
G
H
I
J
K
L
M
N
O
1 CKOUT BARRIER OPTION 2 3 4 5 6 7 8 9 10 11 12 13 14 15 STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#VALUE!
PRICE
Period 2
Period 3
Period 4
Max(St)< Barrier?
Path Knockout risk-neutral option probability payoff
58.80
82.32
115.25
0.00
0.0474
0.00
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
0.00 0.00 0.00 0.00
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
7.63 7.63 7.63 7.63 0.00 7.63
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0.00 0.00 0.00 0.00
19.20
15.36
12.29
1.00
0.0809
0.00
Knockout value
#VALUE!
1.7375
I 42 43 44 45 46 47
J
K
L
M
N #VALUE!
O
A
B
C
D
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
F
G
H
PRICING A KNOCKIN BARRIER OPTION
1
16
E
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
42 43 44 45 46 47
30
C
D
E
F
G
H
I
J
K
L
M
N
O
Path risk-neutral probability
Option payoff
1 OCKIN BARRIER OPTION 2 3 4 5 6 7 8 9 10 11 12 13 14 15 STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#VALUE!
PRICE
Period 2
Period 3
Period 4
Max(St)< Barrier?
58.80
82.32
115.25
0.00
0.0474
85.25
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
35.86 35.86 35.86 35.86
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
0.00 0.00 0.00 0.00 7.63 0.00
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0.00 0.00 0.00 0.00
19.20
15.36
12.29
1.00
0.0809
0.00
Knockin value
#VALUE!
9.0334
I 42 43 44 45 46 47
J
K
L
M
N #VALUE!
O
A
B
C
D
E
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
G
H
KNOCKIN + KNOCKOUT = PLA
1
16
F
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
44 45 46 47 48
30
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1 CKIN + KNOCKOUT = PLAIN VANILLA 2 3 4 5 6 7 8 9 10 11 12 13 14 15STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
#VALUE!
PRICE
Period 2
Period 3
Max(St)< Barrier?
Period 4
Path Knockout risk-neutral payoff probability
Knockin payoff
58.80
82.32
115.25
0.00
0.0474
0
85.2480
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
0 0 0 0
35.8560 35.8560 35.8560 35.8560
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
7.632 7.632 7.632 7.632 0 7.632
0.0000 0.0000 0.0000 0.0000 7.6320 0.0000
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0 0 0 0
0.0000 0.0000 0.0000 0.0000
19.20
15.36
12.29
1.00
0.0809
0
0.0000
Knockin Knockout Sum Plain vanilla
9.0334 1.7375 10.7708 10.7708
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE!
I
J
K
L
M
N
O
44 45 46 47 48
#VALUE!
P
Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
Plain vanilla 85.2480 35.8560 35.8560 35.8560 35.8560 7.6320 7.6320 7.6320 7.6320 7.6320 7.6320 0.0000 0.0000 0.0000 0.0000 0.0000
A
B
D
E
F
PRICING BARRIER OPTIONS BY MONTE CARLO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
C
Up Down Interest
1.4 0.8 1.08
Initialprice Periods Exercise Barrier
30 4 30 50
Runs
100
Knockin option value Actual value
#VALUE! 9.0334
Knockout option value Actual value
#VALUE! #VALUE! 1.7375 <-- Determined from fully-worked out example
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Average of simulations True value
#VALUE! #VALUE!
48 iterations of MCBarrierIn #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 9.0334
#VALUE! #VALUE!
#VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
A
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
B
C
D
E
F
PRICING BARRIER OPTIONS--VBA FUNC Each time interval is divided into n subintervals. In this simulation the initial st the time to maturity = 0.40, and the unit time interval is divided into 80 subint return = 15.00% and standard deviation = 1.25%, and the There are 100 runs in each Monte Carlo sim S0, current stock price X, exercise price Barrier T, time to option exercise r, interest rate m, mean stock return s, standard deviation of stock return
50 45 50 0.4 8% 15% 22%
n, number of sub-intervals of 1 period Delta t
80 0.0125
#VALUE!
Up over 1 sub-interval Down over 1 sub-interval Interest over 1 sub-interval
1.0268 0.9775 1.0010
#VALUE! #VALUE! #VALUE!
Runs
100 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Average of above Minimum Maxmimum Standard deviation
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Data table: Sensitivity of results on number of runs
34
Runs
35 36 37 38 39
50 100 150 200
Average of 48 MCBarrierIn #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Minimum #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Maximum
Standard deviation
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
A 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
B 250 300 350 400 450 500 550
C #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
D #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
E #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
F #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
G
H
I
ER OPTIONS--VBA FUNCTION s simulation the initial stock price = 50.00, the exercise price = 45.00, is divided 1into 80 subintervals. The stock price process has mean eviation = 1.25%, and the interest rate = 8.00%. s in each Monte Carlo simulation
n number
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 #VALUE! 19 20 21 22 23 24 25 26 27 28 29 30 31 32 of33runs 34 35 36 37 38 39
#VALUE!
12.0000 10.0000 8.0000
48 MCBarrierIn Simulations for Various Numbers of Runs
12.0000
48 MCBarrierIn Simulations for Various Numbers of Runs
G 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
H
I
10.0000 8.0000 Average of 48 MCBarrierIn
6.0000
Minimum
4.0000
Maximum Standard deviation
2.0000 0.0000 50
100
150
200
250 300 350 Runs per simulation
400
450
500
550
Numbers
J
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 of Runs 38 39
K
Numbers of Runs J
n
450
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 500 55 550 56 57 58
K
A
KNOCKIN + KNOCKOUT = CALL The almost-continuous case
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
B
S0, current stock price X, exercise price Barrier T, time to option exercise r, interest rate m, mean stock return s, standard deviation of stock return
30 30 40 0.4 8% 15% 22%
n, number of sub-intervals of 1 period Delta t
200 0.0050
Up over 1 sub-interval Down over 1 sub-interval Interest over 1 sub-interval
1.0164 0.9853 1.0004
Runs Knockout barrier Knockin barrier Sum of knockout + knockin Black-Scholes call price
700 #VALUE! #VALUE! #VALUE! #VALUE!
C
KNOCKIN + KNOCKOUT = CALL 1 The almost-continuous case 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
B
C
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
E
F
G
H
PRICING A KNOCKOUT BARRIER OPTION
1
16
D
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
42 43 44 45 46 47
30
C
D
E
F
G
H
I
J
K
L
M
N
O
1 CKOUT BARRIER OPTION 2 3 4 5 6 7 8 9 10 11 12 13 14 15 STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#VALUE!
PRICE
Period 2
Period 3
Period 4
Max(St)< Barrier?
Path Knockout risk-neutral option probability payoff
58.80
82.32
115.25
0.00
0.0474
0.00
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
0.00 0.00 0.00 0.00
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
7.63 7.63 7.63 7.63 0.00 7.63
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0.00 0.00 0.00 0.00
19.20
15.36
12.29
1.00
0.0809
0.00
Knockout value
#VALUE!
1.7375
I 42 43 44 45 46 47
J
K
L
M
N #VALUE!
O
A
B
C
D
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
F
G
H
PRICING A KNOCKIN BARRIER OPTION
1
16
E
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
42 43 44 45 46 47
30
C
D
E
F
G
H
I
J
K
L
M
N
O
Path risk-neutral probability
Option payoff
1 OCKIN BARRIER OPTION 2 3 4 5 6 7 8 9 10 11 12 13 14 15 STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
#VALUE!
PRICE
Period 2
Period 3
Period 4
Max(St)< Barrier?
58.80
82.32
115.25
0.00
0.0474
85.25
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
35.86 35.86 35.86 35.86
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
0.00 0.00 0.00 0.00 7.63 0.00
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0.00 0.00 0.00 0.00
19.20
15.36
12.29
1.00
0.0809
0.00
Knockin value
#VALUE!
9.0334
I 42 43 44 45 46 47
J
K
L
M
N #VALUE!
O
A
B
C
D
E
2 3 4 5 6 7 8 9
Initial stock price Up Down Interest Option exercise price Barrier qU
0.4321 #VALUE!
10 11 12 13 14 15
qD
0.4938 #VALUE!
Risk-neutral probability, up Risk-neutral probability, down
0.4667 #VALUE! 0.5333 #VALUE!
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
G
H
KNOCKIN + KNOCKOUT = PLA
1
16
F
30 1.40 0.80 1.08 30 50.00
STOCK PRICE Paths All up (1 path)
Period 1 Period 2 Period 3
Period 4
Period 0
Period 1
up
up
up
up
30.00
42.00
One down (4 paths)
down up up up
up down up up
up up down up
up up up down
30.00 30.00 30.00 30.00
24.00 42.00 42.00 42.00
Two down (6 paths)
down down down up up up
down up up down up down
up down up down down up
up up down up down down
30.00 30.00 30.00 30.00 30.00 30.00
24.00 24.00 24.00 42.00 42.00 42.00
Three down (4 paths)
up down down down
down up down down
down down up down
down down down up
30.00 30.00 30.00 30.00
42.00 24.00 24.00 24.00
Four down (1 path)
down
down
down
down
30.00
24.00
A
B
44 45 46 47 48
30
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1 CKIN + KNOCKOUT = PLAIN VANILLA 2 3 4 5 6 7 8 9 10 11 12 13 14 15STOCK 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
#VALUE!
PRICE
Period 2
Period 3
Max(St)< Barrier?
Period 4
Path Knockout risk-neutral payoff probability
Knockin payoff
58.80
82.32
115.25
0.00
0.0474
0
85.2480
33.60 33.60 58.80 58.80
47.04 47.04 47.04 82.32
65.86 65.86 65.86 65.86
0.00 0.00 0.00 0.00
0.0542 0.0542 0.0542 0.0542
0 0 0 0
35.8560 35.8560 35.8560 35.8560
19.20 33.60 33.60 33.60 58.80 33.60
26.88 26.88 47.04 26.88 47.04 47.04
37.63 37.63 37.63 37.63 37.63 37.63
1.00 1.00 1.00 1.00 0.00 1.00
0.0619 0.0619 0.0619 0.0619 0.0619 0.0619
7.632 7.632 7.632 7.632 0 7.632
0.0000 0.0000 0.0000 0.0000 7.6320 0.0000
33.60 33.60 19.20 19.20
26.88 26.88 26.88 15.36
21.50 21.50 21.50 21.50
1.00 1.00 1.00 1.00
0.0708 0.0708 0.0708 0.0708
0 0 0 0
0.0000 0.0000 0.0000 0.0000
19.20
15.36
12.29
1.00
0.0809
0
0.0000
Knockin Knockout Sum Plain vanilla
9.0334 1.7375 10.7708 10.7708
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE!
I
J
K
L
M
N
O
44 45 46 47 48
#VALUE!
P
Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
Plain vanilla 85.2480 35.8560 35.8560 35.8560 35.8560 7.6320 7.6320 7.6320 7.6320 7.6320 7.6320 0.0000 0.0000 0.0000 0.0000 0.0000
A
B
D
E
F
PRICING BARRIER OPTIONS BY MONTE CARLO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
C
Up Down Interest
1.4 0.8 1.08
Initialprice Periods Exercise Barrier
30 4 30 50
Runs
100
Knockin option value Actual value
#VALUE! 9.0334
Knockout option value Actual value
#VALUE! #VALUE! 1.7375 <-- Determined from fully-worked out example
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Average of simulations True value
#VALUE! #VALUE!
48 iterations of MCBarrierIn #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 9.0334
#VALUE! #VALUE!
#VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
A
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
B
C
D
E
F
PRICING BARRIER OPTIONS--VBA FUNC Each time interval is divided into n subintervals. In this simulation the initial st the time to maturity = 0.40, and the unit time interval is divided into 80 subint return = 15.00% and standard deviation = 1.25%, and the There are 100 runs in each Monte Carlo sim S0, current stock price X, exercise price Barrier T, time to option exercise r, interest rate m, mean stock return s, standard deviation of stock return
50 45 50 0.4 8% 15% 22%
n, number of sub-intervals of 1 period Delta t
80 0.0125
#VALUE!
Up over 1 sub-interval Down over 1 sub-interval Interest over 1 sub-interval
1.0268 0.9775 1.0010
#VALUE! #VALUE! #VALUE!
Runs
100 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Average of above Minimum Maxmimum Standard deviation
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Data table: Sensitivity of results on number of runs
34
Runs
35 36 37 38 39
50 100 150 200
Average of 48 MCBarrierIn #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Minimum #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Maximum
Standard deviation
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
A 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
B 250 300 350 400 450 500 550
C #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
D #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
E #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
F #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
G
H
I
ER OPTIONS--VBA FUNCTION s simulation the initial stock price = 50.00, the exercise price = 45.00, is divided 1into 80 subintervals. The stock price process has mean eviation = 1.25%, and the interest rate = 8.00%. s in each Monte Carlo simulation
n number
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 #VALUE! 19 20 21 22 23 24 25 26 27 28 29 30 31 32 of33runs 34 35 36 37 38 39
#VALUE!
12.0000 10.0000 8.0000
48 MCBarrierIn Simulations for Various Numbers of Runs
12.0000
48 MCBarrierIn Simulations for Various Numbers of Runs
G 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
H
I
10.0000 8.0000 Average of 48 MCBarrierIn
6.0000
Minimum
4.0000
Maximum Standard deviation
2.0000 0.0000 50
100
150
200
250 300 350 Runs per simulation
400
450
500
550
Numbers
J
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 of Runs 38 39
K
Numbers of Runs J
n
450
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 500 55 550 56 57 58
K
A
KNOCKIN + KNOCKOUT = CALL The almost-continuous case
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
B
S0, current stock price X, exercise price Barrier T, time to option exercise r, interest rate m, mean stock return s, standard deviation of stock return
30 30 40 0.4 8% 15% 22%
n, number of sub-intervals of 1 period Delta t
200 0.0050
Up over 1 sub-interval Down over 1 sub-interval Interest over 1 sub-interval
1.0164 0.9853 1.0004
Runs Knockout barrier Knockin barrier Sum of knockout + knockin Black-Scholes call price
700 #VALUE! #VALUE! #VALUE! #VALUE!
C
KNOCKIN + KNOCKOUT = CALL 1 The almost-continuous case 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
#VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE!
More Documents from "mohit"
Session3_datafile.xlsx
December 2019 45
Ap_gs_mohit Gupta.docx
May 2020 30
Session20_datafile_barrier.xls
December 2019 29
9th_nmcc (2).pdf
November 2019 29
Ces Notes - 29 05 07
October 2019 32