CapeTools Generic Tree Pricer

General Description

Functions to price generic Equity/FX/Commodity structures under a Binomial Tree framework. If your product's payoff does not refer to asset prices in the past (ie - Asian options) you can use this framework. This framework can also handle bermudan callable structures

Here are some general rules to keep in mind regarding how the payoff grid will be processed :

Rows are time points. As you read down the grid you are moving forward in time.

Columns represent your payoff formulas. You can create as many columns as you like. Columns are there to help you split up your payoff, or for a payoff function within a cell to refer to other cells within the grid.

For the Binomial Tree pricer, the pricing starts at the final timestep and prices from left to right.

Formula expressions within a cell cannot refer to formula cells to the right of itself.

Formula expressions within a cell cannot refer to formula cells above itself (past time points).

If you bear the 5 points in mind, this should aid you in constructing payoff grids.

Below is an example of a payoff table. This deal prices an callable structure (The Step column is not required).

Step EventDate Repeat? PayOff Col PayOff Col PayOff Col PayOff Col PayOff Col
1 #19/07/2005# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
2 #19/01/2006# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
3 #19/07/2006# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
4 #19/01/2007# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
5 #19/07/2007# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
6 #19/01/2008# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
7 #19/07/2008# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
8 #19/01/2009# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
9 #19/07/2009# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
10 #19/01/2010# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
11 #19/07/2010# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))
12 #19/01/2011# FALSE Asset(cRow) + 10pct Interp1D(1,cRow) GridRowSum(cRow, cCol-2, cCol-1) max(Cell(cRow, cCol-1) - _var1, 0.0) max(Cell(cRow, cCol-1), PVCol(cCol))

The 'Repeat' column indicates that the instruction at the current step will be repeated for all the timesteps between this and the next.

Even though there are only 12 timesteps above, you can instruct the function to insert additional steps between each step.

Financial variables (case sensitive) :

cRow - refers to the current Row with the payoff table. Within all the Financial functions listed below, we recommend that you always use this variable (with perhaps offset values, ie - cRow, cRow-1, cRow+2 etc...) instead of a fixed value (ie - 1, 2 etc...). This is important especially if you enable American type handling from one timestep to another. Using static integer values will generate incorrect results.

cCol - refers to the current Column with the payoff table. Within all the financial functions listed below, we recommend that you always use this variable (with perhaps offset values, ie - cCol, cCol-1, cCol+2 etc...).

pct - When an integer value is placed in front of this expression (no spaces), the function will interp the whole expression as a percent. (ie - 5pct => 0.05)

bp - When an integer value is placed in front of this expression (no spaces), the function will interp the whole expression as basis points. (ie - 5bp => 0.0005)

_XXX - Constant variables can be defined by filling in the 'ConstParams' parameter of the GenericTreePricer() function. All variables must be defined with a leading underscore character ( ie - _XXX = 0.5)

Financial functions (case sensitive) :

Asset(row) - Returns the asset value at the integer row indicated. Starts at 1. You may refer to future values of the asset's price because the Tree methodology prices backwards in time (from the last expiry date). The expression 'Asset(cRow)' retrieves the asset price for the current timestep. The expression 'Asset(cRow+1)' retrieves the asset price for the next timestep (in the future).

Cell(row, col) - Retrieves a computed value within the PayOff Table. You can only refer to rows and columns below and to the left of the current cell location. You can also refer to the current cell location. This is because the Tree pricer prices backwards in time and thus past payoff values have not been computed as yet. The expression 'Cell(cRow, cCol-1)' retrieves the payoff value for the current timestep but at the previous column.

GridRowMin(Row, startCol, endCol) - Retrieves the minimum value across the columns of the PayOff table (given a 'TimeStep' or row value). For the 'startCol' and 'endCol' parameters you can only refer to columns to the left or equal to the current cell location.

GridRowMax(Row, startCol, endCol) - Retrieves the maximum value across the columns of the PayOff table (given a 'TimeStep' or row value). For the 'startCol' and 'endCol' parameters you can only refer to columns to the left or equal to the current cell location.

GridRowAvg(Row, startCol, endCol) - Retrieves the average value across the columns of the PayOff table (given a 'TimeStep' or row value). For the 'startCol' and 'endCol' parameters you can only refer to columns to the left or equal to the current cell location.

