This is what I have done so far. It contains the basic codes for BS Merton, CRR European and American Options. It also includes the greeks, delta, gamma and theta. I coded it the way I understood options. In a visual way. Though it's not yet tested for other numbers.
Sub Test() just contains the input and output of what is happening on the code since I'm just halfway done. The "user" part and the "output" part isn't completed yet. It is just presentable.
GenEurAm() contains the code in solving the option value and generating the values involved in the tree for Amerucan and European options.
Greeks() computes for the greeks and has an output table of the greeks.
GenBS() is the Black-scholes Merton way for computing european options
DrawTree() generates a designed tree complete with asset price and option prices. Though it is not as efficient as I would like. I computed for 100 steps and it took 9 secs to generate the tree alone.
DrawTree2() generates a tree in a more efficient manner. I have a choice whether what tree to generate. Either the price tree or the option value tree.
The functions are just a way to make the code flexible and more user-friendly. Though I don't know what to do with them right now at my codes..maybe later it will be handy.
Will do the user part next. and then the output sheets.
New to the code:
- Learned: Declaring variables outside the sub or function
- ActiveCell.Offset(0, 0).Value
- Range(starter).Offset(0, 0).Value
- On properties of the range
- With ... End With
- .Interior.ColorIndex = 34
- .Borders.Weight = xlThin
- .HorizontalAlignment = xlCenter
- .Value = OptionValue(lp, j)
Public OptionValue() As Double
Public Price() As Double
Public bsOV As Double
Public kind As String
Public z As Integer
Public So As Double
Public K As Double
Public q As Double
Public r As Double
Public T As Double
Public n As Integer
Sub Test()
So = 100
K = 95
q = 0
r = 0.08
vol = 0.3
T = 0.5
n = 5
'kind = "a" 'american = a, european = e
z = -1 'for call z= -1, if for put z= -1
starter = "C11"
Range(starter).Offset(0, 0).Value = "European:BS"
Range(starter).Offset(0, 1).Value = GenBS(z, So, K, q, r, vol, T, n)
Call GenEurAm("e", z, So, K, q, r, vol, T, n)
Range(starter).Offset(1, 0).Value = "European:CRR"
Range(starter).Offset(1, 1).Value = OptionValue(0, 0)
Range(starter).Offset(3, 0).Value = "Asset Prices:"
Range(starter).Offset(4, 1).Select
Call DrawTree2(n, Price)
Range(starter).Offset(n + 5, 0) = "European Option Prices:"
Range(starter).Offset(n + 6, 1).Select
Call DrawTree2(n, OptionValue)
Call GenEurAm("a", z, So, K, q, r, vol, T, n)
Range(starter).Offset(1, 3).Value = "American:CRR"
Range(starter).Offset(1, 4).Value = OptionValue(0, 0)
Range(starter).Offset(2 * n + 7, 0) = "American Option Prices:"
Range(starter).Offset(2 * n + 8, 1).Select
Call DrawTree2(n, OptionValue)
Range(starter).Offset(3 * n + 9, 0).Value = "Greeks:"
Range(starter).Offset(3 * n + 10, 1).Select
Call Greeks
End Sub
Private Sub GenEurAm(kind, z, So, K, q, r, vol, T, n)
'computation of the formulas
u = Exp(vol * Sqr(T / n))
d = 1 / u
b = Exp((r - q) * T / n)
p = (b - d) / (u - d)
df = Exp(r * T / n)
'Price tree
ReDim Price(0 To n, 0 To n)
'American and European Option tree
ReDim OptionValue(0 To n, 0 To n)
For j = n To 0 Step -1
For lp = 0 To j
'Price tree
Price(lp, j) = So * u ^ lp * d ^ (j - lp)
whenX = z * (Price(lp, j) - K)
'option tree
'at the end nodes
If j = n Then
OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, 0)
'at other nodes
Else
whenNotX = (p * OptionValue(lp + 1, j + 1) + (1 - p) * OptionValue(lp, j + 1)) / df
'for american option
If kind = "a" Then
OptionValue(lp, j) = Application.WorksheetFunction.Max(whenX, whenNotX)
'for european option
ElseIf kind = "e" Then
OptionValue(lp, j) = whenNotX
End If
End If
Next lp
Next j
End Sub
Private Sub Greeks()
'after calculating the prices and the option values
'brute force
Dim del(0 To 2) As Double
Dim gam As Double
Dim the As Double
del(0) = (OptionValue(0, 1) - OptionValue(1, 1)) / (Price(0, 1) - Price(1, 1))
del(1) = (OptionValue(0, 2) - OptionValue(1, 2)) / (Price(0, 2) - Price(1, 2))
del(2) = (OptionValue(1, 2) - OptionValue(2, 2)) / (Price(1, 2) - Price(2, 2))
gamma = (del(1) - del(2)) / (Price(0, 1) - Price(1, 1))
theta = (OptionValue(1, 2) - OptionValue(0, 0)) / (2 * T / n)
ActiveCell.Offset(0, 0).Value = "Delta"
ActiveCell.Offset(0, 1).Value = del(0)
ActiveCell.Offset(1, 0).Value = "Gamma"
ActiveCell.Offset(1, 1).Value = gamma
ActiveCell.Offset(2, 0).Value = "Theta"
ActiveCell.Offset(2, 1).Value = theta
End Sub
Private Function GenBS(z, So, K, q, r, vol, T, n)
'Black Scholes Merton
'defining the parts
d1 = Application.WorksheetFunction.Ln(So / K) + (r - q + vol ^ 2 / 2) * T / (vol * Sqr(T))
d2 = Application.WorksheetFunction.Ln(So / K) + (r - q - vol ^ 2 / 2) * T / (vol * Sqr(T))
If z = 1 Then
nd1 = Application.WorksheetFunction.NormSDist(d1)
nd2 = Application.WorksheetFunction.NormSDist(d2)
ElseIf z = -1 Then
nd1 = Application.WorksheetFunction.NormSDist(-d1)
nd2 = Application.WorksheetFunction.NormSDist(-d2)
End If
'getting black scholes
GenBS = z * (So * Exp(-q * T) * nd1 - K * Exp(-r * T) * nd2)
End Function
Private Sub DrawTree(n, starter)
'with design, not efficient
r = 2 * (2 * n + 1)
For j = 0 To n
nrow = r / 2 - 2 * j
For lp = j To 0 Step -1
With Range(starter).Offset(nrow, j)
.Interior.ColorIndex = 42
.Borders.Weight = xlThin
.HorizontalAlignment = xlCenter
.Value = Price(lp, j)
End With
With Range(starter).Offset(nrow + 1, j)
.Interior.ColorIndex = 34
.Borders.Weight = xlThin
.HorizontalAlignment = xlCenter
.Value = OptionValue(lp, j)
End With
nrow = nrow + 4
Next lp
Next j
End Sub
Private Sub DrawTree2(n, ranger)
'after calculating for the prices and the optionvalue
'simple lng
For col = n To 0 Step -1
For nrow = n - col To n
ActiveCell.Offset(nrow, col).Value = ranger(n - nrow, col)
Next nrow
Next col
End Sub
Public Function EuroCall(So, K, q, r, vol, T, n)
Call GenEurAm("e", "1", So, K, q, r, vol, T, n)
EuroCall = OptionValue(0, 0)
End Function
Public Function EuroPut(So, K, q, r, vol, T, n)
Call GenEurAm("e", "-1", So, K, q, r, vol, T, n)
EuroPut = OptionValue(0, 0)
End Function
Public Function AmCall(So, K, q, r, vol, T, n)
Call GenEurAm("a", "1", So, K, q, r, vol, T, n)
AmCall = OptionValue(0, 0)
End Function
Public Function AmPut(So, K, q, r, vol, T, n)
Call GenEurAm("a", "-1", So, K, q, r, vol, T, n)
AmPut = OptionValue(0, 0)
End Function
Public Function BSCall(So, K, q, r, vol, T, n)
BSCall = GenBS("1", So, K, q, r, vol, T, n)
End Function
Public Function BSPut(So, K, q, r, vol, T, n)
BSPut = GenBS("-1", So, K, q, r, vol, T, n)
End Function
Sources:
http://www.cpearson.com/excel/classes.aspx
http://dmcritchie.mvps.org/excel/colors.htm
http://www.global-derivatives.com/index.php/component/content/52?task=view
Black, F. & Scholes, M. "The Pricing of Options & Corporate Liabilities" The Journal of Political Economy (May '73)
Hull, J. "Options, Futures & Other Derivatives" 5th Edition 2002 - Chapter 12
Merton, R. "Theory of Rational Option Pricing" Bell Journal of Economics & Management (June '73)
Mathlab Example, European or American options
- http://www.goddardconsulting.ca/matlab-binomial-crr.html
American Call Option, Greeks, Python, getting data from google finance, etc., Concepts and formulas included
- http://www.quantandfinancial.com/2012/11/cox-ross-rubinstein-option-pricing-model.html
Option Pricing Formulas book, Complete with greeks and American Options p279-288
- http://r2-d2.bu.edu/AT__The_Complete_Guide_to_Option_Pricing_Formulas__2nd_ed_.pdf
Option Calculator
- http://www.volatilitytrading.net/cox_ross_rubenstein_calculator.htm
No comments:
Post a Comment