By Sam O. Sugiyama and Jeffrey Chow
(Mr. Sugiyama is president of Portland, OR-based Economic & Engineering Consultants, and Mr. Chow is a financial analyst with the firm. They have each used @RISK on a number of different analytic problems.)The example we have modeled is that of a European Put Option used to hedge a stock with no dividends. The standard approach for this type of options is to use the closed form Black-Scholes Model to determine the value of the option. (We have created an option calculator add-in to Microsoft Excel 7.0 that performs this calculation as well as the calculations for American-style options.) This approach is based on the expected price of the stock at the exercise date. The values added by using @RISK is to generate the distribution of prices at the exercise date. This will provide the estimated probability of gains (being "in the money") at various 'exercise date' prices. The model can be extended easily to consider the case of calls, hedging calls, and various of the simpler exotic options. We currently have a mark-to-market model, but it is not currently used in conjunction with @RISK.
The virtue of @RISK is that it is an efficient simulation engine for spreadsheet models that incorporates the effect of adding uncertainty (probability functions or distributions) to an Excel or Lotus spreadsheet analysis. It can then be used to examine the estimated probability of losses for the measure of merit in the analysis. The decision usually is to undertake a strategy ('buy insurance') to cover the risk of extraordinary loss. Insurance comes in many forms, but generally can be considered as the taking of an action to reduce the risk of extraordinary loss. @RISK incorporates the use of Latin Hypercube sampling, a variant of stratified random sampling, which significantly reduces the chance of under-representation of low-probability outcomes. @RISK also incorporates a nonparametric (not restricted to a particular set of probability distributions or distribution-free) technique for correlating uncertain variables. @RISK can be used with this capability to analyse the case where an option does not exist on a given financial asset. In this case, an option needs to be used that is highly correlated with the financial asset.
Other analyses we have performed using @RISK are:
- Adding uncertainty to American-style options. We are in the process of exploring this by adding uncertainty to the volatility of the stock price.
- Adding uncertainty to a capital budgeting analysis.
- Adding uncertainty to annual operating budgets.
- Adding uncertainty to a short-term planning budget (1-3 years).
- Adding uncertainty to a synthetic hedge.
- Adding uncertainty in the consideration of equipment reliability.
