Real-time Option Greeks functions: New Release 9.3.6
To calculate an option's Greeks in Excel, MarketXLS provides opt_ functions that take the stock price, the option's market price, expiry date, option type and strike, and return the Greek: for example =opt_Delta(150, 5, DATE(2024,6,21), "Call", 155) returns the delta of a $155 call priced at $5 with the stock at $150. The same arguments work for opt_Gamma, opt_Theta, opt_Vega, opt_Rho and opt_ImpliedVolatility, and you can change any input to run what-if scenarios. These functions were added in MarketXLS version 9.3.6, released May 10, 2024, alongside new earnings-date functions and an update to StrikeNext.
1. Custom option Greeks functions (opt_ series)
The 9.3.5 update introduced opt_ functions for option traders (details on our blog). Version 9.3.6 added Greeks functions to the series. Because you supply the inputs, you can see how the Greeks would change if the stock price, option price or risk-free rate changed.
Syntax:
=opt_Delta(CurrentStockPrice, MarketOptionPrice, ExpiryDate, OptionType, StrikePrice, [RiskFreeRate], [ImpliedVolatility])
- CurrentStockPrice: the current price of the underlying stock.
- MarketOptionPrice: the current market price of the option.
- ExpiryDate: the option's expiration date, for example
DATE(2024,6,21). - OptionType: "Call" or "Put".
- StrikePrice: the option's strike price.
- RiskFreeRate (optional): defaults to 5% in the current function reference; enter your own rate to override it.
- ImpliedVolatility (optional): supply a volatility instead of deriving it from the option price.
Functions in the series:
=opt_Delta(...): change in option price for a $1 move in the stock=opt_Gamma(...): change in delta for a $1 move in the stock=opt_Theta(...): daily time decay=opt_Vega(...): change in option price for a 1-point change in implied volatility=opt_Rho(...): change in option price for a change in interest rates=opt_ImpliedVolatility(...): the volatility implied by the option's market price
These functions are listed for the Microsoft 365 add-in (Excel on Mac and Excel for the web), where they are written with the mxls. prefix; check the formula list for availability on your platform.
2. Previous earnings report date and time
Two functions return a company's most recent earnings report:
=previousEarningsReportDate("AAPL")returns the date of the last earnings report.=previousEarningsReportTime("AAPL")returns the time of the last announcement, for example "After Market".
3. StrikeNext update
=StrikeNext("ticker", "near_value") returns the listed strike nearest to a value. For example, at the time of release =StrikeNext("msft","near_303") returned 305, the closest Microsoft strike to 303.
Version 9.3.6 also included minor optimizations. Email support@marketxls.com with questions, or join the MarketXLS Discord community. See MarketXLS pricing for plans.
