Need Excel formula to identify ATM Strike

xandman · Feb 19, 2017

Can someone help me with the Excel formula to give the nearest ATM strike with a given underlying price and strike increment?

Additionally, is this more processing intensive than simply creating a label row for option chain as ATM/ITM and OTM?

=IF(A19<$A$2,"ITM",IF(A19=$A$2,"ATM","OTM"))

Thank you.

JackRab · Feb 19, 2017

I use this:

=MROUND(D16*EXP(1)^(D19*(D20/365)),D21)

where
D16 = ex-dividend spot
D19 = interest rate
D20 = time to maturity (dte)
D21 = strike difference

xandman · Feb 19, 2017

JackRab said:
I use this:

=MROUND(D16*EXP(1)^(D19*(D20/365)),D21)

where
D16 = ex-dividend spot
D19 = interest rate
D20 = time to maturity (dte)
D21 = strike difference

MROUND. What a useful function! Thanks, Jack.

JackRab · Feb 19, 2017

I'll PM you @xandman