Attribute VB_Name = "FINAN_DERIV_WARRANTS_LIBR"

Option Explicit     'Requires that all variables to be declared explicitly.
Option Base 1       'The "Option Base" statement allows to specify 0 or 1 as the
                    'default first index of arrays.


'************************************************************************************
'************************************************************************************
'© Copyright NicoSystem 2009. All rights reserved by Rafael Nicolas Fermin Cota,
'San Francisco, CA. USA  www.rnfc.org
'nfermincota.hba2005@ivey.ca
'************************************************************************************
'************************************************************************************
'FUNCTION      : EXECUTIVE_STOCK_OPTIONS_FUNC
'DESCRIPTION   : Executive stock options
'LIBRARY       : DERIVATIVES
'GROUP         : EXECUTIVE / MGMT
'ID            : 001
'AUTHOR        : RAFAEL NICOLAS FERMIN COTA
'LAST UPDATE   : 01/29/2009
'************************************************************************************
'************************************************************************************

Function EXECUTIVE_STOCK_OPTIONS_FUNC(ByVal SPOT As Double, _
ByVal STRIKE As Double, _
ByVal EXPIRATION As Double, _
ByVal RATE As Double, _
ByVal CARRY_COST As Double, _
ByVal SIGMA As Double, _
ByVal Lambda As Double, _
Optional ByVal OPTION_FLAG As Integer = 1, _
Optional ByVal CND_TYPE As Integer = 0)

    Dim D1_VAL As Double
    Dim D2_VAL As Double
    
    Dim OPTION_VALUE As Double
    
    On Error GoTo ERROR_LABEL

    D1_VAL = (Log(SPOT / STRIKE) + (CARRY_COST + _
            SIGMA ^ 2 / 2) * EXPIRATION) / _
           (SIGMA * Sqr(EXPIRATION))
    
    D2_VAL = D1_VAL - SIGMA * Sqr(EXPIRATION)

'------------------------------------------------------------------------------------
        Select Case OPTION_FLAG
'------------------------------------------------------------------------------------
            Case 1 ', "CALL", "C"
'------------------------------------------------------------------------------------
                OPTION_VALUE = SPOT * Exp((CARRY_COST - RATE) * EXPIRATION) * _
                               CND_FUNC(D1_VAL, CND_TYPE) - STRIKE * Exp(-RATE * _
                               EXPIRATION) * CND_FUNC(D2_VAL, CND_TYPE)
'------------------------------------------------------------------------------------
            Case -1 ', "PUT", "P"
'------------------------------------------------------------------------------------
                OPTION_VALUE = STRIKE * Exp(-RATE * EXPIRATION) * _
                               CND_FUNC(-D2_VAL, CND_TYPE) - SPOT * _
                               Exp((CARRY_COST - RATE) * EXPIRATION) * _
                               CND_FUNC(-D1_VAL, CND_TYPE)
'------------------------------------------------------------------------------------
            Case Else
'------------------------------------------------------------------------------------
                GoTo ERROR_LABEL
'------------------------------------------------------------------------------------
        End Select
'------------------------------------------------------------------------------------
        
    EXECUTIVE_STOCK_OPTIONS_FUNC = Exp(-Lambda * EXPIRATION) * OPTION_VALUE
    
Exit Function
ERROR_LABEL:
EXECUTIVE_STOCK_OPTIONS_FUNC = Err.number
End Function

'************************************************************************************
'************************************************************************************
'© Copyright NicoSystem 2009. All rights reserved by Rafael Nicolas Fermin Cota,
'San Francisco, CA. USA  www.rnfc.org
'nfermincota.hba2005@ivey.ca
'************************************************************************************
'************************************************************************************
'FUNCTION      : EXECUTIVE_WARRANTS_FUNC

'DESCRIPTION   : Valuing Management Options or Warrants when there is dilution
'This program is designed to value options, the exercise of which can
'create more shares and thus affect the stock price. This is the case
'with warrants and management options. It is also the case with convertible
'bonds. As a general rule, using an unadjusted option pricing model to value
'these options will overstate their value.

'LIBRARY       : DERIVATIVES
'GROUP         : EXECUTIVE / MGMT
'ID            : 002
'AUTHOR        : RAFAEL NICOLAS FERMIN COTA
'LAST UPDATE   : 01/29/2009
'************************************************************************************
'************************************************************************************

Function EXECUTIVE_WARRANTS_FUNC(ByVal SPOT_PRICE As Double, _
ByVal STRIKE As Double, _
ByVal EXPIRATION As Double, _
ByVal SIGMA As Double, _
ByVal DIVD As Double, _
ByVal RATE As Double, _
ByVal WARRANTS_OUTSTANDING As Double, _
ByVal SHARES_OUTSTANDING As Double, _
Optional ByVal ADJUSTED_SPOT As Double = 0, _
Optional ByVal CALL_LOWER_BOUND As Double = 0#, _
Optional ByVal CALL_UPPER_BOUND As Double = 1000#, _
Optional ByVal CND_TYPE As Integer = 0)

'SPOT_PRICE: Enter the current stock price. If you are
'assessing the value of the stock, enter your estimate.

'STRIKE: Enter the exercise price of the warrants. If you
'are valuing a portfolio of options, enter the weighted average price

'EXPIRATION: Time (in years) until expiration of the warrant.
'Enter months as a fraction of a year.

'SIGMA: Enter the annualized standard deviation in the stock price.
'You can use either a historical estimate or an implied standard deviation.

