Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Trading In Bear Call Spread Options Strategy (Using Excel)

Written by Aaditya Shah (Individual Contributor) on 
Mon Mar 15 2021
 about Option StrategiesOptionsOptions strategies
Trading In Bear Call Spread Options Strategy (Using Excel) - MarketXLS
Boost your market advantage with MarketXLS. Real-time, enterprise-grade data. Book a demo or subscribe now!
Download Template →
Trading In Bear Call Spread Options Strategy (Using Excel) - MarketXLS

The options trader uses the bear call spread options strategy when having moderately bearish expectations on the market or stock. Under this strategy, the trader buys and sells the same asset’s options with the same expiry date but with different strike prices. This strategy involves limited profit and limited risk.Bear call spread options strategy

Features of Bear call spread options strategy

  • One long call and One short call

A Bear call spread options strategy consists of one OTM call and one ITM call option. The options trader goes long on the OTM option with a higher strike price and goes short on the ITM options with a lower strike price.

For example, currently, a stock is trading at $10. If the options trader uses a bear call spread options strategy, the trader will buy one call option at $13 and sell one call option at $7.

  • Limited risk and limited profit strategy

The options trader will pay a premium for buying the OTM call option and receive a premium for selling the ITM call option. As the premium received on the selling the ITM call option is higher, the trader is already in profit at the beginning of the trade.

In case of a rise in market price, the trader’s loss is capped to the difference between the two strike prices minus the call options’ net premium.

Similarly, in case of a fall in market price, the trader keeps the net premium received, which is also the maximum profit.

Let us understand this strategy better with the help of a detailed example.

The current share price of Apple is trading at $118. 1 Lot size is equivalent to 100 shares. To use the bear call spread options strategy, the options trader will:

  • Buy 1 OTM Call Option at $123 (Premium = $2)
  • Sell 1 ITM Call Option at $113 (Premium = $4)

Note: The premium taken above is on a hypothetical basis.

  1. Premium Paid = $2* 100 = $200
  2. Premium Received = $4*100 = 400

Net Premium Received = $400 – $200 = $200

Now, let us consider three different scenarios to better understand the risk-reward ratio in this strategy: –

Scenario 1: Stock price remains unchanged at $118 (above short call)

The OTM buy call option of $123 will not be exercised by the options trader as he will get a better price in the spot market. The buyer of call option would exercise his rights, as he can buy the share at a cheaper rate than the market.

Scenario 2: Stock price goes down to $ 111 (below short call)

Both the options would expire worthless as the traders would get better prices from the open market.

Scenario 3: Stock price increases to $ 125 (above the long call)

As the strike price is lower than the current market price, both parties would exercise their options.

Maximum Profit/Loss in all the scenarios is as given below:

Scenario Spot Price on Expiry Maximum Profit/Loss
1 $118 $200 – $500 = – $300
2 $111 $200
3 $ 125 $200 + $200 – $1200 = – $800

Note: This is a hypothetical example. The maximum profit/loss may vary depending on the premium involved.

Maximum Profit, Loss, and Breakeven in Bear call spread options strategy

The net premium received by the investor is his maximum profit under this strategy. The investor would earn a profit when the stock remains equal to or less than the short call option strike price.  Therefore, maximum profit is realized in the above case at any price below or equal to $113.

Breakeven would be achieved in the above example at $115, a price slightly above the short call.

The maximum loss of trader is capped to the upper strike price minus the lower strike price minus the net premium. In the above example, Maximum Loss = ($113-$123) *100 – $200 = $800.

How to use Bear call spread options strategy using MarketXLS?

MarketXLS is an excel based platform with 600+ functions for stock and options analysis. Using a Bear call spread options strategy with the help of MarketXLS is quite simple. The user only needs to take the following steps in the template provided by MarketXLS:

  1. Mention Stock ticker
  2. Enter the Expiry date of the option. A list of upcoming expiry dates is provided adjacent to the input.
  3. Enter the OTM Strike Price
  4. Enter the spread (the difference between higher and lower strike prices)

MarketXLS Template(Users only need to enter the information marked in yellow in the above image. The below image shows the results users will receive after inputting the required information above)Bear call spread options strategyMarketXLS will make things easier for the trader by directly allowing them to observe the maximum profit and loss they would make using the bear call spread option strategy. MarketXLS would also enable the trader to monitor the maximum profit and loss at different strike points, allowing him to choose the best strategy according to their requirements.

References:

https://zerodha.com/varsity/chapter/bear-call-spread/

https://www.fidelity.com/learning-center/investment-products/options/options-strategy-guide/bear-call-spread

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 for helping users collect the required information from various sources deemed to be an authority in their content. The images, copyrights, and trademarks if any are the property of their owners, and no further representations are made.

Improve your Options Trading

Use MarketXLS to stream real-time Stock Option Pricing in Excel. Save hundreds of hours searching for reliable financial information and get all the options data you need to make your trading decisions in real-time.

We respect your privacy and will never share your email.
Call: 1-877-778-8358
Ankur Mohan MarketXLS
Welcome! I'm Ankur, the founder and CEO of MarketXLS. With more than ten years of experience, I have assisted over 2,500 customers in developing personalized investment research strategies and monitoring systems using Excel.

I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and templates.

We respect your privacy and will never share your email.
MarketXLS is a complete Excel stock solution

Kevin Hsu

StockKevin.com

I have used lots of stock and option information services. This is the only one which gives me what I need inside Excel

Lloyd Lenase

Option Day Trader

MarketXLS is a data junkie’s dream. It gives me the flexibility to mine for hidden treasures.

Dave

Swing trader since 2011

I like to access historical closing prices on a particular date. That makes tracking performance easy.

Patrick Cusatis, Ph.D., CFA

Associate Professor of Finance - Penn State University

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord