Several years ago, I created a spreadsheet to help readers who want to utilize a covered call strategy in their portfolios. Recently, I added cash-covered puts to the spreadsheet. The spreadsheet is a Google Sheet that I update monthly. Here is this link: Utility Forecaster Options Spreadsheet.

In the past, I have provided instructions on using this spreadsheet, which I will repeat here. The first part of the article deals with the covered calls, followed by instructions for using the cash-covered put tab.

Covered Call Instructions

As I discussed previously in “How To Get A Conservative 8% Yield From Our Portfolios“, this new tool integrates FactSet’s proprietary data along with option chain data of a holding to show the potential yield and return of various options. One of our lifetime Wealth Society members and regular Utility Forecaster Stock Talk contributors, “Lisa G”, began modifying my original Excel spreadsheet to work on Google Sheets.

The benefit of this is that Google Sheets is a spreadsheet program included as part of the free, web-based applications available from Google. It is easily shareable to anyone with a link. The downside is that it isn’t quite as functional as the Excel spreadsheet, but most of the functionality is there.

I have taken Lisa’s excellent modification of the original Excel spreadsheet and further refined it. I have put all optionable portfolio holdings in there. I want to point out that the spreadsheet is locked from anyone being able to modify it. If I didn’t do that, anyone with the link could make any changes they wanted, including deleting all the content. Locking means I am the only one who can change it, but you can make your own copy that you are then free to change.

With that caveat, here is the link (again): Utility Forecaster Covered Calls Spreadsheet. I will offer specific instructions below. If you have any trouble, please comment in Stock Talk and I will try to help you solve the problem.

General User Guide – Portfolio Tab

The first thing you need to do if you want to modify it is either make a copy of the sheet or download it into Excel if you prefer to work with Excel. Both choices can be found under the “File” tab. For Excel users, I have some specific instructions near the end. But first, let me walk users through the use of the spreadsheet.

Note that you don’t have to modify the spreadsheet at all. The values in there do change daily, but they will provide you with some direction on which option trades are currently the most attractive.

This is what you should see when you open the link.

At the bottom of the page, you will see four tabs (the new cash-covered put tab is not shown in this screenshot). The first tab – “Portfolio” – contains all of the portfolio holdings. The second – “Single” – will contain option trades for the most recent Spotlight company. This is useful if you want to compare numerous yield/return combinations for one company. The third tab, “Sandbox”, is where I will paste in raw option data from Fidelity that I will then use in the first two tabs.

The green cells in the spreadsheet are inputs. Those represent values you must provide. When I am using the tool in Excel, it automatically retrieves the dividend information, but Google Sheets doesn’t have the ability to do this. So, if you are editing it yourself, you will need to provide the latest quarterly dividend, your desired annual yield and desired premium for having your shares called away, and then the expiration date, strike price, and bid for an option.

Column A is the stock symbol. Once you copy the sheet, you can input other symbols in there to fit your own portfolio. Column B is the company name, which also had to be entered by hand. Column C, the current price, is retrieved automatically by the spreadsheet according to the symbol you entered in Column A. I have sometimes seen an error in this column, particularly for foreign companies. But most of the time it works as intended.

As mentioned, the dividend information in Column D must be supplied by the user. Normally, this is quarterly dividend information. Column E then converts that into an annual yield. For companies that pay dividends on a monthly basis, you need to multiply by 12 in Column E instead of 4.

Column F, “Inc Yld Req” calculates the difference between the yield you stipulated in Cell F2 (8% in our case) and the annualized yield from Column E. Column G then converts that into an annualized call premium you would require in order to achieve that target. For the first entry, Alliant Energy (NYSE: LNT), the required annual call yield to reach 8% is 5.18%, which translates to $2.80 per year.

Column H, “Req Strike”, merely multiplies the current share price by the premium you stipulate in Cell H2. Note that this isn’t the overall return you would get, because you are going to get a call premium, and depending on how far away the expiration date, one or more dividends. The purpose of this column is merely to provide a ballpark estimate for the strike price you are looking for. Again, for Alliant, $62.04 reflected a 15% premium on the day I entered this value, but I know I can accept less than this and still achieve a 15% return because of the call premium and dividend. In that case, I would probably select a $60 strike price for Alliant, although in the spreadsheet I selected a $55 strike that didn’t meet my objectives. If I was trading Alliant, I would test different combinations until my objectives were met (or I got as close as I could).

The next three columns contain three key pieces of information about the option. I copy them directly from Fidelity. For Fidelity users, go into “News and Research”, select “Options”, then the “Trading Ideas” tab, and finally “Strategy Ideas.” Select “Covered Call” from the “Available Strategies” category, and then enter your stock symbol and time frame. You will see a screen that looks like this:

You are interested in the “Expiration”, “Strike Price”, and “Call Bid” columns. I also take note of the Ex-Date when selecting an expiration date, because I don’t want to have a position called away just before going ex-dividend.

For a single option, you can just copy across those three columns and paste that data into the Google Sheet. The rest of the columns in the spreadsheet, Columns L-R, are calculated based on what you pasted in.

A couple of items of note. The “Annualized Call Yield” tab is going to take the call yield (Option Price/Stock Price), and then annualize it. So, if the yield was 2% in six months, it’s going to annualize that to 4%. It will then add that to the dividend yield to calculate a total annual yield. Of course, this implies that you are going to have to sell a new call every time one expires (or roll it forward before expiration, as explained in a recent article).

Also be aware that this option data changes every day, so the calculated return numbers are only valid for current option data that you paste into the spreadsheet.

Column P, “Ret Potential” is calculated as follows. The strike price is divided by the current stock price, less the option price and estimated dividends. This is consistent with the way FactSet and many other data providers calculate returns. There are variations of how to calculate returns, so feel free to plug in your own formula. For example, some might add the dividends to the strike price instead of subtracting them from the current stock price. Note that this is the maximum return potential if shares are called away. If they aren’t called away, you will sell new calls as the old ones expire.

Column Q annualizes the return potential. If a position yields a potential return of 10% in four months, it will annualize that to 30% (since you could hypothetically do this three times a year).

Column R requires a bit of explanation. Because we can only estimate dividend dates that haven’t been announced, there’s no guarantee of a specific number of dividends being paid by the expiration date. For example, if I sell a call with an expiration date in five months, the company might pay one dividend by then, or it might pay two. The way I have addressed this is to simply assume a smooth dividend increment every day.

For example, if a company yields 4% annually, and the expiration date is 150 days away, I am going to assume that the fraction (150/365) of that annual dividend was paid by expiration. In other words, I will assume that the position earned 1.6% of the total annual dividend as an approximation when calculating returns. In reality, it will be slightly lower or higher than this, depending on whether the company paid one or two dividends during that time.

Once you paste in an option and look at the overall yield and return, you then need to decide whether that fits your needs. If not, select a different option and try it. Going back to the Alliant case, I would have retried various $60 call options to see if I could find one that met my yield and return requirements.

General User Guide – Single Tab

The “Single” tab at the bottom is set up exactly like the Portfolio tab, except there is a single company and many options that have been pasted in. It looks like this for Comcast (NSDQ: CMCSA):

When using this tab, I will just copy a block of options from Fidelity (as in the Fidelity screenshot) into the “Sandbox” tab, strip out any unneeded headers, and then just copy over the appropriate three columns as needed. The headers have to be included, or all of the data may be pasted into a single cell. Fidelity breaks the expiration dates into “60 Days or Less”, “Between 60 and 180 Days”, and “180 Days or More.” If I want a full picture, I can copy all options from these three categories to get everything from short-term options all the way up to options that don’t expire for a couple of years.

Your broker may display options differently. The columns may be in different order, so you may want to adjust your version of the spreadsheet to match for an easy cut and pasted. But as long as you can cut and paste the relevant data, you can quickly screen through many choices. It will be slower if you have to hand enter them, but that can also be done.

Incidentally, ignore the extremely high annualized yield and returns shown above. I copied those option chains in three weeks before I wrote this, so the values have changed. But the spreadsheet automatically annualizes yields, so it is using option chains from three weeks ago when the 10/16/20 option, for example, was still a month out. Every time the spreadsheet is opened it automatically updates the “Days to Exp” tab, but using old option data will cause the yield number to be larger than it should be. This shouldn’t be a problem because you should only use current options data.

For Excel Users

If you do download the spreadsheet to Excel, Column L, “Days to Exp” isn’t going to work because the language in Excel is different. All of the columns after that, N-R, are going to show an error as well because they rely on Column L. To fix this, change the function in Cell L4 to read “=I4-TODAY()”, without the quotation marks, and then copy that down to the end of the spreadsheet.

Each row should have the corresponding row number. For example, in Cell L10, the “Days to Exp” cell should say “=I10-TODAY()”. If the number displayed in the cell is a date instead of a number (which represents the number of days until the option expires), then look up at the top and make sure the format for the cell is “Number” and not “Date.”

Cash-Covered Put Tab

You can find the cash-covered put trades in the former covered call spreadsheet, which I have renamed “Utility Forecaster Options Spreadsheet.” There are now four tabs in the spreadsheet. One is for covered calls, one is reserved for the monthly spotlight company, one is a tab I use for cutting and pasting data, and now the new tab, “Puts”, is for cash-covered put trades.