GridRowSum(Row, startCol, endCol) - Retrieves the sum across the columns of the PayOff table (given a 'TimeStep' or row value). For the 'startCol' and 'endCol' parameters you can only refer to columns to the left or equal to the current cell location.

Interp1D(nIndex, XValue ) - If any 1D interpolation objects have been passed into GenericTreePricer() function (a maximum of 2 objects can be defined), then you can execute this function within your payoff grid. The 'nIndex' parameter refers to the intepolation object (1-based). 'XValue' is the value where the interpolation is to be performed. The interpolation object will not extrapolate values.

Interp2D(nIndex, XValue, YValue) - If any 2D interpolation objects have been passed into GenericTreePricer() function (a maximum of 2 objects can be defined), then you can execute this function within your payoff grid. The 'nIndex' parameter refers to the intepolation object (1-based). 'XValue' and 'YValue' are the values where the interpolation is to be performed. The interpolation object will not extrapolate values.

iff(condition, a, b) - Tests for the condition and returns the value of 'a' if true or 'b' if false.

PVCol(Column) - Requests the PV value of the indicated column up to the current step (cRow). This function must be used when pricing Bermudan / American type products.

Predefined functions (case sensitive) :

sin(x) ( sine function )

cos(x) ( cosine function )

tan(x) ( tangens function )

asin(x) ( arcus sine function )

acos(x) ( arcus cosine function )

atan(x) ( arcus tangens function )

sinh(x) ( hyperbolic sine function )

cosh(x) ( hyperbolic cosine function )

tanh(x) ( hyperbolic tangens function )

asinh(x) ( hyperbolic arcus sine function )

acosh(x) ( hyperbolic arcus cosine function )

atanh(x) ( hyperbolic arcur tangens function )

log2(x) ( logarithm to the base 2 )

log10(x) ( logarithm to the base 10 )

log(x) ( logarithm to the base 10 )

