Appendix 18 A Excel VBA Code Binomial Option

Appendix 18 A Excel VBA Code —Binomial Option Pricing Model (18. 4 and 18. 7) By Cheng Few Lee Joseph Finnerty John Lee Alice C Lee Donald Wort

• 2 It is important to note that the thing that makes Microsoft Excel powerful is that it offers a powerful professional programming language called VBA. This section shows the VBA code that generated the Decision Trees for the Binomial Option pricing model. This code is in the form frm. Binomia. Option. The procedure cmd. Calculate_Click is the first procedure to run.

• • • • • 3 '/************************************** '/ Relationship Between the Binomial OPM '/ and Black-Scholes OPM: '/ Decision Tree and Microsoft Excel Approach '/ '/ by John Lee '/ John. Lee. Excel. VBA@gmail. com '/ All Rights Reserved '/************************************** Option Explicit Dim mwb. Tree. Workbook As Workbook Dim mws. Tree. Worksheet As Worksheet Dim mws. Call. Tree As Worksheet Dim mws. Put. Tree As Worksheet Dim mws. Bond. Tree As Worksheet Dim mdbl. PFactor As Double Dim m. Binomial. Calc As Long Dim m. Call. Price As Double 'jcl 12/8/2008 Dim m. Put. Price As Double 'jcl 12/8/2008

• • • • • • 4 '/************************* '/Purpose: Keep track the numbers of binomial calc '/************************* Property Let Binomial. Calc(l As Long) m. Binomial. Calc = l End Property Get Binomial. Calc() As Long Binomial. Calc = m. Binomial. Calc End Property Set Tree. Workbook(wb As Workbook) Set mwb. Tree. Workbook = wb End Property Get Tree. Workbook() As Workbook Set Tree. Workbook = mwb. Tree. Workbook End Property Set Tree. Worksheet(ws As Worksheet) Set mws. Tree. Worksheet = ws End Property Get Tree. Worksheet() As Worksheet Set Tree. Worksheet = mws. Tree. Worksheet End Property

• • • • • 5 Property Set Call. Tree(ws As Worksheet) Set mws. Call. Tree = ws End Property Get Call. Tree() As Worksheet Set Call. Tree = mws. Call. Tree End Property Set Put. Tree(ws As Worksheet) Set mws. Put. Tree = ws End Property Get Put. Tree() As Worksheet Set Put. Tree = mws. Put. Tree End Property Set Bond. Tree(ws As Worksheet) Set mws. Bond. Tree = ws End Property Get Bond. Tree() As Worksheet Set Bond. Tree = mws. Bond. Tree End Property

• • • • • • 6 Property Let Call. Price(d. Call. Price As Double) '12/8/2008 m. Call. Price = d. Call. Price End Property Get Call. Price() As Double Let Call. Price = m. Call. Price End Property Let Put. Price(d. Put. Price As Double) '12/10/2008 m. Put. Price = d. Put. Price End Property Get Put. Price() As Double '12/10/2008 Let Put. Price = m. Put. Price End Property Let PFactor(r As Double) Dim d. Rate As Double d. Rate = ((1 + r) - Me. txt. Binomial. D) / (Me. txt. Binomial. U - Me. txt. Binomial. D) Let mdbl. PFactor = d. Rate End Property

• • • • • 7 Property Get PFactor() As Double Let PFactor = mdbl. PFactor End Property Get q. U() As Double Dim dbl. Delta. T As Double Dim dbl. Down As Double Dim dbl. Up As Double Dim dbl. R As Double dbl. Delta. T = Me. txt. Time. T / Me. txt. Binomial. N dbl. R = Exp(Me. txt. Binomialr * dbl. Delta. T) dbl. Up = Exp(Me. txt. Sigma * VBA. Sqr(dbl. Delta. T)) dbl. Down = Exp(-Me. txt. Sigma * VBA. Sqr(dbl. Delta. T)) q. U = (dbl. R - dbl. Down) / (dbl. R * (dbl. Up - dbl. Down)) End Property

