Uses Excel filtering, sorting, and creation of some calculated fields.
I invest in dividend stocks because I want to create an income stream that will pay for my expenses when I retire in a few months. You can read about my basic approach to dividend investing and using MarketXLS in that context here: https://www.marketxlswp.com/stock-portfolio-in-excel/
Ultimately, I would love to create such a large stream of income that I would never have to sell a single share of stock but could live entirely off the income generated by the stocks. I’m making good progress toward that goal, but I may not be able to get there on dividends alone. A few years ago, I learned of a way to boost the income created by my stocks, by selling covered call options.
The covered call is one of the most conservative options strategies you can find.
If you own at least 100 shares of a stock that allows options trading, you can sell a covered call on it. Here’s an example to illustrate how it works.
I bought 100 shares of Johnson and Johnson (JNJ) on 5/30/18 at $121.53 per share. JNJ pays a dividend of $3.60 per share each year (and they have raised it every year for 56 years which is REALLY nice of them), so I will receive $360 per year on my investment of $12,153, which is a yield of almost 3%. This is one of the lowest-yielding stocks I own. Based on my calculations, I need about 6% income from my investments to live the way I want to during retirement. I could just buy stocks which yield at least 6%, but I like JNJ’s history of dividend increases, and I will need some reliable annual increases to make sure my income keeps up with inflation. It would be so good if I could make my JNJ stock work a little bit harder for me! Enter the covered call.
When you sell a covered call, you establish a price (the strike price) at which you are willing to sell the stock, and a date for this transaction to occur (the option expiration date), and then someone else pays you money to obligate you to that sale….BUT ONLY IF the stock price is greater than the strike price on the expiration date.
I don’t really want to sell my 100 shares of JNJ, but I would be OK letting them go if I could make a reasonable profit on them. In fact, I would be OK selling them at $130 or greater. Today is 12/22/18 and the share price is currently at $128.09.
Here is the JNJ option chain for strike prices in the range I’m interested in, for 2 upcoming expiration dates, as seen on the Fidelity website. There are expiration dates every Friday for JNJ, but I am showing only 2 to keep it simple. I prefer short-term options because there isn’t as much time for something unpredictable to happen to the share price (like a lawsuit or a bad earnings call).
Looking at the bid price in the first row, I see that someone is willing to pay me $1.04 per share (one call is 100 shares so this is really $104.00) if the share price exceeds $130 (strike price) on 12/28/18 (expiration date). I can do the math and see a few different possible outcomes if I sell this specific covered call:
Scenario 1: The share price is less than or equal to $130 on 12/28/18: I receive $104 (less Fidelity’s $5 commission) today in option premium and get to do whatever I want to do with it. I get to keep my 100 shares of JNJ. And after 12/28/18, I am free to sell another call on those shares. The after-fee profit of $99 on my $12,153 investment is small…0.81%, but it occurs over a 6-day period. If this is annualized (which is necessary for comparing the outcomes of different rows on the chart), it would be a 52% return on investment. That would be incredible, but it is not likely to happen. It would mean selling a similar option every 6 days for a year. The purpose of annualizing is to allow for comparison.
Scenario 2: The share price exceeds $130 on 12/28/18: I receive $99 today in option premium ($104 – $5 commission) and get to do whatever I want to do with it. I also have to sell the shares for $130 on 12/28/18. In reality, it is like I am selling for $130.99 per share. Based on my share price of $121.53, I would make a profit of $9.46 per share. This would be a 7.8% profit, and I would have only held the shares since 5/30/18 (206 days). To annualize this, to make it easier to compare to other rows on the options chart, I calculate (7.8%*365)/206 = 13.8% profit on an annualized basis. This is acceptable to me.
Scenario #1 is my preference, but I would be OK with scenario #2. However, I wonder if the next row is better. If the stock price does shoot up, I’d rather get paid $131 or $132 instead of $130. Or maybe the $130 strike price with the 1/4/19 expiration date is better. I can do the math and annualize each one and figure out which is best, but it takes a bit of time.
If I could only get this information into an Excel spreadsheet, I could evaluate every row and find the best one for my situation. Unfortunately, Fidelity does not support the exporting of options tables.
This is where MarketXLS can help things out quite a bit! (I bet you are thinking “Finally she’s getting to the point!”)
MarketXLS has a wonderful utility which allows you to import ALL available options for a given stock. And get live option prices in Excel.
Here are the steps I use to import the data and to make the best data-driven decision.
Open a new Excel spreadsheet and type in the ticker(s) for the stock I am interested in.With the ticker cell selected, click on MarketXLS, Utilities, then Option Chain.All of the available Call and Put options will populate a separate tab on the spreadsheet.Since I’m only interested in Calls, it will help to get rid of the Puts. Here’s a snippet of what MarketXLS produced. Contract symbols with a “P” in the center of the name are Puts (I highlighted them pink), and those with a “C” in the center of the name are Calls. I can filter on the contract symbol column to show only the ones containing “P” and then delete those, and clear the filter. Only the Calls will remain.
Next, I want to add 5 columns (green column headers below). Note that the next 3 screenshots are filtered to show only strike prices between $130 and $145, and expiration dates of 12/28/18 and 1/4/19. This is to keep things simple and make for easy comparisons to the Fidelity screenshot above.
Today’s date
Number of elapsed daysThis is the # of days between today and the expiration date of the option (in the screenshot below, in column P, for row 87, I’ve used the formula “=f87-o87” to calculate this).
My cost basis
% profitThe % profit I would make on the transaction (bid amount divided by cost basis). In my case, I will pay $0.05 per share in fees so I deduct that from the bid amount. That way I’m evaluating the profit after fees. (in column R, row 87, this is “(b87-0.05)/q87”.
Annualized % profit
This considers the number of days before expiration, and allows for comparison (in column S, row 87, this is “(r87*365)/p87”.
Sort by column S to show the largest annualized profit at the top. This shows the profits to be made if Scenario 1 prevails. Clearly, row 87 is the winner!But what if scenario 2 prevails? Do I really want to let my shares be potentially called away for $130 when I could possibly sell them for more than that, by choosing a higher strike price?
Hmmmm……I really need to evaluate Scenario 2 just as objectively.
So I’ll add 5 more columns (blue column headers below).The date my shares of JNJ were acquired.The # of days I would have held the shares if they are called away on the expiration date (f87-t87).The dollar amount of the profit I would make if the shares were called away (M87+b87-0.05-q87)*100The % profit this represents (v87/(q87*100))The annualized % profit if called away (w87*365)/u87
The above chart shows the results for the Scenario 2 analysis after resorting (I’ve hidden the Scenario 1 columns to make the image smaller). It looks like the $145 strike price with the 12/28/18 expiration date would be the best outcome if Scenario 2 prevails. But I would lose money on this one if Scenario 1 prevailed since the bid price is $0.00
Where is the sweet spot? If you have a strong conviction that either scenario 1 or 2 will prevail, then you could use one of the rows we’ve already identified. Personally, I’m not the world’s greatest predictor of stock price movements. I want to find a place where I profit on the covered call, no matter what the share price does. So I just find the rows with the largest amount of annualized profit from Scenario 1, and the same for Scenario 2, and look for something that pays at least 15% either way.
Row 654 below is the winner, for me at least! This is the $133 strike with the 1/4/19 expiration date. Whether scenario 1 or scenario 2 prevails, I will earn around 16% annualized profit. That works for me!
Now that I’ve done this a few times, it takes me about 5 minutes to set up this evaluation for each stock. I’m pretty good at Excel and I think that is a prerequisite for doing this. I really love having an objective, methodical way to determine the best-covered call to sell, that will allow me to achieve my income goals, no matter what the stock price does. MarketXLS lets me make that decision in 5 minutes!
For those not too familiar with options, I do want to tell you some risks.
Only sell a covered call if you are willing to sell the shares for the strike price.
If the price shoots WAY up prior to expiration, you are obligated to sell at the strike price (unless you buy back the option contract, which may cost a considerable amount). You won’t lose money but you will have limited your profit.
If the price shoots WAY up, the shares could get called away before the expiration, although this is rare.
If the ex-dividend date occurs prior to the expiration date, AND the price shoots up, this increases the likelihood of the shares being called away prior to the ex-div date. In this case, you would not receive the dividend.
- Consider expiration dates carefully.
If an ex-div date or an earnings report date occurs prior to expiration, this can introduce some volatility into things that you may not want.
I like to keep my expiration dates less than 60 days out. Crazy things happen all the time (lawsuits, labor strikes, layoffs, economic reports, regulatory changes, heated foreign policy rhetoric) that impact prices, so I want to minimize the time available for the crazy things to happen.
- It’s great if you trade options in a regular or Roth IRA, but if held in a taxable account in the US:
If the option expires, the option premium is taxed as a short-term capital gain.
If the shares are called away, the option premium would be applied to your cost basis, lowering it, and thereby increasing your profits and the tax owed on those profits.
- I would recommend including the cost of fees in your calculations in column R. Fees can eat up a lot of option profits if you don’t keep an eye on them.
Oh! And while my example uses the bid price (because I know for a fact that someone is willing to pay me that amount), you can enter your Sell order with a price somewhere between the Bid and Ask, and maybe do a little bit better than what I’ve illustrated.
Good luck with your options trades!
** Note from MarketXLS. This article is kindly contributed by longtime MarketXLS user. The functionality shown in this article is for representation only and may be different in a different version of MarketXLS. If you have any message for the Author please contact us **
Like what you see?
Click Below to See Our Full Video with Option scanner, option functions and this template.
