Welcome!

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

Wednesday, July 22, 2009

prova tabella

provo a mettere una tabella per veder se viene bene
campo1campo2campo3
326776
178130
678671

Sunday, September 17, 2006

Value of Covered Warrant across time

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!