Understanding Average Strike Options in Excel

An average strike option is a type of option where the strike price depends on the average price of the underlying asset over a specified period of time. The payoff is the difference between the price of the underlying at expiry and the average price (strike). Average strike options are also known as Asian options.

Average strike options are exotic options, which means they have features that are not common in standard options. They are also known as European options, because they can only be exercised at maturity, unlike American or Asian options that can be exercised before expiry.

Average strike options are used by traders who want to hedge against fluctuations in exchange rates, commodity prices, or other underlying assets that have high volatility or low liquidity. They can also be used by traders who want to speculate on the direction of the underlying asset without paying a premium for an American or Asian option.

The advantage of average strike options is that they have lower volatility than standard options due to the averaging mechanism. This means they are typically cheaper than a comparable American or Asian option. The disadvantage is that they have limited flexibility and require more sophisticated pricing models than standard options.

To buy an average strike option, you need to agree on a fixed exchange rate or price for a certain period of time with another party. You pay a premium for this contract, which represents your maximum loss if the underlying asset moves against you. At maturity, you compare your actual exchange rate or price with the average rate or price that was agreed upon at inception. If your actual rate or price is higher than the average rate or price, you exercise your option and receive the difference from your counterpart. If your actual rate or price is lower than the average rate or price, you let your option expire worthless and lose only your premium.

Basic Theory

An Average Strike Option is unique because the strike price is not fixed but is determined by the average price of the underlying asset over a predefined period. This period can be daily, weekly, monthly, etc., depending on the contract specifications. The idea is to mitigate the impact of short-term market fluctuations and provide a more stable reference point for both the buyer and the seller.

Procedures

  1. Setting Up Excel: Before diving into the calculations, set up an Excel spreadsheet with the following columns:
    • Date
    • Underlying Asset Price
    • Cumulative Price
    • Average Strike Price
  2. Data Entry: Enter the historical prices of the underlying asset in the “Underlying Asset Price” column and calculate the cumulative sum in the “Cumulative Price” column.
  3. Calculate Average Strike Price: Determine the average strike price using the formula:
                    Average Strike Price = Cumulative Price / Number of Observations
    

Comprehensive Explanation with Example

Let’s consider a scenario with the following daily closing prices of a stock over a 10-day period:

Date Underlying Asset Price ($)
2024-01-01 100
2024-01-02 105
2024-01-03 98
2024-01-04 102
2024-01-05 110
2024-01-06 95
2024-01-07 100
2024-01-08 105
2024-01-09 108
2024-01-10 115

Calculation

  1. Cumulative Price:
                    Cumulative Price = SUM($B$2:B2)
    
  2. Average Strike Price:
                    Average Strike Price = Cumulative Price / Number of Observations
    

Excel Formulas

  • Cumulative Price (Cell C2): =SUM($B$2:B2)
  • Average Strike Price (Cell D2): =C2/COUNT($B$2:B2)

Drag the formulas down for the entire dataset.

Results

After completing the calculations, the Average Strike Prices for each day will be determined.

Date Underlying Asset Price ($) Cumulative Price ($) Average Strike Price ($)
2024-01-01 100 100 100
2024-01-02 105 205 102.5
2024-01-10 115 1018 101.8

Other Approaches

Alternative methods for calculating the average strike price might involve exponential moving averages, weighted averages, or other statistical techniques depending on the specific requirements of the option contract.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *