Discussion :

[Maxnihilist]

Bonjour à tous,

Débutant en VBA et étudiant en finance, je souhaite automatiser, sous excel ,en VBA, la méthode du "bootstrap".

Exemple de ladite méthode (c'est très certainement trop exhaustif mais je pense qu'il est nécessaire de s'attarder dessus pour mieux comprendre, je m' excuse d'avance pour la lourdeur du message...) :

Obligation 1 : Principal : 100, maturité : 0.5, coupon : 0, prix : 97.5, taux ZC : ?
obligation 2 : principal : 100 , maturité : 1.0, coupon : 0, prix 90.0, taux ZC : ?
obligation 3 : principal : 100, maturité 1.5, coupon : 8, prix : 96.0, taux ZC : ?

Le but est donc de trouver les Taux ZC.
Si le coupon a une valeur nulle, le Taux ZC vaut alors :
ZC = LN(principal/prix) / maturité. le premier donne 10.127%, le second 10.536 %.
Si le coupon n'est pas nul (le troisième) :
ZC = - [ LN ( 96.0 / (8*exp(-0.10127*0.5)+8*exp(-0.10536*1) ) / (100+8) ] / 1.5
Nous avons donc eu besoin des Taux ZC 1 et 2 pour trouver le 3.

Ma question est donc :
Quelle boucle utiliser pour calculer ces taux, quand le coupon n'est pas nul ? j'ai pensé au code suivant (pour le le cas où le coupon est nul, c'est-à-dire les deux premiers) :
sur excel, on prendra une 1ère colonne pour le principal, une seconde pour la maturité, une 3ème pour le coupon, 4ème pour le prix, 5ème pour les ZC cherché :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Sub ZC()
 
Range("D4").Select
Set Plage_ZC = Range(Selection, Selection.End(x1down)).Offset(0, 1)
For Each cell In Plage_ZC
 
If cell.Offset(0, -2).Value = 0 Then
Plage_ZC.Value.FormulaR1C1 = "=LN(Cell.offset(0,-4)/cell.offset(0,-1)*cell.offset(0,-3))"

Mais comment faire pour coder le calcul du 3eme ?
(Si quelqu'un a réussi à me suivre...)

Merci d'avance !

[michoutri]

Bonjour

J'ai corrigé quelques erreurs dans le code et je pense que la maturité de la première obligation diot être 0.25 et non 0.5. Sinon je ne tombais pas bon!!!

Pour le calcul du 3ème, j'ai pris le numéro de la ligne concerné à laquel j'ai enlevé 1 ou 2 suivant le ZC que l'on voulait prendre.

Je te laisse le code, sachant que le premier ZC de mon tableau était en E2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Sub ZC()
Dim Plage_ZC As Range
Dim cell As Range
 
Range("e2").Select
Set Plage_ZC = Range(Selection, Selection.End(xlDown))
For Each cell In Plage_ZC
 
    If cell.Offset(0, -2).Value = 0 Then
        cell.Formula = "=ln(A" & cell.Row & "/D" & cell.Row & ")/B" & cell.Row
 
    Else
        cell.Formula = "=-(ln(D" & cell.Row & "/(C" & cell.Row & "*exp(-E" & cell.Row - 2 & _
        "*B" & cell.Row - 2 & ")+C" & cell.Row & "*exp(-E" & cell.Row - 1 & "*B" & cell.Row - 1 & _
        "))/(A" & cell.Row & "+C" & cell.Row & "))/B" & cell.Row & ")"
    End If
    cell.NumberFormat = "0.000%"
Next cell
 
End Sub

Ciao

[Bear the french]

Bonsoir Maxnihilist,

La réponse de michoutri est parfaite - mais du point de vue de l'efficacité, ne serait-il pas mieux de coller directement le résultat du calcul plutôt que la formule de calcul ? Tant qu'à utiliser VBA, autant le faire à fonds.
Peut-être pour la traçabilité du calcul ?...

Bertrand

[Maxnihilist]

Bonjour Michoutri et bear-the-french,

Navré pour la réponse tardive...
Merci pour la proposition, elle fonctionne ! Par contre, je voudrais savoir si il est possible de compléter le code de façon à ce que Excel fasse cette opération directement (en prenant en compte tous les ZC précédents).

En somme, un programme qui saurait calculer tous les taux ZC quelque soit leur nombre.
Par exemple, ici le code marche car les cellules sont nommés ("A" & cell.row &) une par une. Mais il peut y avoir beaucoup plus de maturités : 0.25, 0.5, 0.75 ... 12 et donc beaucoup plus de taux ZC (qui agrandiront dont la formule pour le taux ZC suivant).

Comment faire pour qu'Excel établisse le calcul des ZC en "reconnaissant" le nombre de ZC précédents et en les prenant tous en compte dans la formule.
Une boucle dans le IF ?

Merci beaucoup de votre aide,
(encore désolé, j'ignore si je suis assez clair)