• • • • Property Get q. D() As Double Dim dbl. Delta. T As Double Dim dbl. Down As Double Dim dbl. Up As Double Dim dbl. R As Double dbl. Delta. T = Me. txt. Time. T / Me. txt. Binomial. N dbl. R = Exp(Me. txt. Binomialr * dbl. Delta. T) dbl. Up = Exp(Me. txt. Sigma * VBA. Sqr(dbl. Delta. T)) dbl. Down = Exp(-Me. txt. Sigma * VBA. Sqr(dbl. Delta. T)) q. D = (dbl. Up - dbl. R) / (dbl. R * (dbl. Up - dbl. Down)) End Property • Private Sub chk. Binomial. BSApproximation_Click() On Error Resume Next 'Time and Sigma only Black. Scholes parameter Me. txt. Time. T. Visible = Me. chk. Binomial. BSApproximation Me. lbl. Time. T. Visible = Me. chk. Binomial. BSApproximation Me. txt. Sigma. Visible = Me. chk. Binomial. BSApproximation Me. lbl. Sigma. Visible = Me. chk. Binomial. BSApproximation txt. Time. T_Change End Sub • • • 8

• • • • • • 9 Private Sub cmd. Calculate_Click() Me. Hide Binomial. Option Unload Me End Sub Private Sub cmd. Cancel_Click() Unload Me End Sub Private Sub txt. Binomial. N_Change() 'jcl 12/8/2008 On Error Resume Next If Me. chk. Binomial. BSApproximation Then Me. txt. Binomial. U = Exp(Me. txt. Sigma * Sqr(Me. txt. Time. T / Me. txt. Binomial. N)) Me. txt. Binomial. D = Exp(-Me. txt. Sigma * Sqr(Me. txt. Time. T / Me. txt. Binomial. N)) End If End Sub

• • • 10 Private Sub txt. Time. T_Change() 'jcl 12/8/2008 On Error Resume Next If Me. chk. Binomial. BSApproximation Then Me. txt. Binomial. U = Exp(Me. txt. Sigma * Sqr(Me. txt. Time. T / Me. txt. Binomial. N)) Me. txt. Binomial. D = Exp(-Me. txt. Sigma * Sqr(Me. txt. Time. T / Me. txt. Binomial. N)) End If End Sub Private Sub User. Form_Initialize()

• • • • • 11 With Me . txt. Binomial. S = 20 . txt. Binomial. X = 20 . txt. Binomial. D = 0. 95 . txt. Binomial. U = 1. 05 . txt. Binomial. N = 4 . txt. Binomialr = 0. 03 . txt. Sigma = 0. 2 . txt. Time. T = 4 Me. chk. Binomial. BSApproximation = False End With chk. Binomial. BSApproximation_Click Me. Hide End Sub

• • • • • • 12 Sub Binomial. Option() Dim wb. Tree As Workbook Dim ws. Tree As Worksheet Dim r. Column As Range Dim ws As Worksheet Set Me. Tree. Workbook = Workbooks. Add Set Me. Bond. Tree = Me. Tree. Workbook. Worksheets. Add Set Me. Put. Tree = Me. Tree. Workbook. Worksheets. Add Set Me. Call. Tree = Me. Tree. Workbook. Worksheets. Add Set Me. Tree. Worksheet = Me. Tree. Workbook. Worksheets. Add Set r. Column = Me. Tree. Worksheet. Range("a 1") With Me . Binomial. Calc = 0 . PFactor = Me. txt. Binomialr . Call. Tree. Name = "Call Option Price" . Put. Tree. Name = "Put Option Price" . Tree. Worksheet. Name = "Stock Price" . Bond. Tree. Name = "Bond" End With

• • • • • • 13 Decision. Tree r. Cell: =r. Column, n. Period: =Me. txt. Binomial. N + 1, _ dbl. Price: =Me. txt. Binomial. S, sng. U: =Me. txt. Binomial. U, _ sng. D: =Me. txt. Binomial. D Decition. Tree. Format Tree. Title ws. Tree: =Me. Tree. Worksheet, s. Title: ="Stock Price " Tree. Title ws. Tree: =Me. Call. Tree, s. Title: ="Call Option Pricing" Tree. Title ws. Tree: =Me. Put. Tree, s. Title: ="Put Option Pricing" Tree. Title ws. Tree: =Me. Bond. Tree, s. Title: ="Bond Pricing" Application. Display. Alerts = False For Each ws In Me. Tree. Workbook. Worksheets If Left(ws. Name, 5) = "Sheet" Then ws. Delete Else ws. Activate Active. Window. Display. Gridlines = False ws. Used. Range. Number. Format = "#, ##0. 0000_); (#, ##0. 0000)" End If Next Application. Display. Alerts = True Me. Tree. Worksheet. Activate End Sub

