Writing Covered Puts in Excel

Writing covered puts is a strategy that involves selling a put option and shorting the underlying stock at the same time. A put option gives the buyer the right to sell the stock at a certain price (the strike price) before a certain date (the expiration date). The seller of the put option collects a premium (the price of the option) from the buyer, but also takes on the obligation to buy the stock from the buyer if the stock falls below the strike price.

The reason to write a covered put is to profit from a bearish outlook on the stock. By shorting the stock, the seller hopes that the stock price will go down. By selling a put option, the seller also hopes that the stock price will stay above the strike price, so that the option will expire worthless and the seller can keep the premium. The premium also provides some protection against a rise in the stock price, as it lowers the breakeven point of the trade.

The risk of writing a covered put is that the stock price could rise significantly above the strike price, causing the seller to lose money on the short stock position. The seller’s loss is unlimited, as there is no limit to how high the stock price can go. The seller’s profit is limited, as the maximum profit is achieved when the stock price is equal to or lower than the strike price at expiration. The maximum profit is equal to the difference between the short stock price and the strike price, plus the premium received.

For example, let’s say a trader writes a covered put on XYZ stock, which is trading at $50. The trader sells the $45 put option for $2 and shorts 100 shares of XYZ at $50. The trader’s breakeven point is $52, which is the short stock price plus the premium received. The trader’s maximum profit is $700, which is ($50 – $45 + $2) x 100. The trader’s maximum loss is unlimited, as the stock price could rise above $52 and cause the trader to lose money on the short stock position.

Basic Theory:

The basic theory revolves around the concept of Net Present Value (NPV), a financial metric used to evaluate the profitability of an investment. NPV calculates the present value of expected future cash flows, discounted at a certain rate. If the NPV is positive, the investment is considered worthwhile.

Basic Theory:

A covered put involves two primary components – owning the underlying stock and selling a put option. By doing so, the investor collects a premium for selling the put option, but in return, commits to buying the stock at the strike price if the option is exercised. The strategy is deemed “covered” because the investor has the funds to buy the stock if necessary.

Procedures:

  1. Select a Stock: Choose a stock that you are comfortable owning in case the put option is exercised.
  2. Determine Strike Price and Expiry: Decide on the strike price and expiration date for the put option. These choices depend on your market outlook and risk tolerance.
  3. Sell Put Option: In your Excel spreadsheet, use the following formula to calculate the premium received from selling the put option:
    =OPTION.PREMIUM(STRIKE, EXPIRY, STOCK_PRICE, VOLATILITY, INTEREST_RATE, DIVIDEND, TYPE)

Where:

  • STRIKE is the strike price of the put option
  • EXPIRY is the time to expiration in years
  • STOCK_PRICE is the current stock price
  • VOLATILITY is the stock’s historical volatility
  • INTEREST_RATE is the risk-free interest rate
  • DIVIDEND is the expected dividend yield
  • TYPE is “put” for a put option
  1. Monitor Position: Keep an eye on the stock’s performance and the option’s value. If the stock price remains above the strike price, the option may expire worthless, allowing you to keep the premium.

Scenario:

Let’s consider a scenario with the following details:

  • Stock: XYZ Inc.
  • Current Stock Price: $50
  • Strike Price: $45
  • Premium Received: $2
  • Time to Expiration: 3 months
  • Volatility: 20%
  • Risk-Free Interest Rate: 3%
  • Dividend Yield: 1%

Calculation:

In your Excel table, input the values and use the formula:

      =OPTION.PREMIUM(45, 0.25, 50, 0.2, 0.03, 0.01, "put")

The result will be the premium received, which is $2.

Other Approaches:

  1. Adjust Strike and Expiry: Experiment with different strike prices and expiration dates to find the optimal balance between premium income and risk.
  2. Portfolio Diversification: Instead of using all funds to cover a single stock, consider diversifying across multiple stocks to spread risk.
  3. Risk Management: Implement stop-loss orders or buy back the put option if the stock price drops significantly to limit potential losses.

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 *