Monsieur Excel
Pour tout savoir faire sur Excel !

30 juin 2008

Insertion automatique de lignes

Une lectrice m’a demandé récemment comment, dans un tableau tel que ci-dessous – mais qui en réalité comporte des centaines de lignes représentant des mesures automatiques – insérer automatiquement une ligne chaque fois qu’il manque une mesure, celles-ci devant normalement être prises toutes les 15 minutes :

Bien entendu, la seule façon d’assurer une telle tâche est de passer par une macro. Voici la macro que j’ai écrite à cet effet, ainsi que son résultat :

26 juin 2008

Récupération depuis le Web

Pour commencer, je tiens à remercier François-Alexandre Léonard, un de mes élèves d’HEC, qui a effectué le travail de recherche ayant mené à la rédaction de cet article.

Supposons que vous souhaitiez récupérer, depuis un site Web, des données diverses : cours de bourse, taux de change, résultats sportifs,…

Pour cela, dans Excel, utilisez la commande Données – Données externes – Nouvelle requête sur le Web et entrez l’adresse du site désiré. Chaque bloc apparaît alors avec une flèche qui vous permet de sélectionner ce bloc. Quand vous sélectionnez un bloc, ce bloc est grisé et la flèche se transforme en symbole de validation sur fond vert :

Après avoir sélectionné le(s) bloc(s) voulu(s), cliquez dans le bouton Enregistrer – en haut à droite de l’écran, à gauche du bouton « Options » – puis validez, et cliquez enfin sur le bouton « Importer », en bas de la page.

Excel récupère alors les données, qu’il ne vous reste plus qu’à convertir (cf. copie d’écran ci-dessous) en utilisant la technique décrite dans mon message du 24 avril 2008.

Remarque 1 – Les requêtes sont enregistrées par défaut sur le disque C.

Remarque 2 – Pour récupérer toute la page, cliquez dans la flèche en haut à gauche de la page.

Remarque 3 – Pour rafraîchir dans Excel le tableau importé du Web, utilisez la commande « Actualiser les données », en bas du menu Données, qui devient active quand le curseur se trouve dans un bloc correspondant à un lien Web.



21 juin 2008

Les fonctions financières de l'UA (2)

Suite et fin de la liste des fonctions financières de l'utilitaire d'analyse...

REND.DCOUPON.IRREG – Renvoie le taux de rendement d'un titre dont la dernière période de coupon est irrégulière.

REND.PCOUPON.IRREG – Renvoie le taux de rendement d'un titre dont la première période de coupon est irrégulière.

RENDEMENT.BON.TRESOR – Calcule le taux de rendement d'un bon du Trésor.

RENDEMENT.SIMPLE – Calcule le taux de rendement d'un emprunt à intérêt simple (par exemple, un bon du Trésor).

RENDEMENT.TITRE – Calcule le rendement d'un titre rapportant des intérêts périodiquement.

RENDEMENT.TITRE.ECHEANCE – Renvoie le rendement annuel d'un titre qui rapporte des intérêts à l'échéance.

TAUX.EFFECTIF – Renvoie le taux d'intérêt annuel effectif.

TAUX.ESCOMPTE – Calcule le taux d'escompte d'une transaction.

TAUX.ESCOMPTE.R – Renvoie le taux d’escompte rationnel d’un bon du trésor.
Fonction non listée dans l’aide d’Excel, mais qui marche…

TAUX.INTERET – Affiche le taux d'intérêt d'un titre totalement investi.

TAUX.NOMINAL – Calcule le taux d'intérêt nominal annuel.

TRI.PAIEMENTS – Calcule le taux de rentabilité interne d'un ensemble de paiements.

TTBILLEQ – Renvoie le taux d'escompte rationnel d'un bon du Trésor.
Fonction listée dans l’aide d’Excel, mais qui ne marche pas…

VALEUR.ENCAISSEMENT – Renvoie la valeur d'encaissement d'un escompte commercial, pour une valeur nominale de 100 F.

VALEUR.NOMINALE – Renvoie la valeur nominale d'un effet de commerce.

VAN.PAIEMENTS – Donne la valeur actuelle nette d'un ensemble de paiements.

VC.PAIEMENTS – Calcule la valeur future d'un investissement en appliquant une série de taux d'intérêt composites.

Remarque 1 – Une fois de plus, nous constatons que l’aide d’Excel est loin de la perfection, ne serait-ce que parce qu’elle ignore encore souvent l’euro ! Mais je serai probablement mort et enterré avant que Microsoft ne s’intéresse à la faire enfin revoir et corriger par quelqu'un de compétent…

