Trading in Bear Call Spread Options Strategy (Using Excel)

In this article

A bear call spread is a moderately bearish options strategy: sell a call at a lower strike and buy a call at a higher strike on the same stock and expiration, collecting a net credit. The maximum profit is the net credit (kept if the stock finishes at or below the lower strike), the maximum loss is the difference between the strikes minus the credit, and the breakeven is the lower strike plus the credit. For example, selling a $113 call for $4 and buying a $123 call for $2 on a $118 stock collects $200 per spread, risks $800, and breaks even at $115. This article is educational, not trading advice.

See it in actionMarketXLS video

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos
Trading In Bear Call Spread Options Strategy (Usin - options strategy analysis and payoff diagram in Excel with MarketXLS

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. Because the premium received for the lower-strike call is higher, the trade opens for a net credit, which is the most it can earn.

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:

ScenarioSpot Price on ExpiryMaximum 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 = ($123 − $113) × 100 − $200 = $800.

How to use Bear call spread options strategy using MarketXLS?

MarketXLS is an Excel add-in with 1,000+ functions for stock and options analysis, including a bear call spread template. 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

  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)

(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) MarketXLS 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.

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.

Important Disclaimer

The information provided in this article is for educational and informational purposes only and should not be construed as investment advice, a recommendation, or an offer to buy or sell any securities. MarketXLS is a financial data platform and is not a registered investment advisor, broker-dealer, or financial planner. Always conduct your own research and consult with a qualified financial professional before making any investment decisions. Past performance is not indicative of future results. Trading and investing involve substantial risk of loss.

The Professional Investment Platform Inside Excel

Market data and options research tools in Excel

  • Option prices and Greeks in Excel
  • Historical options data in Excel
  • US stock and index options data
  • Prices and data on underlying stocks and indices
  • Use MarketXLS formulas in your Excel worksheets
  • Explore options research workflows in Excel
  • Excel formulas and sample worksheets

See MarketXLS in action

Bring this workflow into Excel.

Book a demo with our team to see how MarketXLS supports your market research.

Ankur
AnkurFounder & CEO, MarketXLS
Book a demo