1. Fixez les hypothèses
Dans notre exemple, nous supposons que vous empruntez la somme de 25 000 euros pour une durée de 5 ans à un taux effectif global de 5,50 %. Les remboursements sont fixes, mensuels et débutent en octobre 2005. Créez un
classeur Excel et tapez les données figurant sur l’écran ci-contre (voir écran 1). Le montant du prêt initial est négatif (-25 000 euros). C’est normal car c’est de l’argent que vous recevez. Les sommes que vous remboursez sont
représentées par des nombres positifs.
2. Construisez les lignes de l’échéancier
Tapez en lignes 7 et 8 les libellés affichés ci-contre (voir écran 2). Les 60 remboursements occuperont les lignes 9 à 68. Dans les cellules A9 à
A68, saisissez les nombres de 1 à 60 (servez-vous pour cela de la poignée de recopie). En B9, tapez oct-2005 et utilisez la poignée de recopie pour écrire
les mois de remboursements successifs (nov-2005, déc-2005, janv-2006, etc. ) dans les 59 lignes suivantes.
3. Calculez les mensualités et leur ventilation
En C9, recopiez la formule =VPM($D$4/12;$D$3*12;$D$2). La cellule affiche 477,53 euros, le montant fixe de vos mensualités. Dans cette formule, certaines cellules ne sont
pas utilisées directement. Ainsi, le taux annuel (D4) est divisé par 12 car la fonction VPM exige un taux mensuel. De même, la durée du prêt (D3) est multipliée par 12 car c’est le nombre total de remboursements que demande la fonction VPM. En
D9, tapez =PRINCPER($D$4/12; A9;$D$3*12;$D$2). Vous obtenez 362,95 euros, la part de capital dans le premier versement. En E9, la part d’intérêt est donnée par la formule
=C9-D9. Enfin, en F9, tapez =-D2-D9. Sélectionnez les cellules C9:E9 et, en utilisant la poignée de recopie, entrez ces trois formules sur les lignes
10 à 68. En F10, tapez =F9-D10 et double-cliquez sur la poignée de recopie pour inscrire cette cellule sur le champ F11:F68. Pour vérifier, affichez la cellule
F68, vous constaterez qu’après les 60 versements, il ne reste plus de capital à rembourser et la cellule contient 0,00 euros (voir écran 3).
4. Ajoutez des totaux par année
La fonction Sous-total ne peut s’appliquer qu’à des contenus de champs. Or, si la colonne B contient des dates, aucune ne contient les années correspondantes. Vous allez donc utiliser une colonne
supplémentaire, que vous pourrez masquer. En G8, tapez Année et, en G9, rapportez la formule =ANNEE(B9).
Recopiez-la sur les cellules G10:G68. Sélectionnez une cellule quelconque de votre plan d’amortissement et déroulez Données, Sous-totaux. Dans la liste A chaque changement de, choisissez
Année. Dans la zone Ajouter un sous-total à, ne cochez que capital et intérêts. Validez par OK. Les cumuls pour chaque année apparaissent sous cette dernière. Pour n’afficher que les
sous-totaux par année, cliquez sur le petit chiffre 2 (voir écran 4) visible en haut à gauche de la fenêtre d’Excel.
