Excel : créer un planning de remboursement entre associés avec VPM, SI et capital restant dû
Créez dans Excel un planning d’emprunt entre associés avec intérêts automatiques, échéances variables et capital restant dû fiable.
- Difficulté
- Intermédiaire
Oui, Excel permet de suivre un prêt entre associés avec intérêts automatiques, échéances variables et capital restant dû. La méthode la plus robuste consiste à préparer des paramètres, créer un tableau structuré, calculer une échéance de référence avec VPM, puis ajuster les lignes avec SI.
Ce modèle convient pour un prêt à taux fixe par période, par exemple mensuel. Si votre convention prévoit un calcul selon les dates exactes, ce tableau reste utile pour le suivi mais ne remplace pas un calcul actuariel.
Prérequis
Préparez le montant prêté, le taux annuel, la date de départ, le nombre d’échéances et la périodicité. Vérifiez aussi ce que votre convention prévoit pour le calcul des intérêts et les dates d’exigibilité.
Le tutoriel part d’un calcul périodique simple, par exemple taux annuel / 12 pour du mensuel. Si votre contrat retient une autre base, adaptez les formules.
Matériel nécessaire
- Excel avec fonctions VPM et tableau structuré
- Convention ou conditions du prêt entre associés
- Montant, taux, durée et dates d’échéance
Étapes
Créer les paramètres du prêt
Dans le haut de la feuille, saisissez Capital_initial, Taux_annuel, Nb_periodes et Date_debut. Ajoutez une cellule Taux_periode avec
=Taux_annuel/12si vous travaillez au mois.Ajoutez ensuite Echeance_theorique avec
=-VPM(Taux_periode;Nb_periodes;Capital_initial). Le signe moins sert à afficher une échéance positive.Insérer le tableau structuré
Créez ces colonnes : N°, Date, CRD début, Echéance prévue, Echéance payée, Paiement retenu, Intérêts, Capital remboursé et CRD fin. Sélectionnez la plage puis utilisez Insertion > Tableau.
Nommez le tableau, par exemple tbl_pret. Excel recopiera les formules automatiquement sur les nouvelles lignes.
Renseigner les dates et le capital de départ
Dans la première ligne, mettez 1 en N°, la première date d’échéance et le CRD début égal au capital initial. Pour les lignes suivantes, incrémentez le numéro et la date selon votre périodicité.
Le CRD début de chaque ligne suivante doit reprendre le CRD fin de la ligne précédente. Si vos dates ne sont pas régulières, saisissez-les manuellement.
Calculer l’échéance prévue et le paiement retenu
Dans Echéance prévue, utilisez la cellule Echeance_theorique ou une valeur propre à la ligne si un plan est déjà convenu. Dans Echéance payée, saisissez seulement les écarts réels.
Dans Paiement retenu, utilisez
=SI([@[Echéance payée]]="";[@[Echéance prévue]];[@[Echéance payée]]). Une cellule vide garde ainsi l’échéance prévue au lieu d’être lue comme zéro.Calculer les intérêts de chaque ligne
Dans Intérêts, appliquez le taux de période au capital de début avec
=[@[CRD début]]*Taux_periode. Vous obtenez la part d’intérêts due pour la période.Cette formule reste valable tant que le prêt suit un taux fixe par période. Plus le capital baisse, plus cette part diminue.
Déduire le capital remboursé avec SI
Dans Capital remboursé, utilisez
=SI([@[Paiement retenu]]<=[@Intérêts];0;[@[Paiement retenu]]-[@Intérêts]). Le capital remboursé correspond au paiement retenu moins les intérêts.La condition évite un remboursement de capital négatif si le paiement ne couvre même pas les intérêts. Dans ce cas, le tableau signale une situation à traiter à part.
Mettre à jour le capital restant dû
Dans CRD fin, utilisez
=MAX(0;[@[CRD début]]-[@[Capital remboursé]]). Le solde ne peut ainsi pas devenir négatif en fin de prêt.Sur la ligne suivante, le CRD début reprend ce CRD fin. Le planning se propage alors automatiquement jusqu’au remboursement complet.
Adapter les échéances variables
Pour un mois avec remboursement anticipé ou paiement réduit, remplacez simplement la valeur de Echéance payée par le montant réel. Les intérêts, le capital remboursé et le CRD fin se recalculent seuls.
Si vous avez prévu plus de lignes que nécessaire, vous pouvez neutraliser les calculs après extinction du prêt avec
=SI([@[CRD début]]=0;0;...).Contrôler le solde final
Vérifiez que le dernier CRD fin est égal à 0, ou très proche selon vos arrondis. En cas d’écart minime, ajustez plutôt la dernière échéance payée.
Ajoutez aussi un total des intérêts pour comparer plusieurs scénarios de remboursement. C’est souvent l’indicateur le plus utile pour arbitrer.
Erreurs fréquentes & dépannage
Erreur classique : utiliser le taux annuel directement dans les intérêts mensuels ou dans VPM. Le taux doit être adapté à la périodicité retenue.
Erreur fréquente : mélanger flux positifs et négatifs. Si VPM renvoie un montant négatif, appliquez le signe moins devant la formule ou harmonisez tous les flux.
Erreur de structure : saisir un capital remboursé fixe au lieu de le calculer à partir du paiement et des intérêts. Dès qu’une échéance varie, le solde devient faux.
Astuces & pour aller plus loin
Ajoutez une mise en forme conditionnelle sur Echéance payée pour repérer les lignes différentes de l’échéance prévue. C’est utile pour identifier rapidement les écarts.
Transformez la zone de paramètres en cellules nommées et protégez les colonnes de formule si le fichier est partagé. Laissez modifiables seulement les paramètres et l’échéance payée.
Questions fréquentes
Peut-on utiliser ce modèle dans Google Sheets ?
Oui, la logique reste la même avec des formules équivalentes. Vérifiez simplement la syntaxe locale des fonctions et des séparateurs.
VPM suffit-il si les dates de paiement sont irrégulières ?
Non. VPM donne une échéance théorique pour des périodes régulières ; si les intérêts dépendent des dates exactes, il faut un modèle fondé sur ces dates.
Comment gérer un remboursement anticipé total ?
Saisissez sur la ligne concernée le montant réellement versé dans l’échéance payée. Si ce montant couvre les intérêts de la période et tout le capital restant, le CRD fin tombera à 0, sous réserve des arrondis.
Conclusion
Le montage le plus sûr combine VPM pour l’échéance de référence, SI pour les exceptions et un tableau structuré pour propager les calculs. Avant de l’utiliser comme document de gestion, testez-le avec un cas simple puis avec un remboursement anticipé pour vérifier que le capital restant dû arrive bien à zéro.