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

Covered Calls – What They Are & How You Can Profit (With Marketxls Data)

Written by admin
Fri Apr 10 2020
Covered Calls – What They Are & How You Can Profit (With Marketxls Data) - MarketXLS
See how MarketXLS helps you take advantage in the markets.
Download Option Template →
Covered Calls – What They Are & How You Can Profit (With Marketxls Data) - MarketXLS

Wondering what covered calls are? In this article, we are going to tell you what a covered call is, when you would use it, and how MarketXLS can help you in applying the strategy.

What are Covered Calls?

Covered calls are a bullish investment strategy. You would usually consider applying the covered calls strategy when you expect that a stock price might rise, yet you are not too bullish 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 the price of the stock has bottomed out and will rise, but you are wary of taking an unprotected long position due to the market’s unpredictability. Applying the covered call strategy is a way to reduce risk.

Applying the covered call strategy involves making two separate trades:

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

A Covered Call Example

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

Step 1:

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

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

excel covered calls strategies

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. I would suggest adding the Excel’s “filter” buttons to column headers, so that we can focus exclusively on the call options and to be able to sort them as desired.

The resulting overview, filtered, looks like this:

filter covered calls options in MS Excel

Step 3:

We are interested in making some money by selling a call option on MSFT, so I will sort the filtered call options from the most expensive one to the cheapest one. Top results now look something like this:

sort covered calls options in microsoft excel

Step 4:

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 $31.5 for the option at the top of our list, @MSFT  220916C00165000, means that you can sell a lot of 100 of these options and receive a premium of $3,150. The strike price for these options is $165, 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 buy MSFT shares for no more than $165 per share, and they have this guarantee until September 16th, 2022, which is the expiry date.

Step 5:

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

covered calls example strategy using Excel

How Taking a Short Position in the Options Contract Looks

By taking a short position in the options contract @MSFT  200619C00175000, we can immediately receive a $605 premium ($6.05 per option * 100 options in a lot). To complete the covered call setup, we need to buy 100 MSFT shares, for $16,552. This results in our net cash position at:

$605 – $16,552 = -$15,947

Gain or Loss?

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

  1. In our bullish strategy, we have lowered our cost basis and reduced the risk from the stock price decreasing

Not considering the broker fees which your real-life trades inevitably incur and which you need to take into consideration, selling this call option moves our break-even point to MSFT share price of $159.47. This is because even though we have entered a long stock position, by buying MSFT at $165.52 per share, we have already received the option premium of $6.05 per option. As long as MSFT stock price remains above $159.47, our net cash result for these trades remains positive. Even if the stock price drops to, say, $150 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, buying 100 shares:

-$16,552 (initial expense from buying 100 MSFT shares) + $15,000 (selling the shares at a loss) = -$1,552

With the option trade, covered call strategy, buying 100 shares:

-$16,552 (initial expense from buying 100 MSFT shares) + $605 (received options premium) + $15,000 (selling the shares at a loss) = -$947.

  1. 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 bullish. We expect the stock price to rise a few dollars per share, not to soar 20% or more.

By buying the share at $165.52 and selling a call with a $175 strike price, our potential income maxes out if the options expire at the money, with MSFT trading at $175 per share.

If MSFT shares rise to $190, for example, we will not fully benefit from our long position initiated at $165.52 per share, because the option which we sold will expire in the money and we will be forced to sell the stock for $175 per share. This will be fully clear if we examine three potential scenarios.

Our Covered Calls Example – The Bottom Line

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

-$16,552 (initial expense from buying 100 MSFT shares) + $17,000 (closing the long position if we wish) + $605 (received options premium) = $1,053

In this covered calls case, we have made a net $1,053 from the trade, which is $605 more than if we only bought the stock, without selling call options.

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

-$16,552 (initial expense from buying 100 MSFT shares) + $17,500 (closing the long position if we wish) + $605 (received options premium) = $1,553

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

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

-$16,552 (initial expense from buying 100 MSFT shares) + $19,000 (closing the long position if we wish) + $605 (received options premium) – $1,500 (holder exercises his options, resulting in this loss for us, as the option expired $15 below the market price for the stock) = $1,553

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

Three things need to be considered if you are thinking about applying this covered calls strategy.

  1. Unlike potential income which maxes out as described above, there is a theoretical possibility that you could lose your entire capital invested in a stock, if the stock plummets to zero (again, it is theoretically possible). To protect against unplanned losses, you may consider a stop-loss order on your long stock position.
  2. Broker commissions have not been included in the model. Therefore, your potential profits will be slightly lower than in this theoretical example, and your break-even point will be slightly above $159.47 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. Start your free trial today!

Curious about covered puts?  Click here to learn more about how covered puts work and what they are.

Reverse Iron Condor Template
Get Real-Time Options Pricing in Excel

Use MarketXLS to stream real-time Stock Option Pricing in Excel. Save hundreds of hours searching for reliable financial information and get all the options data you need to make your trading decisions in real-time.

Download Sample Template
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

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

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord