Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator 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.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get 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

Oppenheimer Holdings Inc. Class A (DE) logo

Oppenheimer Holdings Inc. Class A (DE)

42.74
 
USD
 
1.08
 
(2.59%)
85
Rank
Optionable: Yes
Market Cap: 451 M
Industry: Capital Markets
52 week range    
28.41   
   50.14
Midland States Bancorp Inc. logo

Midland States Bancorp Inc.

27.57
 
USD
 
0.66
 
(2.45%)
83
Rank
Optionable: No
Market Cap: 589 M
Industry: Banks - Regional - US
52 week range    
23.48   
   30.60
Kroger Company (The) logo

Kroger Company (The)

48.41
 
USD
 
-0.78
 
(-1.59%)
83
Rank
Optionable: Yes
Market Cap: 35,325 M
Industry: Grocery Stores
52 week range    
41.82   
   62.78
Ingles Markets Incorporated Class A Common Stock logo

Ingles Markets Incorporated Class A Common Stock

100.76
 
USD
 
-0.34
 
(-0.34%)
83
Rank
Optionable: Yes
Market Cap: 1,910 M
Industry: Grocery Stores
52 week range    
74.63   
   102.88
Amdocs Limited logo

Amdocs Limited

89.99
 
USD
 
1.13
 
(1.27%)
82
Rank
Optionable: Yes
Market Cap: 10,525 M
Industry: Software - Infrastructure
52 week range    
69.79   
   90.77
Tecnoglass Inc. logo

Tecnoglass Inc.

30.37
 
USD
 
1.37
 
(4.72%)
81
Rank
Optionable: Yes
Market Cap: 1,349 M
Industry: Building Materials
52 week range    
16.05   
   34.08
Interpublic Group of Companies Inc. (The) logo

Interpublic Group of Companies Inc. (The)

34.46
 
USD
 
0.10
 
(0.29%)
81
Rank
Optionable: Yes
Market Cap: 13,330 M
Industry: Advertising Agencies
52 week range    
25.14   
   39.98
Guess? Inc. logo

Guess? Inc.

20.71
 
USD
 
-0.05
 
(-0.24%)
81
Rank
Optionable: Yes
Market Cap: 1,107 M
Industry: Apparel Stores
52 week range    
14.27   
   25.27
Bristol-Myers Squibb Company logo

Bristol-Myers Squibb Company

80.88
 
USD
 
0.60
 
(0.75%)
81
Rank
Optionable: Yes
Market Cap: 168,030 M
Industry: Drug Manufacturers - Major
52 week range    
55.76   
   81.44
Best Buy Co. Inc. logo

Best Buy Co. Inc.

86.47
 
USD
 
1.17
 
(1.37%)
81
Rank
Optionable: Yes
Market Cap: 18,947 M
Industry: Specialty Retail
52 week range    
60.79   
   112.96

More Features