[VIDEO] Comment calculer les dates de valeurs bancaire sur Excel ?

Dans cet article, fortement orienté sur la gestion de trésorerie nous allons voir comment calculer simplement les dates de valeurs bancaires avec Excel. Dans un premier temps, nous allons commencer par définir ce que sont les dates d'opérations et les dates de valeurs utilisées par les banques pour calculer leurs intérêts.

1. Définitions des termes

Tout d'abord nous allons définir les principaux termes liés aux dates d’enregistrement des opérations de trésorerie.

   1.1. Dates d’opérations

La date d'opération est la date à laquelle une opération est effectivement réalisée. Ce sera par exemple la date à laquelle un particulier ou un professionnel viendra déposer de l'argent directement guichet de sa banque. Ce sera également la date à laquelle un commerçant va réaliser un encaissement en carte bancaire. Ou encore la date à laquelle un particulier ou un professionnel va émettre un virement. Ce sont tous les types de règlements qu'il peut y avoir entre les individus et/ou les entreprises.

   1.2. Les jours de banque

Les jours de banque correspondent à la différence (en nombre de jours) entre le moment où une opération est réalisée et le moment où elle est effectivement prise en compte par la banque pour calculer des intérêts (débiteurs ou créditeurs) au client.

À l'origine les jours de banque permettaient aux banques d'effectuer les opérations nécessaires à la saisie des écritures. Aujourd'hui la plupart des opérations sont automatisées, et ce délai a été fortement réduit (il est même totalement supprimé pour un grand nombre d’entre elles). Cela dit, les banques continuent quand même de les appliquer.

Le nombre de jours de banque est variable en fonction :

• Des conditions générales de chaque banque,
• Des types d'opérations,
• Des jours d’ouvertures,…

La loi fixe tout de même des durées maximales que les banques sont en droit d’appliquer. En voici quelques exemples :

• Encaissement d’un chèque libellé en euro : le délai maximal sera d'un jour ouvré à compter de la date d’opération,
• Émission d'un chèque libellé en euro : la durée sera d'un jour avant la date d’opération,
• Encaissements d’un virement ou d’un prélèvement : le délai maximal est d'un jour ouvrable,
• Émission d’un virement ou d’un prélèvement : il n'y a pas de délai. La banque comptabilise l’opération sur le compte du client le jour même de son émission,
• Retraits ou dépôts d'espèces : la loi fixe un délai maximum d'un jour ouvrable pour les professionnels. Comme pour les émissions de virement, il n’y a pas lieu de définir de jour de banque pour les retraits ou dépôts réalisés par les particuliers (enregistrement de l’opération le jour même de sa réalisation).

   1.3. Date de valeur

La date de valeur est la date à laquelle l’opération est effectivement prise en compte par la banque pour réaliser les calculs d’intérêts (débiteurs ou créditeurs). Nous venons de voir dans le paragraphe précédent que le jour de banque peut être positif, mais également négatif. Cela signifie donc que la date de valeur peut être avant ou après la date d'opération.

Il faut toujours garder à l'esprit ce décalage car les intérêts calculés par la banque (qu'ils soient au crédit ou au débit) reposent uniquement sur la date de valeur.

2. Exemple

Afin d'illustrer ces notions, prenons l'exemple d'un professionnel dont le solde bancaire et de 2000 euros au 14 avril. Ce même jour il encaisse un chèque de 2000 euros et émet un virement de 1500 euros. S'il n'y avait pas de décalage lié aux jours de banque, le solde à la fin de la journée du 14 avril serait donc de :

2000 + 1000 - 1500 = 1500 euros

Mais compte tenu du décalage imposé par la banque, le chèque sera inscrit sur le compte un jour ouvrable après le dépôt (le 15 avril), tandis que le virement sera déduit du solde le jour même.

Le solde sera donc débiteur de -500 euros durant une journée (la journée du 14 avril) puis le lendemain (le 15 avril) il repassera créditeur à hauteur de 1500 euros. Durant cette journée du 14 avril la banque va facturer des intérêts au titre du découvert.

La notion de date de valeur est donc primordiale lorsque l'on veut gérer sa trésorerie de manière optimale. Il est également important de pouvoir la calculer soit pour vérifier les dates proposées par la banque soit pour pouvoir anticiper des flux de trésorerie à venir.

