Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

Calcul de remboursement d’emprunt

À vous de jouer

Dans ce dernier chapitre, nous vous proposons de construire seul le projet qui vous sera décrit en détail dans les paragraphes qui suivent.

Vous devrez, comme nous l’avons fait dans les chapitres précédents, prévoir deux solutions : sans macro et avec macro.

Les solutions pouvant être diverses et variées, nous vous proposerons un exemple de solution que vous pourrez découvrir dans les fichiers à télécharger.

Mais auparavant, quelques explications pour bien cerner le besoin et aussi quelques fonctions Excel à découvrir.

Descriptif du projet

L’objectif sera de mettre en place un outil pour automatiser le calcul du remboursement de l’emprunt et la création de l’échéancier de remboursement, à partir d’éléments d’information tels que capital emprunté, taux, durée, mode de calcul.

Nous allons expliquer les deux types d’emprunts que nous vous demanderons d’automatiser.

Les différentes méthodes de calcul de l’emprunt

Nous allons voir en détail, dans leur principe et dans leur calcul, deux méthodes de remboursement de l’emprunt :

  • Par amortissement constant

  • Par annuité constante

Emprunt à amortissement constant

Définition

Contracter un emprunt à amortissement constant signifie rembourser le capital emprunté par parts égales sur la durée de l’emprunt.

Il se rajoutera à cette part de capital à rembourser, le montant de l’intérêt calculé chaque année en fonction du montant restant dû.

En conséquence, le montant total de l’échéance à régler sera variable.

Calcul

Prenons un exemple pour mieux comprendre. Supposons que nous contractons un emprunt de 20 000 € remboursable sur 5 ans à un taux de 5%. Nous allons calculer le montant à rembourser par année.

Amortissement constant...

Quelques fonctions financières

Si vous êtes amenés à travailler sur la gestion et le suivi des emprunts, les fonctions qui vont suivre pourraient vous être utiles.

CUMUL.INTER

Renvoie le montant total (cumulé) de l’intérêt à payer sur un emprunt et dans une période définie.

=CUMUL.INTER(taux; npm;va;période début; période fin;type)

Les arguments npm, va et type méritent quelques explications :

npm : représente le nombre de périodes de remboursement (5 pour 5 ans ou 5 mois…)

va : valeur actuelle de l’emprunt

type : échéance de règlement : 0 pour fin de période, 1 pour début de période.

Nous gardons le même exemple que ci-dessus pour calculer l’emprunt cumulé sur les 2 premières années.

 Reproduisez les éléments ci-dessous :

images/07SOB03N.PNG

 Cliquez en B7 et activez le bouton Financier dans l’onglet Formules.

 Sélectionnez la fonction CUMUL.INTER et complétez les zones de l’assistant comme indiqué :

images/07SOB04N.PNG

 Attention avant de valider, faites défiler la barre de défilement pour voir le dernier argument de la fonction qui est le type et que nous positionnerons à 0.

 Validez par OK.

Vous obtenez -1819.025202.

Si vous regardez notre tableau du paragraphe Emprunt à...

Définition du travail attendu : précisions et contraintes imposées

Une fiche calcul de l’emprunt automatisée sans macro

Vous aurez à créer une fiche de calcul de l’emprunt dans laquelle les informations à saisir seront :

  • Le montant emprunté

  • La méthode utilisée (amortissement constant ou annuité constante)

  • La durée en année pour simplifier et pouvant aller jusqu’à 20 ans

  • Le taux d’intérêt

  • La date de l’emprunt

Le tableau de remboursement devra :

  • Se calculer en fonction de la méthode choisie.

  • Faire afficher l’année exacte de remboursement.

  • Avoir un nombre de lignes adapté à la durée ; la mise en forme doit suivre la taille du tableau.

  • Vous devrez arrondir les valeurs à deux décimales par calcul et non par format ; et prévoir que la dernière annuité soit éventuellement ajustée pour qu’il n’y ait pas de problème de centimes en trop ou en moins.

Une fiche de calcul de l’emprunt automatisée avec macro

Dans cette version, vous devrez obtenir le tableau de remboursement en cliquant sur un bouton qui lancera la macro.

Les données à saisir et les contraintes restent les mêmes.

Vous trouverez en proposition de corrigé les fichiers Emprunt.xlsx et Emprunt avec macro.xlsm à...