ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

±Nfunctionªº­È±a¦^¥h­«ºâ

±Nfunctionªº­È±a¦^¥h­«ºâ

½Ð°Ý¤@¤U ¥i¥H§âfunctionªºªF¦è©¹¦^¥á¦bºâ¶Ü
-------------------------------------------------------
°ÝÃD¦b©ó·í¶]§¹²Ä¤@­Ófunction®É §âBSCall(StockPrice, StrikePrice, T, r, v)ªº­È ±a¶i¥h TempCall
¤§«áÄ~Äò¶]¦^°é­pºâ ¥i¬Oµo²{¦]¬°TempCallªº­È®Ú¥»¤£´«ÅÜ ¦]¬°¤@¶}©l´N§âBSCall©T©w¦í¤F
©Ò¥Hdif ¤@ª½¤£·|=0 ¥L¤@©w·|¤@ª½¶]¨ìTempISD=0¬°¤î¤~·|¸õ¥X  ¦³¿ìªk§â¦b¨C¦¸­pºâ¥X·sªºTempISD±a¦^¤W¤@­Ófunction¨ú¥Nv
¥h­pºâ¥X·sªºBSCall¦b±a¦^²Ä2­Ófunction¤@ª½ºâ¶Ü

book2.rar (15.5 KB)

GinBow

¬O³o¼Ë¶Ü?

Option Explicit

Public StockPrice As Double, StrikePrice As Double, T As Double, r As Double, v As Double, CurrentCall As Double

Sub ISD()
    Dim StockPrice As Double, StrikePrice As Double, T As Double, r As Double, v As Double, CurrentCall As Double
     
    StockPrice = Worksheets("Áô§tªi°Ê«×").Cells(2, 1)
    StrikePrice = Worksheets("Áô§tªi°Ê«×").Cells(2, 2)
    r = Worksheets("Áô§tªi°Ê«×").Cells(2, 3)
    T = Worksheets("Áô§tªi°Ê«×").Cells(2, 4)
    CurrentCall = Worksheets("Áô§tªi°Ê«×").Cells(2, 5)
    v = Worksheets("Áô§tªi°Ê«×").Cells(2, 7)
    Worksheets("Áô§tªi°Ê«×").Range("F2") = ISDC(StockPrice, StrikePrice, T, r, v, CurrentCall) * 100
End Sub

Function BSCall(StockPrice As Double, StrikePrice As Double, T As Double, r As Double, v As Double) As Double
    Dim D1 As Double
    Dim D2 As Double
    Dim NormD1 As Double
    Dim NormD2 As Double
   
    D1 = (Log(StockPrice / StrikePrice) + (r + 0.5 * v ^ 2) * T) / v / T ^ 0.5
    D2 = D1 - v * T ^ 0.5
    NormD1 = Application.WorksheetFunction.NormSDist(D1)
    NormD2 = Application.WorksheetFunction.NormSDist(D2)
    BSCall = StockPrice * NormD1 - StrikePrice * Exp(-r * T) * NormD2
End Function

Function ISDC(StockPrice As Double, StrikePrice As Double, T As Double, r As Double, v As Double, CurrentCall As Double) As Double
    Dim TempISD As Double
    Dim ISDU As Double
    Dim ISDL As Double
    Dim TempCall As Double
    Dim Dif As Single
   
    ISDU = 3   '³]ªi°Ê²v³Ì°ª¬°300%
    ISDL = 0   '³]ªi°Ê²v³Ì§C¬°300%
    Do          '¶}©l¤ÏÂйêÅç
        TempISD = (ISDU + ISDL) / 2 'ªì©l­È¬°150%
        TempCall = BSCall(StockPrice, StrikePrice, T, r, v)
        Dif = CurrentCall - TempCall '°²¦p»ù®æ¬Û¦P«hµ²§ô¹êÅç
   
        If Dif = 0 Then
            Exit Do
        ElseIf Dif > 0 Then '±Nªi°Ê«×±ø¤j
            ISDL = TempISD
        ElseIf Dif < 0 Then '±Nªi°Ê«×±ø¤p
            ISDU = TempISD
        End If
        
        If TempISD >= 2.9 Then '­Yªi°Ê«×¤j©ó290%
            TempISD = 0  '©Î¬O¤p©ó1%®É«h¬°0
            Exit Do                     '«hµ²³t°j°é
        ElseIf TempISD <= 0.01 Then
            TempISD = 0
            Exit Do
        End If
        v = TempISD
    Loop Until Abs(Dif) < 0.01 * CurrentCall '»~®t­È³]©w½d³ò
    ISDC = TempISD
End Function
­Y¬O§Ú¦^µª¡A¨Ï±zº¡·N¡A½Ð±zÅý§Úª¾¹D¡I                  
­Y¬O§Úªº¦^ÂСA±z¤´¦³¨ä¥L¨£¸Ñ¡A¤]½Ð±z¤£¶Þ«ü±Ð¡I

TOP

¶â¶â ¬O³o¼Ë¨S¿ù ÁÂÁÂMin¤j

Ä~Äò¬ã¨s¤¤ @@
GinBow

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD