This post was initially going to review some of the supposed skew existing over VIX’s implied volatility curves (see: Tricky Vixy). However, a small roadblock was crossed along the way. Unfortunately Excel 2008 for Mac is without any VBA macro capabilities. This required a little bit of improvisation in order to come through with a suitable financial modeling solution.
The result is a rough applescript dev for a trinomial option pricing tree script, executable under excel, with a fully referenced cell layering and editable trinomial option price lattice allowing additional manual modeling, if so inclined.
The Trinomial Options Pricing Model
The model applied here, proposed by Boyle (1986), is a stepup from the Cox, Ross & Rubinstein (1979) binomial options pricing tree with three distinct possibilities per node: either up, down or stable. The model uses a trinomial price distribution function but still follows the broad Black & Scholes (1973) assumption of a lognormal distribution of the underlying’s asset price. The builtup lattice does allow you to modify some of these assumptions with manual applications of rational bounds and prices boundaries across the pricing tree.
CRR’s binomial and Boyle’s trinomial option pricing models are two models for fairvaluation of American and Bermudan styled options. These are either liberally or discretely exercisable and therefore require a finite payoffs analysis (the lattice model) for fairvalue estimation of the contract.
The Black & Scholes model provides a continuous price distribution model that is formally only applicable to Europeanstyled options (exercised solely at expiration).
Both the binomial, at highiterations, and trinomial option pricing models, at relatively lower iteration count, will converge back on a Black & Scholes valuation of European options, illustrating the equivalence of the underlying pricing assumptions.
The Equations: Very Quickly
You can find out more on the specifics of these equations over in most finance textbooks. Essentially, Boyle’s trinomial model is based on Cox, Ross and Rubinstein’s own binomial model and follows through on the same assumptions with the previously mentioned stablebranch improvement.
Price distribution is considered to be recombinant across time periods (eg: p at t0 = price up at t1 and price down at t2) as the following three equations demonstrate.
Once the pricing distribution tree is constructed, the options are then priced back recursively from the expiry nodes, bringing back to t0 a fairvalue estimate over the expected weighted and discounted future possible option prices.
Prices at expiry nodes are the greater of expiry, zero, or intrinsic value: for a call, underlying price minus strike, and for a put, strike minus underlying price.
The expected price point for any point prior is therefore weighted across from the probability equations set out below providing a fairvalue estimate of the option’s price at that specific node. This process is conducted until the initial node is reached once more producing a theoretical fair value of the option at time t0.
Graphical Demonstration
This process is perhaps best understood when visualising an actual tree. I’ve attached a properly formatted tree below for your consideration (the applescript is so far nude in terms of formatting).
Applescript Implementation
This is still early stages, however, initial tests across the lattice have matched up on a onetoone basis so far compared to manual constructions and valuations from 3rd party solutions. The script is far from optimised just yet and is pretty much just raw code all dumped together. I’ll try and split this out in routines over the course of this week for a more userfriendly read. Also, the output is fairly raw, no fancy formatting just yet.
Once the initial tree is built, all cells should be directly referenced allowing customised analysis to take place. Simply change the core parameters in the topleft of the sheet and the values should dynamically refresh themselves throughout.
Again, technically this script should no longer be necessary for those mac users who have already updated to Excel 2011. The latest version now reenabled VBA macros and you should therefore be able to either construct your own or adapt other macros out there to your convenience. For those still on Excel 2008, then this is is certainly the only solution to date beyond a manual lattice construction,
Technically, this script does provide a quick alternative to VBA and, all other things considered, could potentially be useful for other applications (eg ports into Xcode et al.). I might further develop the code into something a bit more practical if time permits. NB: remember that Applescript is a bit touchy about new lines versus return carriage, something worth checking if you copy paste from below.
In order to run the code, simply load it in applescript and launch while having a blank spreadsheet running. Ideally, try and save it in the Excel applescript folder for a smoother launch. If all goes well, you should get a result as per the following screen shot.
 Attachments
 Trinomial Option Pricing Tree Applescript
 Black, F. and Scholes, M. (1973), ‘‘The pricing of options and corporate liabilities’’, Journal of Political Economy, Vol. 81, pp. 63759.
 Boyle, P.P. (1986), ‘‘Option valuation using a threejump process’’, International Options Journal, Vol. 3, pp. 712.
 Clifford, P., Zaboronski, O., “Pricing Options Using Trinomial Trees”, 17/11/2008, Warwick University.
 Cox, J.C., Ross, S. and Rubinstein, M. (1979), ‘‘Option pricing: a simplified approach’’, Journal of Financial Economics, Vol. 7, pp. 22964.
 Direct Line Source Code Available Below
Trinomial Option Pricing Tree Model Source Code

