CS代写 SP500 sigma () from SPY

PowerPoint Presentation

Chapter 17: Black-Scholes Merton

Copyright By PowCoder代写 加微信 powcoder

Black-Scholes Merton
Most widely-used option pricing model
Hard to fully understand and hard to prove
Easy to use (especially in Excel)

Digression: Mathematical theorems
Every good mathematical theorem:
Starts with a couple of Ifs
Has a Then
A really good theorem follows “Then” with Where
Black-Scholes is a really good theorem!
If the stock price is lognormally distributed
If the stock has no dividends before option expiration T
If the option is European

Black-Scholes Merton as a theorem: THEN
Then the call and put price are given by:

where N() indicates values of the standard normal distribution
s = annualized standard deviation of the stock’s return
T = option exercise date

Note use of Excel formula Norm.S.Dist to compute N( )
Implementing Black-Scholes Merton in Excel

Black-Scholes Merton (BSM) in VBA

Function dOne(Stock, Exercise, Time, Interest, sigma)
dOne = (Log(Stock / Exercise) + Interest * Time) / _
(sigma * Sqr(Time)) + 0.5 * sigma * Sqr(Time)
End Function
Function dTwo(Stock, Exercise, Time, Interest, sigma)
dTwo = dOne(Stock, Exercise, Time, Interest, sigma) – _
sigma * Sqr(Time)
End Function
Function BSCall(Stock, Exercise, Time, Interest, sigma)
BSCall = Stock * Application.NormSDist(dOne(Stock, Exercise, _
Time, Interest, sigma)) – Exercise * Exp(-Time * Interest) * _
Application.NormSDist(dTwo(Stock, Exercise, Time, Interest, sigma))
End Function
‘Put pricing function uses put-call parity theorem
Function BSPut(Stock, Exercise, Time, Interest, sigma)
BSPut = BSCall(Stock, Exercise, Time, Interest, sigma) _
+ Exercise * Exp(-Interest * Time) – Stock
End Function
VBA functions BSCall and BSPut

The BSM parameters
Most are observed or known:
S = current stock price; X = option exercise price; T = time to option maturity; r = risk-free interest rate (Treasury rate)
Only is difficult!
Two approach for :
Historical prices/returns
Annualize standard deviation by multiplying by:
n = 12 if data is monthly
n = 52 if data is weekly
n = 250, 252, 365 (???) if data is daily
Find s so that BSM price = market price

Computing the SP500 sigma () from SPY

Function CallVolatility(Stock, Exercise, Time, Interest, Target)
Do While (High – Low) > 0.0001
If BSCall(Stock, Exercise, Time, Interest, (High + Low) / 2) > _
Target Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
CallVolatility = (High + Low) / 2
End Function
Note stopping condition While(High-Low) > 0.0001. Do not use High = Low as a stopping condition—it may never end!
Implied call volatility VBA

Implied put volatility (VBA)
Function PutVolatility(Stock, Exercise, Time, Interest, Target)
Do While (High – Low) > 0.0001
If BSPut(Stock, Exercise, Time, Interest, (High + Low) / 2) > _
Target Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
PutVolatility = (High + Low) / 2
End Function

Using CallVolatility function for same example

CallVolatility dialog box

Dividend adjustments
Black-Scholes must be adjusted for known or anticipated dividends on stock before option maturity T
Method: Deduct from stock price S0 the present value of future dividends before maturity.
For discrete dividends: See Financial Modeling for Coca Cola example

Continuous dividends: Merton adjustment to Black-Scholes
Assumption: stock pays out continuous dividend stream
Appropriate for indices (say, options on S&P500)
Used to price options on foreign currency (forex)

Merton formula, continuous dividends

where k is the dividend yield on the stock

Pricing Spyders: k = 1.7% (cell B6)

