- 帖子
- 172
- 主題
- 39
- 精華
- 20
- 積分
- 177
- 點名
- 0
- 作業系統
- Microsoft Windows XP
- 軟體版本
- Microsoft Office 2003
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 高雄
- 註冊時間
- 2010-5-1
- 最後登錄
- 2023-3-19
 
|
2#
發表於 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 |
|
發表於 2010-5-26 19:29
| 
