Excel : créer un suivi automatique des immobilisations avec amortissement linéaire, prorata temporis et alerte de fin
Automatisez dans Excel le suivi des immobilisations : amortissement linéaire, prorata temporis, VNC et alerte de fin avec SI, DATEDIF, EDATE.
- Difficulté
- Intermédiaire
Vous pouvez automatiser un suivi d’immobilisations dans Excel avec un tableau principal et quelques formules standard. Le plus simple est de calculer la date de fin, l’annuité linéaire, le cumul, la VNC et une alerte de fin à partir d’une date d’arrêté unique.
Ce modèle reste générique. Les conventions de prorata, d’arrondi ou de fin de plan peuvent varier selon votre organisation : vérifiez-les avant usage.
Prérequis
Disposer d’Excel avec les fonctions SI, DATEDIF, EDATE et SOMME.SI.ENS. Savoir créer un tableau Excel et recopier des formules.
Matériel nécessaire
- Excel
- Liste des immobilisations avec date d’acquisition, coût et durée d’amortissement
Étapes
Créer la structure du tableau
Créez les colonnes Référence, Désignation, Date d’acquisition, Base amortissable, Durée (mois), Date de fin, Annuité mensuelle, Mois écoulés, Amortissement cumulé, VNC et Alerte.
Ajoutez une cellule de pilotage pour la date d’arrêté, par exemple en B1. Toutes les formules s’appuieront sur cette date.
Saisir les données de base
Renseignez la date d’acquisition, la base amortissable et la durée en mois pour chaque bien. Si vous partez d’années, convertissez-les en mois pour éviter les mélanges.
Exemple : un ordinateur acquis le 15/03/2025 pour 1 200 € sur 36 mois. Dans ce modèle, la base amortissable est 1 200 € si vous ne gérez pas de valeur résiduelle.
Calculer la date de fin d’amortissement
Dans Date de fin, utilisez
=EDATE(C2;E2)si la date d’acquisition est en C2 et la durée en mois en E2. Vous obtenez la fin théorique du plan.Cette date servira à borner les calculs et à déclencher l’alerte.
Calculer l’annuité mensuelle linéaire
Dans Annuité mensuelle, saisissez
=SI(E2>0;D2/E2;0). Vous divisez la base amortissable par la durée en mois tout en évitant une division par zéro.Le résultat est un montant mensuel constant, avant ajustement éventuel du prorata du premier mois.
Mesurer les mois écoulés à la date d’arrêté
Calculez les mois entiers avec
=SI($B$1<C2;0;SI(DATEDIF(C2;$B$1;"m")>E2;E2;DATEDIF(C2;$B$1;"m"))). La formule renvoie 0 avant acquisition et plafonne le résultat à la durée du plan.Vous disposez ainsi d’un compteur simple pour borner le cumul.
Ajouter le prorata temporis du premier mois
Ajoutez une colonne Prorata 1er mois si vous voulez affiner le premier mois. Le coefficient peut être calculé selon les jours restants du mois d’acquisition, mais la convention exacte n’est pas universelle.
Dans un modèle simple, ce coefficient s’ajoute ensuite au cumul. Avant mise en production, documentez la règle retenue.
Créer un tableau de mouvements mensuels
Créez un second tableau avec au moins Référence, Période et Dotation. Chaque ligne représente une dotation mensuelle par immobilisation.
Ce découpage permet d’obtenir à la fois un détail mensuel et un état de synthèse.
Cumuler l’amortissement avec SOMME.SI.ENS
Dans le tableau principal, cumulez les dotations avec
=SOMME.SI.ENS(Mouvements[Dotation];Mouvements[Référence];[@Référence];Mouvements[Période];"<="&$B$1). La formule additionne uniquement les lignes de la bonne immobilisation jusqu’à la date d’arrêté.Vous obtenez un cumul lisible et filtrable, sans ressaisir les montants.
Calculer la valeur nette comptable
Dans VNC, utilisez
=SI(D2-I2>0;D2-I2;0)si la base est en D2 et le cumul en I2. Vous soustrayez l’amortissement cumulé tout en bloquant la VNC à zéro.Cette sécurité évite une valeur négative en fin de plan.
Déclencher l’alerte de fin d’amortissement
Dans Alerte, saisissez
=SI(OU($B$1>=F2;J2=0);"Fin d’amortissement";""). Le message s’affiche si la date d’arrêté atteint la fin du plan ou si la VNC est nulle.Ajoutez une mise en forme conditionnelle pour repérer plus vite les lignes concernées.
Tester le fichier sur un cas simple
Testez avec le bien acquis le 15/03/2025, base 1 200 €, durée 36 mois et date d’arrêté au 31/12/2025. L’annuité mensuelle est alors de 33,33 € avant prise en compte d’un éventuel prorata de premier mois.
Vérifiez surtout la date de fin, le plafonnement des mois écoulés et la VNC qui ne descend jamais sous zéro.
Erreurs fréquentes & dépannage
L’erreur la plus fréquente est de mélanger des durées en années et en mois dans le même tableau. L’autre piège est de calculer un prorata sans règle écrite, ce qui crée des écarts difficiles à justifier.
Attention aussi à DATEDIF : la fonction mesure un écart de dates, mais ne remplace pas votre convention comptable.
Astuces & pour aller plus loin
Figez la date d’arrêté avec une référence absolue comme $B$1. Vous recalculerez tout le portefeuille en ne changeant qu’une seule cellule.
Si vous devez produire des totaux par catégorie ou par site, ajoutez ces colonnes puis réutilisez SOMME.SI.ENS pour les synthèses.
Questions fréquentes
Peut-on faire ce suivi sans tableau de mouvements mensuels ?
Oui. Pour un suivi simple, vous pouvez calculer directement le cumul à partir des mois écoulés et de l’annuité mensuelle. Le tableau de mouvements devient utile si vous voulez un détail par période et exploiter pleinement SOMME.SI.ENS.
Pourquoi utiliser EDATE plutôt qu’un ajout de jours ?
Parce que le plan d’amortissement est généralement exprimé en mois. EDATE ajoute des mois calendaires et évite les décalages liés à la longueur variable des mois.
DATEDIF suffit-il pour tous les prorata temporis ?
Non. DATEDIF aide à mesurer un écart, mais la règle de prorata dépend de votre convention de calcul. Validez donc la formule retenue avec votre pratique interne.
Conclusion
Le montage le plus robuste consiste à séparer les données du bien et les mouvements mensuels, puis à faire le cumul avec SOMME.SI.ENS. Si vous partez d’un fichier existant, fiabilisez d’abord la date d’arrêté et la durée en mois : ce sont les deux champs qui conditionnent le reste.