Remarque 2 – L’aide en ligne sur les fonctions est inexistante : certes, Microsoft décrit brièvement chaque fonction et propose ses arguments mais, quand on clique dans l’assistant fonction, sur le bouton « Aide sur cette fonction », on obtient une fenêtre d’aide totalement vide.

17 juin 2008

Les fonctions financières de l'UA (1)

L’utilitaire d’analyse est un add-in (macro complémentaire) fourni avec Excel.et dont nous avons parlé en particulier le 25 février 2007. Cet add-in ajoute en réalité 38 fonctions financières aux 16 fonctions présentées il y a quatre jours. En voici une brève description, dont nous terminerons la liste dans le prochain message.

AMORDEGRC – Renvoie l'amortissement correspondant à chaque période comptable en utilisant un coefficient d'amortissement.

AMORLINC – Renvoie l'amortissement correspondant à chaque période comptable.

CUMUL.INTER – Renvoie l'intérêt cumulé payé sur un emprunt entre deux périodes.

CUMUL.PRINCPER – Renvoie le montant cumulé des remboursements du capital d'un emprunt effectués entre deux périodes.

DATE.COUPON.PREC – Renvoie la date de coupon précédant la date de règlement.

DATE.COUPON.SUIV – Renvoie la première date de coupon ultérieure à la date de règlement.

DUREE – Renvoie la durée, en années, d'un titre dont l'intérêt est perçu périodiquement.

DUREE.MODIFIEE – Renvoie la durée de Macauley pour un titre d'une valeur nominale supposée égale à 100 F.

INTERET.ACC – Renvoie l'intérêt couru non échu d'un titre dont l'intérêt est perçu périodiquement.

INTERET.ACC.MAT – Renvoie l'intérêt couru non échu d'un titre dont l'intérêt est perçu à l'échéance.

NB.COUPONS – Renvoie le nombre de coupons dus entre la date de règlement et la date d'échéance.

NB.JOURS.COUPONS – Affiche le nombre de jours pour la période du coupon contenant la date de règlement.

NB.JOURS.COUPON.PREC – Calcule le nombre de jours entre le début de la période de coupon et la date de règlement.

NB.JOURS.COUPON.SUIV – Calcule le nombre de jours entre la date de règlement et la date du coupon suivant la date de règlement.

PRIX.BON.TRESOR – Renvoie le prix d'un bon du Trésor d'une valeur nominale de 100 F.

PRIX.DCOUPON.IRREG – Renvoie le prix par tranche de valeur nominale de 100 F d'un titre dont la dernière période de coupon est irrégulière.

PRIX.DEC – Convertit un prix en euros exprimé sous forme de fraction en un prix en euros exprimé sous forme de nombre décimal.

PRIX.FRAC – Convertit un prix en euros exprimé sous forme de nombre décimal en un prix en euros exprimé sous forme de fraction.

PRIX.PCOUPON.IRREG – Renvoie le prix par tranche de valeur nominale de 100 F d'un titre dont la première période de coupon est irrégulière.

PRIX.TITRE – Renvoie le prix d'un titre dont la valeur nominale est 100 F et qui rapporte des intérêts à l'échéance.

PRIX.TITRE.ECHEANCE – Renvoie le prix d'un titre dont la valeur nominale est 100 F et qui rapporte des intérêts à l'échéance.

13 juin 2008

Les autres fonctions financières

Excel vous propose en standard 16 fonctions financières, dont voici la liste exhaustive avec leur syntaxes, ainsi qu’une brève description et la référence éventuelle à l’un des quatre articles précédents.

=AMORLIN(coût;valeur_rés;durée)
Calcule l'amortissement linéaire d'un bien pour une période donnée :

=DB(coût;valeur_rés;durée;période;mois)
Renvoie l'amortissement d'un bien pour une période spécifiée en utilisant la méthode de l'amortissement dégressif à taux fixe.

=DDB(coût;valeur_rés;durée;période;facteur)
Renvoie l'amortissement d'un bien pour toute période spécifiée, en utilisant la méthode de l'amortissement dégressif à taux double ou selon un coefficient à spécifier.

=INTPER(taux;pér;npm;va;vc;type)
Calcule le montant des intérêts d'un investissement pour une période donnée.

=ISPMT(taux;pér;npm;va)
Calcule l'intérêt payé pour une période donnée d'un investissement.

=NPM(taux;vpm;va;vc;type) – cf. 9 juin 2008
Renvoie le nombre de versements nécessaires pour rembourser un emprunt.

=PRINCPER(taux;pér;npm;va;vc;type)
Calcule, pour une période donnée, la part de remboursement du principal d'un investissement.