Note that I am still making small tweaks to the spreadsheet but let me walk you through the current version. Here is a screenshot from 9/10/24 after I pasted in the option contract details (Columns D-F).

Column H is the put yield, which is the amount we will get paid, divided by the amount we are risking. For example, if we sell a put with a strike price of $10, and we get paid $1.00, the total money we are risking is $9.00. That is the most we could lose. We will have to buy the stock for $10 if we are assigned, but we were already paid $1.00. So, the yield in this case is $1.00/$9.00 = 11.1%.

Column I annualizes the trade yield, which gives you a consistent comparison between trades. That’s because one trade may have a duration of three months and the next a duration of a year. But you can do multiple short-term trades per year. That’s why trades are annualized.

Column J — “Approximate Assignment Risk” — was the biggest challenge. It’s the reason I hadn’t created this spreadsheet before. But I am working with a clever IT professional who created an approximation for the assignment risk. I have checked around multiple trades, and it’s always pretty close. This assignment risk is based on historical volatility but is merely a guideline. During a market crash, you might get assigned on all of your trades that had a calculated 30% assignment risk. That’s why we will not trade on margin.

If you want to double-check the assignment risk, your brokerage probably has a tool for checking. I know Fidelity does. You can also do a quick check at https://www.stockoptionschannel.com/. Input the stock symbol and it will present you the option chain. Select the cash covered put you want, and it will tell you the assignment risk. Here are two screenshots from that site showing how this works, using Growth Portfolio holding Archer-Daniels-Midland (NYSE: ADM) as an example. After entering the stock symbol, you will get this screen of option chains:

If we select the December 20, 2024 cash covered put contract, we see a screen like this:

Note that the $57.50 contract at the bottom has an annualized yield of 14.14%. I would note that this underestimates yield, because it is being calculated based on the $57.50 contract and not the actual money at risk, which is the contract less the option premium.

The calculated chance the put will expire worthless is 62%. This means your assignment risk is 38%. This is what we want. If the put we sold expires worthless, then we made the full profit on the put. In this case, the bid was $2.25, so you would have received $225 for selling one put, and that obligates you to buy 100 shares of ADM only if the share price goes below $57.50.

In this case, the ADM trade meets my desired parameters of a double-digit annualized yield and a <40% assignment risk.

There are two more pieces of information worth mentioning in the new cash-covered put tab. One is Column L, “Discount if Assigned.” That is how much of a discount you would get based on the current price of the stock if you were assigned.

For example, the NRG trade in the table meets our criteria with a 12.6% put yield and a 29.5% assignment risk. But, with shares trading at $77.94, our assignment cost on this $70 put — accounting for the $5.50 put premium — is only $64.40. That’s a significant 17.2% discount from the current price of NRG. In this case, that represents a really nice combination of trade parameters.

Finally, a word about Column M, “Beta.” This is a measure of a stock’s volatility relative to the S&P 500. In general, higher beta stocks are riskier, but they also command higher option premiums. Because we tend to trade low-beta stocks, in keeping with the objective to keep downside risks low, sometimes it is hard to find a trade that meets the desired parameters.

For example, the spreadsheet presently shows a trade for CMS Energy (NYSE: CMS), which has a beta of 0.40 (not shown in the screenshot). This means CMS is only 40% as volatile as the S&P 500, and the option premiums reflect that. Remember, people buy options to mitigate risk, and CMS would be viewed as a low-risk stock. As a result, the option premium for this trade only provides an annualized yield of 2.8% with a 49% assignment risk. I would skip that trade, and perhaps instead opt for a covered call on CMS.

But if you look at the averages for the entire portfolio, it comes very close to meeting my target parameters. Note that the NRG trade has a beta greater than 1, which is why the trade parameters are better.

Trades like CMS are preventing the overall portfolio average from reaching the 10%/40% parameters I mentioned, and that is also reflected by the overall 0.73 beta for the portfolio. However, plenty of portfolio companies do meet those targets, and those are where I would focus attention.

Using cash-covered puts can be a highly effective strategy for generating income with manageable risk. By selling puts, you are paid to potentially buy stocks at a lower price while also earning premiums, which adds to your returns.

However, it is essential to understand the risks associated with market volatility and to avoid trading on margin, as this can significantly increase your exposure to losses. With careful planning and adherence to conservative trading guidelines, cash-covered puts can be a powerful tool in your investment arsenal, complementing other strategies such as covered calls to optimize income and reduce risks.

I hope the new cash-covered put tab helps you in making those decisions.

Final Thoughts

Hopefully this explanation has been clear enough for most subscribers. This tool is designed to add at least a few percentage points to your annual returns. If you do run into a problem or have a question or want to make a suggestion, please reach out on Stock Talk.