S50Current stock price
X45Exercise price
r4.00%Risk-free rate of interest
T0.75Time to maturity of option (in years)
Stock volatility,
0.6509<-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T)) -sigma*SQRT(T) <-- Uses formula NormSDist(d <-- Uses formula NormSDist(d Call price8.64 )-X*exp(-r*T)*N(d Put price2.31<-- call price - S + X*Exp(-r*T): by Put-Call parity <-- X*exp(-r*T)*N(-d ) - S*N(-d ): direct formula Black-Scholes Option-Pricing Formula S50Current stock price X45Exercise price r4.00%Risk-free rate of interest T0.75Time to maturity of option (in years) Stock volatility, Call8.64<-- =BSCall(B2,B3,B5,B4,B6) Put2.31<-- =BSPut(B2,B3,B5,B4,B6) Black-Scholes in VBA DateAdj CloseReturn 10-Oct-11117.07 Count 252<-- =COUNT(C:C) 11-Oct-11117.190.10%<-- =LN(B4/B3)Average daily return 0.08%<-- =AVERAGE(C:C) 12-Oct-11118.220.88%<-- =LN(B5/B4)Standard deviation of daily return 1.03%<-- =STDEV.S(C:C) 13-Oct-11117.98-0.20% 14-Oct-111201.70% Annualized mean return 0.99%<-- =12*G4 17-Oct-11117.71-1.93% Annualized sigma 16.34%<-- =SQRT(252)*G5 18-Oct-11120.011.94% 19-Oct-11118.59-1.19% 20-Oct-11119.110.44% Count 126<-- =COUNT(C130:C255) 21-Oct-11121.371.88% Average daily return 0.05%<-- =AVERAGE(C130:C255) 24-Oct-11122.861.22% Standard deviation of daily return 0.87%<-- =STDEV.S(C130:C255) 25-Oct-11120.47-1.96% 26-Oct-11121.691.01% Annualized mean return 0.59%<-- =12*G12 27-Oct-11125.933.42% Annualized sigma 13.76%<-- =SQRT(252)*G13 28-Oct-11125.9-0.02% Return statistics, one year SPY HISTORICAL PRICES, DAILY DATA Return statistics, last half year Current date 09-Oct-12 Option expiration date 19-Jan-13 Current SPY price, S 144.2 Option strike price, X 144 Time to maturity, T 0.279452<-- =(B3-B2)/365 Interest rate 0.08% Actual call price 4.74 Actual put price 4.91 Implied call volatility 15.22%<-- =CallVolatility(B5,B6,B7,B8,B10) Proof: Black-Scholes call price 4.74<-- =BSCall(B5,B6,B7,B8,B13) Implied put volatility 16.54%<-- =PutVolatility(B5,B6,B7,B8,B11) Proof: Black-Scholes put price 4.91<-- =BSPut(B5,B6,B7,B8,B16) IMPLIED VOLATILITY FOR THE JANUARY 2013 SPY OPTIONS ABCDEF G H Current date09-Oct-12 Current SPY price, S 144.2 Exercise price, X 144 16.34%<-- ='Page 431'!G8 Expiration maturity, T historical Implied call volatility 20-Oct-121.400.08%0.03011.73<-- =BSCall($B$3,$B$4,D8,C8,$B$5) 12.99%<-- =CallVolatility($B$3,$B$4,D8,C8,B8) 17-Nov-122.800.08%0.10683.18<-- =BSCall($B$3,$B$4,D9,C9,$B$5) 14.33%<-- =CallVolatility($B$3,$B$4,D9,C9,B9) 22-Dec-123.930.08%0.20274.34 14.75% 31-Dec-124.060.08%0.22744.59 14.40% 19-Jan-134.740.08%0.27955.08 15.22% 16-Mar-135.880.08%0.43296.30 15.23% 28-Mar-136.250.12%0.46586.55 15.58% 22-Jun-138.370.12%0.70148.02 17.07% 28-Jun-138.530.12%0.71788.11 17.20% 21-Sep-139.550.12%0.95079.33 16.74% 30-Sep-1310.180.12%0.97539.45 17.64% 18-Jan-1411.930.18%1.276710.85 18.01% PRICING THE SPY AT-THE-MONEY CALL OPTIONS Historical and Implied volatility 02468101214Actual Call Price versus Black-Scholes using Historical sMarket priceBS, historical sigma S 127.98current stock price X 127.00exercise price T 0.6329<-- option expires 16-Mar-07, today's date 28-Jul-06 r 5.00%risk-free rate of interest k 1.70%dividend yield Sigma 14%stock volatility 0.3122<-- =(LN(B2/B3)+(B5-B6+0.5*B7^2)*B4)/(B7*SQRT(B4)) 0.2008<-- =B8-B7*SQRT(B4) <--- Uses formula NormSDist(d <--- Uses formula NormSDist(d Call price 7.51 <-- S*Exp(-k*T)*N(d )-X*exp(-r*T)*N(d Put price 3.94<-- call price - S*Exp(-k*T) + X*Exp(-r*T): by Put-Call parity <-- X*exp(-r*T)*N(-d )-S*Exp(-k*T)*N(-d ): direct formula Merton's Dividend-Adjusted Option Pricing Model used here to price S&P 500 Spiders (symbol: SPY) /docProps/thumbnail.jpeg 程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com