返回列表 上一主題 發帖

將function的值帶回去重算

Min
　暱稱: 閔

版主Rank: 7Rank: 7Rank: 7

帖子
172 
主題
39 
精華
20 
積分
177 
點名
0  
作業系統
Microsoft Windows XP 
軟體版本
Microsoft Office 2003 
閱讀權限
100 
性別
男 
來自
高雄 
註冊時間
2010-5-1 
最後登錄
2023-3-19 

論壇貢獻勳章帥哥勳章
1# 跳轉到 »
發表於 2010-5-26 22:40 | 顯示全部帖子
是這樣嗎?

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("隱含波動度").Cells(2, 1)
    StrikePrice = Worksheets("隱含波動度").Cells(2, 2)
    r = Worksheets("隱含波動度").Cells(2, 3)
    T = Worksheets("隱含波動度").Cells(2, 4)
    CurrentCall = Worksheets("隱含波動度").Cells(2, 5)
    v = Worksheets("隱含波動度").Cells(2, 7)
    Worksheets("隱含波動度").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   '設波動率最高為300%
    ISDL = 0   '設波動率最低為300%
    Do          '開始反覆實驗
        TempISD = (ISDU + ISDL) / 2 '初始值為150%
        TempCall = BSCall(StockPrice, StrikePrice, T, r, v)
        Dif = CurrentCall - TempCall '假如價格相同則結束實驗
   
        If Dif = 0 Then
            Exit Do
        ElseIf Dif > 0 Then '將波動度條大
            ISDL = TempISD
        ElseIf Dif < 0 Then '將波動度條小
            ISDU = TempISD
        End If
        
        If TempISD >= 2.9 Then '若波動度大於290%
            TempISD = 0  '或是小於1%時則為0
            Exit Do                     '則結速迴圈
        ElseIf TempISD <= 0.01 Then
            TempISD = 0
            Exit Do
        End If
        v = TempISD
    Loop Until Abs(Dif) < 0.01 * CurrentCall '誤差值設定範圍
    ISDC = TempISD
End Function
若是我回答，使您滿意，請您讓我知道！                  
若是我的回覆，您仍有其他見解，也請您不嗇指教！

TOP

        靜思自在 : 【停滯不前，終無所得】人都迷於尋找奇蹟，因而停滯不前；縱使時間再多、路再長，也了無用處，終無所得。
返回列表 上一主題