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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114
| Function calcul_taux_ss_risque(line As Integer, date_eval As Range)
Dim i, j, year_col As Integer
Dim borne_sup, borne_inf As Integer
Dim ligne_borne_sup, ligne_borne_inf As Integer
Dim devise As String
Dim redemp_term, redemp_month, redemp_year As Double
Dim eval_year, eval_month As Double
Dim df_year, taux_borne_sup, taux_borne_inf, a, b As Double
Dim taux As Double
'calcul de la maturité de l'option
eval_year = Year(date_eval)
eval_month = Month(date_eval)
redemp_year = Sheets("inputs").Range("H" & line).Value
redemp_month = Sheets("inputs").Range("I" & line).Value
redemp_term = ((redemp_year - eval_year) * 12 + (redemp_month - eval_month)) / 12
Sheets("B&S").Range("D9").Value = redemp_term
'détermination de la devise de l'option
If Sheets("inputs").Range("F" & line).Value = "CHF" Then
i = 8
devise = "CHF"
ElseIf Sheets("inputs").Range("F" & line).Value = "EUR" Then
devise = "EUR"
i = 39
ElseIf Sheets("inputs").Range("F" & line).Value = "USD" Then
i = 60
devise = "USD"
ElseIf Sheets("inputs").Range("F" & line).Value = "GBP" Then
i = 74
devise = "GBP"
ElseIf Sheets("inputs").Range("F" & line).Value = "CAD" Then
i = 88
devise = "CAD"
End If
'encadrement de redemp_term
While Sheets("ESG").Range("B" & i).Value = devise
If redemp_term > Sheets("ESG").Range("E" & i).Value Then
i = i + 1
ElseIf redemp_term < Sheets("ESG").Range("E" & i).Value Then
ligne_borne_sup = i
ligne_borne_inf = i - 1
devise = "stop"
End If
Wend
borne_sup = Sheets("ESG").Cells(ligne_borne_sup, 5).Value
borne_inf = Sheets("ESG").Cells(ligne_borne_inf, 5).Value
'détermination de l'année du discount factor
If eval_month <> 12 Then
df_year = eval_year - 1
ElseIf eval_month < 12 Then
df_year = eval_year
End If
j = 1
While Sheets("ESG").Cells(1, j).Value <> df_year
j = j + 1
Wend
'Passage en taux ZC
taux_borne_sup = (Sheets("ESG").Cells(ligne_borne_sup, j)) ^ (-1 / borne_sup) - 1
taux_borne_inf = (Sheets("ESG").Cells(ligne_borne_inf, j)) ^ (-1 / borne_inf) - 1
'interpolation linéaire
a = (taux_borne_sup - taux_borne_inf) / (borne_sup - borne_inf)
b = taux_borne_sup - a * borne_sup
'Calcul du taux sans risque
taux = a * redemp_term + b
Sheets("B&S").Range("D10") = calcul_taux_ss_risque
End Function |
Partager