BasicsCryptocurrencyETFsOption StrategiesOptionsOptions strategies

Retirement Income Series: Find The Best Covered Call Option Using Marketxls

Written by kindkath
Sun Jun 13 2021
Retirement happy place
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Retirement happy place
 

Background

I like to sell covered calls against many of my stock positions. It’s a safe and easy way to boost the income I receive. As a retired person who does not yet receive social security, and does not have a pension, income from investments is very important to me! 

Today’s Income Challenge

Today I was looking at my Apple (AAPL) shares. My call on those shares expired last week so I’m looking to sell a new call today. I bought my 100 shares on 9/11/20 at $117.70. Today’s price is $126.88. I would like to keep them but, when I sell a call, there is some risk the shares could be called away. So I want to set my strike price higher than I think the shares will rise. If they do get called, at least it will be at a price I’m comfortable with.

Note: when I sell a covered call against my shares, I am being paid to take on a contractual obligation. I choose the strike price and expiry date, and receive a premium, which is paid in cash into my account. The contract says that the option buyer can buy my shares at the strike price any time between now and the expiry date. In practice, the option buyer would most likely exercise this right only if the share price exceeded the strike price at expiry. 

Back to AAPL. Based on my observation of recent share price movements, I would like to set a strike price between $135 and $145 because I don’t think the shares will rise above that in the next few months. It’s been range-bound between $115 and $135 for almost 6 months, and I’m not aware of any major catalyst which will boost the price much above that in the near future. But which specific contract will give me the best return? 

And I am looking at expiration dates between 52 and 388 days into the future. 

Tackling the Challenge

I can view the option table in my broker (Fidelity) software and do the calculations, one by one.

Or I can use MarketXLS to download the option chain into Excel and get exact returns in a format that makes it easy to compare. Let’s take a look.

Here’s the AAPL option chain from Fidelity, filtered to show the strikes and expiries of interest. Note that the call options are on the left-hand side.

options chain
From fidelity.com

At this point, my task is to find the row that gives me the best annualized return, and also has a strike price that makes it unlikely for the shares to be called away.

I can do each calculation. Like on the first row, using the last price: I would receive $149 in premium for a 52-day holding period. Based on my acquisition price: $149 / $11,700 = 1.27% absolute return. Since this return is over a 52-day period, I will annualize it to make comparisons to other expiry dates easier: (0.84% / 52) * 365 = 8.90% annualized return. Not terrible for a call on a stock that is also paying a small dividend. But can I do better? I could do these 2 calculations 21 times and get the answer for each row, but why don’t I let MarketXLS do most of the work for me? Good idea!

MarketXLS Can Help

Options Scanner

Here’s how to do it: open an excel workbook, type in the ticker and select that cell, and navigate to the MarketXLS section on the ribbon. Click on Utilities and select Option Scanner from the dropdown menu.

In about 2 seconds, this screen appears.

Option Scanner
MarketXLS Option Scanner Results

I’m going to filter the list a little bit. I will choose Calls in the Calls or Puts dropdown. And I will click on the Edit Filter button (bottom right) to set the strike price range from $135 to $145. 

filter editor for option scanner
MarketXLS Option Scanner Filter Editor

And then I’ll click Send these to Excel. That part will take about 90 seconds, so I’m gonna go refill my coffee.

OK. Back now. And here are the results which populated a new tab on the same spreadsheet.

The Analysis

It resulted 94 rows (I didn’t filter on expiration dates) and here is what the first few rows look like.

option chain

