Learn about European strike reset options, and download a pricing spreadsheet.
Reset options are somewhat similar to lookback options. However, the strike is reset to the security price at a predetermined time if the option is out-of-the-money.
- For a call option, this happens when the security price dips below the strike price
- For a put option, this happens when the security price rises above the initial strike price
This extra flexibility means that reset options cost more than floating strike lookback options.
The price of these options are given by the following formulas, derived by Gray and Whaley (1999) and referenced by Haug in Derivative Models on Models (2008).
where
- c(S,K) and p(S,K) are call and put prices
- S is the spot sprice
- K is the strike price
- r is the risk-free rate
- d is the dividend yield
- σ is the volatility
- T is the time to expiry
- τ is the time to reset
- N is the cumulative normal distribution
- M is bivariate normal distribution
N() is calculated by Excel’s NormSDist() function. However, Excel does not offer a tool to calculate the bivariate cumulative normal distribution, M(). Instead, a VBA approximation given by West is used.
This Excel spreadsheet implements the above formulas.
Download Excel Spreadsheet to Price Strike Reset Options
Samir Khan,
Please review the attached from John ? … I have tried to contact him without success. His stock option calculator is what I am looking for. I don’t have the time or skill write a spreadsheet my self.
https://www.youtube.com/channel/UCeQ623CDtI37zSkYP5UdhMg
Do you have or can produce something that would get similar results?
Thank you for your time.
Sincerely,
Richard Hilger