# Excel Pricing Calculator

Description

Excel Pricing Calculator document sample

Shared by:
Categories
-
Stats
views:
164
posted:
2/3/2011
language:
English
pages:
13
Document Sample

```							               Option Pricing Models
I. Binomial Model
II. Black-Scholes Model (Non-dividend paying European
Option)
A.    Black-Scholes Model is the Limit of the Binomial Model
B. Equations
• C = S N(d1) - Xe-rT N(d2), where
• d1 = [ln(S/X) + (r + 0.5S2)T]/ST
• d2 = d1 - ST
N(d1), and N(d2) are cumulative normal probabilities of d1 and d2,
respectively.
   Factors that determine the option value
– Underlying stock price
– Exercise price
– Time to expiration
– Interest rate
– Underlying stock volatility
Example : B-S Option Pricing
S = \$98 ;     X = \$100
r = 0.05 (continuously compounded annual risk-free rate)
T = 0.25 (one quarter of a year)
S = 0.5 (Annual standard deviation of the continuously compounded stock
returns)
d1 = [ln(S/X) + (r + 0.5S2)T]/ST
= [ln(98/100) + (0.05 + 0.5(0.25))(0.25)] / 0.5(0.5)
= 0.0942
d2 = d1 - ST
= 0.0942 - (0.5) 0.25 = -0.1558

N(d1) = N(0.0942)          N(0.09) = 0.5359
N(d2) = N(-0.1558)         N(0.15) = 0.5596

N(-0.15) = 1 - 0.5596 = 0.4404

Excel: normsdist( )
N(d1) = N(0.0942) = 0.5375
N(d2) = N(-0.1558) = 0.4381

C = S N(d1) - Xe-rT N(d2)
= (98) (0.5375) - 100 e -0.05 (0.25) (0.4381)
= \$9.41
OPTION CALCULATOR
B. Hedge Ratio
•  =  C /  S = N(d1)

III. More about the model inputs
A. Underlying stock price
B. Time to Expiration
C. The Risk-free Rate: continuously compounded
risk-free rate

D. Volatility

Expected Volatility

Proxy: Historical Volatility
•         Variance of continuously compounded returns
1. Calculate continuously compounded returns: r S = ln(1+RS),
or, rs = ln(Pt/Pt-1)
2. Calculate standard deviation S
3. Annualized standard deviation

•   Example
•   CBOE Historical Volatility
F. Historical Volatility and Implied Volatility
•    represents expected volatility of the stock over the life of
the option.
•    Historical  provides estimates of the future volatility.
•   Implied volatility is the market’s estimates of the stock
volatility.
•   Option traders can compare their own expectations of future
price volatility with the implied price volatility. If these are
not consistent with one another, the option price may be
wrong.
 Estimation of implied volatility
IV. Relationship between Model Inputs and Call Price
A. Delta
    =  C /  S = N(d1)
 = f (S)
 = f (T)
•   Delta is the hedge ratio
•   Position Delta – the sum of the deltas

•   long 10,000 shares of stock, short 63 calls with  = 0.377, long 134
puts with  = -0.196
Position Delta = 10,000 (1) + (-63) (100) (0.377) + 134 (100) (-0.196)
= 4,998.5
Meaning: total portfolio is equivalent in market risk of 4,998 shares of
stocks
   B. Gamma
  =/S

For stock-option hedgers, the value of  measures
the extent to which a change in the stock
price will force a revision in the hedge ratio.

 = f(S)
 = f(T)

C. Rho
•   =C/i

This is a liner relationship; the impact of changes in i
on changes in C normally is small.
D. Vega

 =  C /  s
 = f (S)

E. Theta

=C/T

Theta is a measurement of the rate of time value decay.

 = f(S)
V. Put Option Pricing
A. Equations
•   P = -S [1-N(-d1)] + Xe-rT [1-N(-d2)], where
•   d1 = [ln(S/X) + (r + 0.5S2)T]/ST
•   d2 = d1 - ST
•     Homework
1. Calculate B-S call price for INTC using Friday’s closing stock price and a
X that is most close to S. (Use Excel, not manual calculation)
2.   Calculate three implied volatility – one near at-the-money call, one deep
out-of-the-money call, and one deep in-the-money call. Draw a diagram
relating these three implied volatility and option moneyness.
3.   Calculate 6 “Vegas” assuming six different stock prices. Plot these six
“Vegas” against six stock prices.

```
Related docs