1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| Sub test()
Function str(CP As String, Spot As Double) As Double
Dim taux_domestique As Double
Dim taux_etranger As Double
Dim i As Integer
Dim j As Integer
Dim duree(12) As Double
Dim delta(7) As Double
Dim volatilite(12, 7) As Double
Dim tmp1 As Double
Dim tmp3 As Double
Dim tmp2 As Double
i = 1
Dim ObjCell1 As Range
For Each ObjCell In Range("A3:A14" ).Cells
duree(i) = ObjCell.Value
i = i + 1
Next
i = 1
Dim ObjCell2 As Range
For Each ObjCell2 In Range("B2:H2" ).Cells
delta(i) = ObjCell2.Value
i = i + 1
Next
Dim ObjCell3 As Range
i = 1
j = 1
For Each ObjCell3 In Range("B3:H14" ).Cells
If j <= 7 And i <= 12 Then
volatilite(i, j) = ObjCell3.Value
End If
If j > 7 And i <= 12 Then
j = 1
i = i + 1
End If
Next
For i = 1 To 12
If CP = "call" Then
taux_domestique = InterpoleTx(Range("A17:A26" ), Range("B17:B26" ), duree(i)) And taux_etranger = InterpoleTx(Range("A29:A42" ), Range("C29:C42" ), duree(i))
ElseIf CP = "put" Then
taux_domestique = InterpoleTx(Range("A17:A26" ), Range("C17:C26" ), duree(i)) And taux_etranger = InterpoleTx(Range("A29:A42" ), Range("B29:B42" ), duree(i))
End If
For j = 1 To 7
tmp1 = taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))
tmp2 = Log(1 / (delta(j) * Sqr(2 * 3.14)))
tmp3 = volatilite(i, j) * Sqr(2 * (duree(i) / 365) * tmp2)
Cells(i, j + 9).Value = str = Spot * Exp((tmp1 * (duree(i) / 365)) - tmp3)
Next j
Next i
End Function
End Sub |
Partager