1 / 21

# 외환관리사 실무과정 - EXCEL PRICING 실습 - - PowerPoint PPT Presentation

외환관리사 실무과정 - EXCEL PRICING 실습 -. 제일은행 자금부 과장 신 종 찬 ([email protected]). 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' 외환관리사 실무과정 - EXCEL PRICING 실습 -' - Patman

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

### 외환관리사 실무과정- EXCEL PRICING 실습 -

제일은행 자금부 과장

신 종 찬 ([email protected])

• 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

• 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

• 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

• 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

• 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 Sample

• U\$ Market Information

• Today : Nov. 16, 2002

• Spot date : Nov. 19, 2002

• Swap day-count : xxx% p.a. Act/360

• Market Rate

• U\$ Market Information

• Today : Nov. 16, 2002

• Spot date : Nov. 18, 2002

• Swap daycount : xxx% s.a. Act/365

• Market Rate

Epilogue (s.a.)

• Q&A ?

• Other issues in derivatives pricing with EXCEL ?