Mastering Put Purchases in Excel

A put purchase is a type of investment strategy that involves buying a put option contract on an underlying asset, such as a stock, index, commodity, or currency. A put option gives the buyer the right, but not the obligation, to sell the underlying asset at a specified price (called the strike price) within a certain time period (called the expiration date). The buyer of a put option pays a fee (called the premium) to the seller of the option for this right.

The main reason why investors buy put options is to hedge against downside risk in their portfolio. For example, if an investor owns 100 shares of XYZ stock that is currently trading at $50 per share, they may buy a put option with a strike price of $45 and an expiration date of one month. This way, if the stock price drops below $45 before the expiration date, they can exercise their right to sell their shares at $45 and limit their loss to $5 per share (minus the premium they paid for the option). However, if the stock price stays above $45 before the expiration date, they can let their option expire worthless and keep their shares.

Another reason why investors buy put options is to speculate on bearish market movements. For example, if an investor believes that ABC index will fall below 1000 points by the end of next week, they may buy a put option with a strike price of 990 points and an expiration date of next week. This way, if the index falls below 990 points by next week, they can exercise their right to sell their index futures at 990 points and make a profit from selling them at a higher market price. However, if the index stays above 990 points by next week, they can let their option expire worthless and lose only the premium they paid for it.

Put options are complex financial instruments that have various factors that affect their value and profitability. Some of these factors are:

• The price of the underlying asset: Put options increase in value as long as the underlying asset decreases in price relative to its strike price.
• The time to expiration: Put options lose value as they approach their expiration date because of time decay.
• The volatility of the underlying asset: Put options increase in value as volatility increases because it implies more uncertainty about future price movements.
• The interest rate: Put options lose value as interest rates rise because it makes borrowing cheaper and reduces present value.
• The dividend yield: Put options lose value as dividend yields increase because it reduces future cash flows from holding shares.

Basic Theory:

A put option gives the holder the right, but not the obligation, to sell an underlying asset at a specified strike price before or at the option’s expiration date. Investors typically buy put options as a form of insurance against potential declines in the value of their portfolio.

Key terms:

• Put Option: A financial contract that gives the holder the right to sell an underlying asset at a predetermined price within a specified timeframe.
• Strike Price: The price at which the option holder can sell the underlying asset.
• Expiration Date: The date at which the option contract expires.

Procedures:

1. Determine the Underlying Asset: Identify the asset you want to protect in your portfolio. This could be a stock, ETF, or any other financial instrument.
2. Choose an Expiration Date: Decide on the timeframe for your put option. Typically, investors choose expiration dates based on their outlook for the underlying asset.
3. Select the Strike Price: Choose a strike price at which you would like the option to be exercisable. This is a crucial decision, as it determines the level at which you are protected.
4. Purchase the Put Option: Execute the trade through your brokerage platform, specifying the chosen expiration date and strike price.

Comprehensive Example:

Let’s consider a scenario:

• Underlying Asset: XYZ Corporation stock
• Current Stock Price: $100 per share
• Put Option Expiration Date: 3 months
• Strike Price: $90 per share

Assuming you’re concerned about a potential downturn in XYZ Corporation’s stock, you decide to purchase a put option with a strike price of $90.

Excel Calculation:

Create an Excel table with the following columns:

1. Column A: Date
2. Column B: Stock Price
3. Column C: Put Option Value

Fill in the Date and Stock Price columns with relevant data. Use the Black-Scholes or another suitable pricing model to calculate the Put Option Value in Column C. You can use the formula =BLACKSCHOLES.PUTPRICE() or any other relevant function.

For simplicity, let’s assume the Black-Scholes model gives a put option value of $5 when the stock is at $100.

Result:

In this scenario, if the stock price drops below $90 before the expiration date, your put option provides a level of protection, limiting your potential losses.

Other Approaches:

1. Delta Hedging: Adjust your portfolio’s exposure by trading the underlying asset to offset changes in the option’s value.
2. Collar Strategy: Combine a put purchase with a covered call to offset the cost of the put option.
3. Dynamic Hedging: Regularly adjust your options position based on market movements to maintain a desired level of protection.