Iceberg Code

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

Some time I trade on stocks and CW.
Usually CW are a robbery especially when they are close to the expiration date, but they are good trading tools because they:
1) let you pay less commissions
2) have an embedded stop loss (if you are not a disciplined trader)
3) reduce trade size
4) leverage the trade to increase the potential return (LOSS) of an investment.

The big problem with CW is that "time goes by" so it is very important to know How long you can hold them and if it is the right time to trade them.

For this reason here you can find the excel
CW_Time_Value.xls
that calculate with Black-Sholes pricing model evolution of price in
different market condition and time horizon.

What did I add to the model that I can't find elsewhere? ... Estimation of Volatility
The original formula need half dozen of variable input to work, but most of them are constants or easy to find, but Volatility is very difficult to get.
For this reason I applied the Bisection method to evaluate Volatility at a given period when both CW price and Underlying Asset Price are known (Open/close price).

The idea behind the Bisection method (In two words) is to compare the output of a formula with the known result of the formula itself when all but one input are known.

In this case we know all the input and the result (open price of the CW) and we want to measure the unknown Volatility.
We know that Volatility should be inside the interval >0% and <1000% so we try the half point:
500%
If the formula output is higher than the open price of the CW we understand that the interval is 0-500%. So we go down to 250%, 125%, 62.5%, 31.25%, 15.625%, 7,8125%
and the volatility should be higher than 7,8125% so now we go up: 11.71875%, 13.671875%, ...
the volatility interval should be 13.671875% - 15.625%

As you see we decrease very fast the order of magnitude of the interval (even if we start from a creasy value like 100,000%).
We must stop this recursive testing when the difference of the calculated output and the know result of the formula is less than the significant decimal (0.0001)

Here the formula to evaluate the embedded Volatility

Function CwStimaVolatilitaData(ByVal ValoreCW As Single, ByVal ValoreSottostante As Single, ByVal StrikePrice As Single, ByVal Scadenza As Date, ByVal TassoBCE As Single, Optional ByVal Moltiplicatore As Single = 1, Optional ByVal CwCall As Boolean = True) As Single      Application.Volatile      Dim AnniResidui As Single, Volatilita As Single, DeltaVolatilita As Single, Distanza As Single, a As Single, b As Single, c As Single, d1 As Single, d2 As Single      AnniResidui = (Scadenza - Date) / 365      Volatilita = 1000      DeltaVolatilita = Volatilita / 2      Distanza = 1000      Do Until Abs(Distanza) < 0.0001      Distanza = CwStimaValore(ValoreSottostante, StrikePrice, AnniResidui, TassoBCE, Volatilita, Moltiplicatore, CwCall) - ValoreCW          If Distanza > 0 Then              Volatilita = Volatilita - DeltaVolatilita          Else              Volatilita = Volatilita + DeltaVolatilita          End If      DeltaVolatilita = DeltaVolatilita / 2      i = i + 1      Loop      CwStimaVolatilitaData = Volatilita End Function

Download it Now!

Enjoy it!