i want to clarify my comment about the spx not accounting for dividends. the dividend is lost, from comparing the index against the total return index, but the drop in price of the index components, on their ex-date, affects the index. the yield is currently 1.25%, based on spy. if the components ex-dates were evenly distributed, it amounts to about $.22 per trading day. the actual daily price change, and how to model it, is difficult to determine.
options risk graph using excel
- Thread starter jim leahy
- Start date
I'm currently assuming 1.65% dividend rate and a 1.5% interest rate. May not be correct but I guess I'm using those inputs to 'curve fit' the T+0 line to match up w TOS.
Do you tweak those values to curve fit to the values for each expiration and strike on TOS ?
Otherwise I don't see how you would get the right values if you are using the IV shown on TOS
It may work for a certain strike and expiration but it will not work over the entire SPX chain
Actually no, that's not the case. When I change the expiry, all the IV's in my model update to reflect that date. My PnL line adjusts accordingly and it is still quite accurate within $10. I checked 3 different expiration's and positions. All accurate to the Analyze model PnL line.
The is really just me tinkering with a model and trying to mold it where it will be useful to me for adjustment time. Yesterday I didn't even look at it as I have other tools I'm using to monitor instantaneous PnL outside of TOS. The IV's dropping out is an issue tho. I'll have to look into something that remembers the last IV streamed when it goes blank or something.
I thought you were much more accurate based on the screenshot you showed earlier unless I misunderstood something
On that screenshot you were less than 50 cents off
I suppose that's as close as you can get without providing more accurate inputs
I will try those inputs and see if it gives me p/l prices that I can work with
Thanks for clarifying
In that case I can probably ignore the dividend and interest and not enter anything and probably get about the same accuracy
I have tested a few expirations and strikes and so far I am in the same range (50 cents ) that you showed earlier
I think the reason I wasn't getting the right values before because I was trying to use either the price after close or the think back
The problem with that is that while the price is locked after trading hours the IV is still moving so it no longer matches the price at the close
At least now I know I can get close enough prices I just have to do it during trading hours which is something I have to get used to as usually try to look at any new strategy or adjustment after hours or on the weekend when I have more time
In that case I can probably ignore the dividend and interest and not enter anything and probably get about the same accuracy
I have tested a few expirations and strikes and so far I am in the same range (50 cents ) that you showed earlier
I think the reason I wasn't getting the right values before because I was trying to use either the price after close or the think back
The problem with that is that while the price is locked after trading hours the IV is still moving so it no longer matches the price at the close
At least now I know I can get close enough prices I just have to do it during trading hours which is something I have to get used to as usually try to look at any new strategy or adjustment after hours or on the weekend when I have more time
i added a page to the original spreadsheet that will calculate the iv based on the inputs to the model. this page uses activex controls and a few vba functions and routines so it may not run on all excel installations and you have to enable macro permissions for this file. you enter the expiration date, the symbol, put or call, and the strike on this page. the opra and rtd formulas are automatically calculated. the screenshot is shown below. the auto update feature updates on a 15 second interval, if enabled.
getsystemtime isn't used and can be safely deleted. gettimezoneinformation is referenced in
Sub get_timezone(), Function LocalOffsetFromGMT and Function ConvertLocalToGMT. these aren't used functions but i've seen undeclared function errors pop up at times even when not used. it's better to delete these functions or comment out the call to the function in these routines and then leave the declaration commented out or delete it. i'm using 32 bit office so these errors don't show up in my system. you can also try editing it as:
Private Declare PtrSafe Function GetTimeZoneInformation
but i don't know if that will cause other problems since i can't verity it on my installation.
Sub get_timezone(), Function LocalOffsetFromGMT and Function ConvertLocalToGMT. these aren't used functions but i've seen undeclared function errors pop up at times even when not used. it's better to delete these functions or comment out the call to the function in these routines and then leave the declaration commented out or delete it. i'm using 32 bit office so these errors don't show up in my system. you can also try editing it as:
Private Declare PtrSafe Function GetTimeZoneInformation
but i don't know if that will cause other problems since i can't verity it on my installation.
I did a comparison of the original file (Risk-graph) using the RTD input of IV from tos and nothing for the Div and Interest and your updated file (Risk-graph plus)using the calculated IV and 2.5 for Div and 0.1 for interest I find that the when I use the calculated IV in the Risk-graph I get an option price difference of about 1.66 between the calculated price and the TOS price
If I use the TOS IV without Div and Interest in the Risk-graph I get a difference of about 0.33 so it looks like at this time the TOS IV is still closer to the real price
If I use the TOS IV without Div and Interest in the Risk-graph I get a difference of about 0.33 so it looks like at this time the TOS IV is still closer to the real price
if you use the calculated iv and the same other parameters and prices in both the put/call page and the stuff page, the fair value should be within a penny. they use the same model.
the auto update doesn't use the systemtime function. i found the function declarations were in another module, called ThisWorkBook. the timer routine references a variable in that module so that probably caused the object to load and gave the error for the function declaration. you can delete the declarations in that module and the auto update timer should function ok. (i think)
the auto update doesn't use the systemtime function. i found the function declarations were in another module, called ThisWorkBook. the timer routine references a variable in that module so that probably caused the object to load and gave the error for the function declaration. you can delete the declarations in that module and the auto update timer should function ok. (i think)
Yes they are now
I think I had one of the settings for weekly instead of monthly so that was throwing off the result