'DIVD: Annualized dividend yield on stock. Divide the dollar
'dividends (annual) by the stock price.

'RATE: Treasury bond rate. Enter the rate on a government bond with maturity
'closest to option expiration.

'WARRANTS_OUTSTANDING: Enter the number of warrants (options)
'outstanding. Enter the number of shares outstanding

'SHARES_OUTSTANDING: Primary shares outstanding currently.
'(Do not count shares that will be created by warrant exercise)

Dim k As Long
Dim D1_VAL As Double
Dim D2_VAL As Double

Dim MID_POINT As Double
Dim DELTA_VALUE As Double
Dim CALL_OPT_VALUE As Double

Dim TEMP_VECTOR As Variant

On Error GoTo ERROR_LABEL

'----------------------------------------------------------------------------
If (ADJUSTED_SPOT) <> 0 Then
'----------------------------------------------------------------------------

    D1_VAL = (Log(ADJUSTED_SPOT / STRIKE) + (RATE - DIVD + _
    ((SIGMA ^ 2) / 2)) * EXPIRATION) / (SIGMA * (EXPIRATION ^ 0.5))

    D2_VAL = D1_VAL - (SIGMA * (EXPIRATION ^ (0.5)))

    CALL_OPT_VALUE = ((Exp((0 - DIVD) * EXPIRATION)) * _
        ADJUSTED_SPOT * CND_FUNC(D1_VAL, CND_TYPE) - _
        STRIKE * (Exp((0 - RATE) * EXPIRATION)) * CND_FUNC(D2_VAL, CND_TYPE))

'----------------------------------------------------------------------------
Else 'Searching algorithm for the Adj. Stock Price using the BISEC Method
'----------------------------------------------------------------------------

    k = 0
    
    Do While (CALL_UPPER_BOUND - CALL_LOWER_BOUND) ^ 2 > 0.00000000000001
    'the tolerance for the root searching algorithm
            
        MID_POINT = (CALL_UPPER_BOUND + CALL_LOWER_BOUND) / 2
        
        If MID_POINT > CALL_OPT_VALUE Then
            CALL_UPPER_BOUND = (CALL_UPPER_BOUND + CALL_LOWER_BOUND) / 2
            Else: CALL_LOWER_BOUND = (CALL_UPPER_BOUND + CALL_LOWER_BOUND) / 2
        End If
        
        DELTA_VALUE = WARRANTS_DILUTION_FUNC(SPOT_PRICE, _
            (CALL_UPPER_BOUND + CALL_LOWER_BOUND) / 2, WARRANTS_OUTSTANDING, _
            SHARES_OUTSTANDING)
        
        D1_VAL = (Log(DELTA_VALUE / STRIKE) + (RATE - DIVD + _
            ((SIGMA ^ 2) / 2)) * EXPIRATION) / _
            (SIGMA * (EXPIRATION ^ 0.5))

        D2_VAL = D1_VAL - (SIGMA * (EXPIRATION ^ (0.5)))

        CALL_OPT_VALUE = ((Exp((0 - DIVD) * EXPIRATION)) * _
            DELTA_VALUE * CND_FUNC(D1_VAL, CND_TYPE) - STRIKE * (Exp((0 - RATE) * _
            EXPIRATION)) * CND_FUNC(D2_VAL, CND_TYPE))
        
        k = k + 1
        
        If k = 100 Then: Exit Do
    Loop
    
'----------------------------------------------------------------------------
End If
'----------------------------------------------------------------------------

ReDim TEMP_VECTOR(1 To 2, 1 To 2)

TEMP_VECTOR(1, 1) = UCase("Value per Option")
TEMP_VECTOR(2, 1) = UCase("Value of all Options Outstanding")

TEMP_VECTOR(1, 2) = CALL_OPT_VALUE
TEMP_VECTOR(2, 2) = CALL_OPT_VALUE * WARRANTS_OUTSTANDING

EXECUTIVE_WARRANTS_FUNC = TEMP_VECTOR

Exit Function
ERROR_LABEL:
EXECUTIVE_WARRANTS_FUNC = Err.number
End Function

'************************************************************************************
'************************************************************************************
'© Copyright NicoSystem 2009. All rights reserved by Rafael Nicolas Fermin Cota,
'San Francisco, CA. USA  www.rnfc.org
'nfermincota.hba2005@ivey.ca
'************************************************************************************
'************************************************************************************
'FUNCTION      : WARRANTS_DILUTION_FUNC
'DESCRIPTION   : Valuing Management Options or Warrants when there is dilution
'LIBRARY       : DERIVATIVES
'GROUP         : EXECUTIVE / MGMT
'ID            : 003
'AUTHOR        : RAFAEL NICOLAS FERMIN COTA
'LAST UPDATE   : 01/29/2009
'************************************************************************************
'************************************************************************************


Private Function WARRANTS_DILUTION_FUNC(ByVal SPOT_PRICE As Double, _
ByVal CALL_VALUE As Double, _
ByVal WARRANTS_OUTSTANDING As Double, _
ByVal SHARES_OUTSTANDING As Double)

On Error GoTo ERROR_LABEL

    WARRANTS_DILUTION_FUNC = (SPOT_PRICE * SHARES_OUTSTANDING + _
                            CALL_VALUE * WARRANTS_OUTSTANDING) / _
                            (SHARES_OUTSTANDING + WARRANTS_OUTSTANDING)

Exit Function
ERROR_LABEL:
WARRANTS_DILUTION_FUNC = Err.number
End Function