Description

Instrinsic Stock Value Spreadsheet document sample

Shared by:
Categories
Tags
-
Stats
views:
1
posted:
2/22/2011
language:
English
pages:
50
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
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
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
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
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
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?

```
Related docs
Other docs by vsp19127
Investment Banking System
Investment Banks 2010 Analyst
Is Account Manager - Excel
Investing into a Company Contract