ln(x) ( logarithm to the base e (2.71828...)

exp(x) ( e raised to the power of x )

sqrt(x) ( square root of a value )

sign(x) ( sign function ) -1=(if x<0), 1=(if x>0 )

rint(x) ( round to nearest integer )

abs(x) ( absolute value )

min(a,b,c,d,e,...) ( min of all arguments ie - min(-2,1,5,-6) )

max(a,b,c,d,e,...) ( max of all arguments ie - max(-2,1,5,-6) )

sum(a,b,c,d,e,...) ( sum of all arguments ie - sum(-2,1,5,-6) )

avg(a,b,c,d,e,...) ( mean value of all arguments ie - avg(-2,1,5,-6) )

ite(x,y,z) ( if ... then ... else ... : ie - ite(a<b, 5.0, -3.0) )

Predefined operators :

+ ( addition )

- ( subtraction )

* ( multiplication )

/ ( division )

^ ( raise x to the power of y )

Predefined operators that can be used within the first parameter of the ite() or iff() function :

and ( logical and )

or ( logical or )

<= ( less or equal )

>= ( greater or equal )

!= ( not equal )

== ( equal to )

> ( greater than )

< ( less than )

Brackets, '(' and ')', can also be used to simplify expressions.

Some possible examples of what is possible now follow (Today = 19/07/2005):

Vanilla Option. (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2006# FALSE max(Asset(cRow) - 10, 0)

Sum of two Vanilla Options (Call and Put). (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2006# FALSE 100 max(Asset(cRow) - Cell(cRow, cCol-1), 0) + PVCol(cCol)
2 #19/07/2007# FALSE 90 max(Cell(cRow, cCol-1) - Asset(cRow), 0)

American Option (Repeat column and PVCol() function are used differently). (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2005# TRUE max(Asset(cRow) - 10, PVCol(cCol))
1 #19/07/2006# FALSE max(Asset(cRow) - 10, PVCol(cCol))

Delayed start American Option (starts at 19/10/2005, today is 19/07/2005). (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/10/2005# TRUE max(Asset(cRow) - 10, PVCol(cCol))
1 #19/10/2006# FALSE max(Asset(cRow) - 10, PVCol(cCol))

Bermudan Option. (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2005# FALSE max(Asset(cRow) - 10, PVCol(cCol))
2 #19/01/2006# FALSE max(Asset(cRow) - 10, PVCol(cCol))
3 #19/07/2006# FALSE max(Asset(cRow) - 10, PVCol(cCol))
4 #19/01/2007# FALSE max(Asset(cRow) - 10, PVCol(cCol))
5 #19/07/2007# FALSE max(Asset(cRow) - 10, PVCol(cCol))
6 #19/01/2008# FALSE max(Asset(cRow) - 10, PVCol(cCol))
7 #19/07/2008# FALSE max(Asset(cRow) - 10, PVCol(cCol))
8 #19/01/2009# FALSE max(Asset(cRow) - 10, PVCol(cCol))
9 #19/07/2009# FALSE max(Asset(cRow) - 10, PVCol(cCol))

Starts as American then becomes Bermudan Option. (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2005# TRUE max(Asset(cRow) - 10, PVCol(cCol))
2 #19/01/2006# TRUE max(Asset(cRow) - 10, PVCol(cCol))
3 #19/07/2006# TRUE max(Asset(cRow) - 10, PVCol(cCol))
4 #19/01/2007# TRUE max(Asset(cRow) - 10, PVCol(cCol))
5 #19/07/2007# FALSE max(Asset(cRow) - 10, PVCol(cCol))
6 #19/01/2008# FALSE max(Asset(cRow) - 10, PVCol(cCol))
7 #19/07/2008# FALSE max(Asset(cRow) - 10, PVCol(cCol))
8 #19/01/2009# FALSE max(Asset(cRow) - 10, PVCol(cCol))
9 #19/07/2009# FALSE max(Asset(cRow) - 10, PVCol(cCol))

Chooser Option. (The Step column is not required).

Step EventDate Repeat? PayOff Col PayOff Col
1 #19/07/2005# FALSE max(PVCol(cCol-1), PVCol(cCol))
2 #19/07/2006# FALSE max(Asset(cRow) - 10, 0) max(10 - Asset(cRow), 0)

Compound Option (european on european). (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2006# FALSE max(8.0 - PVCol(cCol), 0)
2 #19/01/2007# FALSE max(Asset(cRow) - 10, 0)

Compound Option (european on american, delayed by 2 days). (The Step column is not required).

Step EventDate Repeat? PayOff Col
1 #19/07/2006# FALSE max(8.0 - PVCol(cCol), 0)
2 21/07/2006# TRUE max(Asset(cRow) - 10, PVCol(cCol))
3 #19/07/2008# FALSE max(Asset(cRow) - 10, 0)

Step	EventDate	Repeat?	PayOff Col	PayOff Col	PayOff Col	PayOff Col	PayOff Col
1	#19/07/2005#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
2	#19/01/2006#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
3	#19/07/2006#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
4	#19/01/2007#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
5	#19/07/2007#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
6	#19/01/2008#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
7	#19/07/2008#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
8	#19/01/2009#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
9	#19/07/2009#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
10	#19/01/2010#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
11	#19/07/2010#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))
12	#19/01/2011#	FALSE	Asset(cRow) + 10pct	Interp1D(1,cRow)	GridRowSum(cRow, cCol-2, cCol-1)	max(Cell(cRow, cCol-1) - _var1, 0.0)	max(Cell(cRow, cCol-1), PVCol(cCol))

Step	EventDate	Repeat?	PayOff Col
1	#19/07/2006#	FALSE	100	max(Asset(cRow) - Cell(cRow, cCol-1), 0) + PVCol(cCol)
2	#19/07/2007#	FALSE	90	max(Cell(cRow, cCol-1) - Asset(cRow), 0)

Step	EventDate	Repeat?	PayOff Col
1	#19/07/2005#	TRUE	max(Asset(cRow) - 10, PVCol(cCol))
1	#19/07/2006#	FALSE	max(Asset(cRow) - 10, PVCol(cCol))

Step	EventDate	Repeat?	PayOff Col
1	#19/10/2005#	TRUE	max(Asset(cRow) - 10, PVCol(cCol))
1	#19/10/2006#	FALSE	max(Asset(cRow) - 10, PVCol(cCol))

Step	EventDate	Repeat?	PayOff Col	PayOff Col
1	#19/07/2005#	FALSE		max(PVCol(cCol-1), PVCol(cCol))
2	#19/07/2006#	FALSE	max(Asset(cRow) - 10, 0)	max(10 - Asset(cRow), 0)

Step	EventDate	Repeat?	PayOff Col
1	#19/07/2006#	FALSE	max(8.0 - PVCol(cCol), 0)
2	#19/01/2007#	FALSE	max(Asset(cRow) - 10, 0)