Modeling a European Put Option
By Sam O. Sugiyama and Jeffrey Chow
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 analyze 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:
  1. Adding uncertainty to American-style options. We are in the process of exploring this by adding uncertainty to the volatility of the stock price.
  2. Adding uncertainty to a capital budgeting analysis.
  3. Adding uncertainty to annual operating budgets.
  4. Adding uncertainty to a short-term planning budget (1-3 years).
  5. Adding uncertainty to a synthetic hedge.
  6. Adding uncertainty in the consideration of equipment reliability.
(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.)

For more information, contact:

Randy Heffernan
Tel: 607-277-8000
Fax: 607-277-8001

Palisade Corporation
130 East Seneca Street
Suite 505
Ithaca, NY 14850
800 432 RISK (US/Can)
+1 607 277 8000
+1 607 277 8001 fax
Palisade EMEA & India
+44 1895 425050
Palisade Asia-Pacific
+61 2 9252 5922
Palisade アジア・
+81 3 5456 5287 tel
Palisade Latinoamérica
+1 607 277 8000 x318
+54-1152528795  Argentina
+56-25813492 Chile
+507-8365675 Panamá
+52 55 5350 2852 México
+511-7086781 Perú
+57-15085187 Colombia
Palisade Brasil
+55 (21) 3958 1443
+1 607 277 8000 x318 tel