Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS
Option StrategiesOptionsOptions strategies

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

Written by admin
Thu Apr 09 2020
Covered Puts – What They Are & How You Can Profit From Them (Marketxls Options Data) - MarketXLS
See how MarketXLS helps you take advantage in the markets.
Download Option Template →
Covered Puts – What They Are & How You Can Profit From Them (Marketxls Options Data) - MarketXLS

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.)

covered put instructions - get option chains from utilities of MarketXLS

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:

MarketXLS - filter put options in Excel

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:

Excel - sort put options by most expensive to the cheapest

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.

MarketXLS Covered Put example strategy

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.
Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class A Common Stock logo

Rush Enterprises Inc. Class A Common Stock

Optionable: Yes
Market Cap: 2,812 M
Industry: Auto & Truck Dealerships
52 week range    
PulteGroup Inc. logo

PulteGroup Inc.

Optionable: Yes
Market Cap: 11,760 M
Industry: Residential Construction
52 week range    
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

Optionable: Yes
Market Cap: 8,021 M
Industry: Specialty Retail
52 week range    
Moelis & Company Class A logo

Moelis & Company Class A

Optionable: Yes
Market Cap: 2,906 M
Industry: Asset Management
52 week range    
Houlihan Lokey Inc. Class A logo

Houlihan Lokey Inc. Class A

Optionable: Yes
Market Cap: 6,656 M
Industry: Capital Markets
52 week range    
Coca-Cola European Partners plc logo

Coca-Cola European Partners plc

Optionable: Yes
Market Cap: 25,255 M
Industry: Beverages - Soft Drinks
52 week range    
Dun & Bradstreet Corporation (The) logo

Dun & Bradstreet Corporation (The)

Optionable: Yes
Market Cap: 6,031 M
Industry: Business Services
52 week range    
Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

Optionable: Yes
Market Cap: 10,470 M
Industry: Specialty Retail
52 week range    
Columbia Sportswear Company logo

Columbia Sportswear Company

Optionable: Yes
Market Cap: 5,627 M
Industry: Apparel Manufacturing
52 week range    
CNX Coal Resources LP logo

CNX Coal Resources LP

Optionable: Yes
Market Cap: 7,105 M
Industry: All Other Business Support Services
52 week range    

More Features