Excel Vba,Finance - PDF

							                                Examen Terminal

            Gestion de Portefeuille sur VBA( Finance A & B )

                        Pratique d’Eviews (Finance A )

                  Msc 1 : Banque & Ingénierie Financière

                                Dr Babacar Sène

                                   2009 – 2010

                         Présentez bien votre travail

           Première Partie : Gestion de Portefeuille sur VBA

Cas Pratique : Gestion de fonds et Conseil en Investissement
Vous venez d’être recruté comme gestionnaire d’actifs dans une banque de
marchés. Cette dernière a reçu des fonds collectés auprès de sa clientèle privée (
particuliers et investisseurs institutionnels ). Vous devez mettre en place un
portefeuille de titres et gérer sa performance et son risque grâce aux outils de
modélisation d’ Excel et VBA. Votre méthodologie consiste à mettre en place
une méthode de simulation fondée sur 5 fonds d’actifs et d’utiliser les critères
cités plus haut pour les comparer :

Composition des différents fonds selon votre stratégie :

Fonds           Action 1           Action 2        Action 3       Action 4
1               X                  Y               Z              W
2               A                  B               X              Z
3               X                  Y               A              B
4               A                  B               C              W
5               X                  A               C              Z
( Pour les données voir le fichier Excel joint )

1°) En partant des différents fonds et en utilisant le code VBA suivant,
calculer le rendement et le risque de chaque titre qui compose les fonds :
Option Base 1
Sub Rendement()

Dim plage_rentabilités As Range

Dim Feuille As Worksheet

Dim Nombre_lignes, numéro_ligne As Integer

Nombre_lignes = Worksheets.Count - 1

ReDim tableau_résultats(Nombre_lignes, 3) As Variant

For Each Feuille In Worksheets

If Feuille.Name <> "rendrisk" Then

Feuille.Activate

numéro_ligne = numéro_ligne + 1

Range("C3").Select

Set plage_rentabilités = Range(Selection, Selection.End(xlDown)).Offset(-1, 1)

plage_rentabilités.FormulaR1C1 = "=LN((R[1]C2+RC3)/RC2)"

tableau_résultats(numéro_ligne, 1) = Feuille.Name

tableau_résultats(numéro_ligne, 2) = WorksheetFunction.Count(plage_rentabilités)

tableau_résultats(numéro_ligne, 3) = WorksheetFunction.StDev(plage_rentabilités)

End If

Next Feuille

Worksheets("rendrisk").Activate

For i = 1 To Nombre_lignes

For j = 1 To 3

Cells(i + 1, j).Value = tableau_résultats(i, j)

Next j

Next i

End Sub

2°) Comparer les différents titres en terme de rendement et de risque
3°) La première étape étant terminée, l’objectif est maintenant de comparer les
rendements et les risques des différents fonds : On vous donne les parts investies
sur chaque fonds :

Fonds             Action 1           Action 2            Action 3   Action 4
1                 20%                20%                 20%        20%
2                 30%                20%                 20%        30%
3                 10%                30%                 20%        40%
4                 30%                30%                 20%        20%
5                 20%                20%                 30%        30%


En utilisant le calcul matriciel déterminez le fonds le plus rentable et le plus
risqué. Faites le même calcul en utilisant le code VBA suivant :
Function Rendfonds(retvec, wtsvec)

If Application.Count(retvec) = Application.Count(wtsvec) Then

If retvec.Columns.Count <> wtsvec.Columns.Count Then

wtsvec = Application.Transpose(wtsvec)

End If

PortfolioReturn = Application.SumProduct(retvec, wtsvec)

Else

End If

End Function



Function Riskfonds(wtsvec, vcvmat)

Dim nc As Integer, nr As Integer

Dim v1 As Variant

nc = wtsvec.Columns.Count

nr = wtsvec.Rows.Count

If nc > nr Then wtsvec = Application.Transpose(wtsvec)

v1 = Application.MMult(vcvmat, wtsvec)
PFVariance = Application.SumProduct(v1, wtsvec)

End Function

Interprétez les lignes du programme des rendements et du risque

Quel est le fonds le plus rentable et le risqué ?

Conseillerez vous à la banque d’investir sur ces fonds ( justifiez votre réponse) .

 4°) Une société de capital risque veut s’inspirer de votre méthodologie pour faire le
choix entre différents projets ( investissement sur des sociétés non cotées ) . On vous
donne les informations sur la feuille 2 du classeur d’Excel

Grâce aux codes suivants déterminez les rendements et les risques de chaque projets en
partant des cash flows prévisionnels et des probabilités

Function ExpVal(vvec, pvec)
If Application.sum(pvec) <> 1 Or –
Application.Count(vvec) <> Application.Count(pvec) Then
ExpVal = -1
Exit Function
ElseIf pvec.Rows.Count <> vvec.Rows.Count Then vvec =
Application.Transpose(vvec)
End If
ExpVal = Application.SumProduct(vvec, pvec)
End Function

Function WVariance(vvec, pvec)
Dim expx
Dim i As Integer
If Application.sum(pvec) <> 1 Or –
Application.Count(vvec) <> Application.Count(pvec) Then
WVariance = -1
Exit Function
End If
expx = ExpVal(vvec, pvec) ’using the ExpVal function
WVariance = 0
For i = 1 To Application.Count(pvec)
WVariance = WVariance + pvec(i)*(vvec(i) - expx)^2
Next i
WVariance = Application.Sum(WVariance)
End Function

                     Deuxième Partie : Pratique d’Eviews
Vous devez estimer les bêtas des différents actions et fonds en utilisant l’approche

d’évaluation Financière des actions cotées à l’aide du Capital Asset Pricing Model.

1 - Importer les données liées aux rentabilités estimées avec VBA sur Eviews

2 - Déterminez les graphiques des rendements des différents titres ( copier et coller sur Word
)

3 - Déterminez les statistiques descriptives et interprétez pour chaque titre avec
l’histogramme de fréquence ( copier et coller sur Word )

Le taux annuel des Obligations notées AAA étant égal à 6% calculez le taux équivalent
journalier

4 - Estimez les bêtas des titres à l'aide du CAPM ( Capital Asset Pricing Model ) et
interprétez à l’aide des tests de Student ou des probabilités critiques

5 – Grâce à la requête procs poser le modèle de façon linéaire

6 - Déterminez la matrice des corrélations et la matrice variance - covariance

7 - Déterminez les statistiques descriptives en déduire la rentabilité moyenne du marché

8 - Comparer les estimations du CAPM par rapport à celles du modèle de Marché

9 - Décomposez le risque total de chaque titre en risque systématique et en risque spécifique.

10 - A partir du risque total de chaque titre quel est l’actif le plus risqué

11- A partir des rendements moyens des titres, quel est le titre le plus rentable

12- En interprétant les distributions de rendement des titres, lesquels suivent une loi normale

13 - Calculez les ratios de Sharpe et Treynor des différents fonds et comparer les
performances de ces fonds.