Les emprunts à remboursements constants sont les plus courants. Il existe dans Excel deux fonctions INTPER() et PRINCPER() qui donnent respectivement la part d’intérêt et de remboursement du capital à chaque échéance.
Je vous propose dans la suite de calculer ces montants en revenant à la formule de base. Prêt ? Allons-y.
Vous empruntez 10000 € sur 1 an (12 mensualités) à un taux de 3,25%.
Établissons la feuille de calcul. A gauche, le tableau comporte en mois 0 le montant dû initial repris de la cellule E1. Le tableau comporte 12 échéances. Le taux mensuel est obtenu en divisant le taux annuel par 12. La cellule E4 est prévue pour contenir le montant du remboursement mensuel (constant).
La formule de calcul du montant dû après la nième échéance (Dn) est la suivante :
Dn = Dn-1 + Tm x Dn-1 – R
Soit, le montant dû après l’échéance précédente, plus les intérêts dus sur cette somme pour le mois n (Tm x Dn-1), moins le remboursement mensuel (R).
Écrivons cette formule dans la cellule B3 calculant le montant dû au mois 1.
Remarquez les $ dans les références des cellules fixes de la formule.
Après recopie de la formule sur les 11 mensualités suivantes, nous obtenons le tableau ci-dessous.
Le montant dû ne fait qu’augmenter ?! C’est normal car pour l’instant vous ne remboursez rien !
Il s’agit à présent d’ajuster le montant de remboursement pour que le montant dû après le douzième versement (cellule B14) soit nul.
Nous pourrions tâtonner en ajustant petit à petit le montant de remboursement. Utilisons plutôt la fonction Valeur cible… qui fait le même travail beaucoup plus rapidement.
Activons la fonction Valeur cible… que nous trouvons dans la liste de fonctions Analyse scénarios de l’onglet Données. Remarquez que la cellule sélectionnée est le dernier montant dû, celui que l’on souhaite rendre nul (Valeur à atteindre).
La cellule à modifier est ici la cellule E4 contenant le montant du remboursement. Quand le paramétrage est terminé, cliquez sur OK.
Le calcul du remboursement est quasi immédiat.
Nous pouvons, si nous le souhaitons, compléter le tableau pour faire apparaître la part des intérêts et du capital dans chaque mensualité.
Le tableau ci-dessus est calculé sans arrondi. Il est possible que vous constatiez des écarts minimes avec les tableaux transmis par votre banque.
En combinant une formule de récurrence simple et l’application de la valeur cible, nous avons réalisé ce qui est certainement le calcul le plus fréquent dans notre monde économique.
Si cet article vous a intéressé, n’oubliez pas de le partager en cliquant sur un des liens ci-dessous. A bientôt !
Commentaires récents