Excel Formulas for Call Spread Purchase

A call spread purchase is an option strategy that involves buying a call option and selling another call option of the same underlying asset and expiration date, but with a higher strike price. This strategy is used when the trader expects a moderate rise in the price of the underlying asset, but also wants to limit their risk and cost.

The trader who buys the call option pays a premium to acquire the right to buy the underlying asset at a certain price (the strike price) before a certain date (the expiration date). The trader who sells the call option receives a premium for agreeing to sell the underlying asset at the same strike price if the buyer exercises their right. The difference between the premiums paid and received is called the net debit or credit of the trade.

The maximum profit of this strategy is achieved when the underlying asset’s price is above both strike prices at expiration. In this case, the trader who bought the call option can exercise it and buy the underlying asset at a lower price than its market value, while selling it at its market value. The trader who sold the call option will have to deliver them at their higher strike price, resulting in a loss equal to their premium received. The net profit will be equal to (strike price – market value) – premium received.

The maximum loss of this strategy is limited to the net debit paid for buying and selling the call options. This occurs when the underlying asset’s price is below both strike prices at expiration. In this case, neither of them can exercise their options, and they will lose their premiums paid.

The breakeven point of this strategy is calculated by adding or subtracting the net debit from either strike price. For example, if a trader buys a call option with a strike price of $50 and sells another call option with a strike price of $55, and pays $2 for each option, then their breakeven point is $52 ($50 + $2 – $2). This means that if they expect that both options will expire worthless or be exercised out of money, they will neither make nor lose money on this trade.

A call spread purchase can be seen as a way of combining two bullish bets on different strike prices with different premiums. It can also be seen as a way of reducing exposure to an expensive or in-the-money call option by offsetting it with another less expensive or out-of-the-money call option. However, it also caps potential profits and increases time decay risk as expiration approaches.

Basic Theory

A call spread, also known as a bull call spread, is a strategy where an investor buys a call option while
simultaneously selling another call option with a higher strike price. This strategy is used when an
investor expects a moderate increase in the price of the underlying asset but wants to limit the potential
loss.

The two components of a call spread are:

1. Long Call Option (Lower Strike): This is the call option you purchase, giving you the
   right to buy the underlying asset at a specified strike price.
2. Short Call Option (Higher Strike): This is the call option you sell, obligating you to
   sell the underlying asset at a higher strike price if the option is exercised.

The goal of a call spread is to benefit from the price increase of the underlying asset while minimizing the
overall cost of entering the trade.

Procedures

Executing a call spread involves the following steps:

1. Market Analysis: Analyze the market and select an underlying asset with a bullish
   outlook.
2. Option Selection: Choose call options with the desired expiration date and strike
   prices. Buy a lower strike call option and sell a higher strike call option.
3. Excel Formulas: Use Excel to calculate the cost of the call spread, potential profit, and
   break-even points.
4. Monitor and Manage: Keep track of the market and manage the trade according to changing
   conditions.

Excel Formulas for Call Spread Purchase

Let’s create a scenario to illustrate the process. Assume you are bullish on XYZ stock, currently trading at
$50. You decide to implement a call spread using the following options:

• Long Call Option (Lower Strike):
  • Strike Price: $45
  • Premium (Cost): $5
• Short Call Option (Higher Strike):
  • Strike Price: $55
  • Premium (Income): $2

Excel Table

Calculation

1. Net Premium:
   • Long Call: =B2 – B5 = -$5
   • Short Call: =C2 – C5 = +$2
2. Cost of Call Spread:
   • =B7 * C7 = -$5 * 1 = -$5

Result

In this scenario, the cost of the call spread is -$5, representing the maximum loss if the trade goes against
you. The potential profit is capped at the difference in strike prices minus the net premium paid.

Other Approaches

1. Adjusting Strike Prices: Depending on market conditions, you can adjust the strike prices
   to tailor the risk and reward to your preferences.
2. Different Expiry Dates: Consider using different expiration dates for the long and short
   call options to modify the strategy.
3. Analyzing Greeks: Incorporate Greeks (Delta, Gamma, Theta) to assess and manage the
   sensitivity of the call spread to changes in market conditions.