Calculer vos mensualités d'emprunt

Comment calculer votre remboursement d'emprunt

Il nous est tous arrivés un jour d'avoir eu besoin de réaliser un prêt ou un emprunt pour l'achat d'un bien immobilier, véhicule ou autre...

Savez-vous qu'Excel peut vous aider à savoir quel sera votre remboursement périodique à partir d'un montant initial, d'une durée et d'un taux d'intérêt constant.

Pour illustrer cette fonction, prenons l'exemple ci-dessous qui reprend les conditions initiales d'un prêt : 

La fonction permettant de calculer vos remboursements périodiques (dans le cas présent vos mensualités) est la fonction : VPM

Syntaxe : VPM(taux; npm; va; [vc]; [type])

• taux  :  Obligatoire. Représente le taux d’intérêt de l’emprunt.
• npm  :  Obligatoire. Représente le nombre de remboursements pour l’emprunt.
• va  :  Obligatoire. Représente la valeur actuelle ou la valeur que représente à la date d’aujourd’hui une série de remboursements futurs ; il s’agit du principalement de la valeur d'achat.
• vc  :  Facultatif. Représente la valeur capitalisée, c’est-à-dire le montant que vous souhaitez obtenir après le dernier paiement. Si vc est omis, la valeur par défaut est 0 (zéro), c’est-à-dire que la valeur capitalisée d’un emprunt est égale à 0.
• type :   Facultatif. Représente le nombre 0 (zéro) ou 1 et indique quand les paiements doivent être effectués.

Calcul : 

• Se positionner sur la cellule D12
• Taper la formule  : =VPM(D6/D8;D10*D8;D4;0;0)

ou comme représenté dans la boite d'arguments de la fonction :

Explications : 

