I've downloaded a few sample Excel spreadsheets and compared the Greeks with the output from Hoadley. All tally except for Theta. Hoadley and another VBA download show -0.0044 Two separate spreadsheets with formulas calculating Greeks show Theta as -1.6054 and -1.5967, which is a huge difference. Parameters are; Spot Price: 92.73 Strike Price: 98 Risk-free Rate: 2.50% Volatility:7.228% Dividend Yield:0% Analysis Date: 12-Sep-13 Expiry Date: 18-Jan-14, 128 days Option type-Call The respective formulas are similar, 1)=-(S*'Black Scholes Calculator'!$K$37*Sigma*EXP(-q*T))/(2*SQRT(T))+q*S*NORMSDIST(d_1)*EXP(-q*T)-Rf*X*EXP(-Rf*T)*NORMSDIST(d_2) 2)=-(SpotPrice * L5 * sigma * EXP(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2))) + (DividendYield * SpotPrice * J5 * EXP(-DividendYield * TimeToMaturity)) - (RiskFreeRate * B6 * EXP(-RiskFreeRate * TimeToMaturity) * K5) http://www.google.co.th/url?sa=t&rc...twZaN17X__VOGyQ&bvm=bv.52164340,d.bmk&cad=rja https://sites.google.com/site/simul...lesandGreeksCalculator.xls?attredirects=0&d=1 I can't see where the problem is, can someone please calculate and advise? I reckon the 1.60 is wrong as it is disproportionate to the option price. Note: I have scanned the downloads with MSE, clear, but if you have doubts then please don't download.
It could be that the two spreadsheets in question did not normalize theta to a daily value: -1.6054 / 365 = -.0044
Bingo! Problem solved, thank you. I'm trying to build my own spreadsheets to track portfolio greeks more easily. Hoadley is great for analysing single positions for entry, etc, but too tedious for portfolio level stuff every day. Good to go now.