Option StrategiesOptionsOptions strategies

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

Written by admin
Thu Apr 09 2020
MarketXLS Covered Put example strategy
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
MarketXLS Covered Put example strategy

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
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class B Common Stock logo

Rush Enterprises Inc. Class B Common Stock

Optionable: No
Market Cap: 2,753 M
Industry: Auto & Truck Dealerships
52 week range    
Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
BWX Technologies Inc. logo

BWX Technologies Inc.

Optionable: Yes
Market Cap: 4,846 M
Industry: Aerospace & Defense
52 week range    
Ritchie Bros. Auctioneers Incorporated logo

Ritchie Bros. Auctioneers Incorporated

Optionable: Yes
Market Cap: 6,928 M
Industry: Business Services
52 week range    
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
General Mills Inc. logo

General Mills Inc.

Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
CRA InternationalInc. logo

CRA InternationalInc.

Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
ConAgra Brands Inc. logo

ConAgra Brands Inc.

Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
Agree Realty Corporation logo

Agree Realty Corporation

Optionable: Yes
Market Cap: 5,522 M
Industry: REIT - Retail
52 week range    
UNITIL Corporation logo

UNITIL Corporation

Optionable: Yes
Market Cap: 928 M
Industry: Utilities - Diversified
52 week range    

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!