Qu'est-ce que la fonction ISPMT ?
ISPMT te sert dès que tu dois isoler la charge d'intérêts d'un mois précis sur un prêt à capital constant, pour une écriture comptable ou une projection de trésorerie, sans reconstruire tout le tableau d'amortissement.
Concrètement, c'est la fonction qu'il te faut pour analyser les charges financières d'un emprunt immobilier à amortissement constant, pour projeter les flux de trésorerie d'un financement professionnel, ou pour comparer le coût total d'un prêt linéaire face à un prêt classique à mensualités fixes.
Syntaxe
Clique sur un argument pour aller à son explication.
La numérotation des périodes commence à 0, pas à 1. La première période est pér = 0, la dernière est pér = npm - 1. Si tu passes pér = 1 pour calculer le premier mois, tu obtiendras en réalité les intérêts du deuxième mois.
Comprendre chaque paramètre de la fonction ISPMT
Les quatre arguments d'ISPMT sont tous obligatoires et arrivent dans cet ordre : le taux par période, la période visée, le nombre total de périodes, puis le montant emprunté. Le piège n'est pas dans l'ordre mais dans la numérotation : pér démarre à 0, donc le premier mois s'écrit pér = 0 et le dernier pér = npm - 1. Pense aussi à garder taux et npm dans la même unité (mensuelle ou annuelle), sinon tout le calcul dérape.
taux
: c'est le taux d'intérêt par périodeSi ton prêt a un taux annuel de 5% et que tu paies mensuellement, divise le taux par 12 (5%/12 = 0,4167% par mois). Si tu paies annuellement, utilise directement le taux annuel. Le taux doit être exprimé en décimal (5% = 0,05) ou en pourcentage avec le symbole %.
pér
: c'est la période pour laquelle tu veux calculer les intérêtsLa première période est numérotée 0 (pas 1). Pour calculer les intérêts du premier mois, utilise pér = 0. Pour le deuxième mois, pér = 1. Pour le dernier mois d'un prêt sur 10 ans (120 mois), pér = 119.
Attention : La numérotation commence à 0, pas à 1. Si tu utilises pér = 1 pour le premier mois, tu calculeras en réalité les intérêts du deuxième mois, ce qui faussera tout ton tableau d'amortissement.
npm
: c'est le nombre total de périodes de paiement pour le prêtPour un prêt sur 10 ans avec des mensualités, npm = 120 (10 ans × 12 mois). Pour un prêt sur 5 ans avec des paiements trimestriels, npm = 20 (5 ans × 4 trimestres). Assure-toi que cette valeur est cohérente avec ton taux : taux mensuel = npm en mois, taux annuel = npm en années.
va
: c'est la valeur actuelle du prêt, autrement dit le montant empruntéPour un prêt immobilier de 200 000 €, va = 200000. Par convention Excel, les montants empruntés sont positifs, et les intérêts calculés par ISPMT seront négatifs (car c'est de l'argent que tu paies).
Exemples pratiques pas à pas
Comment calculer les intérêts d'une période avec la fonction ISPMT
Ton entreprise rembourse un prêt de 60 000 € sur 60 mois à 4,5 %, avec une part de capital identique chaque mois et des intérêts qui fondent au fil du temps. Tu dois isoler la charge d'intérêts de plusieurs échéances pour tes écritures.
La fonction ISPMT donne cette charge période par période, sans reconstruire tout le tableau d'amortissement.
- 1Dans une cellule, écris
=ISPMT(. - 2En 1er argument : écris le taux d'intérêt ramené à l'échelle d'un mois,
0,045/12. Ce prêt est affiché à 4,5 % par an, mais ses échéances tombent chaque mois, donc on divise le taux annuel par douze pour que chaque période porte sa juste part d'intérêts. - 3En 2ᵉ argument : clique sur la cellule qui porte le numéro de la période à calculer,
B2. Elle vaut1sur cette ligne, et comme la numérotation d'ISPMT démarre à0, cette valeur vise en réalité le deuxième mois du prêt et non le premier. - 4En 3ᵉ argument : saisis le nombre total de mensualités,
60. Cette durée doit être comptée dans la même unité que le taux, ici en mois, sinon la part de capital attribuée à chaque période est faussée et les intérêts avec. - 5En 4ᵉ argument : saisis le capital emprunté au départ,
60000. On le saisit en positif, ce qui explique le signe négatif des intérêts rendus par la fonction, puisqu'ils représentent de l'argent qui sort de ta trésorerie. - 6Ferme la parenthèse et appuie sur Entrée.
=ISPMT(0,045/12; B2; 60; 60000)
Comment numéroter les périodes de la fonction ISPMT du premier au dernier mois
Ton tableau d'amortissement affiche un mois de retard partout et le total ne tombe jamais juste, alors que la formule semble correcte. Le coupable est la numérotation des périodes, qui ne commence pas là où l'intuition la place.
En alignant le mois réel du prêt et le numéro attendu par la fonction ISPMT, l'écart d'un cran saute aux yeux.
- 1Dans une cellule, écris
=ISPMT(. - 2En 1er argument : écris le taux d'intérêt ramené au mois,
0,045/12. Il ne bouge pas d'une ligne à l'autre du tableau, tout comme la durée et le capital, car ce qui change ici, c'est uniquement la période. - 3En 2ᵉ argument : clique sur la cellule qui porte le numéro de la période,
B2. Sur la première ligne elle vaut0, et c'est bien le premier mois du prêt : le capital est alors encore entier, donc les intérêts atteignent leur maximum,-225 €. - 4En 3ᵉ argument : saisis le nombre total de mensualités,
60. Cette durée reste identique sur chaque ligne et se compte en mois, l'unité du taux, pour que le capital remboursé mois après mois tombe juste. - 5En 4ᵉ argument : saisis le capital emprunté,
60000. Il ne varie pas non plus d'une ligne à l'autre et se saisit en positif, ce qui donne des intérêts négatifs puisqu'ils quittent ta trésorerie. - 6Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour que chaque ligne lise sa propre période, de
B3àB7. Le dernier mois du prêt s'écrit59, tandis que le60de la ligne suivante rend0sans lever d'erreur, parce qu'à ce stade le capital est déjà entièrement remboursé.
=ISPMT(0,045/12; B2; 60; 60000)0 et le dernier 59, pas 60. Les intérêts baissent de 3,75 € à chaque période, exactement le taux mensuel appliqué aux 1 000 € de capital remboursés entre-temps.
Comment totaliser les intérêts d'un prêt en combinant les fonctions ISPMT, SOMME et SEQUENCE
Avant de signer, il te faut le coût total du crédit, pas la charge d'un mois isolé. Dérouler soixante lignes pour les additionner fonctionne, mais tu veux le chiffre en une cellule que tu pourras rejouer sur d'autres durées.
La fonction ISPMT accepte une liste de périodes au lieu d'un seul nombre, ce qui permet de tout calculer et totaliser d'un coup.
- 1Dans une cellule, écris
=SOMME(. - 2En 1er argument : écris d'un bloc le calcul des intérêts de toutes les périodes et referme-le,
ISPMT(0,045/12; SEQUENCE(60) - 1; 60; 60000). Le taux0,045/12, la durée60et le capital60000restent ceux d'une formule à une seule période. Ce qui change tient à la deuxième place :SEQUENCE(60)génère les entiers de1à60, alors qu'ISPMT numérote ses périodes de0à59, et le- 1décale donc toute la liste d'un cran. - 3Ferme la parenthèse de la fonction SOMME et appuie sur Entrée. Celle de la fonction ISPMT a déjà été refermée dans l'étape précédente.
=SOMME(ISPMT(0,045/12; SEQUENCE(60) - 1; 60; 60000))
Les erreurs fréquentes avec la fonction ISPMT
Commencer la numérotation des périodes à 1 au lieu de 0
C'est l'erreur la plus courante avec ISPMT. La première période est numérotée 0, pas 1. Si tu utilises pér = 1 pour calculer les intérêts du premier mois, tu obtiendras en réalité les intérêts du deuxième mois, décalant tout ton tableau d'amortissement.
Solution : Utilise pér = 0 pour le premier mois et pér = npm - 1 pour le dernier. Par exemple, pour un prêt de 60 mois : =ISPMT(4%/12;0;60;10000) donne les intérêts du mois 1, =ISPMT(4%/12;59;60;10000) ceux du mois 60.
Incohérence entre l'unité du taux et le nombre de périodes
Si tu utilises un taux annuel avec un nombre de périodes en mois (ou inversement), tes calculs seront complètement faux. =ISPMT(5%;0;60;10000) interprète un taux de 5% par mois avec 60 mensualités, soit un taux annuel implicite de 60%.
Solution : Adapte toujours le taux à l'unité des périodes : =ISPMT(5%/12;0;60;10000) pour un taux annuel de 5% avec 60 mensualités, ou =ISPMT(5%;0;5;10000) pour un taux annuel de 5% avec 5 annuités.
Confondre ISPMT et INTPER pour le mauvais type de prêt
ISPMT est pour les prêts à amortissement linéaire (capital constant). INTPER est pour les prêts à mensualités constantes (capital + intérêts = même montant chaque mois). Si tu utilises ISPMT pour un prêt classique à mensualités fixes, tes calculs d'intérêts seront faux.
Solution : Vérifie le type d'amortissement de ton prêt avant de choisir ta fonction. Si ta mensualité est fixe (même montant chaque mois), utilise INTPER. Si tu rembourses le même montant de capital à chaque période et que tes mensualités diminuent, utilise ISPMT.
ISPMT vs INTPER vs VPM
| Critère | ISPMT | INTPER | VPM |
|---|---|---|---|
| Type de prêt | Amortissement linéaire | Mensualités constantes | Mensualités constantes |
| Ce que la fonction calcule | Intérêts d'une période | Intérêts d'une période | La mensualité totale |
| Capital remboursé par période | Constant (va / npm) | Variable (croissant) | Variable (croissant) |
| Mensualité totale | Décroissante | Constante | Constante |
| Numérotation des périodes | Commence à 0 | Commence à 1 | N/A (calcul global) |
| Cas d'usage typique | Prêt professionnel à capital constant | Prêt immobilier classique | Calculer la mensualité d'un emprunt |
Questions fréquentes
ISPMT calcule les intérêts pour un prêt à amortissement linéaire du capital, où le même montant de capital est remboursé à chaque période et les intérêts diminuent progressivement. INTPER est conçu pour les prêts à mensualités constantes, où la somme capital + intérêts reste la même chaque mois. Si ton prêt a une mensualité fixe, utilise INTPER. Si le capital est remboursé de façon constante et que tes mensualités diminuent, utilise ISPMT.
Dans un amortissement linéaire, tu rembourses la même part de capital chaque période. Par exemple, pour un prêt de 120 000 € sur 10 ans, tu rembourses 12 000 € de capital par an. Les intérêts diminuent progressivement car ils sont calculés sur le capital restant dû, qui baisse de 12 000 € à chaque période. C'est différent du prêt classique à mensualités constantes, où la part de capital dans chaque mensualité augmente progressivement au fil du temps.
Oui. Il suffit d'ajuster le taux en le divisant par 12 et d'exprimer npm en mois. Par exemple, pour un taux annuel de 4% sur 10 ans : taux = 4%/12, npm = 120 mois, et pér varie de 0 à 119.
Attention : la première période est pér = 0, pas pér = 1.
Utilise une formule matricielle en combinant SOMME et SEQUENCE : =SOMME(ISPMT(taux;SEQUENCE(npm)-1;npm;va)). SEQUENCE(npm) génère les entiers de 1 à npm, et -1 les décale vers 0 à npm-1 pour respecter la numérotation d'ISPMT.
Tu peux aussi tirer ISPMT vers le bas pour chaque période de 0 à npm-1 dans une colonne, puis utiliser SOMME sur cette plage.
Pour aller plus loin
Continue sur ta lancée après la fonction ISPMT : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
