Previous Topic

Next Topic

Entering an Array Formula

An array formula is a formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are entered by selecting the appropriate range of cells in a worksheet and then pressing CTRL+SHIFT+ENTER.

Resolution contains many functions that can be entered as an array formula. An array formula is required when more than one output value is needed from the selected function, e.g. when the value and the "Greeks" are required for an option, or when the yield and the risk statistics are required for a bond.

 

Consider the Black Scholes function as an example. Using the same option set up as used in oBS( ) Example 1, we have the following function specification:

=oBS(1, "1/4/02", "1/4/03", 50, 60, 0.25, 0.07, 0)

 

If the user wanted to return only the option value, the following function specification would be entered into a single cell, as demonstrated below:

 


Note that the output flag in the above function is set to "1" (see oBS( ) Function), which means that the function will return the option value only. The value for this example, given in cell A1, is $2.7804209.

 

If the user wanted to return not only the option value but also all the greeks associated with the option, the following function specification would be entered into six consecutive cells, as demonstrated below:

 

That is, enter the function into cell 'A1', copy the formula into the range 'A2:A6', highlight the entire range, and then press CTRL+SHIFT+ENTER.

Note that the output flag in the above function is set to "0" (see oBS( ) Function), which means that the function will return the value and all the greeks. The option value for this example, given in cell A1, is $2.7804209, while Delta, Gamma, Theta, Vega, and Rho are given in cells A2 - A6 respectively.

The easiest way to enter an array formula is to use the following 4-step process:

 

Step 1.

Enter the formula into a single cell

 

See Inserting Resolution Functions for the various ways to insert a function. Note, the output flag should correspond to a multiple return value.

 

 

 

 

 

Step 2.

Highlight the required number of cells.

 

Note, for single array formulas (such as this example) the highlighted cells can be horizontal or vertical, as long as they are consecutive. For multiple array formulas (e.g. a table, say 4 columns wide by 10 rows deep), the user is required to select the appropriate number of cells.

Be sure that the cell containing the formula (i.e., A1) has the focus (i.e., is white). This is achieved by selecting this cell first and then highlighting the other cells.

 

 

Step 3.

Copy the formula.

 

With the cells highlighted, highlight the formula in the formula bar (which will contain the formula if the cell containing the function has the focus) and select Edit > Copy from the menu.

 

 

 

Step 4.

Paste the Array Function

 

After copying the formula, and with the appropriate cells still highlighted, simultaneously select CTRL+SHIFT+ENTER on your keyboard and the array formula will be pasted into the highlighted cells.

 

 

 

Referring to the oBS( ) Function Definition, the cells in A1 - A6 contain the Option's Value, Delta, Gamma, Theta, Vega, and Rho respectively.

 

Comments:

If too many cells are highlighted, e.g. if cell A7 was also highlighted in the above example, a "#N/A" error will be generated in the additional cells. This will not impact the relevant cells.

If the output flag instructs the function to return a single output (e.g. if the output flag was set to "1" in the above example), and the function is entered as an array formula, then the single output will be repeated in each highlighted cell.

Return to www.derivativepricing.com website

Copyright 2013 Hedgebook Ltd.