歯엑셀모델링

Size: px

Start display at page:

Download "歯엑셀모델링"

지인 동
6 years ago
Views:

2 I II II III III

3 I VBA Understanding Excel VBA - 'VB & VBA In a Nutshell' by Paul Lomax, October,1998

5 To enter code: Tools/Macro/visual basic editor At editor: Insert/Module Type code, then compile by: debug/compile VBAproject

7 Sub..End sub / Function.. End fuction

8 Public Function Black(CallPutFlag As String, F As Double, X _ As Double, T As Double, r As Double, v As Double) As Double (Variable declarations) : VBA [VBA ] string ( : For names like Bob ) Integer ( : ~ ) Long ( : interger ) Boolean ( : True, False ) Single ( : ) Double ( : single ) Currency ( : monetary values) Variant ( : ) Dim d1 As Double, d2 As Double d1 = (Log(F / X) + (v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) Dim : d1d2. Dim : Dim var As datatype var datatype

9 If - Then : If [ ] [ ] [] then End If (true) If Then Else : If [ ] [ ] [] then else (false) End If If CallPutFlag = c Then Black = exp(-r*t)*(f*cnd(d1) X* CND(d2) ElseIf CallPutFlag = p Then Black = Exp(-r*T)*(X*CND(-d2) F*CND(-d1) End If Select.. Case : select case case n case Else End Select

10 For-Next : For = To [Step ] [ ] [Exit For] [ ] Next [ ] Do- While -Loop : Do while Loop [ 1] [ 2] Do-Untill-Loop : Do Untill Loop [ 1] [ 2]

11 II Understanding option pricing model - The pricing of option and coporate liabilities' by Black& Sholes, May,1973

12 (Option Theory) : (KOSPI200 ) - (Binomial) : (KOSPI200 / ) - (Black model) : (,,, ) - - (Garman-Kollagen) : (/ ) put-call parity : C + Xe -rt + D = S + p put-call parity : C + Xe -rt + D >S + P

13 c = S 0 p = X N ( d e rt 1 ) X N ( d 2 e rt ) S N ( d 0 2 N ( d ln( S / X ) + ( r + σ 2 / 2) T 0 where d = 1 σ T ln( S / X ) + ( r σ 2 / 2) T 0 d = = 2 σ T ) 1 ) d 1 σ T Black Sholes Model(1973) - Log normal price distrbution: : no tax, no transaction cost, short sale

14 - (Plain Vanilla Excell) S X r T σ d1 d2 N(d1) N(d2) Call price Put price <-- =(LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T)) 1.20 <-- = d1-sigma*sqrt(t) 0.91 <-- NormsDist, = NormsDist(d1) 0.88 <-- NormsDist, = NormsDist(d2) <-- = S*N(d1)-X*exp(-r*T)*N(d2) 2.21 <-- = X*exp(-r*T)*N(-d2)-S*N(-D1) : 2.21 <-- = call price-s+x*exp(-r*t) : -

15 - (VBA) Call/Put c c=, p= S X 70 r T σ '// - (1973) Public Function BlackScholes(CallPutFlag As String, S As Double, X _ As Double, r As Double, T As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then BlackScholes = S * Application.NormSDist(d1) - X * Exp(-r * T) * Application.NormSDist(d2) ElseIf CallPutFlag = "p" Then BlackScholes = X * Exp(-r * T) * Application.NormSDist(-d2) - S * Application.NormSDist(-d1) End If End Function

16 Visual Basic '// - (1973) 2. Public Function BlackScholes(CallPutFlag As String, S As Double, X _ As Double, r As Double, T As Double, v As Double) As Double 3. Dim d1 As Double, d2 As Double. d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) 4. If CallPutFlag = "c" Then BlackScholes = S * Application.NormSDist(d1) - X * Exp(-r * T) * Application.NormSDist(d2) ElseIf CallPutFlag = "p" Then BlackScholes = X * Exp(-r * T) * Application.NormSDist(-d2) - S * Application.NormSDist(-d1) End If 5.End Function Visual Basic ( ) 1. ( ) 2. BlackScholes : String, Doble ( ) 3. Dim d1d2 4. If : If- Then- Elseif Application.NormSDist => NormSDistVBA Appplication. * : : normdist, : normsdist CND : (Normsdist ) 5.

17 D) = call(put) sensitivity to asset value C = S N(d 1 ) - KB(0,t) N(d 2 ) c rt call = = e Nd ( 1) f 0 S *N(d 1 ) = (D) = (hedge ratio) = p rt put = = e [ Nd ( 1) 1] p 0 = : 1 S * = '// _ Public Function Delta(CallPutFlag As String, S As Double, X As Double, T As Double, _ r As Double, v As Double) As Double Dim d1 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) If CallPutFlag = "c" Then GDelta = Exp((-r) * T) * Application.NormSDist(d1) ElseIf CallPutFlag = "p" Then GDelta = Exp((-r) * T) * (Application.NormSDist(d1) - 1) End If End Function

18 (G) =delta sensitivity to asset value changes 2 2 c p n( d 1 ) e Γ call, put = = = 2 2 S S Sσ T rt f 0 : (convexity), / * : * = * :, *Call, Put Long Position: Long gamma(positive gamma) Short position : Short gamma(negative gamma) '// _ Public Function Gamma(S As Double, X As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) GGamma = Exp((-r) * T) * ND(d1) / (S * v * Sqr(T)) End Function

19 theta) = call(put) sensitivity to time changes Θ Θ call put c = T p = T rt Se n( d ) σ 1 = + rse 2 T rt Se n( d ) σ 1 = + rse 2 T N( d 1 N( d ) rxe 1 rt ) rxe * = (theta) rt rt N( d rt 2 N( d ) 0 2 ) 0 '// _ Public Function Theta(CallPutFlag As String, S As Double, X As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then GTheta = -S * Exp((-r) * T) * Application.NormSDist(d1) * v / (2 * Sqr(T)) - (-r) * S * Exp((-r) * T) * Application.NormSDist(d1) - r * X * Exp(-r * T) * Application.NormSDist(d2) ElseIf CallPutFlag = "p" Then GTheta = -S * Exp((-r) * T) * Application.NormSDist(d1) * v / (2 * Sqr(T)) + (-r) * S * Exp((-r) * T) * Application.NormSDist(-d1) + r * X * Exp(-r * T) * Application.NormSDist(-d2) End If End Function

