I In- range Al . . G70 contains much of the basic input data. Some of the input variables are single numbers and many have variable names. Other ;ii uibles are in the form of vectors or ranges and these are also named.
General Single Number Input Variables
A brief description of each of the single number input variables is listed below along with each variable's cell address, name and example values.
• Cell B3 (smid) is the central stock price that appears in the computed risk matrix output. It usually makes sense to pick the current stock price as the central price. The output table thus shows the various risk characteristics at stock prices around this central value. In the given example this value is set to 100.
• Cell D3 (sdif) is the price difference. This value dictates the range of different stock prices used in producing the risk matrix. In the given example the value is set to 4 so that the difference between each successive stock price is $4. Thus the 13 different stock prices:
$76, $80, $84, .... $124 are calculated using: (smid - 6 * sdif), (smid - 5 * sdif), (smid - 4
* sdif),.... (smid + 6 * sdif).
• Cell Dl has the function TODAYQ which returns the current date in the relevant format.
This is used in calculating the time to expiry of the various options.
• Cell D2 (rate) is the relevant interest rate. In the given example this is set to zero.
• Cell D4 is the theta difference parameter in days. In the given example this value is set to 1 day and so the output values in the columns headed (+ time) represent the change in P&L or delta caused by the passing of 1 day.
• Cell F4 (tdif) is the theta difference parameter that is used in all subsequent mathematical computations. Accordingly it is expressed in years and is given a minus sign. This value is calculated from the entry in Cell D4 using the expression: [ = -D4/365].
• Cell D5 (vdif) is the vega difference parameter. In the given example this value is set to 0.01 or 1% and so the output values in the columns headed (+ vol) represent the changes in P&L or delta caused by increasing volatility across the board by 1%.
• Cell D6 (gdif) is the gamma difference parameter and in the given example is set to 0.10.
With this setting, the entries in the gamma column of the risk matrix indicate the change in stock exposure caused by an increase in the underlying stock price of $0.10 or 10 cents.
• Cell D7 (mult) is the option multiplier. In the given example this is set to 100 because all options discussed in this book are exercisable into 100 shares. Different options markets have different numbers of shares per option. The UK and Australian stock options contracts, for example, are exercisable into 1,000 shares. Many options on futures contracts are exercisable into just one future.
• Cell B5 (ssz) is the stock position. Often a complex options portfolio will also contain a long or short position in the underlying stock. If the portfolio in question had a short position, say in 5,000 shares, then one should input -5,000 into this cell. In the example given, this cell is set to zero since the portfolio has no stock position.
Cell B6 (spr) is the average stock price. In the given example this is irrelevant and so is set to zero. However, say the portfolio had a short position in 5,000 shares then the average price of the position should be placed in this cell. As an example, say the share position was acquired as the result of shorting 2,000 shares at $100 and shorting 3,000 shares at $110. then the average share price input into Cell B6 should be
= 2,000 X $100 + 3,000 x $110 5,000
= $106
• Cell B7 (scost) is the cost of carrying the stock position. The cost of carrying a stock position is calculated from the theta difference parameter, the interest rate, the average stock price and the stock position using the following expression:
scost = -rate * ssz * spr * tdif
In the given example the cost is obviously zero but consider the situation if the portfolio has a short position of 5,000 units at an average cost of $106. If interest rates were 10% and the theta parameter set to 1 day, this means that the cost of running the stock position would be
scost = -0.10 * (-5000) * 106 * (1/365) = +$145.21
The position has a net positive cost of carry. The short position is generating an income of
$145.21 per day. With large stock positions this cost can have a considerable effect on the (+ time) column in the P&L section of the risk matrix.
Expiry Specific Single Number Input Variables
The example spreadsheet RISK1.XLS has three different expiry cycles situ-cited in the ranges A9 . . G24, A32 . . G47 and A55 . . G70 respectively. The single number input variables associated with the first expiry are as follows:
• Cell D9 is the expiry date in the correct format for the options in expiry cycle #1.
• Cell D10 is the number of days to expiry of this cycle and is computed as the difference between Cell D9 and the current date in Cell Dl.
• Cell Oil (time I) is the time to expiry of the options in cycle #1 expressed in years and is simply the number of days divided by 365. The variable iniu'1 is a single number variable that is used throughout the spreadsheet when referring to those positions in cycle #1 options.
i Jl^ in the ranges containing expiry cycle #2 and #3 option positions have iinil.ii entries.
The single number variables corresponding to the time (in spr
years) to expiry are time2 and time3 respectively. In order to replicate exactly the figures in Table 7.3 it will be necessary to alter the expiry dates so that the number of days to expiry are 90,182 and 272 respectively.
Expiry Specific Range Input Variables or Vectors
To simplify the mathematical expressions the spreadsheet utilises the range variable facility of Microsoft Excel. This allows a column (or row) of numbers to be referred to by a single mathematical expression. The spreadsheet has six such range variables:
• Range: C16 . . C70 (exprice) is the set of exercise prices for all expiry cycles.
• Range: B16 . . B70 (vol) is the set of individual volatilities. In the given example each element is set to 15% but the user can set each volatility to separate values.
• Range: D16 .. D70 (puts?:) is the set of put option positions for all expiry cycles and all strike prices. In the given example the portfolio has a short position of 200 options in the near (3-month) $95 strike puts and a long position of 100 puts in the near (3-month)
$105 puts. Accordingly the Cell D18 has the entry -200 and the Cell D20 the entry +100 with all other cells set to zero.
• Range: F16 .. F70 (callsz) is the set of call option positions for all expiry cycles and all strike prices. In the given example the portfolio has a long position of 100 options in the mid (6-month) $115 strike calls. Accordingly the Cell F45 has the entry +100 with all other cells set to zero.
• Range: E16 .. E70 (putpr) is for the average executed prices for each put option. These averages are computed in a similar fashion to the average stock price above. In the given example the 3-month $95 and $105 put averages are set to $1.05746 and $6.17616 respectively.
• Range: G16 .. G70 (callpr) is for the average executed prices for each call option. In the given example the 6-month $115 call average is set to $0.49599.
It is not essential to input the last two variables, putpr and callpr, as these are only used in calculating the P&L column in the final risk matrix. The rest of the risk matrix is completely independent of these prices.
All the information necessary to calculate the risk matrix is on Sheet 1 described above and for the non-mathematician and non-programmer that is all one needs to know. The reader is invited to experiment with the software by constructing various portfolios. The resultant risk matrices can be examined in tabular form or printed in chart form. Those readers not interested in the mathematics of the risk software can skip the next section.