Name: Gestion comptable avec Excel (versions 2016 et 2019)
Rating: 5 (3 reviews)

À 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 :

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é :

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.