Valuation and code for Binomial Lattice
Quant Services: The binomial model is one way an option can be valued, it’s not used anymore as the Black-Scholes model has somewhat taken its place, but, it sets a solid foundation for the quant work you may find yourself doing in whatever type of firm you work in that requires a quant.
The underlying assumption of this method is that the stock price follows a random walk and, in each time step, the stock has a certain probability of moving up or down by a certain percentage amount (Hull & White).
I pulled this image from Wikipedia by the way:
You may have heard this model being referred to as the ‘binomial lattice’ or, the ‘binomial tree’, this is because, if you look at the image above, as you increase the number of steps or nodes, n, the model replicates a tree. The exponent of the root of time is used to calculate the size of the up movement and the down movement, although the down is denoted as a negative, you will see that I have it as 1/u in the formula –in my head this somewhat saves processing power.
Before we can really start coding the model with the loops etc, we have to tell VBA what our inputs are- the information we’ll be working with:
First I dim’d my array’s -the stock projection tree ‘StockTree’ and the tree that will output the option’s value ‘BinomialTree’.
I also set up my inputs and where they are in the sheet (there are more efficient ways to do this by the way), iopt is for the option type (1= European Call, -1= Put), and: We have the Share Price, S, of 100 An Exercise/Strike Price, K, of 95 Sigma/ Volatility, v, of 20% Time to maturity/ Option Life, T, of 0.5 Interest or Risk-free rate, ir, of 8% Dividend yield, q, of 3% Amount of steps/ nodes, n, of 9
I then ReDim the arrays as we don’t want to fix the size of our array because the size of the triangle we’re creating is increasing in size.
In this instance we have a call with 9 steps or nodes and for us to project the price of the underlying asset and be able to calculate the options value, we need the following formulae, with a little theory:
dt (Delta t)
The time to maturity split into x time-steps, or nodes, we have 9 in our case. Therefore:
∆t= (Time to maturity,T)/(Time-steps,n )
u (Up)
We assume that in each period, the underlying assets price can go up by:
u=e^(σ*√∆t)
where: σ (sigma) = volatility, ‘v’ in the code
d (Down)
This is what the underlying asset can go down by, it is therefore:
d=1/u
exprt (Discount Factor)
The discount factor per step:
DF (exprt)= e^(-ir*∆t)
p (Probability) Consider a stock paying a known dividend yield at rate q. The total return from dividends and capital gains in a risk-neutral world is ir. The dividends provide a return of q. Capital gains must therefore provide a return of ir - q. If the stock starts at S0, its expected value after one time step of length ∆t must be S0*e^((ir-q)∆t)
This means that: pS_0 u+(1-p) S_0 d=S_0 e^((ir-q)∆t)
So that p=(e^((ir-q)∆t)-d)/(u-d)
Hull & White: Options, Futures and Other Derivatives
Loops I used nested for loops to create the stock price projection, the upper bound for the counter of the inner for loop is dependent on the step number to create a triangular-shaped tree. I then did the same for calculating the option’s value using the projected prices.
This code has a number of shortcomings:
For starters, I numbered 1 to 9 on the spreadsheet, this numbering should be added to the code using an ‘offset’ function in order to have dynamic number headings for each node (if you change n to anything >9, the code will overwrite the headings). Secondly, the offset function I used needs to also be dynamic in order to know where each tree should start should the number of nodes increase or decrease.
For proprietary's sake, I left a few things out
If you’re up for a task, you can edit this code and show me how you’d improve on it :-)
The code for you to copy and paste into the template (VBA):
Option Explicit Sub Binomial() 'Eurpoean option (exercise at end)
Dim StockTree(), BinomialTree() Dim iopt, S, K, v, T, ir, q, n As Integer Dim dt, u, d, p, expq, exprt, i, j
iopt = Cells(15, 4) 'Define where our inputs are S = Cells(3, 4) K = Cells(4, 4) v = Cells(12, 4) T = Cells(10, 4) ir = Cells(5, 4) q = Cells(7, 4) n = Cells(14, 4)
ReDim StockTree(1 To n + 1, 0 To n) 'We have to redimension the arrays plus one since we're starting from zero ReDim BinomialTree(1 To n + 1, 0 To n)
dt = T / n 'Time to maturity/ number of steps or nodes u = Exp(v * Sqr(dt)) 'Up movement d = 1 / u 'Down movement expq = Exp((ir - q) * dt) exprt = Exp(-ir * dt) 'Discount factor per step p = (expq - d) / (u - d) 'Risk neutral probablity of up movement
StockTree(1, 0) = S 'Initial stock price at time zero Worksheets("Sheet1").Range("a19").Offset(n + 1, 1) = StockTree(1, 0)
'Now we generate our stock price projections - 'The nested For loops used to create the stock price tree. 'The upper bound for the counter of the inner For loop is dependent - 'on the step number to create a triangular-shaped tree.
For j = 1 To n 'Counts steps For i = 1 To j + 1 If i = 1 Then StockTree(i, j) = StockTree(i, j - 1) * d Worksheets("Sheet1").Range("A19").Offset(n + 1, j + 1) = StockTree(i, j) Worksheets("Sheet1").Range("A19").Offset(n + 1, j + 1).NumberFormat = "#,##0.00" Else StockTree(i, j) = StockTree(i - 1, j - 1) * u Worksheets("Sheet1").Range("a19").Offset(n + 1 - i + 1, j + 1) = StockTree(i, j) Worksheets("Sheet1").Range("A19").Offset(n + 1 - i + 1, j + 1).NumberFormat = "#,##0.00" End If Next Next
'Now we generate our option values and discount backwards.
For j = n To 0 Step -1 For i = 1 To j + 1 If j = n Then BinomialTree(i, j) = Application.WorksheetFunction.Max(iopt * (StockTree(i, j) - K), 0) Worksheets("Sheet1").Range("A32").Offset(n + 1 - i + 1, j + 1) = BinomialTree(i, j) Worksheets("Sheet1").Range("A32").Offset(n + 1, j + 1).NumberFormat = "#,##0.00" Else BinomialTree(i, j) = (p * u * BinomialTree(i, j + 1) + (1 - p) * BinomialTree(i + 1, j + 1)) / exprt Worksheets("Sheet1").Range("A32").Offset(n + 1 - i + 1, j + 1) = BinomialTree(i, j) Worksheets("Sheet1").Range("A32").Offset(n + 1 - i + 1, j + 1).NumberFormat = "#,##0.00" End If Next Next
Cells(3, 11).Value = BinomialTree(1, 0) 'Output is the option value at time zero
End Sub
Note that there is no code on the xls file so you'll have to copy and paste the code written above to VBA (Alt+F11).
댓글