tell application “Microsoft Excel”
activate worksheet “Sheet1″
set N_step_count to 1
set N_branch_count to 3
set N_node_count to 0
 set N to the text returned of (display dialog “enter the number of tree branches” default answer “5″) as integer
display dialog “Trinomial Step Count Set to ” & N
(* start node and bringing steps to +1*)
 set Start_price to the text returned of (display dialog “Enter Start Price” default answer “5″) as number
display dialog “Start Price ” & Start_price
 set Strike_price to the text returned of (display dialog “Enter Strike Price” default answer “5″) as number
display dialog “Strike Price ” & Strike_price
 set Option_type to the text returned of (display dialog “Option type” default answer “Call”) as text
display dialog “Option type ” & Option_type
 set Risk_free_rate to the text returned of (display dialog “Risk free rate” default answer “%”) as number
display dialog “Risk free rate ” & Risk_free_rate
 set Security_yield to the text returned of (display dialog “Security yield” default answer “%”) as number
display dialog “Security yield ” & Security_yield
 set Volatility to the text returned of (display dialog “Volatility” default answer “%”) as number
display dialog “Volatility ” & Volatility
 set Time_to_maturity to the text returned of (display dialog “Days to Maturity” default answer “100″) as integer
display dialog “Days to Maturity ” & Time_to_maturity
 set trinomial_parameters to {“Trinomial Steps”, “Start_price”, “Strike_price”, “Option type”, “Risk free rate”, “Security yield”, “Volatility”, “Days to Maturity”}
 set trinomial_user_inputs to {N, Start_price, Strike_price, Option_type, (Risk_free_rate / 100), (Security_yield / 100), (Volatility / 100), Time_to_maturity}
 set trinomial_cell_reference to {“$B$1″, “$B$2″, “$B$3″, “$B$4″, “$B$5″, “$B$6″, “$B$7″, “$B$8″}
 set trinomial_variable to {“u”, “d”, “p(u)”, “p(d)”, “p(d)”}
 set trinomial_formulas to {“=EXP(” & (item 7 of trinomial_cell_reference) & “*((2*(” & (item 8 of trinomial_cell_reference) & “/365)/” & (item 1 of trinomial_cell_reference) & “)^(0.5)))”, ¬
“=1/EXP(” & (item 7 of trinomial_cell_reference) & “*((2*(” & (item 8 of trinomial_cell_reference) & “/365)/” & (item 1 of trinomial_cell_reference) & “)^(0.5)))”, ¬
“=((EXP((” & (item 5 of trinomial_cell_reference) & “” & (item 6 of trinomial_cell_reference) & “)*(((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5)))EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))^0.5)))/(EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & N & “)*(0.5))^0.5))EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))^0.5))))^2″, ¬
“=((EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))^0.5))EXP((” & (item 5 of trinomial_cell_reference) & “” & (item 6 of trinomial_cell_reference) & “)*(((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))))/(EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))^0.5))EXP(” & (item 7 of trinomial_cell_reference) & “*((((” & (item 8 of trinomial_cell_reference) & “)/365/” & (item 1 of trinomial_cell_reference) & “)*(0.5))^0.5))))^2″, ¬
“=1E3E4″}
set row_counter to 1
 repeat with i in trinomial_parameters
 set value of cell (get address of cell row_counter of column 1) of worksheet “Sheet1″ to (item row_counter of trinomial_parameters)
set value of cell (get address of cell row_counter of column 2) of worksheet “Sheet1″ to (item row_counter of trinomial_user_inputs)
set row_counter to row_counter + 1
end repeat
set row_counter to 1
 repeat with i in trinomial_variable
 set value of cell (get address of cell row_counter of column 4) of worksheet “Sheet1″ to (item row_counter of trinomial_variable)