• Taux : D6/D8 (Nous divisons le taux annuel par 12 pour le ramener en mois)
• Npm :  D10*D8 (Durée de 6 ans multipliée par 12 mois =>72 mensualités)
• VA : D4 (Représente notre valeur d'achat)
• VC et Type : 0 (Champ facultatif)

Résultat : 

• Nous obtenons la valeur : -239,49 €
  La valeur est négative car Excel comprend que c'est de l'argent que vous allez sortir de votre portefeuille et non une épargne. 
• Pour remédier à cela, il suffit de rajouter le signe moins - devant la fonction, comme ceci : =-VPM(D6/D8;D10*D8;D4;0;0)

Nous obtenons : 

Dès à présent, il est possible de modifier votre valeur d'achat, le taux ou la durée afin de réaliser des simulations.

Bien sûr Excel utilise bien d'autres fonctions financières, notamment permettant de calculer les intérêts, l'amortissement d'un prêt afin de réaliser un tableau d'amortissement.

La création d'un tableau d'amortissement qui fera l'objet d'un de mes prochains tutoriels.

Utiliser la Valeur Cible

Savoir utiliser la fonction Valeur Cible

Nous utilisons dans Excel des fonctions qui permettent de calculer un résultat à partir de données. A l'inverse, il est parfois intéressant de retrouver des données à partir d'un résultat.

Définition :  la Valeur Cible permet de rechercher un résultat spécifique pour une cellule en ajustant la valeur d'une autre cellule.

Pour schématiser et imager l'utilité de la Valeur Cible :

Prenons l'exemple d'achat de barres de chocolat au lieu de calculer le prix pour acheter mes friandises, là je vais déterminer combien de chocolat je peux acheter avec la somme dont je dispose.

Mais mieux vaut un exemple pour illustrer cette fonction : Prenons le cas d'un commerçant qui souhaite faire des calculs lui permettant de fixer son prix de vente, son prix d'achat et  sa marge. 

La situation actuelle :

En cellule C11 la formule est : =C9/(1-C10)

Après étude de la concurrence, il doit fixer son prix vente à 90€ pour rester compétitif. Il souhaite garde sa marge actuelle mais peut négocier un prix d'achat plus faible,

Mais lequel ?

=> Utilisons la valeur cible

• Onglet Données/Groupe Outils de données/Analyses scénarios/Valeur Cible...
• Compléter la boite de dialogue comme indiqué ci-dessous puis valider par OK : 

Explications : 

• Cellule à définir : C11 (le prix de vente)
• Valeur à atteindre : 90 (le commerçant décide de fixer son prix de vente à 90€)
• Cellule à modifier : C9 (le prix d'achat)

La réponse : 

L'outil Valeur Cible indique qu'il a trouvé une solution de calcul et met à jour les valeurs après avoir valider par OK.

Donc notre commerçant devra négocier un prix d'achat à 72€ s'il veut garder sa marge actuelle et fixer un prix de vente à 90€

Un autre exemple :

    

 Objectif : Quelle devra être la Note 3 de l'élève pour que sa moyenne soit égale à 10 ?

Je vous laisse essayer de trouver la solution sur le même principe que précédemment. 

:

:

:

Alors vous avez trouvé combien ? 

Je vous donne la réponse, l'élève doit obtenir une note de 13 s'il veut avoir une moyenne de 10 sur l'ensemble de ses résultats.

     Solution : 

Mettre en évidence une zone d'un Graphique

Mettre en évidence une zone définie d'un graphique

Il peut être important lors d'une représentation d'un graphique de mettre en évidence une zone particulière d'un graphique.

Nous allons prendre l'exemple du tableau ci-dessous représentant les ventes annuelles d'une entreprise. 

Les données affichées ne montrent pas qu'en fin d'année, l'entreprise à mis sur le marché la commercialisation d'un nouveau produit dès Septembre. Nous allons donc montrer sous forme graphique cette évolution. 

En effet, si nous réalisons une représentation graphique des données, nous obtenons : 

Ce que nous souhaiterions, c'est mettre en évidence les 4 derniers mois en mettant une couleur de fond sur le graphique. Pour réaliser cela, la solution sera de créer une seconde série de données. 

• Créer une série de données dans les cellules D12:D15 et écrire la valeur 100.000 qui représente la valeur maximale de l'axe vertical actuel
• Fixer l'axe vertical à la valeur 100.000 :
• clic droit sur l'axe vertical puis mise en forme de l'axe 
• Choisir dans maximum le bouton fixe et écrire la valeur 100000

• Cliquer sur un bord du graphique, la plage des cellules sources du graphique est encadré, faire glisser le cadre pour rajouter les données de la colonne D

Maintenant , nous allons modifier la seconde série sans changer la 1ère série. Pour réaliser cela nous allons mettre la nouvelle série sur un axe secondaire :

• Clic droit sur la seconde série puis cliquer sur mettre en forme une série de données...
• Dans la nouvelle boite de dialogue : 

• Dans l'onglet remplissage : 

• Pour finir cliquer sur le deuxième axe vertical à droite et supprimer le en tapant sur la touche Suppr.

Nous obtenons la représentation graphique suivante : 

Faire un graphique avec une Mise en forme conditionnelle

Comment réaliser un graphique en utilisant les Mises en forme conditionnelle

Nous allons voir comment réaliser un représentation graphique en utilisant les mises en forme conditionnelle.

Sans avoir à créer systématiquement des graphiques, il est possible d'obtenir une représentation graphique des données grâce à une mise en forme conditionnelle. 

Prenons comme exemple  le tableau ci-dessous : 

1. Mise en forme des ventes réalisées par le commercial Michel :

• Sélectionner la plage de cellule D4:D8
• Dans le ruban, groupe Accueil/Mise en forme conditionnelle/Nouvelle règle...
• Dans la boite de dialogue, compléter les paramètres suivants : 

2. Mise en forme des ventes réalisées par le commercial Jacques :

• Sélectionner la plage de cellule B4:B8
• Dans le ruban, groupe Accueil/Mise en forme conditionnelle/Nouvelle règle...
• Dans la boite de dialogue, compléter les paramètres suivants : 

3. Le résultat est le suivant  :

La sélection de cellules ou tableaux

Comment sélectionner des cellules ou tableaux

Dans cet article je vais vous montrer des astuces pour sélectionner des zones de cellules ou un tableau plus efficacement vous permettant ainsi de gagner du temps dans votre travail quotidien sur Excel.
Prenons le cas du tableau ci-dessous, je vais vous montrer plusieurs méthodes pour sélectionner les cellules d'un tableau :

Méthode n° 1 : Utiliser la touche clavier Fin

• Sélectionner A1 
• Appuyer sur la touche Fin (ou End) de votre clavier
• En appuyant sur la touche Maj ou Shift du clavier, cliquer sur D10
• Appuyer sur la touche Fin (ou End) de votre clavier pour sortir du mode Fin

 

Méthode n° 2 : Etendre la sélection (Touche F8)

• Sélectionner A1 
• Taper sur la touche F8 de votre clavier (pour activer le mode étendu)
• Cliquer sur la cellule D10
• Taper sur la touche F8 de votre clavier (pour retirer le mode étendu)

Méthode n° 3 : Zone en cours

• Cliquer sur n'importe quelle cellule du tableau A1:D10
• Appuyer sur Ctrl + A de votre clavier

Méthode n° 4 : Zone en cours

• Cliquer sur n'importe quelle cellule du tableau A1:D10
• Appuyer sur Ctrl + T de votre clavier
• Dans la boite de dialogue "Atteindre", cliquer sur "Cellules..."
• Choisir Zone en cours 
• Valider par OK

Méthode n° 5 : Maj + Clic

• Sélectionner A1 
• Taper sur la touche Maj (ou Shift) de votre clavier sans la relâcher
• puis Cliquer sur la cellule D10

Compléter les vides d'un tableau

Comment compléter les vides d'un tableau

Prenons l'exemple du tableau ci-dessous :

Il vous est tous arrivés un jour d'avoir eu à travailler sur un tableau identique qui nous empêche de réaliser des tris ou des filtres car des champs vides sont présents. 

Pour palier cela, il faut compléter les espaces vides par les champs correspondants.

Je vais vous montrer une astuce pour ne pas perdre du temps à saisir ces champs.

• Sélectionner le tableau à partir de A2:D10
• Utiliser le raccourci clavier Ctrl+T pour avoir la boite de dialogue « Atteindre »
• Cliquer sur Cellules…
• Choisir Cellules Vides, Valider par OK
• Ecrire =A2, Valider avec Ctrl+Entrée

Et voilà les champs ont été complétés :