Download
Skip this Video
1 / 21
????? ???? - EXCEL PRICING ?? - - PowerPoint PPT Presentation
- 336 Views
- Uploaded on
외환관리사 실무과정 - EXCEL PRICING 실습 -. 제일은행 자금부 과장 신 종 찬 (johnshin@kfb.co.kr). Currency Option Pricing. Black-Scholes Pricing VBA Code. Function EC(S, X, Sday, Mday, vol, r, rf) As Double Dim T As Double Dim d1 As Double Dim d2 As Double T = (Mday - Sday) / 365
Download Presentation 
An Image/Link below is provided (as is) to download presentation
PowerPoint Slideshow about '????? ???? - EXCEL PRICING ?? -' - Patman
An Image/Link below is provided (as is) to download presentation
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.
While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Currency Option Pricing
- Black-Scholes Pricing VBA Code
Function EC(S, X, Sday, Mday, vol, r, rf) As Double
Dim T As Double
Dim d1 As Double
Dim d2 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
d2 = d1 - vol * T ^ 0.5
EC = Exp(-rf * T) * S * Application.NormSDist(d1) - Exp(-r * T) * X * Application.NormSDist(d2)
End Function
Function EP(S, X, Sday, Mday, vol, r, rf) As Double
Dim T As Double
Dim d1 As Double
Dim d2 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
d2 = d1 - vol * T ^ 0.5
EP = Exp(-r * T) * X * Application.NormSDist(-d2) - Exp(-rf * T) * S * Application.NormSDist(-d1)
End Function
Currency Option Pricing
- Binomial European Option Pricing VBA Code
Function Binomial_European(S, X, Sday, Mday, vol, r, rf, Call_Put, N)
Dim St(0 To 200, 0 To 200) As Double
Dim optlet_price(0 To 200, 0 To 200) As Double
tau = (Mday - Sday) / 365
dt = tau / N
u = Exp(vol * Sqr(dt))
d = 1 / u
a = Exp((r - rf) * dt)
b = Exp(-r * dt)
p = (a - d) / (u - d)
For i = 0 To N
For j = 0 To i
St(i, j) = S * u ^ j * d ^ (i - j)
Next j
Next i
For j = 0 To N
If (Call_Put = "Call") Then
optlet_price(N, j) = Application.WorksheetFunction.Max(St(N, j) - X, 0)
Else
optlet_price(N, j) = Application.WorksheetFunction.Max(X - St(N, j), 0)
End If
Next j
For i = N - 1 To 0 Step -1
For j = 0 To i
optlet_price(i, j) = (p * optlet_price(i + 1, j + 1) + (1 - p) * optlet_price(i + 1, j)) * b
Next j
Next i
Binomial_European = optlet_price(0, 0)
End Function
Currency Option Pricing
- Binomial American Option Pricing VBA Code
Function Binomial_American(S, X, Sday, Mday, vol, r, rf, Call_Put, N)
Dim St(0 To 200, 0 To 200) As Double
Dim optlet_price(0 To 200, 0 To 200) As Double
tau = (Mday - Sday) / 365: dt = tau / N
u = Exp(vol * Sqr(dt)): d = 1 / u
a = Exp((r - rf) * dt): b = Exp(-r * dt)
p = (a - d) / (u - d)
For i = 0 To N
For j = 0 To i
St(i, j) = S * u ^ j * d ^ (i - j)
Next j
Next i
For j = 0 To N
If (Call_Put = "Call") Then
optlet_price(N, j) = Application.WorksheetFunction.Max(St(N, j) - X, 0)
Else: optlet_price(N, j) = Application.WorksheetFunction.Max(X - St(N, j), 0)
End If
Next j
For i = N - 1 To 0 Step -1
For j = 0 To i
optlet_price(i, j) = (p * optlet_price(i + 1, j + 1) + (1 - p) * optlet_price(i + 1, j)) * b
If (Call_Put = "Call") Then
optlet_price(i, j) = Application.WorksheetFunction.Max(St(i, j) - X, optlet_price(i, j))
Else: optlet_price(i, j) = Application.WorksheetFunction.Max(X - St(i, j), optlet_price(i, j))
End If
Next j
Next i
Binomial_American = optlet_price(0, 0)
End Function
Currency Option Pricing
- European Option Greeks VBA Code I
Function Delta_Call(S, X, Sday, Mday, vol, r, rf) As Double
Dim T As Double
Dim d1 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
Delta_Call = Exp(-rf * T) * Application.NormSDist(d1)
End Function
Function Delta_Put(S, X, Sday, Mday, vol, r, rf) As Double
Dim T As Double
Dim d1 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
Delta_Put = Exp(-rf * T) * (Application.NormSDist(d1) - 1)
End Function
Function Gamma(S, X, Sday, Mday, vol, r, rf)
Dim T As Double: Dim d1 As Double: Dim N1 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
N1 = (1 / (2 * Application.Pi()) ^ 0.5) * Exp((-d1 ^ 2) / 2)
Gamma = (N1 * Exp(-rf * T)) / (S * vol * T ^ 0.5)
End Function
Currency Option Pricing
- European Option Greeks VBA Code II
Function Vega(S, X, Sday, Mday, vol, r, rf)
Dim T As Double: Dim d1 As Double: Dim N1 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5)
N1 = (1 / (2 * Application.Pi()) ^ 0.5) * Exp((-d1 ^ 2) / 2)
Vega = S * (T ^ 0.5) * N1 * Exp(-rf * T)
End Function
Function Theta_Call(S, X, Sday, Mday, vol, r, rf)
Dim T As Double: Dim d1 As Double: Dim N1 As Double: Dim d2 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5): d2 = d1 - vol * T ^ 0.5
N1 = (1 / (2 * Application.Pi()) ^ 0.5) * Exp((-d1 ^ 2) / 2)
Theta_Call = -(S * N1 * vol * Exp(-rf * T)) / (2 * T ^ 0.5) + rf * S * Application.NormSDist(d1) * Exp(-rf * T) - r * X * Exp(-r * T) * Application.NormSDist(d2)
End Function
Function Theta_Put(S, X, Sday, Mday, vol, r, rf)
Dim T As Double: Dim d1 As Double: Dim d2 As Double: Dim N1 As Double
T = (Mday - Sday) / 365
d1 = (Log(S / X) + (r - rf + vol ^ 2 / 2) * T) / (vol * T ^ 0.5): d2 = d1 - vol * T ^ 0.5
N1 = (1 / (2 * Application.Pi()) ^ 0.5) * Exp((-d1 ^ 2) / 2)
Theta_Put = -(S * N1 * vol * Exp(-rf * T)) / (2 * T ^ 0.5) - rf * S * Application.NormSDist(-d1) * Exp(-rf * T) + r * X * Exp(-r * T) * Application.NormSDist(-d2)
End Function
Currency Option Pricing
- Currency Option Pricing Sample
Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)
- U$ Market Information
- Today : Nov. 16, 2002
- Spot date : Nov. 19, 2002
- Swap day-count : xxx% p.a. Act/360
- Market Rate
Zero Coupon Yield Curve Construction with GBP Swap Rate (s.a.)
- U$ Market Information
- Today : Nov. 16, 2002
- Spot date : Nov. 18, 2002
- Swap daycount : xxx% s.a. Act/365
- Market Rate
Epilogue
- Q&A ?
- Other issues in derivatives pricing with EXCEL ?
- More Information
- E-mail : johnshin@kfb.co.kr
- Call : 02-3702-4412
- Class Material & other files : http://vols.com.ne.kr/fxpractice_mar03.html
