Get documents about "Instrinsic Stock Value Spreadsheet
Description
Instrinsic Stock Value Spreadsheet document sample
Document Sample
UN-13B
A B C D E F G H I J K L M
1 Black-Scholes Option-Pricing Formula
2 S 50 Current stock price
3 X 45 Exercise price
4 r 4.00% Risk-free rate of interest
5 T 0.75 Time to maturity of option (in years)
6 Sigma 30% Stock volatility, s
7
8 d1 0.6509 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))
9 d2 0.3911 <-- d1-sigma*SQRT(T)
10
11 N(d1) 0.7424 <-- Uses formula NormSDist(d1)
12 N(d2) 0.6521 <-- Uses formula NormSDist(d2)
13
14 Call price 8.64 <-- S*N(d1)-X*exp(-r*T)*N(d2)
15 Put price 2.31 <-- call price - S + X*Exp(-r*T): by Put-Call parity Data table header: = B14
16 2.31 <-- X*exp(-r*T)*N(-d2) - S*N(-d1): direct formula
17
Data table: Comparing
Data table header:
the Black-Scholes =Max(B2-B3,0).
18 to the intrinsic value This is the option's
Stock Call Intrinsic intrinsic value.
19 price price value
20 8.6434 5
21 5 0.0000 0 Black-Scholes Price Versus Instrinsic Value
22 10 0.0000 0
23 15 0.0000 0
24 20 0.0029 0 35
25 25 0.0484 0 Call
30 price
26 30 0.3101 0
Intrinsic
27 35 1.1077 0 value
25
28 40 2.7319 0
29 45 5.2777 0 20
30 50 8.6434 5
31 55 12.6307 10 15
32 60 17.0378 15
33 65 21.7056 20 10
34 70 26.5256 25
35 75 31.4304 30 5
36 80 36.3811 35
37 0
38 0 10 20 30 40 50 60 70 80
39 Stock price, S
40
UN-13C
A B C D E F G H I J K L M
1 BLACK-SCHOLES MODEL IN VBA
2 S 100 =B9
3 X 100 Stock price Call Put
=B8
4 T 1.00 20.3185 10.8022 <--This is the header of the Data Table start 40
5 Interest 10.00% 40 0.1802 50.6639 step 5
6 Sigma 40.00% 45 0.4104 45.8941
7 50 0.8081 41.2918
8 Call price 20.3185 #NAME? 55 1.4241 36.9079
9 Put price 10.8022 #NAME? 60 2.3019 32.7857
10 65 3.4739 28.9576
11 70 4.9600 25.4437
12 To the right is a data 75 6.7683 22.2520
13 table that gives the 80 8.8965 19.3803
Call and Put Prices using Black-Scholes
14 call and put values for 85 11.3341 16.8179
15 various stock 90 14.0645 14.5482
16 prices. 50 95 17.0669 12.5506
17 45 100 20.3185 10.8022
18 40 105 23.7954 9.2791
19 35 Call 110 27.4740 7.9578
20 Put 115 31.3316 6.8154
21 30 120 35.3469 5.8306
22 25 125 39.5002 4.9839
23 20 130 43.7736 4.2574
24 15
25
10
26
27 5
28 0
29 40 50 60 70 80 90 100 110 120 130
30 Stock price, S
31
A B C D E F G
QQQQ HISTORICAL PRICES, DAILY DATA
1 and resulting statistics
Closing
2 Date price Return
3 30-May-06 38.61
4 31-May-06 38.79 0.47% #NAME? Return statistics
5 1-Jun-06 39.71 2.34% #NAME? Average daily return -0.09%
6 2-Jun-06 39.61 -0.25% #NAME? Standard deviation of daily return 1.31%
7 5-Jun-06 38.75 -2.20% #NAME?
8 6-Jun-06 38.72 -0.08% Annualized mean return -23.59%
9 7-Jun-06 38.43 -0.75% Annualized sigma 20.66%
10 8-Jun-06 38.37 -0.16%
11 9-Jun-06 38.12 -0.65%
12 12-Jun-06 37.37 -1.99%
13 13-Jun-06 37.22 -0.40%
14 14-Jun-06 37.6 1.02%
38 19-Jul-06 36.62 1.29%
39 20-Jul-06 36.08 -1.49%
40 21-Jul-06 35.7 -1.06%
41 24-Jul-06 36.41 1.97%
42 25-Jul-06 36.62 0.58%
43 26-Jul-06 36.59 -0.08%
44 27-Jul-06 36.35 -0.66%
45 28-Jul-06 37.11 2.07%
H
DATA
1
2
3
4
5 #NAME?
6 #NAME?
7
8 #NAME?
9 #NAME?
10
11
12
13
14
38
39
40
41
42
43
44
45
A B C D E
PRICING THE AUGUST 2006 QQQQ OPTIONS
1 Using the historical volatility s
2 Current date 28-Jul-06
3 Option expiration date 18-Aug-06
4
5 S 37.11
6 X 37
7 T 0.06 #NAME?
8 Interest 5.00%
9 Sigma 20.66%
10
11 Call price 0.8447 #NAME?
12 Put price 0.6284 #NAME?
13
14 Actual prices
15 Call 0.75
16 Put 0.55
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
44
45
46
47
48
49
A B C D E
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
F G H I J K L M N
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
44
45
46
47
48
49
F G H I J K L M N
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
O P Q R S T U V
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
44
45
46
47
48
49
O P Q R S T U V
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
A B C D E F
QQQQ HISTORICAL PRICES, MONTHLY DATA
1 and resulting statistics
Closing
2 Date price Return
3 30-Jul-04 34.40
4 2-Aug-04 33.54 -2.53% Return statistics
5 1-Sep-04 34.64 3.23% Average monthly return 0.18%
6 1-Oct-04 36.38 4.90% Standard deviation of monthly return 3.31%
7 1-Nov-04 38.57 5.85%
8 1-Dec-04 39.73 2.96% Annualized mean return 2.17%
9 3-Jan-05 37.22 -6.53% Annualized sigma 11.48%
10 1-Feb-05 37.05 -0.46%
11 1-Mar-05 36.40 -1.77%
12 1-Apr-05 34.82 -4.44%
13 2-May-05 37.90 8.48%
14 1-Jun-05 36.64 -3.38%
38 19-Jul-06 36.62 1.29%
39 20-Jul-06 36.08 -1.49%
40 21-Jul-06 35.70 -1.06%
41 24-Jul-06 36.41 1.97%
42 25-Jul-06 36.62 0.58%
43 26-Jul-06 36.59 -0.08%
44 27-Jul-06 36.35 -0.66%
45 28-Jul-06 37.11 2.07%
G
THLY DATA
1
2
3
4
5 #NAME?
6 #NAME?
7
8 #NAME?
9 #NAME?
10
11
12
13
14
38
39
40
41
42
43
44
45
A B C D E F
IMPLIED VOLATILITY FOR THE
1 AUGUST 2006 QQQQ OPTIONS
2 Current date 28-Jul-06
3 Option expiration date 18-Aug-06
4
5 S 37.11
6 X 37
7 T 0.06 #NAME?
8 Interest 5.00%
9 Implied volatility, s 17.96%
10
11 Call price 0.7500 #NAME?
12 Put price 0.5337 #NAME?
13
14 Actual prices
15 Call 0.75
16 Put 0.55
G H I J K
1
2
3
4
5
6
7
8
9
10 0
11
12
13
14
15
16
Page 518
A B C D
1 Black-Scholes Option Price is Monotonic in Sigma
2 S 45 Current stock price
3 X 50 Exercise price
4 T 1 Time to maturity of option (in years)
5 r 8.00% Risk-free rate of interest
6 Sigma 30.00% Stock volatility
7
8 Call price 4.88 #NAME?
9
10 Data table: Call price as function of volatility s
11 4.8759 #NAME?
12 15% 2.1858
13 16% 2.3646 Call Price and Volatility
14 17% 2.5437 4.50
15 18% 2.7229 4.20
16 19% 2.9023
3.90
17 20% 3.0817
18 21% 3.2612 3.60
BS call price
19 22% 3.4407 3.30
20 23% 3.6202
3.00
21 24% 3.7997
22 25% 3.9792 2.70
23 26% 4.1587 2.40
24 27% 4.3381 2.10
25
1.80
26
15% 18% 21% 24% 27% 30%
27
Volatility, s
28
Page 15
Page 518
E F G
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
Page 16
Page 520
A B F
1 BLACK-SCHOLES IMPLIED VOLATILITY
The VBA module attached to this spreadsheet defines a function called
CallVolatility(S,X,T,interest,target_call_price). To use this function fill in the relevant rows (in boldface). The
2 cell labeled "Implied call volatility" contains the function.
3 S 51.00
4 X 50.00
5 T 1
6 Interest 8.00%
7 Target call price 6.00
8
9 Implied call volatility 15.35% #NAME?
10
11 Data Table: Implied volatility as a function of the call price
Implied
12 Call price volatility
13 15.35% #NAME?
14 5.00 7.51%
15 5.50 11.96% Implied Call Volatility as Function of
16 6.00 15.35% Call Price
17 6.50 18.45% 45%
18 7.00 21.39% 40%
19 7.50 24.25% 35%
20 8.00 27.07%
30%
Volatility, s
21 8.50 29.84%
25%
22 9.00 32.59%
23 9.50 35.33% 20%
24 10.00 38.05% 15%
25 10.50 40.77% 10%
26
5%
27
0%
28
5 6 7 8
29
30 Call price
31
Page 17
Page 520
G
PLIED VOLATILITY
1
unction called
this function fill in the relevant rows (in boldface). The
2
3
4
5
6
7
8
9
10
f the call price11
12
13
14
Call Volatility as Function of
15
Call Price
16
17
18
19
20
21
22
23
24
25
26
27
28
8 9 10 11
29
Call price
30
31
Page 18
Time t
Time 0 Dividend
payment
Stock price = S Div
Stock price minus
PV(dividend) =
S - Div*exp[-rt]
Time T
Option
expiration
Max[ST - X,0]
A B C D
1 PRICING THE COCA COLA JAN07 CALLS AND PUTS
2 Current date 28-Jul-06
3 Option expiration date 19-Jan-07
4 Current stock price 44.52
5 Interest rate 5.00%
6
Anticipated Present
Date
7 dividend value
8 Mid September 13-Sep-06 0.31 0.31
9 End November 29-Nov-06 0.31 0.30
10
11 Stock price net of PV(dividends) 43.91 #NAME?
12 Exercise price, X 45.00 <-- Approximately at the money
13 Time to maturity, T 0.4795 #NAME?
14 Interest rate, r 5.00% Risk-free rate of interest
15 Call price 1.80 <-- Call price on 28jul06
16 Put price 1.85 <-- Put price on 28jul06
17
18 Implied volatility
19 Call, S net of dividends 14.95% #NAME?
20 Put, S net of dividends 15.15% #NAME?
21
22 Call, S with dividends 12.19% #NAME?
23 Put, S with dividends 17.45% #NAME?
E
JAN07 CALLS AND PUTS
1
2
3
4
5
6
7
8 #NAME?
9 #NAME?
10
11
12
ately at the money
13
14
15
16
17
18
19
20
21
22
23
A B C
Merton's Dividend-Adjusted Option Pricing Model
1 used here to price S&P 500 Spiders (symbol: SPY)
2 S 127.98 current stock price
3 X 127.00 exercise price
4 T 0.6329 <-- option expires 16-Mar-07, today's date 28-Jul-06
5 r 5.00% risk-free rate of interest
6 k 1.70% dividend yield
7 Sigma 14% stock volatility
8
9 d1 0.3122 #NAME?
10 d2 0.2008 #NAME?
11
12 N(d1) 0.6226 <--- Uses formula NormSDist(d1)
13 N(d2) 0.5796 <--- Uses formula NormSDist(d2)
14
15 Call price 7.51 <-- S*Exp(-k*T)*N(d1)-X*exp(-r*T)*N(d2)
16 Put price 3.94 <-- call price - S*Exp(-k*T) + X*Exp(-r*T): by Put-Call parity
17 3.94 <-- X*exp(-r*T)*N(-d2)-S*Exp(-k*T)*N(-d1): direct formula
18
19
20
21
22
23
24 Actual dates for Mar07 SPY options
25 Current date 28-Jul-06
26 Option expiration date 16-Mar-07
D E F G
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
A B
1 Pricing Currency Options
2 S 1.276
3 X 1.285
4 rUS 5.00%
5 r€ 5.50%
6 T 0.0575
7 Sigma 4.70%
8 d1 -0.6095
9 d2 -0.6208
10
11 Number of Euros per call contract 10,000
12
13 N(d1) 0.2711
14 N(d2) 0.2674
15
16 Call price 23.69
17 Put price 112.23
18
19
20
21
22
23
24 Dates
25 Current date 28-Jul-06
26 Option expiration date 18-Aug-06
C D
Pricing Currency Options
1
2 Current exchange rate: U.S. dollar price of one Euro Intuition: The underlying asset of the
3 Exercise price currency option is a Euro. The Euro
pays a dividend, which is the Euro
4 U.S. interest rate interest rate. Therefore the Merton
5 Euro interest rate model applies, with the underlying
6 Time to maturity of option (in years) asset price being S*exp(-r€*T), where r€
7 Euro volatility in dollars is the interest rate on Euros. Note also
8 <--(LN(S/X)+(rUS-r€+0.5*sigma^2)*T)/(sigma*SQRT(T)) the change in d1, where rUS -r€ appears
instead of rUS as in the regular Black-
9 <-- d1 - sigma*SQRT(T) Scholes formula.
10
11
12
13 <--- Uses formula NormSDist(d1)
14 <--- Uses formula NormSDist(d2)
15
16 <-- (S*Exp(-r€*T)*N(d1)-X*exp(-rUS*T)*N(d2))*B11
17 <-- (X*exp(-rUS*T)*N(-d2)-S*Exp(-r€*T)*N(-d1))*B11: direct formula
18
19
20
21
22
23
24
25
26
E F G
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
A B
ANALYZING A SIMPLE STRUCTURED PRODUCT
1 $1,000 Deposit with 50% Participation in S&P Increase over 5 Years
2 Initial S&P 500 price, S0 950
3 Structured exercise price, X 950
4 Risk-free interest rate for 5 years, r 5.00%
5 Time to maturity, T 5
6 Volatility of S&P 500, sSP 25%
7 Participation rate 50%
8
9 Strutured components, value today
10 Bond paying $1000 at maturity 778.80
11 Participation rate /S0*at-the-money call on S&P 500 162.52
12 Value of structured security today 941.32
C
SIMPLE STRUCTURED PRODUCT
% Participation in S&P Increase over 5 Years
1
2 <-- The price of the S&P 500 at PPUP issuance
3
4
5
6
7 <-- Percentage of increase in the S&P going to PPUP owner
8
9
10 #NAME?
11 #NAME?
12 #NAME?
A B
ANALYZING A SIMPLE STRUCTURED PRODUCT
1 $1,000 Deposit with 50% Participation in S&P Increase over 5 Years
2 Initial S&P 500 price, S0 950
3 Structured exercise price, X 950
4 Risk-free interest rate for 5 years, r 5.00%
5 Time to maturity, T 5
6 Volatility of S&P 500, sSP 42.00%
7 Participation rate 50%
8
9 Strutured components, value today
10 Bond paying $1000 at maturity 778.80
11 Participation rate /S0*at-the-money call on S&P 500 221.20
12 Value of structured security today 1000.00
C
SIMPLE STRUCTURED PRODUCT
% Participation in S&P Increase over 5 Years
1
2 <-- The price of the S&P 500 at PPUP issuance
3
4
5
6
7 <-- Percentage of increase in the S&P going to PPUP owner
8
9
10 #NAME?
11 #NAME?
12 #NAME?
A B C
1 ABN-AMRO AIRBAG
2 Y 1,000.00
3 X1 1,618.50
4 X2 2,158.00
5 ST 2,373.80
6 Airbag payoff
7 By Airbag definition 1100.00 #NAME?
8 Option formula 1100.00 #NAME?
9
10
11 Data table of payoffs
Airbag Option
12 ST definition formula
13
14 0 0.00 0.00
15 100 61.79 61.79
16 500 308.93 308.93
17 750 463.39 463.39
18 1,000 617.86 617.86
19 1,250 772.32 772.32
20 1,618.5 1,000.00 1,000.00
21 1,750 1,000.00 1,000.00
22 2,000 1,000.00 1,000.00
23 2,158 1,000.00 1,000.00
24 2,500 1,158.48 1,158.48
25 2,750 1,274.33 1,274.33
26 3,000 1,390.18 1,390.18
27 3,250 1,506.02 1,506.02
28 3,500 1,621.87 1,621.87
29 3,750 1,737.72 1,737.72
30
D
ABN-AMRO AIRBAG
1
2
3
4
5
6
7
8
9
10
11
12
13 <-- Data table headers hidden
14
15 2000
16 1800
17 1600
18 1400
Airbag payoff
19 1200
20
1000
21
22 800
23 600 Airbag definition
24 400
Option formula
25 200
26 0
27
0 1000 2000 3000 4000
28
Stoxx50 at Airbag expiration
29
30
A B
PRICING THE ABN-AIRBAG
1 Find the Implied Volatility
2 Stoxx50 price today, S0 2,158.0
3 X1 1,618.50
4 X2 2,158.0
5 Y 1,000.0
6 Risk-free interest rate for 5 years, r 7.00%
7 Time to maturity, T 5
8 Volatility of the Stoxx50, sigma 15.75%
9
10 Airbag components, value today
11 Bond paying X1 at maturity 704.69
12 Y/X1 * written puts with exercise X1 -4.69
13 Purchased call with exercise X2 320.01
14 Value of structured security today 1020.00
15
16
17 Table: Sensitivity of Airbag to Sigma 1,020.00
18 0% 1,000.00
19 1% 1,000.00
20 3% 1,000.00
21 6% 1,000.16
22 9% 1,002.76
23 10% 1,004.57
24 11% 1,006.80
25 12% 1,009.34
26 13% 1,012.09
27 14% 1,014.95
28 15% 1,017.84
29 16% 1,020.70
30 17% 1,023.49
31 18% 1,026.16
32 19% 1,028.70
33 20% 1,031.11
34 21% 1,033.35
35 22% 1,035.45
36 23% 1,037.39
37 24% 1,039.19
38 25% 1,040.84
C
RICING THE ABN-AIRBAG
Find the Implied Volatility
1
2
3
4
5
6
7
8
9
10
11 #NAME?
12 #NAME?
13 #NAME?
14 #NAME?
15
16
17 #NAME?
18
19
20 Airbag Pricing: Sensitivity to s
21 1045
22 1040
23 1035
24
1030
25
Airbag initial price
26 1025
27 1020
28 1015
29
1010
30
31 1005
32 1000
33 995
34
0% 5% 10% 15% 20% 25%
35
Stoxx50 volatility, s
36
37
38
A B C D E
1 ABN-AMRO AIRBAG SENSITIVITY TO TIME TO MATURITY AND SIGMA
2 Stoxx50 price today, S0 2,158.0
3 X1 1,618.50
4 X2 2,158.0
5 Y 1,000.0
6 Risk-free interest rate for 5 years, r 7.00%
7 Time to maturity, T 5
8 Volatility of the Stoxx50, sigma 15.75%
9
10 Airbag components, value today
11 Bond paying X1 at maturity 704.69 #NAME?
12 Y/X1 * written puts with exercise X1 -4.69 #NAME?
13 Purchased call with exercise X2 320.01 #NAME?
14 Value of structured security today 1020.00 #NAME?
15
16 Time to maturity, T
17 Data table 1020.00 5 4 3
18 header: 5% 1000.02 1000.07 1000.20
19 =B14 10% 1004.57 1006.22 1008.40
20 15% 1017.84 1021.09 1024.72
21 20% 1031.11 1035.21 1039.61
22 Volatility of the Stoxx50, sigma --> 25% 1040.84 1045.48 1050.44
23 30% 1047.16 1052.22 1057.69
24 35% 1050.86 1056.29 1062.26
25 40% 1052.66 1058.44 1064.88
26 45% 1053.10 1059.19 1066.10
27 50% 1052.55 1058.94 1066.29
F G H
MATURITY AND SIGMA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 2 1 0.0001
18 1000.59 1001.77 1000.20
19 1011.13 1013.78 1000.40
20 1028.28 1029.65 1000.59
21 1043.69 1044.54 1000.79
22 1055.14 1056.54 1000.99
23 1063.09 1065.58 1001.19
24 1068.39 1072.19 1001.39
25 1071.75 1076.95 1001.59
26 1073.70 1080.28 1001.79
27 1074.59 1082.53 1001.99
Time 0 1 2 3
-1,000 97.50 97.50 97.50
1,000-25.641*Max(39-ST,0)
A B C D E F
1 EQUIVALENCE OF 2 WAYS OF WRITING THE PAYOFF
2 Cisco price, 23 July 2002, ST 32
3 Payoff ratio 25.6410 #NAME?
4 Terminal payoff
5 As described by UBS 820.51 #NAME?
6 In option terms 820.51 #NAME?
7
8 Data table: Comparing the payoff on Cisco-linked GOALS
Alternative
Cisco stock price UBS option
9 on 23 July 2002, ST description description
10 820.51 820.51 <-- Data table headers, B5 and B6 respectively
11 0 0.00 0.00
12 10 256.41 256.41 UBS Cisco-Lined GOALS Terminal Pa
13 15 384.62 384.62
1000
14 20 512.82 512.82
15 22 564.10 564.10 900
16 24 615.38 615.38 800
17 26 666.67 666.67 700
18 30 769.23 769.23 600
19 32 820.51 820.51
20 34 871.79 871.79
500
21 38 974.36 974.36 400
22 39 1,000.00 1,000.00 300
23 40 1,000.00 1,000.00 200
24 42 1,000.00 1,000.00
100
25 44 1,000.00 1,000.00
26 46 1,000.00 1,000.00 0
27 47 1,000.00 1,000.00 0 10 20
28 50 1,000.00 1,000.00
G H I J
RITING THE PAYOFF
1
2
3
4
5
6
7
8
9
respectively
rs, B5 and B610
11
Lined12 GOALS Terminal Payoff
13
14
15
16
17
18
19
20
21 UBS
22 description
23 Alternative
option description
24
25
26
20
27 30 40 50
28
A B C
1 PRICING THE UBS GOALS IMPLICIT PUT
2 Annual risk-free rate 5.20%
3 Coupon rate 19.50%
4 Initial cost 1,000
Conversion ratio: # of shares of Cisco
5 received if share price is low 25.641 #NAME?
6
7 Valuing the fixed payments at 5.20%
8 Fixed payments
9 Date Cash flow
10 23-Jan-01 (1,000.00)
11 23-Jul-01 97.50 #NAME?
12 23-Jan-02 97.50
13 23-Jul-02 1,097.50
14 PV of Goals bond component 205.11 #NAME?
15
16 Value of 25.641 puts embedded in Goals 205.11 #NAME?
17 Value per put 8.00 #NAME?
18 This is what UBS is paying the Goals purchaser for the embedded puts.
19
20 Valuing the puts with Black-Scholes
21 S 42.625 Current stock price
22 X 39 Exercise price
23 r 5.20% Risk-free interest rate
24 T 1.5 Time to maturity of option (in years)
25 Sigma 80% Stock volatility
26 Put price 11.71 #NAME?
27
28 Is the Goals a good buy? No #NAME?
29
Technical note: For didactic clarity, the computations use 5.2% as the interest rate for valuing
both the bond component of the Goals (rows 10-14) and for the option valuation. Given a 2.6%
semi-annual discrete interest rate, it would be technically more correct to use an equivalent
continuously-compounded interest rate of LN((1.026)^2) in the option computations. The reader
30 can confirm that the effect of this correction is negligible.
A B C D
CREATING A RISKLESS SECURITY WITH THE
1 UBS GOALS AND 25.641 PUTS
2 Initial cash flows
3 Buy UBS security -1,000.00
4 Buy 25.641 puts -300.21 #NAME?
5
Cash flow of "engineered" security:
6 GOALS + 25.641 bought puts
7 Date Cash flow
8 23-Jan-01 (1,300.21) #NAME?
9 23-Jul-01 97.50
10 23-Jan-02 97.50
11 23-Jul-02 1,097.50
12
13 IRR of above -0.43% #NAME?
14
15 Inputs for Black-Scholes formula in cell B4
16 S 42.625 Current stock price
17 X 39 Exercise price
18 r 5.20% Risk-free rate of interest
19 T 1.5 Time to maturity of option (in years)
20 Sigma 80% Stock volatility
E F G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
A B C D E
1 "BANG FOR THE BUCK" WITH OPTIONS
2 S 25 Current stock price Data table: Effect of S o
3 X 25 Exercise price
4 r 6.00% Risk-free rate of interest
5 T 0.5 Time to maturity of option (in years) 15
6 Sigma 30% Stock volatility 16
7 17
8 d1 0.2475 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T)) 18
9 d2 0.0354 <-- d1-sigma*SQRT(T) 19
10 20
11 N(d1) 0.5977 <-- Uses formula NormSDist(d1) 21
12 N(d2) 0.5141 <-- Uses formula NormSDist(d2) 22
13 23
14 Call price 2.47 <-- S*N(d1)-X*exp(-r*T)*N(d2) 24
15 Put price 1.73 <-- call price - S + X*Exp(-r*T): by put-call parity 25
16 26
17 Call bang 6.0483 #NAME? 27
18 Put bang 5.8070 #NAME? 28
19 29
20 30
21
22
23 Data table: Effect of S a
24 Data table
25 header:
26 =B17 6.0483
27 15
28 16
29 17
30 18
31 19
32 20
33 21
34 22
35 23
36 24
37 25
38 26
39 27
40 28
41 29
42 30
F G H I
1
Data table: Effect of S on "bang"
2
3 Calls Puts
4 <-- Data table header
5 2.3828 14.3100
6 2.6095 13.0925
7 2.8695 11.9770
8 3.1641 10.9534
9 3.4933 10.0134
10 3.8555 9.1503
11 4.2481 8.3581
12 4.6675 7.6321
13 5.1100 6.9676
14 5.5716 6.3605
15 6.0483 5.8070
16 6.5367 5.3034
17 7.0335 4.8463
18 7.5358 4.4321
19 8.0414 4.0578
20 8.5481 3.7202
21
22
Data table: Effect of S and T on "call
23 bang"
24
25 T--option time to exercise
26 0.25 0.5 0.75 1
27 25.8566 14.1767 10.1698 8.1113
28 23.3203 12.9886 9.4124 7.5625
29 20.9931 11.9035 8.7218 7.0623
30 18.8591 10.9122 8.0913 6.6056
31 16.9055 10.0067 7.5154 6.1882
32 15.1222 9.1804 6.9891 5.8062
33 13.5006 8.4274 6.5082 5.4565
34 12.0334 7.7424 6.0691 5.1362
35 10.7137 7.1205 5.6682 4.8426
36 9.5347 6.5572 5.3025 4.5737
37 8.4892 6.0483 4.9691 4.3272
38 7.5694 5.5896 4.6655 4.1012
39 6.7664 5.1773 4.3892 3.8941
40 6.0706 4.8074 4.1379 3.7043
41 5.4720 4.4764 3.9094 3.5303
42 4.9598 4.1807 3.7019 3.3708
"Bang for the Buck"
The Price Elasticity of Calls and Puts
as a Function of the Exercise Price X
16
14
12
Profit elasticity--"bang"
10
8
6
4
Calls
2
Puts
0
15 17 19 21 23 25 27 29 31
Option exercise price, X ($)
UN-13B
A B C D E F G
USING THE BLACK (1976) MODEL
1 TO PRICE A BOND OPTION
2 F 133.011 <-- Bond forward price
3 X 130.000 <-- Exercise price
4 r 4.00% <--Risk-free rate of interest
5 T 0.5
6 Sigma 6% <-- Bond forward price volatility, s
7
8 d1 0.5609 #NAME?
9 d2 0.5185 #NAME?
10
11 Call price 4.13 #NAME?
12 Put price 1.02 #NAME?
A B C D E F
1 THE FORWARD INTEREST RATE
2 Bond maturity, W 7
3 Option maturity, T 4
4 Year W pure discount rate 6%
5 Year T pure discount rate 5%
6
7 Discretely-compounded interest rates
8 0 1 2 3 4
9 7-year deposit at 6.00% 100.00
10 4-year loan at 5.00% -100.00 121.55
11 Sum of above: A 3-year deposit at year 4 0.00 121.55
12
Discretely-compounded forward interest rate
13 from year 4 to year 7 7.35% #NAME?
14
15 Continuously-compounded interest rates
16 0 1 2 3 4
17 7-year deposit at 6.00% 100.00
18 4-year loan at 5.00% -100.00 122.14
19 Sum of above: A 3-year deposit at year 4 0.00 122.14
20
Continuously-compounded forward interest rate
21 from year 4 to year 7 7.33% #NAME?
G H I J K L
ST RATE 1
2
3
4
5
6
7
8 5 6 7 8 9 10
9 -150.36
10
11 -150.36
12
13
14
15
16 5 6 7 8 9 10
17 -152.20
18
19 -152.20
20
21
A B C
DETERMINING THE FORWARD PRICE
1 OF THE BOND
2 Bond's maturity, N 2
3 Option maturity, T 0.5
4 Bond maturity value 147
5
6 Interest rate to N 6%
7 Interest rate to T 4%
8
9 Bond forward price to T 133.011 #NAME?