3. Présentation de la formule SERIE.JOURS.OUVRE.INTL()

Pour calculer une date de valeur avec Excel, il faut utiliser la formule avancée suivante :

=SERIE.JOURS.OUVRE.INTL()

Cette formule est-elle même dérivée d'une autre formule (=SERIE.JOURS.OUVRE()). La différence entre les deux réside dans le fait que dans la fonction =SERIE.JOURS.OUVRE.INTL() que nous allons utiliser permet de préciser les jours exacts d'ouvertures et de fermetures au sein d'une semaine.

Cette formule utilise les paramètres suivant :

=SERIE.JOURS.OUVRE.INTL(date_départ, nb_jours, [nb_jours_week-end], [jours_fériés])

Où :

• date_départ : il s’agit de la date à partir de laquelle le calcul va être effectué. Cette date de départ correspond donc à la date d’opération. Attention, il faut impérativement que la valeur de date_départ soit une date et qu’elle soit reconnue comme telle par Excel.
• nb_jours : il s’agit du décalage en nombre de jours ouvrés, que l’on veut appliquer sur la date_départ que nous venons tout juste de voir. Il s’agit ici de venir saisir le nombre de jours de banque. Ce décalage peut être soit positif (dans ce cas la projection se fera vers l'avant) soit négatif (dans ce cas nous reviendrons avant la date de départ).
• nb_jours_week-end : est le paramètre qui permet de spécifier les jours d'ouverture et de fermeture de la banque. Il est composé d'une série de sept chiffres (des 0 et des 1 uniquement) dans lequel les 0 correspondent aux jours d'ouvertures et les 1 aux jours de fermeture.
  • Ainsi par exemple dans le cadre une ouverture du lundi au vendredi il y aura une série de cinq 0 suivie de deux 1 (« 0000011 ») .
  • Par contre, pour dans le cadre d’une ouverture du mardi au samedi nous allons commencer par un 1 suivi de cinq 0 et pour finir avec un 1 (« 1000001 ».
• jours_fériés : il s’agit de la liste facultatif de dates à exclure. Il peut s'agir soit d'une référence à une plage de cellules contenant des dates, mais il peut également s’agir d'une matrice représentant ces mêmes dates.

4. Calcul de la date de valeur bancaire avec Excel : exemple

Maintenant que nous avons fait connaissance avec la formule =SERIE.JOURS.OUVRE.INTL(), nous allons voir comme l’utiliser afin de déterminer simplement une date de valeur à partir d’une date d’opération.

Pour cela, nous allons réaliser un exemple simple.

Nous disposons ici d'un relevé de banque fictifs dans lequel nous avons des dates d'opérations, le type d'opération correspondant (des virements émis, des remises de chèques, ...), et du montant de l’opération, soit dans une colonne débit, soit dans une colonne crédit, en fonction du sens de l’opération.

Nous disposons également d’un tableau qui reprend les conditions que notre banque applique pour établir le nombre de jours de banques en fonction de chaque opération.

Et enfin, dans un dernier onglet nous retrouvons un tableau constitué d’une liste de jours fériés de l'année 2018 (qui reprend les jours fériés).

Revenons sur notre relevé, pour pouvoir traiter facilement les informations nous allons transformer notre tableau en tableau de données :

• Sélectionner une des cellules du tableau (par exemple la cellule A1, qui est l’entête de la première colonne),
• Puis dans le RUBAN (bandeau constitué des menus et menus en haut de la feuille de calcul), choisissez « Insérer », puis « Tableau »,

• Dans la fenêtre qui s’affiche, vérifiez que la plage de cellule de cellules proposée reprend bien les coordonnées de l’intégralité du tableau (en-tête compris), et cochez l’option « Mon tableau comporte des en-têtes »

• Validez en cliquant sur « OK »
• Votre tableau est maintenant transformé en un tableau de données :
  • La mise-en-forme est enrichie automatiquement (une ligne sur deux voit son fond passer au bleu, l’en-tête est mis en forme,…)
  • Des menus sont ajoutés sur chaque en-tête de colonne pour trier et filtrer les données,
  • Il nous suffira ensuite de saisir notre formule juste à droite du tableau pour qu'automatiquement Excel l’étende sur l'ensemble de la colonne. La saisie des formules est également simplifiée, il suffit en effet de cliquer sur une des cellules contenues sur la même ligne du tableau pour qu’Excel insère le nom de l’en-tête de colonne à la place de ces coordonnées (dans l’exemple qui suit, le premier paramètre « [@Date] » remplace « A2 »).

Sélectionnez la cellule E2 (qui est la première cellule de données à droite du tableau), puis cliquez sur la barre des formules pour saisir la formule suivante :

=SERIE.JOUR.OUVRE.INTL([@Date];SOMME.SI('Jours banque'!$A$2:$A$8;[@[Type opération]];'Jours banque'!$B$2:$B$8);"1000001";'Jours fermeture'!$A$2:$A$12)

Où :

• date_départ → [@Date] : comme nous venons de le voir dans le paragraphe précédent,  paramètre date_départ correspond à la date à partir de laquelle nous allons effectuer notre calcul. Il s’agit donc de la date d'opération de la ligne sélectionnée du relevé,
• le nb_jours → SOMME.SI('Jours banque'!$A$2:$A$8;[@[Type opération]];'Jours banque'!$B$2:$B$8) : nous sommes ici en présence d’une formule imbriquée. En d’autres termes, nous saisissons une formule dans une autre formule. L’objectif de cette formule imbriquée est de récupérer le nombre de jours de banque appliqués par notre banque pour chaque ligne de notre relevé. Grâce à la formule SOMME.SI(), nous allons demander à Excel d’effectuer la somme de tous les jours de banques contenus dans la plage « 'Jours banque'!$B$2:$B$8 », lorsque le nom de l’opération (contenu dans la plage « 'Jours banque'!$A$2:$A$8 ») correspond à l’opération de la ligne du relevé (« [@[Type opération]] »). Il n'y a pas de doublons dans les types d’opérations du tableau des jours de banque, la somme renvoyée sera donc constituée d’une seule ligne.
• nb_jours_week-end → « 1000001 » : Nous l'avons vu tout à l'heure, les « 1 » correspondent aux week-end (jours de fermeture), tandis que les « 0 » correspondent aux jours d’ouverture. Nous considérons ici que notre banque est ouverte du mardi au samedi (un jour de fermeture qui correspond au lundi, suivi de cinq jours d'ouverture et un jour de fermeture qui correspond au dimanche).
• Jours_fériés →'Jours fermeture'!$A$2:$A$12 : Et enfin la liste des jours fériés que nous récupérons dans le tableau de la feuille de calcul « Jours fermeture ».

Note 1 : La première formule à laquelle on pense lorsqu’il s’agit d’effectuer une recherche est la formule =RECHERCHEV(). Nous pourrions en effet très bien nous en servir, en effectuant une recherche sur l'intitulé du tableau (première colonne). Mais l'inconvénient de la fonction RECHERCHEV() est que les champs qui permettent d’effectuer notre recherche doivent être classés par ordre alphabétique. Ce qui n'est pas le cas ici.

C’est pourquoi nous avons eu recours à une autre formule qui est la formule SOMME.SI(). Celle-ci se prête tout à fait à ce genre de recherche étant donné qu'il n'y a pas doublon dans les champs de recherche et que le champ retourné sera un champ de type numérique.

Note 2 : Lorsque nous faisons référence à une cellule ou une plage de cellules extérieure au tableau de données, nous utilisons des références absolues, identifié avec les symboles dollar (« $ »). Cela permet d’étendre une formule à d’autres cellules en conservant les références aux mêmes cellules.

En effet, si nous avions utilisé des références relatives (sans symboles dollar), les références se seraient décalées lorsque la formule aurait été étendue. Par exemple la référence aux jours de fermetures (« 'Jours fermeture'!A2:A12 ») saisi dans la cellule E2 serait devenue « 'Jours fermeture'!A3:A13 » lorsque la formule aurait été étendue à la cellule E3.

La saisie de la formule est maintenant terminée, il ne reste plus qu'à affecter un format de type « Date » à la colonne pour obtenir des dates de valeurs clairement identifiables.