20 (vega) = call(put) sensitivity to volatility changes κ call c p rt, = = = Se n( d 1 ) T 0 put σ σ '// _ Public Function Vega(S As Double, X As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) Vega = S * Exp((-r) * T) * ND(d1) * Sqr(T) End Function ( kappa) : / (%) => *, * = * = + *,

21 (rho) = call(put) sensitivity to riskless rate changes ρ ρ call call = = c r p r = = XTe rt XTe N ( d rt 2 N ( d ) 0 2 ) 0 : / * '// _ Public Function Rho(CallPutFlag As String, S As Double, X As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then Rho = X * T * Exp(-r * T) * Application.NormSDist(d2) ElseIf CallPutFlag = "p" Then Rho = -X * T * Exp(-r * T) * Application.NormSDist(-d2) End If End Function

22 % 4.22% c % 4.22% c (1y=365d) 1 day % % Black Model(1976) - (option on futures) (cap,floor, swaption) - (forward) (futures) - - black sholes

23 '// Public Function ND(X As Double) As Double ND = 1 / Sqr(2 * Pi) * Exp(-X ^ 2 / 2) End Function '// Public Function CND(X As Double) As Double Dim L As Double, K As Double Const a1 = : Const a2 = : Const a3 = : Const a4 = : Const a5 = L = Abs(X) K = 1 / ( * L) CND = 1-1 / Sqr(2 * Pi) * Exp(-L ^ 2 / 2) * (a1 * K + a2 * K ^ 2 + a3 * K ^ 3 + a4 * K ^ 4 + a5 * K ^ 5) If X < 0 Then CND = 1 - CND End If End Function '// (1976): Public Function Black(CallPutFlag As String, F As Double, X _ As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(F / X) + (v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then Black = Exp(-r * T) * (F * CND(d1) - X * CND(d2)) ElseIf CallPutFlag = "p" Then Black = Exp(-r * T) * (X * CND(-d2) - F * CND(-d1)) End If End Function

Financial Modeling, Simon Benninga, 2000

24 Paul Wilmott on Quantitative Finance, Paul Willmott, Wiely, 2000 The Complete Guide to Option Pricing Formulas,Haug, 1998, McGrawHill Implementing Derivatives models,clewlow&strickla nd, 1998 VBA for Modelers: Developing Decision Support Systems Using Microsoftr Excel, Albright, 2001 Financial Modeling, Simon Benninga, 2000 Advanced Modelling in Finance: Using Excel and VBA, Jackson& Staunton, 2001

y 0.5 9, 644 e = 10, y = ln = 3.6(%) , May. 20, 2005

y 0.5 9, 644 e = 10, y = ln = 3.6(%) , May. 20, 2005 8 116, May. 20, 2005 y 0.5 9, 644 e = 10, 000 1 9644 y = ln = 3.6(%) 0.5 10000 9 116, May. 20, 2005 y 0.5 9, 644 e = 10, 000 1 9644 y = ln = 3.6(%) 0.5 10000 1 FV r T = ln T PV 10 116, May. 20, 2005 Public

歯엑셀모델링

歯엑셀모델링