Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Option StrategiesOptionsOptions strategies

Bear Put Spread Option Strategy (Explained With Excel Template)

Written by Pooja Tanwani (Individual Contributor)
Tue Mar 02 2021
Bear put spread template
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Bear put spread template
 

What is the Bear Put Spread?
A bear put spread is an options strategy that consists of buying one put option with a higher strike price and at the same time selling one put option with a lower strike price. Both the put contracts have the same underlying stock and expiration date. Traders can establish this strategy when they have a moderately bearish outlook.
Both the profit and loss are capped in this strategy. Maximum gain realizes if the underlying stock falls at or below the short put strike (lower strike). The maximum loss occurs if the underlying stock moves at or above the long put strike (higher strike). Both the put contracts will expire worthless if the underlying stock closes at or above the long put strike.

Let’s built Bear Put Spread option strategy in Excel using the MarketXLS template.

Step-1: provide the inputs
Provide the Stock Ticker, Expiry Date, ITM strike price, and Spread (higher strike – lower strike) for the strategy. Upcoming expiry dates are available for reference.
Here, we have built a bear put spread with MSFT. Currently, MSFT is trading at $241.98. We have selected an ITM strike of $ 245 and a spread of $5. Our spread will expire on 19-2-2021.

Bear put spread step-1

Step-2: Setup of the Bear Put Spread strategy

  • Bought one $245 ITM put option contract of MSFT at $380 (3.8*100).
  • Sold one $240 OTM put option contract of MSFT at $93 (0.93*100).
  • Net premium paid = $287 ($380 – $93).
  • We have built net debit strategy and paid $287 upfront to enter into the trade.

Bear put spread step-2

Step-3: Payoff schedule and diagram at the expiration date

Here you will find the payoff schedule and payoff diagram alongside. The maximum loss of $287 occurs if the underlying price moves up at or above $245. And the maximum gain of $213 realizes if the underlying stock goes down at or below $240. The profit and the loss are limited in this strategy as the benefit from the long put is offset by the loss on the short put if the underlying stock goes down.

Bear put spread step-3

Maximum profit
Maximum profit is equal to the difference between the higher strike and the lower strike minus the net cost incurred (excluding commissions).
Maximum profit= ($5 – $2.87)* 100 = $213.

Maximum loss
Maximum loss is equal to the spread’s cost—the amount paid to enter into the trade. Therefore, the maximum risk is limited to the amount you paid upfront while entering into the transaction.
Maximum loss= $287.

Break-even
Breakeven is the point of zero profit and zero loss. The underlying stock should move down to the long strike minus the net premium paid to achieve the break-even. Hence, if the underlying stock closes at $242.13 ($245 – $2.87) on the expiration, there will be neither profit nor loss on our bear put spread.

Bottom line
If the trader expects the stock to fall within the particular range till the expiration date, then bear put spread can be a good deal. Selling a put contract helps to reduce the cost of the trade. The risk in bear put spread is lesser compared to buying a put contract alone. The maximum potential profit is also limited; hence, the long put’s gain is offset by the short put’s loss if the stock falls sharply. Since both profit and loss is limited we can square off the position before the expiration of the contract as soon as the stock price hits the target.

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 trademarks,if any, are the property of their owners, and no representations are made.

Reference

To know more about options trading, click here.

To know more about bear put spread, click here.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

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

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

116.74
 
USD
 
6.17
 
(5.58%)
83
Rank
Optionable: Yes
Market Cap: 8,757 M
Industry: Specialty Retail
52 week range    
63.45   
   142.78
Academy Sports and Outdoors Inc logo

Academy Sports and Outdoors Inc

47.63
 
USD
 
2.31
 
(5.10%)
81
Rank
Optionable: Yes
Market Cap: 3,614 M
Industry: Beer, Wine, and Liquor Stores
52 week range    
25.10   
   51.51
Texas Roadhouse Inc. logo

Texas Roadhouse Inc.

89.63
 
USD
 
3.72
 
(4.33%)
79
Rank
Optionable: Yes
Market Cap: 5,743 M
Industry: Restaurants
52 week range    
68.58   
   96.41
Destination XL Group Inc. logo

Destination XL Group Inc.

5.60
 
USD
 
0.10
 
(1.82%)
79
Rank
Optionable: No
Market Cap: 340 M
Industry: Apparel Stores
52 week range    
3.27   
   9.00
Annaly Capital Management Inc logo

Annaly Capital Management Inc

19.42
 
USD
 
0.02
 
(0.10%)
77
Rank
Optionable: Yes
Market Cap: 8,363 M
Industry: REIT - Diversified
52 week range    
5.45   
   22.78
BJ's Wholesale Club Holdings Inc. logo

BJ's Wholesale Club Holdings Inc.

76.06
 
USD
 
0.04
 
(0.05%)
77
Rank
Optionable: Yes
Market Cap: 10,268 M
Industry: Specialty Retail
52 week range    
51.45   
   79.69
Waste Connections Inc. logo

Waste Connections Inc.

139.06
 
USD
 
1.38
 
(1.00%)
76
Rank
Optionable: Yes
Market Cap: 35,409 M
Industry: Waste Management
52 week range    
113.50   
   148.20
STORE Capital Corporation logo

STORE Capital Corporation

31.90
 
USD
 
0.27
 
(0.85%)
75
Rank
Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
Sprouts Farmers Market Inc. logo

Sprouts Farmers Market Inc.

28.58
 
USD
 
1.51
 
(5.58%)
75
Rank
Optionable: Yes
Market Cap: 2,909 M
Industry: Grocery Stores
52 week range    
21.18   
   35.34
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

107.01
 
USD
 
6.17
 
(6.12%)
75
Rank
Optionable: Yes
Market Cap: 7,483 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   126.93

More Features