Création d'un tableau d'amortissement prêt ou une hypothèque en utilisant Excel 2007 et Excel 2010


Bonjour et bienvenue à ma dernière plaque tournante de Excel. Ce est la deuxième partie de mon hub sur la création d'une hypothèque. Dans la première partie, nous avons créé une calculatrice hypothécaire de base en utilisant une gamme de fonctionnalités à la fois Excel 2007 et Excel 2010 (PMT, PRINCPER et INTÉRÊTS pour déterminer la valeur de rachat, le principal payé et les intérêts versés, respectivement, ainsi que ABS qui a été utilisé pour convertir un nombre à partir d'un négatif à positif). Ce hub peut être trouvé ici:

Aujourd'hui, dans la seconde moitié , je augmente sur ce moyeu et la calculatrice hypothécaire de base (ou prêt) permettra de créer un plan de remboursement. Un calendrier d'amortissement montre essentiellement le solde de votre prêt ou une hypothèque après chaque paiement et indiquer combien de capital et d'intérêts que vous payez chaque fois que vous effectuez un paiement. En outre, il permettra d'illustrer l'effet des paiements en trop que vous faites sur votre prêt hypothécaire ou de résidus.




Nous allons une fois de plus utiliser le PMT, PRINCPER et INTÉRÊTS fonctions pour calculer la valeur du prêt ou une hypothèque au fil du temps. En outre, nous allons utiliser le IFERROR pour supprimer toute #NOMBRE erreurs qui peuvent voir si la durée du prêt hypothécaire ou raccourcie. Je ai un hub qui étudie la IFERROR et SI fonctions plus en détail, qui peut également être trouvé ici:

Créer manuellement un tableau d'amortissement dans Excel 2007 et Excel 2010

Avant d'utiliser le modèle automatique, nous cherchons à créer le programme manuellement. De cette façon, non seulement nous obtenons une compréhension de la façon dont le modèle fonctionne, mais gagne aussi la connaissance de la façon d'utiliser trois fonctions dans Excel 2007 et Excel 2010 que nous pourrons peut-être utiliser dans d'autres situations à l'avenir.

Pour commencer, nous devons relier notre plan de remboursement avec la calculatrice hypothécaire que nous avons fait dans la première partie. Les données seront saisies directement de la calculatrice à notre dépréciation d'alimentation.

La première ligne, qui paie 0 (ou le début du programme) est tout simplement le solde de l'hypothèque ou de prêt. La formule est simple:

F16 =

Ainsi, si l'utilisateur de la calculatrice modifie le montant du prêt ou une hypothèque qui est pris par rapport au calendrier. Donc, la formule devient

= $ I $ 16

Remarque: $ est d'une importance vitale car il rend une référence absolue afin que Microsoft Excel ne modifie pas le I ou 16 lors de la copie. Ceci est particulièrement important pour les formules suivantes.

Calculer les intérêts payés sur un prêt hypothécaire avec Excel 2007 et Excel 2010

L'équation suivante calcule la partie des intérêts de la restitution à la INTÉRÊTS fonction.

