標題: 將function的值帶回去重算 [打印本頁]

---

作者: ginbow    時間: 2010-5-26 19:29     標題: 將function的值帶回去重算

請問一下 可以把function的東西往回丟在算嗎
-------------------------------------------------------
問題在於當跑完第一個function時 把BSCall(StockPrice, StrikePrice, T, r, v)的值 帶進去 TempCall
之後繼續跑回圈計算 可是發現因為TempCall的值根本不換變 因為一開始就把BSCall固定住了
所以dif 一直不會=0 他一定會一直跑到TempISD=0為止才會跳出  有辦法把在每次計算出新的TempISD帶回上一個function取代v
去計算出新的BSCall在帶回第2個function一直算嗎

---

作者: Min    時間: 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

---

作者: ginbow    時間: 2010-5-27 18:32

嗯嗯 是這樣沒錯 謝謝Min大

繼續研究中 @@

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)