Hedging Risk with Put Options

 Back

1.        Objective:

As a function of the number of puts purchased, construct a worst-case and best-case scenario for the return earned on Harry Rockfeller’s portfolio between June 30, 1998, and November 22, 1998, assuming that he sells his stock on the latter date.

2.        Decision and External Variables:

1)       Decision variables: The decision variables are values that a decision maker has control over. In this example, Harry can determine how many put options he is able to purchase. Therefore, the number of puts to purchase is the decision variable.

2)       External variables: By definition, the external variables are determined by the environment that a decision maker has no control over, such as the future stock price on the date that the put option expires in our example.

Top

3.        Construction of the Spreadsheet Model:

1)       The return of the portfolio is given by the formula

 Return = Amount received – Amount invested

Amount invested

2)       Inputs: They are given values that are fixed. Current price of stock, price per put, and exercise price are the inputs in this example.

3)       Range names: For exercise purpose, we will name cells to simplify the formulas.

There is an easy way to create this list by going to Insert, selecting Name, then Paste…. A dialog will appear. Select Paste the list. A list like the above will be pasted at the cell where the cursor is.

4)       Amount invested. The total amount is the sum of the current price of the stock and the cost of puts. The formula is

= CurrPrice + NumPuts * PutPrice

5)       Amounts received. The amount Harry will receive when he exercises his option is the future price of the stock at which Harry sells his holding plus the payoff from the put option. Portfolio Insurance Payoff can be summarized as follow

Enter the formulas

= FutPrice

= NumPuts * IF (FutPrice > ExerPrice, 0, ExerPrice – FutPrice) and

= Sum (B14 : B15)

in cells B14, B15, and B16. The IF function in cell B15 indicates the put option payoff - if the future price is greater than exercise price, the payoff will be zero; if the future price is less than or equal to the exercise price, the payoff will be the difference between the exercise price and future price.

6)       Return. The formula is

= (AmtReceived – AmtInvested) / AmtInvested

Top

4.        Analyses:

1)       Selection of analysis method: As the objective of this example is to determine the worst-case and best-case scenario for the return as a function of the number of puts to purchase, what-if analysis will be the most appropriate method to forecast values.

2)       Ways with what-if analysis: There are three ways to forecast values with what-if analysis – data tables, goal seek and solver, and scenarios. In our example, two variables, number of puts to purchase and future price of stock, will affect the return. And by definition, a data table is a range of cells that shows how changing certain values in the formulas affects the results of the formulas. Therefore, two-variable data table will be the best choice.

3)       Construction of data table:

a.        In cell A25, enter the formula that refers to the two input cells

= Return

b.       Type the list of future stock prices in the same column, below the formula. Type the list of number of puts to purchase in the same row, to the right of the formula.

c.        Select the range of cells that contains the formula and both the row and column of values - A25 : G136.

d.     On the Data menu, click Table.

e.        In the Row input cell box, enter the reference for the input cell for the input values in the row, which is cell B9.

f.         In the Column input cell box, enter the reference for the input cell for the input values in the column, which is cell B13.

4)       Worst and best case returns: Use MIN and MAX functions on the columns in the data table to find the worst and best returns.

5)       The chart of return as a function of the future price for various numbers of puts: The chart shows

     a.        How puts shield Harry from risk if the price of the stock falls dramatically.

b.       The more puts he buys, the more he stands to gain if the stock price drops significantly.

c.        If the price stays about the same or increases, he loses slightly by buying more puts, but the difference is fairly minor.

5.        Observations:

1)       If Harry buys no puts, his worst case is a 57.56% loss, whereas if he buys 1 put, his worst case is only a 19.6% loss.

2)       By purchasing a put, he caps his maximum return at 50.75%. If he buys no puts, his maximum return is 59.15%.

3)       In effect, the put is portfolio insurance that hedges Harry’s downside risk but limits his upside benefits.

4)       Purchasing more puts actually makes Harry's worst case inferior to purchasing 1 put.

6.    Modeling Issues:

1)     Nevertheless, it is still not clear how many puts Harry should purchase.

2)     2)    This depends on the probability distribution of the future stock price.

3)     It also depends on Harry’s attitude toward risk. Does he mind taking risks, or does he avoid them whenever possible?

4)   Finally, it is easy to scale this model. If Harry purchases 100 shares of stock rather than 1, we simply multiply the appropriate quantities in the model by 100.