To do a proper analysis, I need to add 3 columns, and then enter some simple Excel formulas:

  • Days to expiry: (expiry date minus today)
  • Return: last price* divided by my acquisition cost per share of $117.70 
  • Annualized Return (value in the Return column divided by # of days to expiry) * 365

*Some may prefer to use the bid or ask price rather than the last price for the option. Bid and ask prices can be pulled into your spreadsheet easily; but the last price is automatically there, so I’ll just use that one.

Here’s what it looks like with those columns added, and I went ahead and sorted by annualized return (largest to smallest) because that is the next step:

option chain with calculated fields

As expected, the best annualized returns are for the $135 strike price. That strike is closest to current price and thus more likely to be exceeded; so the option buyers are willing to pay more premium for that likelihood. Now that I think about it, I’m much more comfortable with a $140 strike price, so let’s find the first time $140 appears in the strike column.

Chaching! It looks like the 19Nov$140 contract pays a very acceptable 8.38% annualized return. But, that expiry date is 6 months away and a lot can happen to impact the share price in that length of time. So let’s look at ones with fewer days to expiry. 

Continuing to scan down the list, the 15Oct and 17Sept expiries pay 7.81% and 7.39% annualized returns, respectively. For covered calls, I average about 5.9%, so getting more than 7% is definitely OK with me. I think I’ll sell the 15Oct$140 call for about $360 premium. 

option chain

After the Trade

I will monitor this option position carefully and look for opportunities to buy it back early and cheaply. Or, if it gets in trouble (share price creeps above $140), I might roll it (buy back this option and sell another one for a higher strike, further into the future, for a net credit on premium). Or I can just let the shares go and be happy enough with my profit. This is the reason I really love selling options…I can create income in a variety of market conditions, with very little risk. Yay!

But there are some risks with this strategy. One risk occurs if the share price declines substantially before the expiration. If you had not sold the call, you might be inclined to sell your shares before price declines further; but you are contractually obligated to hold the shares thru expiry. You could buy back the call, but this is often expensive. The other significant risk occurs if the share price shoots up like a rocket. For example, if AAPL price increases to $160 or so, I am still obligated to sell at $140. I’ve capped my potential profit. But, in general those risks are manageable and are minimized if you select the correct strike price.

PS – once you go through this process the first time, you can save your spreadsheet. The next time you want to find the best covered call, paste the MarketXLS results into the first 15 columns of your “template”, click Refresh All on the MarketXLS ribbon,  and let the formulas in the last 3 columns do their work automatically. No need to add them every time. Turn on your filters, sort, and voila! You will be on your way to making great decisions when you sell covered calls.

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.

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

Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

92.84
 
USD
 
2.48
 
(2.74%)
84
Rank
Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
69.32   
   93.45
Rush Enterprises Inc. Class B Common Stock logo

Rush Enterprises Inc. Class B Common Stock

51.55
 
USD
 
1.94
 
(3.91%)
81
Rank
Optionable: No
Market Cap: 2,753 M
Industry: Auto & Truck Dealerships
52 week range    
35.64   
   57.55
BWX Technologies Inc. logo

BWX Technologies Inc.

56.44
 
USD
 
1.35
 
(2.45%)
80
Rank
Optionable: Yes
Market Cap: 4,846 M
Industry: Aerospace & Defense
52 week range    
42.22   
   61.75
Ritchie Bros. Auctioneers Incorporated logo

Ritchie Bros. Auctioneers Incorporated

67.49
 
USD
 
2.43
 
(3.74%)
77
Rank
Optionable: Yes
Market Cap: 6,928 M
Industry: Business Services
52 week range    
48.44   
   75.28
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

24.33
 
USD
 
-0.16
 
(-0.65%)
77
Rank
Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
19.49   
   29.33
General Mills Inc. logo

General Mills Inc.

75.79
 
USD
 
0.34
 
(0.45%)
76
Rank
Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
55.38   
   75.96
CRA InternationalInc. logo

CRA InternationalInc.

89.15
 
USD
 
-0.17
 
(-0.19%)
76
Rank
Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
78.05   
   115.47
ConAgra Brands Inc. logo

ConAgra Brands Inc.

34.56
 
USD
 
0.32
 
(0.93%)
76
Rank
Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
29.80   
   36.67
Agree Realty Corporation logo

Agree Realty Corporation

73.53
 
USD
 
1.40
 
(1.94%)
76
Rank
Optionable: Yes
Market Cap: 5,522 M
Industry: REIT - Retail
52 week range    
60.78   
   74.67
International Money Express Inc. logo

International Money Express Inc.

21.77
 
USD
 
1.30
 
(6.35%)
75
Rank
Optionable: Yes
Market Cap: 781 M
Industry: Business Services
52 week range    
14.66   
   22.86

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!