• • • • • 14 Sub Tree. Title(ws. Tree As Worksheet, s. Title As String) ws. Tree. Range("A 1: A 5"). Entire. Row. Insert (xl. Shift. Down) With ws. Tree With. Cells(1) . Value = s. Title . Font. Size = 20 . Font. Italic = True End With With. Cells(2, 1) . Value = "Decision Tree" . Font. Size = 16 . Font. Italic = True End With With. Cells(3, 1) . Value = "Price = " & Me. txt. Binomial. S & _ ", Exercise = " & Me. txt. Binomial. X & _ ", U = " & Format(Me. txt. Binomial. U, "#, ##0. 0000") & _ ", D = " & Format(Me. txt. Binomial. D, "#, ##0. 0000") & _ ", N = " & Me. txt. Binomial. N & _ ", R = " & Me. txt. Binomialr

• • • • • • 15 . Font. Size = 14 End With With. Cells(4, 1) . Value = "Number of calculations: " & Me. Binomial. Calc . Font. Size = 14 End With If ws. Tree Is Me. Call. Tree Then With. Cells(5, 1) . Value = "Binomial Call Price= " & Format(Me. Call. Price, "#, ##0. 0000") . Font. Size = 14 End With If Me. chk. Binomial. BSApproximation Then ws. Tree. Range("A 6: A 7"). Entire. Row. Insert (xl. Shift. Down) With. Cells(6, 1) . Value = "Black-Scholes Call Price= " & Format(Me. BS_Call, "#, ##0. 0000") _ & ", d 1=" & Format(Me. BS_D 1, "#, ##0. 0000") _ & ", d 2=" & Format(Me. BS_D 2, "#, ##0. 0000") _ & ", N(d 1)=" & Format(Worksheet. Function. Norm. SDist(BS_D 1), "#, ##0. 0000") _ & ", N(d 2)=" & Format(Worksheet. Function. Norm. SDist(BS_D 2), "#, ##0. 0000")

• • • • • 16 . Font. Size = 14 End With End If Else. If ws. Tree Is Me. Put. Tree Then With. Cells(5, 1) . Value = "Binomial Put Price: " & Format(Me. Put. Price, "#, ##0. 0000") . Font. Size = 14 End With If Me. chk. Binomial. BSApproximation Then ws. Tree. Range("A 6: A 7"). Entire. Row. Insert (xl. Shift. Down) With. Cells(6, 1) . Value = "Black-Scholes Put Price: " & Format(Me. BS_PUT, "#, ##0. 0000") . Font. Size = 14 End With End If End With End Sub

• • • • • 17 Sub Bond. Decision. Tree(r. Price As Range, ar. Cell As Variant, i. Count As Long) Dim r. Bond As Range Dim r. Pup As Range Dim r. PDown As Range Set r. Bond = Me. Bond. Tree. Cells(r. Price. Row, r. Price. Column) Set r. Pup = Me. Bond. Tree. Cells(ar. Cell(i. Count - 1). Row, ar. Cell(i. Count - 1). Column) Set r. PDown = Me. Bond. Tree. Cells(ar. Cell(i. Count). Row, ar. Cell(i. Count). Column) If r. Pup. Column = Me. Tree. Worksheet. Used. Range. Columns. Count Then r. Pup. Value = (1 + Me. txt. Binomialr) ^ (r. Pup. Column - 1) r. PDown. Value = r. Pup. Value End If With r. Bond . Value = (1 + Me. txt. Binomialr) ^ (r. Bond. Column - 1) . Borders(xl. Bottom). Line. Style = xl. Continuous End With

• • • • • • 18 r. PDown. Borders(xl. Bottom). Line. Style = xl. Continuous With r. Pup . Borders(xl. Bottom). Line. Style = xl. Continuous . Offset(1, 0). Resize((r. PDown. Row - r. Pup. Row), 1). _ Borders(xl. Edge. Left). Line. Style = xl. Continuous End With End Sub Put. Decision. Tree(r. Price As Range, ar. Cell As Variant, i. Count As Long) Dim r. Call As Range Dim r. Pup As Range Dim r. PDown As Range Set r. Call = Me. Put. Tree. Cells(r. Price. Row, r. Price. Column) Set r. Pup = Me. Put. Tree. Cells(ar. Cell(i. Count - 1). Row, ar. Cell(i. Count - 1). Column) Set r. PDown = Me. Put. Tree. Cells(ar. Cell(i. Count). Row, ar. Cell(i. Count). Column) If r. Pup. Column = Me. Tree. Worksheet. Used. Range. Columns. Count Then r. Pup. Value = Worksheet. Function. Max(Me. txt. Binomial. X - ar. Cell(i. Count - 1), 0) r. PDown. Value = Worksheet. Function. Max(Me. txt. Binomial. X - ar. Cell(i. Count), 0) End If

• • • • • • 19 With r. Call '12/10/2008 If Not Me. chk. Binomial. BSApproximation Then . Value = (Me. PFactor * r. Pup + (1 - Me. PFactor) * r. PDown) / (1 + Me. txt. Binomialr) Else . Value = (Me. q. U * r. Pup) + (Me. q. D * r. PDown) End If Me. Put. Price =. Value '12/8/2008 . Borders(xl. Bottom). Line. Style = xl. Continuous End With r. PDown. Borders(xl. Bottom). Line. Style = xl. Continuous With r. Pup . Borders(xl. Bottom). Line. Style = xl. Continuous . Offset(1, 0). Resize((r. PDown. Row - r. Pup. Row), 1). _ Borders(xl. Edge. Left). Line. Style = xl. Continuous End With End Sub

• • • • • 20 Sub Call. Decision. Tree(r. Price As Range, ar. Cell As Variant, i. Count As Long) Dim r. Call As Range Dim r. Cup As Range Dim r. CDown As Range Set r. Call = Me. Call. Tree. Cells(r. Price. Row, r. Price. Column) Set r. Cup = Me. Call. Tree. Cells(ar. Cell(i. Count - 1). Row, ar. Cell(i. Count - 1). Column) Set r. CDown = Me. Call. Tree. Cells(ar. Cell(i. Count). Row, ar. Cell(i. Count). Column) If r. Cup. Column = Me. Tree. Worksheet. Used. Range. Columns. Count Then With r. Cup . Value = Worksheet. Function. Max(ar. Cell(i. Count - 1) - Me. txt. Binomial. X, 0) . Borders(xl. Bottom). Line. Style = xl. Continuous End With r. CDown . Value = Worksheet. Function. Max(ar. Cell(i. Count) - Me. txt. Binomial. X, 0) . Borders(xl. Bottom). Line. Style = xl. Continuous End With End If

• • • • • 21 With r. Call If Not Me. chk. Binomial. BSApproximation Then . Value = (Me. PFactor * r. Cup + (1 - Me. PFactor) * r. CDown) / (1 + Me. txt. Binomialr) Else . Value = (Me. q. U * r. Cup) + (Me. q. D * r. CDown) End If Me. Call. Price =. Value '12/8/2008 . Borders(xl. Bottom). Line. Style = xl. Continuous End With r. Cup. Offset(1, 0). Resize((r. CDown. Row - r. Cup. Row), 1). _ Borders(xl. Edge. Left). Line. Style = xl. Continuous End Sub

• • • • • 22 Sub Decition. Tree. Format() Dim r. Tree As Range Dim n. Columns As Integer Dim r. Last As Range Dim r. Cell As Range Dim l. Count As Long Dim l. Cell. Size As Long Dim vnt. Column As Variant Dim i. Count As Long Dim l. Times As Long Dim ar. Cell() As Range Dim s. Format. Column As String Dim r. Price As Range Application. Status. Bar = "Formatting Tree. . " Set r. Tree = Me. Tree. Worksheet. Used. Range n. Columns = r. Tree. Columns. Count

• • • • • 23 Set r. Last = r. Tree. Columns(n. Columns). Entire. Column. Special. Cells(xl. Cell. Type. Constants, 23) l. Cell. Size = r. Last. Cells. Count For l. Count = n. Columns To 2 Step -1 s. Format. Column = r. Last. Parent. Columns(l. Count). Entire. Column. Address Application. Status. Bar = "Formatting column " & s. Format. Column Re. Dim vnt. Column(1 To (r. Last. Cells. Count / 2), 1) Application. Status. Bar = "Assigning values to array for column " & _ r. Last. Parent. Columns(l. Count). Entire. Column. Address vnt. Column = r. Last. Offset(0, -1). Entire. Column. Cells(1). Resize(r. Last. Cells. Count / 2, 1) r. Last. Offset(0, -1). Entire. Column. Clear. Contents Re. Dim ar. Cell(1 To r. Last. Cells. Count) l. Times = 1 Application. Status. Bar = "Assigning cells to arrays. Total number of cells: " & l. Cell. Size For Each r. Cell In r. Last. Cells Application. Status. Bar = "Array to column " & s. Format. Column & " Cells " & r. Cell. Row Set ar. Cell(l. Times) = r. Cell l. Times = l. Times + 1

