Put Spread Sale in Excel

A put spread sale is a type of options strategy that involves selling a put option with a lower strike price and buying another put option with a higher strike price on the same underlying asset and expiration date. The seller of the put option receives a net credit from the buyer, which reduces the cost of holding the option trade. The seller hopes that the price of the underlying asset will stay above or close to the lower strike price until expiration, so that both options expire worthless and the seller keeps the net credit as profit.

A put spread sale is also known as a debit put spread or a short put spread. It is a bearish strategy, meaning that it profits from a moderate-to-large decline in the price of the underlying asset. The maximum profit from this strategy is equal to the net credit received, minus any commissions or fees paid to execute the trade. The maximum loss from this strategy is equal to the difference between the two strike prices minus the net credit received, if the price of the underlying asset falls below either strike price at expiration.

A put spread sale has some advantages and disadvantages compared to buying a single put option outright. Some of the advantages are:

  • It reduces the initial cost of entering a bearish position on an underlying asset.
  • It limits the maximum loss to a fixed amount, regardless of how much the underlying asset declines.
  • It allows for more flexibility in choosing different strike prices and expiration dates.

Some of the disadvantages are:

  • It requires paying commissions or fees to sell one or both options.
  • It caps the maximum profit potential, which may be lower than buying a single put option outright.
  • It exposes to unlimited risk if there is no significant decline in price before expiration.

Basic Theory:

  1. Short Put Option: The investor sells a put option to receive a premium.
  2. Long Put Option: Simultaneously, the investor buys another put option with a lower strike
    price, acting as a hedge.

Procedures:

1. Constructing the Put Spread Sale:

Step Description Formula/Action
1 Input the current stock price =Current Stock Price
2 Set the higher strike price (Short Put) =Current Stock Price + Buffer
3 Set the lower strike price (Long Put) =High Strike Price – Spread Width
4 Input the option premium =Premium for Short Put
5 Calculate the net premium received =Premium for Short Put – Premium for Long Put

2. Scenario Example:

Let’s assume:

  • Current Stock Price = $100
  • Buffer (Safety Margin) = $5
  • Spread Width = $10
  • Premium for Short Put = $3
  • Premium for Long Put = $1

3. Excel Calculation:

Step Description Formula/Action
1 Current Stock Price $100
2 High Strike Price $100 + $5 = $105
3 Low Strike Price $105 – $10 = $95
4 Premium for Short Put $3
5 Premium for Long Put $1
6 Net Premium Received $3 – $1 = $2

4. Result:

By executing the Put Spread Sale, the investor receives a net premium of $2.

Other Approaches:

  1. Changing Spread Width: Adjust the width between the two strikes to alter the risk-reward
    profile.
  2. Varying Safety Margin: Modify the buffer to manage risk based on market conditions.
  3. Dynamic Scenario Analysis: Use Excel’s data table or Goal Seek function to analyze potential
    outcomes under different stock prices and premiums.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *