Covered Puts – What They Are & How You Can Profit From Them (Marketxls Options Data)

In this article, we are going to demonstrate what a covered put is, when you would use it, and how MarketXLS can help you in applying the strategy.

What are Covered Puts?

A covered put is a bearish investment strategy. You would usually consider applying it when you expect that a stock price might fall, yet you are not too bearish on the stock.

At the time of writing this article – in the midst of the coronavirus pandemic, this strategy is all the more relevant. Maybe you estimate that there is still potential for the market to go down, but you are wary of taking a short position due to the market’s unpredictability. Applying the covered put strategy is a way to reduce risk.

Applying the covered put strategy involves making two separate trades:

1. short selling the selected stock and
2. selling an “out of the money” put option on the same stock.

How to Perform a Covered Put Using MarketXLS

Let us see how we would apply the strategy on Microsoft (MSFT) shares.

Step 1.

Using the well-known MarketXLS function =Last(“MSFT“) I see that, at the time of writing the article, a MSFT share last traded at $163.49.

Step 2.

Next, I need to import the MSFT options data into an Excel sheet. You can do this by selecting a cell inside the spreadsheet, in which you have entered the ticker MSFT, and then selecting “Get Option Chains” from the “Utilities” drop-down menu in the MarketXLS tab in your Excel ribbon. We need to select “Option Chain Out of The Money”. (Note: to access options data, you will need the additional QuoteMedia data subscription.)

This will import 27 columns of options data into your spreadsheet. For the purpose of this article, I have hidden the columns which we won’t be using at this time.

Step 3.

The resulting overview looks like this:

Step 4.

I would suggest adding the Excel “filter” buttons to column headers so that we can focus exclusively on the put options and to be able to sort them as desired. We want to make some money by selling a put option on MSFT, so I will sort the filtered put options from the most expensive one to the cheapest one. Top results now look something like this:

If you are new to options, you may need to be informed that, while option prices are depicted as prices for 1 option for 1 underlying security (in this case, a MSFT share), options are traded in lots of 100. So, the latest price of $28.65 for the option at the top of our list, @MSFT  220916P00160000, means that you can sell a lot of 100 of these options and receive a premium of $2,865.

The strike price for these options sits at $160, very close to the latest MSFT stock price.

The reason why anyone would buy this put option is that, for them, it represents a guarantee that, whatever happens, they will be able to sell their MSFT shares for at least $160 per share, and they have this guarantee until September 16th, 2022, which is the expiration date.

Step 5.

For our example explanation of the covered put strategy, we don’t want to be tied in the option contract for so long. Filtering the above table further, to only show us only options which expire in the next three months, I get the following list.  The option contract that I selected is marked blue.

How Taking a Short Position in the Options Contract Looks

By taking a short position in the options contract @MSFT  200619P00150000, we can immediately receive a $695 premium ($6.95 per option * 100 options in a lot). To complete the covered put setup, we need to short sell 100 MSFT shares, for $16,349. This results in our net cash position at:

$695 + $16,349 = $17,044

What have we accomplished with this setup and how much do we stand to lose or gain?

1. In our bearish strategy, we have reduced the risk from the stock price increasing

Loss or Gain?

Not considering the broker fees which your real-life trades inevitably incur and which you need to take into consideration, selling this put option moves our break-even point to MSFT share price of $170.44. This is because even though we have entered a short stock position, by selling MSFT at $163.49 per share, we have already received the option premium of $6.95 per option.

As long as MSFT stock price remains below $170.44, our net cash result for these trades remains positive. Even if the stock price climbs to, say, $180 per share by the expiration date, June 19th, 2020, our loss will be lower than if we did not enter the options trade, as shown below.

Without the option trade, short selling 100 shares:

$16,349 (initial cash position from shorting MSFT) – $18,000 (closing the short position at a loss) = -$1,651

With the option trade, covered put strategy, short selling 100 shares:

$16,349 (initial cash position from shorting MSFT) + $695 (received options premium) – $18,000 (closing the short position at a loss) = -$956.

Assuming that our assumption pans out, we have received additional income

Keep in mind that our estimate for MSFT shares, which prompted us to enter the covered put strategy, was moderately bearish. We expect the stock to fall a few dollars per share, not to lose 20% or more of its value.

By shorting the share at $163.49 and selling a put with a $150 strike price, our potential income maxes out if the options expire at the money, with MSFT trading at $150 per share.

If MSFT shares fall to $140, for example, we will not fully benefit from our short position initiated at $163.49 per share, because the option which we sold will expire in the money and we will be forced to buy the stock back for $150 per share. This will be fully clear if we examine three potential scenarios.

Our Covered Put Example – The Bottom Line

Scenario 1 – option expires out of the money, with MSFT trading at $155 per share

$16,349 (initial cash position from shorting MSFT) – $15,500 (closing the short position if we wish) + $695 (received options premium) = $1,544

In this case, we have made a net $1,544 from the trade, which is $695 more than if we only shorted the stock, without selling the put options.

Scenario 2 – option expires at the money, with MSFT trading at $150 per share

$16,349 (initial cash position from shorting MSFT) – $15,000 (closing the short position if we wish) + $695 (received options premium) = $2,044

This is our maximum possible profit, with the described covered put setup.

Scenario 3 – option expires in the money, with MSFT trading at $140 per share

$16,349 (initial cash position from shorting MSFT) – $14,000 (closing the short position if we wish) + $695 (received options premium) – $1,000 (holder exercises his options, resulting in this loss for us, as the option expired $10 above the market price for the stock = $2,044

Scenarios 2 and 3 show us why this strategy is applied when we are only moderately bearish on the stock. If the stock price falls below the option strike price, it would have been better for us if we had not sold the put option. But in any other case, received option premium provides considerable additional income for the trader.

Three Things to Consider Before You Implement this Investment Strategy

1. Unlike potential income which maxes out as described above, there is no limit to possible losses if the stock price shoots up. To protect against that threat, you may consider a stop-loss order on your short sale.
2. Broker commissions were omitted in the model. Therefore, you will see slightly lower profits than in this theoretical example, and your break-even point will be slightly below $170.44 for a MSFT share.
3. Our example uses only last closing prices, both for options and stock prices. Using MarketXLS with QuoteMedia data subscription enables you to monitor bid and ask prices for both stocks and options in real-time.