something.com

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 step-up 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 log-normal distribution of the underlying’s asset price. The built-up 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 fair-valuation of American and Bermudan styled options. These are either liberally or discretely exercisable and therefore require a finite pay-offs analysis (the lattice model) for fair-value estimation of the contract.

The Black & Scholes model provides a continuous price distribution model that is formally only applicable to European-styled options (exercised solely at expiration).

Both the binomial, at high-iterations, 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 stable-branch 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.

Equations1 Trinomial Options Pricing Model

Underlying prices can move up, down or remain stable across the tree and are recombinant across time periods

Once the pricing distribution tree is constructed, the options are then priced back recursively from the expiry nodes, bringing back to t0 a fair-value 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 fair-value 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.

Equations2 Trinomial

Option pricing is conducted recursively from the end nodes with an expected weighted value applied to all preceding nodes up to the analysis date.

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).

Formatted Trinomial Option Pricing Tree Example

Note the recombinant nature of price moves. The option's theoretical value is then priced back from the expiry nodes through to the analysis date.

Applescript Implementation

This is still early stages, however, initial tests across the lattice have matched up on a one-to-one 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 user-friendly 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 top-left 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 re-enabled 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.

Trinomial-Option-Tree-Screencap

At the moment, the script is without formatting features. These might be added with basic Greek calcs too.

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″, ¬
      “=1-E3-E4″}

    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

5 Responses to “How-To: Create Trinomial Option Pricing Trees Using Excel Applescripts”

  1. [...] 14th, 2011 by Tariq Scherer « How-To: Create Trinomial Option Pricing Trees Using Excel Applescripts A Defensive Trade » var addthis_product = 'wpp-254'; var addthis_config = [...]

  2. Sim Con says:

    I’ve just written this spreadsheet for Binomial Option pricing in Excel: http://investexcel.net/1095/binomial-tree-american-option/

    I’ll update it to reflect the trinomial method outlined in your article. Thank you!

    Sim Con

  3. Jingga says:

    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.

Leave a Reply

Disclaimer: Material posted on 24-something does not contain (and should not be construed as containing) personal financial or investment advice or other recommendations. The information provided does not take into account your particular investment objectives, financial situation or investment needs. You should assess whether the information provided is appropriate to your particular investment objectives, financial situation and investment needs. You should do this before making an investment decision based on the material above. You can either make this assessment yourself or seek the assistance of an independent financial advisor. 24-Something, associated parties and Tariq Scherer accept no responsibility for any use that may be made of these comments and for any consequences that result.