Updated 11-28-21 with revised expectancy calculator. The previous expectancy calculator is still included but I wasn’t 100% on the long debit spread calculation. The revised expectancy calculates correctly. Feel free to explore both. God bless! Jeff
As agreed, this is the second post in a series that will include free excel spreadsheets. In this instance, a vertical spread options tracker.
Every sheet is useful for tracking your trades but perhaps its greatest feature is helping to stay on track. In my own options trading journey, often the next great strategy or indicator is a distraction from what I already know works. Thus, by making these spreadsheets available for everyone I hope to help you can avoid the discomfort of financial loss using that “little known trick the experts don’t want you to know about”. Be honest, you have about 10 emails right now with that exact title. I know, so do I ;).
In any case here is a link to the free vertical spread options tracker.
For those that wish to dive right in and start entering data you can feel free to skip the walkthrough below. For those like me that want to know every detail of how something works then proceed onward. I’ll do my best to discuss every aspect of the sheet either in the steps listed or the video above.
Step 1 – Create a copy
Step 2 – Append copy to a new sheet
*Admittedly, steps one, two, and three are likely something you didn’t need me to list as steps but they’re important so you always have the primary sheet as a functioning template. Pretty much, just copy and paste the primary sheet to a new sheet.
Step 3 – Rename sheet to your liking
*For details on how to complete steps 1 – 3 please have a look at my free covered call spreadsheet here or simply google it.
Step 4 – Personal investment criteria
In this instance, you’ll notice I’ve entered my round-trip commission costs for a vertical spread trade. Your commission costs may differ or very likely they’ll be zero. Either way make sure you have that set to round-trip costs or the commission costs will not accurately reflect what you’re paying.
The cell below it will need the starting account balance and finally, your desired maximum risk per trade. 5% is generally viewed as very aggressive with 1-2% being much more common. That said, for smaller accounts it isn’t uncommon to accept slightly more risk.
Step 5 – Enter contract quantity
Step 6 – Add the Ticker or Company symbol
Step 7 – Entry price
Step 8 – Exit price
* Please note – I list debits as a “-” minus sign and credits as a “+” sign. This is necessary to be sure all calculations function appropriately. That said, feel free to tinker with the functions to find the best fit for you. I’m 98% certain as I write this none of the formulas contain absolute references so moving or shifting shouldn’t be an issue.
You will also notice the vertical spread options tracker calculates each trades net profit or loss, as well as, cash flow and max risk per trade but we’ll talk more about these columns below.
With these complete you’ve officially added you’re first trade and set all the criteria necessary to start trading positively expectant vertical spreads. Let’s now take a look at a few of the additional features the spreadsheet provides.
Vertical Spread Options Tracker Key Features
First, please acquaint yourself with the idea of positively expectant options trading with this post from MacroOptions or by googling it. Its important to understand and in my opinion vital for long term options trading success.
Key benefit # 1 – Positive expectancy Calculator
Should you desire trading credit spreads as I do you’ll want to focus attention on the top calculator. Alternatively, should you wish to enter a debit spread you can easily see that information by using the calculator on the bottom.
While simple in construction, these two features will keep you from making that next lottery ticket pick. Sure, the lottery ticket sometimes pays off but in the long run its a losing proposition. Thus, it isn’t typically a desirable options trading strategy.
From the short vertical expectancy calculator you’ll need to enter the credit received and the probability of that trade finishing out-of-the-money. This can be determined using a many brokerage platforms or by simply googling “options calculator”. You may also see this described as probability of success or probability of profit. All of which refer to the same percentage you’ll need to enter here.
Once entered, you’ll be returned a theoretical gain and loss for 10 trades using the entered criteria. In the instance depicted above you’ll notice that this is in fact a positively expectant options trade.
Why? Because, trading this exact position or similar for 10 trades would return $412.50 while only losing $337.50. Additionally, to safeguard your account please take note that the stop loss to breakeven calculates out to equal the exact theoretical gain. This is important because theory and real life don’t always work out quite right, especially in the short term.
Key Benefit # 2 – Compounding risk/reward
The spreadsheet is designed to provide a max risk parameter for each trade that will increase as the account grows. This is paramount if you want to grow a small account to a large account quickly. In my own use of the vertical spread options tracker, I look to take positively expectant trades that use as much of the allowable risk as possible. Current max risk is provided in column H or inside cell L8. See below.
Key Benefit # 3 – Win, Loss, & ROI Percentages
For ease of use I’ve also included the necessary percentages to keep a close eye on necessary percentages. In my own experience, if that win percentage dips below 50% for very long the account will take some damage. At that point, it may be time to reevaluate the current market condition or possibly the trades being accepted.
Key Benefit # 4 – Fully Redesigned Expectancy Calculator
To use the new options expectancy calculator you’ll want to enter the data required in the yellow cells. If you’re looking at a credit spread you’ll want to leave the debit cell blank. Similarly, you’ll want to leave the credit cell blank if evaluating a debit spread. Enter the probability of profit (POP) and the strike width to theoretically calculate that trade 100 times. As you can see in the image above the credit spread example would produce an eye-watering ($1,765) dollar loss if I traded that 100 times.
Wrapping up
With that I think you’re on your way to trading smarter vertical spreads and putting the power of compounding and positive expectancy to work immediately. However, should you have a question please don’t hesitate to ask in the comments below or e-mail me at Jeff@optionboxer.com. If you e-mail I’ll likely respond within a few hours. If you comment it may take me a day or more in case I can’t login.
And for anyone interested in tracking, analyzing, and reviewing every trade, do check out the OptionBoxer Analyze and Journal spreadsheet. You may be surprised at all it can do.
If you’re reading this and directional plays aren’t your favorite then you may find value in this weekly iron condor strategy from Options Trading IQ.
God bless,
Jeff
Trackbacks/Pingbacks