I have started using the tool set from Peter Hoadley. I bought the whole package and I love it. I do have a question. On the "Positions" sheet in the OpenPositions workbook the current option price is a calculated number. In order to force the number to the actual trading price at the current moment it is neccessary to calculate the IV for each position and replace the IV in use. This has the obvious and desireable effect of forcing the current price to the actual and applying an up to date IV to all the other dates etc. They provide a handy little tool on the sheet for this purpose. My question is: why isn't this automatic? Why can't the spreadsheet obtain the current price for each option code? Am I missing something?
Never mind. Peter Hoadley very patiently wrote me a detailed and very helpful answer to this rather dumb question.
I'm sure he wouldn't mind. With acknowledement to Peter Hoadley, here is what he said: "The Open Positions Manager is mean for end of day valuation of an option portfolio. It values each option using an IV which you enter manually and the underlying asset price (which you can enter manually, use the Yahoo button, or link to another provider using functions like HoadleySWQuotes. To value your options at the end of the day (or an times during the day) you just press the button and bring in the latest underlying price. That's all. You would NOT normally recalculate IV. The reason is that IV fluctuates all over the place for individual options for many reasons (eg the option prices and underlying prices may not be taken at the same point in time (very common); Just calculating IV for an option at the end of each day would be pretty useless. You might as well just use the option values themselves for valuation (which is the same thing as calculating IV). The whole point of CALCULATING theoretical option values using the underlying rather than just valuing your portfolio based on the last traded or quoted option price is to get around the problems of using the option prices themselves. Options are a very poor basis for valuing your portfolio. The way most people handle it is to use the IV calculator to calculate IV of the whole chain -- to get the complete volatility surface. Various smoothing methods are used to eliminate market anomalies for individual options in the IV calculator. The results are much more stable -- volatility itself is fairly stable from day to day. Then to select a reasonable IV from that for each option you are interested in and to use that in the open positions manager. You would normally specify the volatility for individual options (in column AA) of the positions sheet) rather than using the default volatility on the underlying assets, settings sheet, as this way you will correctly take into account the volatility smile. Done this way you would not need to calculate IV every day. Perhaps once every week or so you would check to see if there have been changes in the volatility surface. The key thing is: portfolio valuation (as opposed to making decisions about an individual trade) is best done using theoretical option values using the latest underlying price and a "stable" estimate of IV. That's the assumption on which the Open Positions Manager was designed -- you definitely don't want to calculate IV each day as, apart from being very time consuming, that would be exactly the same as using the option prices themselves for valuation which would be a very poor basis for valuing a portfolio."