I II II III III
I VBA Understanding Excel VBA - 'VB & VBA In a Nutshell' by Paul Lomax, October,1998
To enter code: Tools/Macro/visual basic editor At editor: Insert/Module Type code, then compile by: debug/compile VBAproject
Sub..End sub / Function.. End fuction
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 ( : -32768 ~ 32767 ) 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
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
For-Next : For = To [Step ] [ ] [Exit For] [ ] Next [ ] Do- While -Loop : Do while Loop [ 1] [ 2] Do-Untill-Loop : Do Untill Loop [ 1] [ 2]
II Understanding option pricing model - The pricing of option and coporate liabilities' by Black& Sholes, May,1973
(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
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
- (Plain Vanilla Excell) S X r T σ d1 d2 N(d1) N(d2) Call price Put price 64.64 70 0.0704 0.0767 0.5202 1.35 <-- =(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) -2.77 <-- = 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) : -
- (VBA) Call/Put c c=, p= S 64.64 X 70 r 0.0704 T 0.0767 σ 0.5202 1.86 '// - (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
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.
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
(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
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
(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) : / (%) => *, * = * = + *,
(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
105.00 102.00 6.82% 4.22% 0.082192 c 105.00 103.00 6.82% 4.22% 0.082192 c (1y=365d) 1 day 3.0511 92.90% 0.0636 0.0392-0.004069-0.0025 2.1672 83.68% 0.1182 0.0729-0.008057-0.0018 Black Model(1976) - (option on futures) (cap,floor, swaption) - (forward) (futures) - - black sholes
'// 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 = 0.31938153: Const a2 = -0.356563782: Const a3 = 1.781477937: Const a4 = -1.821255978: Const a5 = 1.330274429 L = Abs(X) K = 1 / (1 + 0.2316419 * 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
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