Je suis analyste informatique professionnel pour un grand cabinet comptable. Dans mes temps libres, j’écris sur les jeux vidéo et divers autres sujets.
Le calculateur d’hypothèque rempli avec des colonnes supplémentaires pour l’impôt foncier, le PMI et l’assurance des biens.
SimeyC
La fonction PMT dans Excel est une fonction financière utilisée pour calculer les remboursements de prêt (elle peut également être utilisée pour calculer la valeur des investissements au fil du temps). Avec les fonctions IPMT et PPMT associées, il est facile de créer une feuille de calcul qui calculera un tableau d’amortissement pour une hypothèque ou un autre prêt.
Ces fonctions vous permettent de modéliser différentes situations de prêt, y compris des remboursements de capital supplémentaires, différents montants de prêt, différents taux d’intérêt, etc., et de calculer les paiements totaux mensuels ainsi que les paiements sur la durée de l’hypothèque.
Cet article fournira un guide étape par étape sur la façon de créer une calculatrice de prêt hypothécaire à l’aide de Microsoft Excel. Bien que la feuille de calcul ait été conçue dans Microsoft Excel 2007, la fonctionnalité sera la même sur Microsoft Excel 2003.
Création du format de la calculatrice hypothécaire
Explication des fonctions financières utilisées dans la calculatrice hypothécaire
Fonctions PMT, IPMT et PPMT – pour un prêt à taux d’intérêt et remboursement constants:
- EMP calcule le paiement total.
- IPMT calcule le paiement des intérêts pour une période donnée.
- PPMT calcule le remboursement du principal pour une période donnée.
Syntaxe de ces fonctions :
- =PMT(taux, nombre de paiements, valeur actualisée, [Future Value], [type])
- =IPMT(taux, période, nombre de paiements, valeur actualisée, [Future Value], [type])
- =PPMT(taux, période, nombre de versements, valeur actualisée, [Future Value], [type])
Où:
- évaluer: le taux d’intérêt mensuel du prêt.
- Période: la période pour laquelle vous souhaitez calculer les intérêts ou le principal.
- Nombre de paiements: le nombre total de remboursements du prêt.
- valeur actuelle: la valeur actuelle que valent maintenant les paiements futurs.
- Valeur future: la valeur future que vous souhaitez atteindre après le dernier paiement. Si vous ne le faites pas, la dernière valeur sera supposée égale à zéro.
- Taper: indique quand les paiements sont dus. (0 est la fin de la période, 1 est le début de la période.)
Le processus de création d’un calendrier hypothécaire dans Microsoft Excel est relativement simple, bien qu’il utilise certaines fonctions financières complexes.
Commencez par créer une feuille de calcul simple dans laquelle les données seront saisies et l’amortissement sera calculé. Dans cet exemple, ajoutez les variables de base telles que le montant du prêt, le nombre de mois, le taux d’intérêt, etc., et les rubriques du tableau d’amortissement réel. Pour faire ça:
- Créez une plage avec les principaux détails du prêt : total du prêt, taux d’intérêt, mois. Ce sont les variables qui permettront de modéliser les différentes conditions de prêt.
- Ajoutez le paiement mensuel total, les paiements totaux et le total des intérêts payés à cette plage – ceux-ci seront calculés plus tard.
- Créez le calendrier réel en ajoutant l’en-tête : Mois, Solde d’ouverture, Principal, Intérêts, Paiement supplémentaire, Solde de clôture et Paiement total. Une fois les formules ajoutées, cette section calculera automatiquement le tableau d’amortissement en fonction des variables saisies dans la plage de prêt principale.
- Ajoutez un montant de prêt, un taux d’intérêt et le nombre de mois à la plage. Ceci est fait pour s’assurer que les formules fonctionnent et peuvent être n’importe quelle valeur.
- Mettez en forme les titres et la plage.
- Ajoutez la formule =-PMT(C3/12,C4,C2) à la cellule C5 (Voir l’explication de PMT à droite) NOTE IMPORTANTE : l’intérêt dans la gamme principale est un intérêt annuel. Divisez cette valeur par 12 pour obtenir le taux d’intérêt mensuel utilisé dans la formule)
Faites défiler pour continuer
Le format de base est complet. Autres éléments pouvant être ajoutés (voir la capture d’écran ci-dessus pour un exemple de feuille de calcul finale plus complexe) :
- Paiement AMP
- Paiement d’assurance mensuel
- Impôts fonciers
Ajout des formules initiales à la calculatrice hypothécaire
Maintenant que le format initial est défini, les formules restantes dans la plage récapitulative et la ligne supérieure du tableau d’amortissement peuvent être créées. Dans chaque cellule référencée ci-dessous, entrez la formule appropriée.
Ajout des formules finales à la calculatrice hypothécaire
La première ligne de formules a été créée. Maintenant, le reste des formules peut être créé. Ils sont essentiellement les mêmes, mais vérifiez si le mois est vide (le mois vérifie si le solde de clôture précédent est nul et renvoie vide si c’est le cas, sinon il renvoie la valeur du mois précédent plus un. Dans chaque cellule référencée ci-dessous, entrez la formule appropriée.
Copiez ces formules jusqu’à la ligne 500. La calculatrice hypothécaire de base est maintenant terminée. Le tableau d’amortissement sera automatiquement recalculé lorsque les valeurs des cellules C2, C3 et C4 seront modifiées.
Ajout de ‘Paiements supplémentaires’.
SimeyC
Ajouter des paiements supplémentaires au calculateur de prêt hypothécaire
Une fois la conception terminée, vous pouvez modifier n’importe quel détail du prêt pour modéliser l’amortissement ; en outre, vous pouvez ajouter des paiements supplémentaires tout au long du prêt. Dans l’exemple, un paiement supplémentaire a été appliqué au prêt chaque mois. Ce paiement supplémentaire réduit la durée du prêt, le total payé et les intérêts payés. Ce paiement supplémentaire peut être effectué à tout moment pendant la durée du prêt et n’a pas à être mensuel.
Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.
michelle dee de Charlotte, Caroline du Nord le 11 septembre 2012 :
Très utile et il semble qu’il ait fallu beaucoup de travail pour assembler ce hub – il a l’air génial et merci pour le partage, voté et partagé (sauf drôle).
chrissieklinger de Pennsylvanie le 21 juillet 2012 :
très utile; Je ne savais pas que vous pouviez le faire dans Excel 2007. Une meilleure route à suivre que d’utiliser des calculatrices Web en ligne.
