Hi
If Im using Data Format / Custom Units:
One with hours showing in one cell and the other with Wk / Day / Hour / Mins format in another cell and both results show 1 hr as the difference between a start and end date why am I getting two different results 1.07 vs 0.08 for the same Expected Move formula:
Asset Price * Implied Volatility (annual) * Sqrt DTE (Days to Expiry)?
Cheers,
MacBook Pro 15″, macOS 13.4
In your original formulas you did not tell Numbers how to convert your durations. It converted them into numbers based on the highest unit you chose to display the duration in the cell. For the first it converted it into a decimal number of hours because the format was in hours. For the second it converted it into a decimal number of weeks because the format was in weeks. Rather than relying on the cell format to tell it how to convert it, use one of the DUR2 functions. The formula you posted later had the square root of days/365 so DUR2DAYS seems like the most appropriate choice.
I am surprised it works at all to tell you the truth. What is the square root of an hour?
One is using the number 1 (1 hr with duration units removed) in the square root, the other is using 0.00595 weeks (which is 1 hr).
Use DUR2HOURS in your formula to convert your durations into the number of hours.
Sqrt of 1 hr? = About a wk if you experimenting with using the DUR2 function (mins/hours/days/wks) and trying establish if your math is correct 😅.
The Expected Move formula is actually the sqrt of a fraction: Sqrt(DTE/365) (pls see EM screenshot), so 1 hr will produce a result worth knowing when trading “0 Days to Expiry” (daily options contracts) as the price of an option can explode against short sellers if the underlying moves adversely, more so as expiration approaches.
Are the results above not both showing as 1 hr in Column D because they do in the formatting menu? (Pls see 2 images with red underlines).
I’m unclear why one is treated as a single unit (the number 1) and the other a single hour. The format menu appears to show I'm still working with units of time for both calculations and both are showing the same formatting units of time?
(Expected Moves in bold are using DUR2 functions, just checking if the math is correct for different timeframes).
"Use DUR2HOURS" thank you.
So I'm gathering then that one function like DUR2HOURS or DUR2DAYS will work for all hrly/daily/wkly/monthly EM's as long as the denominator reflects the DUR2 functions timeframe,
eg use DUR2HOURS throughout but make sure the denominator is SQRT(x/8765.82) (a yr as I'm working with annual volatility readings) or use DUR2DAYS and use SQRT(x/365)?
Cheers,
Duration can be thought of as a special data format that Excel doesn't have. It makes it easy to do certain types of date and time math.
The DUR2 functions convert that format into decimal values that can be used in your option equations. DUR2DAYS, for example, would convert the Duration value into the Number if Calendar Days used in the equation in your screenshot.
SG
Hi SGIII, I'm still not sure about this:
"I’m unclear why one is treated as a single unit (the number 1) and the other a single hour. The format menu appears to show I'm still working with units of time for both calculations and both are showing the same formatting units of time? "
Re: "So I'm gathering then that one function like DUR2HOURS or DUR2DAYS will work for all hrly/daily/wkly/monthly Expected Moves as long as the denominator reflects the DUR2 functions timeframe,
eg use DUR2HOURS throughout but make sure the denominator is SQRT(x/8765.82) (a yr as I'm working with annual volatility readings) or use DUR2DAYS and use SQRT(x/365)?"
Thanks.
Duration measures a length of time. It's a data format specific to Numbers.
DUR2...(Duration) results in a decimal number that could measure units of time or many other things.
SG
Duration measures a length of time. It's a data format specific to Numbers.
And my screenshots in my OP show that I am using duration in the Format section yet one calculation is using the number 1 and the other 1 hour.
Why is that?
In your original formulas you did not tell Numbers how to convert your durations. It converted them into numbers based on the highest unit you chose to display the duration in the cell. For the first it converted it into a decimal number of hours because the format was in hours. For the second it converted it into a decimal number of weeks because the format was in weeks. Rather than relying on the cell format to tell it how to convert it, use one of the DUR2 functions. The formula you posted later had the square root of days/365 so DUR2DAYS seems like the most appropriate choice.
Nice one, 👍 that makes makes total sense now.
Duration Format with same 1 Hour Cell Value gives Two Different Results