set value of cell (get address of cell row_counter of column 5) of worksheet “Sheet1″ to (item row_counter of trinomial_formulas)
set row_counter to row_counter + 1
end repeat
set N to N + 1
if Option_type = “Call” then
 set Option_price to {“=MAX(” & (get value of cell (get address of cell 3 of column 5)) & “*”, ¬
“+” & (get value of cell (get address of cell 4 of column 5)) & “*”, ¬
“+” & (get value of cell (get address of cell 5 of column 5)) & “*”, ¬
“,”, ¬
“” & Strike_price}
else
 set Option_price to {“=MAX(” & (get value of cell (get address of cell 3 of column 5)) & “*”, ¬
“+” & (get value of cell (get address of cell 4 of column 5)) & “*”, ¬
“+” & (get value of cell (get address of cell 5 of column 5)) & “*”, ¬
“,”, ¬
“+” & Strike_price}
end if
repeat N times
 set value of cell (get address of cell (1 + (N) * 3) of column N_step_count) of worksheet “Sheet1″ to “=” & (item 2 of trinomial_cell_reference)
set value of cell (get address of cell (2 + (N) * 3) of column N_step_count) of worksheet “Sheet1″ to ¬
((get item 1 of Option_price) & (get address of cell ((N) * 3 – 1) of column (N_step_count + 2)) & ¬
(get item 2 of Option_price) & (get address of cell ((N) * 3 + 5) of column (N_step_count + 2)) & ¬
(get item 3 of Option_price) & (get address of cell (2 + (N) * 3) of column (N_step_count + 2)) & ¬
(get item 4 of Option_price) & (get address of cell (1 + (N) * 3) of column N_step_count) & ¬
(get item 5 of Option_price))
 repeat N_node_count times
 set value of cell (get address of cell (1 + (N) * 3 – (N_branch_count)) of column N_step_count) of worksheet “Sheet1″ to ¬
“=” & ((get address of cell ((N) * 3 – (N_branch_count) + 4) of column (N_step_count – 2))) ¬
& “*” & ¬
((get address of cell 1 of column 5))
set value of cell (get address of cell (2 + (N) * 3 – (N_branch_count)) of column N_step_count) of worksheet “Sheet1″ to ¬
((get item 1 of Option_price) & (get address of cell ((N) * 3 – (N_branch_count) – 1) of column (N_step_count + 2)) & ¬
(get item 2 of Option_price) & (get address of cell ((N) * 3 – (N_branch_count) + 5) of column (N_step_count + 2)) & ¬
(get item 3 of Option_price) & (get address of cell (2 + (N) * 3 – (N_branch_count)) of column (N_step_count + 2)) & ¬
(get item 4 of Option_price) & (get address of cell (1 + (N) * 3 – (N_branch_count)) of column N_step_count) & ¬
(get item 5 of Option_price))
set value of cell (get address of cell (1 + (N) * 3 + (N_branch_count)) of column N_step_count) of worksheet “Sheet1″ to ¬
“=” & ((get address of cell ((N) * 3 + (N_branch_count) – 2) of column (N_step_count – 2))) ¬
& “*” & ¬
((get address of cell 2 of column 5))
set value of cell (get address of cell (2 + (N) * 3 + (N_branch_count)) of column N_step_count) of worksheet “Sheet1″ to ¬
((get item 1 of Option_price) & (get address of cell ((N) * 3 + (N_branch_count) – 1) of column (N_step_count + 2)) & ¬
(get item 2 of Option_price) & (get address of cell ((N) * 3 + (N_branch_count) + 5) of column (N_step_count + 2)) & ¬
(get item 3 of Option_price) & (get address of cell (2 + (N) * 3 + (N_branch_count)) of column (N_step_count + 2)) & ¬
(get item 4 of Option_price) & (get address of cell (1 + (N) * 3 + (N_branch_count)) of column N_step_count) & ¬
(get item 5 of Option_price))
set N_branch_count to N_branch_count + 3
end repeat
set N_branch_count to 3
set N_step_count to N_step_count + 2
set N_node_count to N_node_count + 1
end repeat
end tell
[...] 14th, 2011 by Tariq Scherer « HowTo: Create Trinomial Option Pricing Trees Using Excel Applescripts A Defensive Trade » var addthis_product = 'wpp254'; var addthis_config = [...]
I’ve just written this spreadsheet for Binomial Option pricing in Excel: http://investexcel.net/1095/binomialtreeamericanoption/
I’ll update it to reflect the trinomial method outlined in your article. Thank you!
Sim Con
Hi Sim Con,
Thank you for your post and your reply and for making available a VBA implementation for your users. I’ll be sure to review your site for any future updates.
Kind Regards and a Happy New Year,
Tariq Scherer
Actually, if you are really interested in building up a more expanded valuation model, perhaps the HullWhite model might be of value to you?
The link attached provide a paper by Hull and White proposing a more modular lattice tree model. It is a bit heavy on the maths side but essentially it allows the user to combine meanreversion and skewness into the lattice across the tree.
Please let me know if you would like more information than what is available in the article: http://efinance.org.cn/cn/FEshuo/250105%20%20%20%20%20Valuing%20Derivative%20Securities%20Using%20the%20Explicit%20Finite%20Difference%20Method,%20pp.%2087100.pdf
Kind Regards,
Tariq Scherer
PS I provide a brief mention on HullWhite and other models in my subsequent post Implied Volatility Squared
Hei there,
I’m trying to build trinomial tree, but it seems there’re many ways to build it. One if what you wrote. Can I know what reference/ paper did you use? Is it, by any chance, ‘Pricing Option Using Trinomial Tree’ by Paul Clifford, etc?
Thank you.