La formule est la suivante (je ai utilisé un chiffre de sorte qu'il peut mettre en évidence la variable qui ne utilise pas de $):

= INTÉRÊT ($ R $ 2/$ M $ 2, A30, $ M $ M $ 5 * $ 2 - $ 16 $ F)

La formule en détail (je ai utilisé gras pour le rendre plus facile à voir):

= INTÉRÊT (Taux d'intérêt%/an Nombre de remboursements, le nombre de paiement, le nombre de remboursements annuels * le nombre d'années, - le montant de l'hypothèque)

Remarque: Il ya un certain nombre de choses importantes à noter dans cette formule.

Le taux d'intérêt doit avoir %

Le nombre de paiement ne doit pas Demandez $ pour le numéro de ligne

Le montant de l'hypothèque doit avoir - de sorte que le résultat est positif

Lorsque vous créez votre propre formule vous devez utilisation $ partout où je ai fait dans mes sinon les résultats ne seront pas exactes.

Calcul du principal payé sur un prêt hypothécaire en utilisant Excel 2007 et Excel 2010

Le calcul du montant du capital payé en utilisant la fonction PRINCPER. La syntaxe de la formule est identique à la syntaxe utilisée précédemment pour INTPER.

= PRINCPER ($ R $ 2/$ M $ 2, A30, $ M $ M $ 2 * $ 5 - $ 16 $ F)

Une fois de plus, le même soin doit être pris avec l'$ dans la formule, se ils sont manqués sur alors la formule sera en mesure de produire des résultats précis.

Calcul du solde d'un prêt hypothécaire avec Excel 2007 et Excel 2010

La formule finale, nous devons ajouter est la formule pour le calcul du solde après le remboursement est effectué.

La formule est la suivante:

= $ F $ 16 F30

Remarque: Comme ci-dessus, le $ sont essentiels pour permettre à cette formule fonctionne correctement.

Créer l'amortissement de l'horaire complet en utilisant Excel 2007 et Excel 2010

Maintenant que nous avons cette deuxième rangée cruciale est terminée, nous avons besoin pour remplir les lignes restantes.

Pour compiler le Nombre de paiement,

  • Sélectionnez la cellule dans la deuxième rangée et Nombre de paiement (A30 dans mon exemple)
  • Cliquez sur le Remplir Préparation groupe sur le Maison
  • Sélectionnez Série
  • Sélectionnez Série en colonne
  • Pour Valeur arrêt sélectionner le nombre de paiements (240 dans mon exemple)

Vous pouvez voir comment il devrait se regarder dans la figure ci-dessous.

Pour compléter les colonnes restantes, sélectionnez la ligne de cellules qui contiennent les premières valeurs de Le remboursement du prêt, les intérêts versés, principal payé et Balance et faites-la glisser à la dernière Nombre de paiement à une rangée.

Réglage du calendrier d'amortissement créé en utilisant Excel 2007 et Excel 2010

Parce que le programme est dynamique, toute modification du taux d'intérêt ou prêt ou une hypothèque quantité, par exemple, seront automatiquement répercutées dans le programme.

Si vous augmentez ou diminuez le nombre d'années, ajouter ou supprimer des lignes que vous devez remplir le programme.

Pour ajouter rapidement les lignes, de prolonger la série comme nous l'avons fait précédemment. Pour remplir les colonnes:

  • Sélectionner cellules contenant nos formules dans la dernière rangée de chiffres
  • Double-cliquez sur en bas à droite (indiqué par la flèche dans la figure ci-dessous)
  • Les autres lignes de remplissage automatique

Suppression des erreurs #num dans le plan de remboursement créé en utilisant Excel 2007 et Excel 2010

Lors du réglage de la longueur de l'hypothèque ou de prêt pour une période plus courte, vous remarquerez que vous vous retrouvez avec une charge d'erreurs #NOMBRE. Pour résoudre ce problème, nous avons besoin de faire un changement aux formules dans notre feuille de calcul. Nous allons utiliser le IFERROR fonction dans Excel pour supprimer les erreurs #num:

  • Cliquez sur la première rangée de formules Intérêts payés

La formule ressemblera


= INTÉRÊT ($ R $ 2/$ M $ 2, A30, $ M $ M $ 5 * $ 2 - $ 16 $ F)


  • Ajouter = IFERROR ( au début de la formule, et delete = avant INTÉRÊTS
  • Ajouter , "") à la fin de la formule
  • La formule devient:


= IFERROR (2 INTÉRÊT ($ R $ 2/$ M $, A270, 5 $ M $ M $ * $ 2 - $ F $ 16), "")


  • Remplissez le reste de la feuille de calcul en utilisant ces formules adaptées

Maintenant, si je défiler et sélectionner l'une des cellules qui servent à contenir un #NOMBRE, je vois qui a une formule, mais ne affiche pas une erreur.

Utilisation des modèles d'amortissement dans Excel 2007 et Excel 2010

Maintenant que nous comprenons comment créer manuellement un plan de remboursement, il convient de noter que vous pouvez utiliser un modèle pré-construit ainsi. Dans Excel 2007

  • Cliquez sur le Exceller
  • Sélectionnez N ew
  • Type d'amortissement dans la boîte de recherche et cliquez sur la flèche
  • Microsoft va généralement chercher et obtenir des résultats similaires à la mienne ci-dessous:

  • Cliquez sur Décharge
  • Le nouveau modèle est maintenant disponible dans Nouveau Mes modèles

Pour Excel 2010:

  • Sélectionnez Fichier Menu
  • Cliquez sur Nouveau

  • Comme avec Excel 2007, cliquez sur Télécharger
  • Le nouveau modèle sera ajouté à votre Les modèles récents et Mes modèles menu Fichier/Nouveau

Conclusion

Dans le moyeu d'aujourd'hui, nous avons élargi la base de la calculatrice hypothécaire que nous avons créé dans Première partie

  • Encore une fois, nous avons utilisé le PMT, INTÉRÊTS et PRINCPER fonctions pour calculer le montant du remboursement et la part des rachats qui étaient intérêts et du capital.
  • Avec les formules en place, nous avons cherché à créer une série de remplir automatiquement la période de remboursement.
  • Enfin, nous avons utilisé le IFERROR pour supprimer le #NOMBRE erreurs que vous recevez lorsque vous réduisez la durée du prêt ou une hypothèque.

Je espère que vous avez trouvé ce moyeu et également la première partie utile et informatif. Merci pour la lecture, ne hésitez pas à laisser un commentaire ci-dessous.

(0)
(0)

Commentaires - 0

Sans commentaires

Ajouter un commentaire

smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile
Caractères restants: 3000
captcha