Put Sale in Excel Formulas

A put sale is a type of option trading strategy that involves selling a put option contract to an investor who wants to buy the underlying asset at a specified price (the strike price) before or at a certain date (the expiration date). The seller of the put option receives a premium from the buyer for taking on the obligation to sell the underlying asset if the buyer exercises the option.

The seller of the put option hopes that the underlying asset will not fall below the strike price before the expiration date, so that they can keep the premium and avoid having to sell the asset at a lower price than its market value. However, if the underlying asset does drop below the strike price, then the seller will have to deliver the asset to the buyer at that price, resulting in a loss for them.

The seller of a put option also faces some risks and costs associated with this strategy. For example, they have to pay commissions and fees for selling and buying options contracts, which can reduce their profit margin. They also have to deal with margin requirements, which means they have to deposit some money or securities as collateral with their broker in case they fail to meet their obligations under an option contract. Additionally, they may face liquidity issues if there are not enough buyers or sellers for their options contracts in the market.

Therefore, selling a put option is not suitable for everyone. It requires careful analysis of the market conditions, such as the volatility, interest rates, and time decay of the underlying asset, as well as an understanding of one’s risk tolerance and investment objectives. Selling a put option can be used as a way of generating income from an existing position in an underlying asset, or as a way of hedging against downside risk in an otherwise bullish outlook.

Basic Theory:

A put option gives the holder the right, but not the obligation, to sell a specified amount of an underlying asset at a predetermined price (strike price) within a specified period. When an investor sells a put option, they assume the obligation to buy the underlying asset at the strike price if the option is exercised by the option holder.

The basic idea behind put sale is to profit from the premium received when selling the put option. If the option expires unexercised, the seller keeps the premium as profit.

Procedures:

  1. Selecting the Underlying Asset: Choose a stock or security that you would be comfortable owning at a potentially lower price.
  2. Determining Strike Price and Expiration Date: Decide on a strike price at which you are willing to buy the stock if the option is exercised. Also, choose an expiration date for the option.
  3. Selling the Put Option: Execute the put sale by selling a put option on the chosen security. You’ll receive a premium for undertaking the obligation.
  4. Monitoring the Option: Keep an eye on the stock price and the option’s performance. If the stock price remains above the strike price, the option will likely expire worthless, and you keep the premium.

Excel Formulas:

Let’s create a scenario to illustrate put sale using Excel.

Scenario:

  • Underlying Asset: XYZ Company
  • Current Stock Price: $50
  • Strike Price: $45
  • Premium Received: $2
  • Number of Contracts: 1
  • Expiration Date: 30 days from today

Excel Table:

A B
1. Current Price $50.00
2. Strike Price $45.00
3. Premium $2.00
4. Expiration Date [Enter Date]
5. Number of Contracts 1
6. Profit/Loss [Formula Result]

Excel Formulas:

In cell B4, enter the expiration date. Then, in cell B6, input the following formula:

=IF(A1 >= A2, B3*B5, -B3*B5)

This formula calculates the profit or loss based on whether the stock price at expiration is above or below the strike price.

Calculation:

  • If XYZ stock is above $45 at expiration, you keep the premium: Profit = $2 * 1 = $2
  • If XYZ stock is below $45 at expiration, you buy the stock at a lower price: Loss = -$2 * 1 = -$2

Other Approaches:

  1. Cash-Secured Put Sale: Ensure you have enough cash to cover the potential stock purchase if the option is exercised.
  2. Collateralized Put Sale: Use a margin account and the value of existing securities as collateral.
  3. Rolling Options: If the option is in-the-money as expiration nears, consider rolling the position by buying back the current put option and selling another with a later expiration date.

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 *