=SYD(coût;valeur_rés;durée;période)
Calcule l'amortissement d'un bien pour une période donnée sur la base de la méthode américaine Sum-of-Years Digits (amortissement dégressif à taux décroissant appliqué à une valeur constante).

=TAUX(npm;vpm;va;vc;type;estimation) – cf. 9 juin 2008
Calcule le taux d'intérêt par période d'un investissement donné.

=TRI(valeurs;estimation) – cf. 1er et 5 juin 2008
Calcule le taux de rentabilité interne d'un investissement pour une succession de trésoreries.

=TRIM(valeurs;taux_emprunt;taux_placement)
Calcule le taux de rentabilité interne lorsque les paiements positifs et négatifs sont financés à des taux différents.

=VA(taux;npm;vpm;vc;type) – cf. 9 juin 2008
Calcule la valeur actuelle d'un investissement.

=VAN(taux;valeur1;valeur2;...) – cf. 24 et 28 mai 2008
Calcule la valeur actuelle nette d'un investissement basé sur une série de décaissements et un taux d'escompte.

=VC(taux;npm;vpm;va;type)
Renvoie la valeur future d'un investissement.

=VDB(coût;valeur_rés;durée;période_début;période_fin;facteur;valeur_log)
Calcule l'amortissement d'un bien pour toute période spécifiée, y compris une période partielle, en utilisant la méthode de l'amortissement dégressif à taux double ou selon un coefficient à spécifier. VDB signifie « variable declining balance », qui est l'équivalent d'amortissement dégressif à taux variable.

=VPM(taux;npm;va;vc;type) – cf. 9 juin 2008
Calcule le paiement périodique d'un investissement donné.

09 juin 2008

Le remboursement d’un prêt

Pour les calculs liés au remboursement d’un prêt, Excel vous propose quatre fonctions. En effet, on distingue quatre paramètres dans le calcul d’un prêt : le montant emprunté, la durée du prêt, le taux (annuel, mensuel ou autre) et le versement (annuel, mensuel ou autre).

Ces quatre paramètres sont liés : quand on en connaît trois, cela détermine automatiquement la valeur du quatrième paramètre. C’est ce que nous avons illustré ci-dessus pour un prêt de 1 M€ sur 12 ans à un taux annuel de 6 %, avec des remboursements mensuels.

Calcul du versement (colonne B)

La colonne B montre comment nous avons calculé le taux mensuel puis le versement mensuel, à partir du taux annuel, du nombre de versements et du montant emprunté.

Calcul du taux mensuel (colonne C)

La colonne C montre comment nous avons calculé le taux mensuel, à partir du nombre de versements, du versement mensuel et du montant emprunté.

Calcul de la durée du prêt (colonne D)

La colonne D montre comment nous avons calculé la durée du prêt, à partir du taux mensuel, du versement mensuel et du montant emprunté.

Calcul du montant emprunté (colonne E)

La colonne E montre comment nous avons calculé le montant emprunté, à partir du taux mensuel, du nombre de versements et du versement mensuel.

05 juin 2008

Evaluation de TRI multiples

Dans la mesure où l’échéancier des cash-flows contient plusieurs valeurs positives et plusieurs valeurs négatives, on peut identifier plusieurs taux de rendement interne. Dans l’exemple ci-dessous, il y a trois taux, dont on peut obtenir la valeur exacte à l’aide de la fonction TRI() en D5:D7, en partant respectivement des valeurs initiales 0%, 20% et 100%.

Si vous voulez en voir d’autres exemples, voici des échéanciers divers avec – en bleu, à gauche – les trois taux de rendement interne obtenus pour chaque échéancier :

01 juin 2008

Le taux de rendement interne (TRI)

Le taux de rendement interne (TRI) est le taux d’actualisation pour lequel la valeur actuelle nette est nulle.

La syntaxe de la fonction est =tri(cash-flows[;estimation])

Pour le projet que nous avons étudié il y a huit jours, représenté par le bloc A1:G2, le taux de rendement interne (cf. cellule D4) est de 12,05 %.

Il peut ne pas y avoir de taux de rendement interne, comme il peut y en avoir plusieurs. Dans l’exemple des lignes 6 et 7, dont la courbe des VANs selon le taux d’actualisation est fournie plus bas, il y en a deux que l’on peut identifier séparément – cf. cellules D10 et D11 – en partant respectivement de 0% et de 100%.

Si vous voulez en savoir plus sur le taux de rendement interne, et sur son intérêt par rapport à la VAN, je vous recommande un article très intéressant de Ray Martin, « Internal Rate of Return Revisited » :

http://members.tripod.com/~Ray_Martin/DCF/nr7aa003.html