How to Use the BCI Portfolio Setup Spreadsheet to Craft Our Put-Selling Portfolios – September 25, 2023
When we plan our covered call writing and cash-secured puts trades, we must focus in on diversification and cash allocation. In order to accomplish these goals, in April 2023, BCI introduced the BCI Portfolio Setup Spreadsheet with our CEO streamlined approach to covered call writing. This article will detail how the spreadsheet can also be used when establishing our cash-secured put portfolios. We will assume a cash available amount of $100,000.00 and will use 3 securities.
Entry share prices for calls and puts
For covered call writing, we enter the share price we paid for the securities or its current market value at the time of the trades. For put-selling, we enter the amount of cash our brokers require us to place into our cash accounts to secure the put trade, per-share. The formula for the cash required is:
[(put strike – put premium)]
Real-life example with 3 Select Sector SPDRs (current price) on 4/20/2023
- XLE (Energy): $86.24
- XLB (Materials): $81.51
- XLF (Financials): 33.44
Step #1: check put option-chain (XLE shown)
The $83.00 out-of-the-money put strike shows a bid price of $1.07. Here are the strikes, bid prices and net amounts [(put strike – put premium)] we will factor into the spreadsheet:
- XLE: $83.00 strike at $1.07 ($81.93)
- XLB: $80.00 strike at $1.23 ($78.77)
- XLF: $33.00 strike at $0.43 ($32.57)
Information entered into the top portion of the Portfolio Setup Spreadsheet
The spreadsheet shows a guideline range for a cash reserve of $2,000.00 – $4,000.00 for potential exit strategy executions.
Information entered into the blue cells in the lower portion of the Portfolio Setup Spreadsheet with resulting calculations
Note the following spreadsheet calculations (appear once blue cells are populated):
- Each position has $33,333.33 allocated
- Based on current price-per-share, the # of shares that can be secured for put sale is calculated (407 for XLE)
- The spreadsheet will round up or down to the nearest 100 (1 option contract = 100 shares: 400 for XLE)
- This # will also determine the # of contracts to be sold (4 for XLE)
- The cash required to secure these rounded shares is calculated ($32,772.00 for XLE)
- The total investment (all 3 positions) is calculated ($96,850.00)
- Cash reserve for exit strategies is calculated ($3,150.00)
- ***The spreadsheet allows for adjustments if our guidelines are not met)
Discussion
The BCI Portfolio Setup Spreadsheet can be used for both covered call writing and selling cash-secured puts. For puts, we use the [(put strike – the put premium)] as our entry prices (cash requirements). This will allow us to adhere to our diversification and cash allocation portfolio requirements.
Author: Alan Ellman