excel pricing l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
외환관리사 실무과정 - EXCEL PRICING 실습 - PowerPoint Presentation
Download Presentation
외환관리사 실무과정 - EXCEL PRICING 실습 -

Loading in 2 Seconds...

share
play prev play next
play fullscreen
1 / 21

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

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

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

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


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

    1. 외환관리사 실무과정- EXCEL PRICING 실습 - 제일은행 자금부 과장 신 종 찬 (johnshin@kfb.co.kr)

    2. 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

    3. 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

    4. 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

    5. 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

    6. 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

    7. Currency Option Pricing • Currency Option Pricing Sample

    8. Structured Currency Option - Sample

    9. 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

    10. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    11. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    12. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    13. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    14. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    15. Zero Coupon Yield Curve Construction with U$ Swap Rate (p.a.)

    16. 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

    17. Zero Coupon Yield Curve Construction with GBP Swap Rate (s.a.)

    18. Swap & Cashflow Manager (Sample)

    19. Swap & Cashflow Manager (Sample)

    20. Swap & Cashflow Manager (Sample)

    21. 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