• • • • 24 Next l. Times = 1 Application. Status. Bar = "Formatting leaves for column " & s. Format. Column For i. Count = 2 To l. Cell. Size Step 2 Application. Status. Bar = "Formatting leaves for cell " & ar. Cell(i. Count). Address If r. Last. Cells. Count <> 2 Then Set r. Price = ar. Cell(i. Count). Offset(-1 * ((ar. Cell(i. Count). Row - ar. Cell(i. Count - 1). Row) / 2), -1) r. Price. Value = vnt. Column(l. Times, 1) Else Set r. Price = ar. Cell(i. Count). Offset(-1 * ((ar. Cell(i. Count). Row - ar. Cell(i. Count - 1). Row) / 2), -1) r. Price. Value = vnt. Column End If

• • • • • 25 ar. Cell(i. Count). Borders(xl. Bottom). Line. Style = xl. Continuous With ar. Cell(i. Count - 1) . Borders(xl. Bottom). Line. Style = xl. Continuous . Offset(1, 0). Resize((ar. Cell(i. Count). Row - ar. Cell(i. Count - 1). Row), 1). _ Borders(xl. Edge. Left). Line. Style = xl. Continuous End With l. Times = 1 + l. Times Call. Decision. Tree r. Price: =r. Price, ar. Cell: =ar. Cell, i. Count: =i. Count Put. Decision. Tree r. Price: =r. Price, ar. Cell: =ar. Cell, i. Count: =i. Count Bond. Decision. Tree r. Price: =r. Price, ar. Cell: =ar. Cell, i. Count: =i. Count Next Set r. Last = r. Tree. Columns(l. Count - 1). Entire. Column. Special. Cells(xl. Cell. Type. Constants, 23) l. Cell. Size = r. Last. Cells. Count Next ' / outer next r. Last. Borders(xl. Bottom). Line. Style = xl. Continuous Application. Status. Bar = False End Sub

• • • • • 26 '/*********************************** '/Purpse: To calculate the price value of every state of the binomial '/ decision tree '/*********************************** Sub Decision. Tree(r. Cell As Range, n. Period As Integer, _ dbl. Price As Double, sng. U As Single, sng. D As Single) Dim l. Itemin. Column As Long If Not n. Period = 1 Then 'Do Up Decision. Tree r. Cell: =r. Cell. Offset(0, 1), n. Period: =n. Period - 1, _ dbl. Price: =dbl. Price * sng. U, sng. U: =sng. U, _ sng. D: =sng. D 'Do Down Decision. Tree r. Cell: =r. Cell. Offset(0, 1), n. Period: =n. Period - 1, _ dbl. Price: =dbl. Price * sng. D, sng. U: =sng. U, _ sng. D: =sng. D End If

• • • • • 27 l. Itemin. Column = Worksheet. Function. Count. A(r. Cell. Entire. Column) If l. Itemin. Column = 0 Then r. Cell = dbl. Price Else If n. Period <> 1 Then r. Cell. Entire. Column. Cells(l. Itemin. Column + 1) = dbl. Price Else r. Cell. Entire. Column. Cells(((l. Itemin. Column + 1) * 2) - 1) = dbl. Price Application. Status. Bar = "The number of binomial calcs are : " & Me. Binomial. Calc _ & " at cell " & r. Cell. Entire. Column. Cells(((l. Itemin. Column + 1) * 2) - 1). Address End If Me. Binomial. Calc = Me. Binomial. Calc + 1 End Sub

• • • • • 28 Function BS_D 1() As Double Dim dbl. Numerator As Double Dim dbl. Denominator As Double On Error Resume Next dbl. Numerator = VBA. Log(Me. txt. Binomial. S / Me. txt. Binomial. X) + _ ((Me. txt. Binomialr + Me. txt. Sigma ^ 2 / 2) * Me. txt. Time. T) dbl. Denominator = Me. txt. Sigma * Sqr(Me. txt. Time. T) BS_D 1 = dbl. Numerator / dbl. Denominator End Function BS_D 2() As Double On Error Resume Next BS_D 2 = BS_D 1 - (Me. txt. Sigma * VBA. Sqr(Me. txt. Time. T)) End Function

• • • • 29 Function BS_Call() As Double BS_Call = (Me. txt. Binomial. S * Worksheet. Function. Norm. SDist(BS_D 1)) _ - Me. txt. Binomial. X * Exp(-Me. txt. Binomialr * Me. txt. Time. T) * _ Worksheet. Function. Norm. SDist(BS_D 2) End Function 'Used put-call parity theorem to price put option Function BS_PUT() As Double BS_PUT = BS_Call - Me. txt. Binomial. S + _ (Me. txt. Binomial. X * Exp(-Me. txt. Binomialr * Me. txt. Time. T)) End Function