كيفية إنشاء حاسبة الإقراض القرض الخاص بك

معظم الناس يعتقدون أن هناك نوعا من السحر الفودو الذي يذهب إلى إنشاء قرض جدول الإطفاء . ولكن يسعدني أن أخبرك أنه لا يوجد - بل وحتى أفضل - يمكن للمبتدئين إنشاء واحد بسهولة باستخدام Microsoft Excel.

جدول إطفاء القرض يمنحك الكثير من المعلومات الجيدة: المبلغ الإجمالي المستحق للمقرض ، والجزء الذي هو دفعة الفائدة ، والجزء الذي هو الدفعة الرئيسية ورصيد القرض القائم.

عندما يتم إطفاء القرض ، يعني ذلك ببساطة دفع فائدة ودفع دفعة أساسية في دفعة دورية واحدة وتكون مبالغ الدفع مساوية لكل فترة دفع. لكن واحدة من أكثر الميزات إثارة للاهتمام للقرض المطفأ هي أنه مع كل دفعة ، فإن مقدار الفائدة والمبلغ الرئيسي يتغيران.

Excel هو أداة مفيدة للغاية لحساب جدول الإطفاء الخاص بك. يمكنك حتى تجربة المدخلات المختلفة لمعرفة كيف تؤثر أسعار الفائدة المختلفة وفترات السداد ومبالغ القروض الرئيسية على مدفوعات القرض التي ستقدمها.

دعونا ننظر إلى مثال لتوضيح العملية.

لنفترض أنك اشتريت للتو سيارة بقروض بقيمة 20،000 دولار يجب سدادها بالكامل خلال 4 سنوات. سعر الفائدة السنوي على القرض هو 3 ٪. منذ إطفاء القرض ، فإن جميع الدفعات الشهرية ستكون بنفس المبلغ.

الخطوة الأولى هي حساب مبلغ الدفع الشهري. يمكن القيام بذلك باستخدام الدالة PMT (التي تعني "الدفع") في Excel.

ابدأ بإنشاء جدول بيانات جديد وإدخال البيانات التالية المطلوبة لحساب مبلغ الدفع.

سيحتوي العمود A على التصنيفات التالية لبياناتنا:

A1 = أصل القرض الأصلي (مبلغ القرض)

A2 = مدة القرض بالسنوات

A3 = معدل الفائدة السنوي

A4 = عدد الدفعات في السنة

A5 = مبلغ الدفعة (ما نقوم بحسابه)

سوف يكون العمود B تحتوي على القيم المقابلة لكل تسمية في العمود A:

B1 = $ 20،000

B2 = 4

B3 = 3٪

B4 = 12

B5 = القيمة التي نبحث عنها

وظيفة PMT الخاصة بـ Excel يمكن العثور عليها في قائمة الوظائف المالية. حدد أولاً الخلية B5 ، ثم انقر فوق قائمة الوظائف المالية ضمن علامة تبويب الصيغ وحدد الدالة PMT من القائمة. سيتم عرض مربع حيث يجب عليك إدخال البيانات من جدول البيانات تمامًا كما هو في هذه الصورة:

هناك بعض الأشياء المهمة التي يجب ملاحظتها هنا. أولاً ، نحتاج إلى إجراء تعديلين مهمين ، لأننا نحسب دفعات شهريًا. نحن بحاجة إلى تقسيم المعدل السنوي لدينا إلى 12 للحصول على سعر الفائدة الشهري (Rate) ، ونحن بحاجة إلى مضاعفة 4 في 12 للحصول على إجمالي عدد الأشهر في فترة السداد (Nper).

ثانيًا ، يتم إدخال القيمة الحالية (PV) كقيمة سالبة لأن هذا المبلغ مستحق للمقرض.

بمجرد ملء الصناديق ، اضغط على "موافق".

من المفترض أن يظهر جدول البيانات الآن على النحو التالي:

الآن سنبدأ في بناء جدول الإطفاء.

تخطي صف ما أسفل جدول البيانات الذي تم إنشاؤه في الجزء الأول ، وبدءًا من الصف 7 ، قم بإجراء ما يلي تسميات تبدأ من العمود A وتنتهي في العمود E:

A7 = شهر

B7 = الدفع

C7 = الفائدة

D7 = المدير

E7 = الرصيد

هذه هي خمسة عناوين لجدول الاستهلاك.

الآن حان الوقت لبدء ملء الجدول. الخطوة الأولى هي إدخال عدد الأشهر في حياة القرض. تحت العنوان "شهر" ، اكتب "0" في الخلية A8 (وقت إصدار القرض). املأ الخلايا اللاحقة بالترتيب التسلسلي خلال الرقم 48 (الذي سيضعك في الخلية A56). خدعة واحدة: بمجرد إدخال 0 ، تأكد من تحديد الخلية (A8) ، ثم اسحب الزاوية اليمنى السفلى للخلية إلى السطر 56. ثم حدد "سلسلة التعبئة" من المربع الصغير الذي يظهر على يمين الخلية الأخيرة. يجب أن يملأ ذلك تلقائيًا السلسلة الرقمية لمدة 48 شهرًا.

بعد ذلك ، يمكن ملء جميع الدفعات لأن قيمة كل دفعة شهرية قد تم تحديدها بالفعل. اعتبارًا من الشهر الأول (لا يوجد دفع في الشهر 0 لأن الدفعات تتم في نهاية كل شهر) قم بملء القيمة 442.69 $ لكل شهر 1-48. بعد ذلك ، يمكن حساب الفائدة والمبالغ الرئيسية باستخدام وظائف Excel الخاصة بها.

حساب الفائدة والمكونات الرئيسية للدفعات باستخدام وظيفة Excel بسيطة ويمكن استخدامها لملء جدول الإطفاء بأكمله. لحساب دفع الفائدة ، حدد الخلية C9 ثم انقر فوق قائمة الوظائف المالية تحت علامة تبويب الصيغ. حدد وظيفة IPMT (للدفع "الفائدة") من القائمة. سيتم عرض مربع حيث يجب عليك إدخال البيانات من جدول البيانات تمامًا كما هو في هذه الصورة:

ملاحظة: علامات "$" بين أحرف الخلية والأرقام ضرورية عند نسخها الصيغ لأسفل إلى الخلايا المتبقية في وقت لاحق. يقوم "$" بتأمين الخلية المشار إليها ، بحيث تظل الصيغ سليمة عند نسخها إلى الخلايا المتبقية. يمكنك قفل الخلايا (ورؤية "$") عن طريق ضرب F4.

بعد ذلك يمكننا حساب مبلغ أصل في الدفعة. على غرار حساب IPMT ، حدد الخلية D9 ثم انقر فوق قائمة الوظائف المالية تحت علامة تبويب الصيغ. حدد PPMT (للدالة "الدفع الأساسي" من القائمة. سيتم عرض مربع حيث يجب عليك إدخال البيانات من جدول البيانات كما هو في هذه الصورة:

ملاحظة: يتم حسابها باستخدام هذه الوظيفة أو ببساطة بطرح مبلغ الفائدة من إجمالي مبلغ الدفع ، وبالمثل ، يمكن حساب الفائدة إذا قمنا بحساب المبدأ الأول باستخدام دالة PPMT ثم طرحنا هذه القيمة من إجمالي الدفعة. لمعرفة كيفية استخدام كل من الصيغتين ، والتي تسمح أيضًا بملء باقي الجدول بطريقة أكثر كفاءة كما سترى أدناه.

العمود الأخير (العمود E) هو عمود الرصيد المتبقي. في كل فترة الآن ، حددنا مبالغ الفائدة والمبلغ الأصلي ، أما بالنسبة للشهر 0 ، لم يتم إجراء أي دفعات حتى الآن ، لذلك الرصيد هو 20،000 دولار ، ومع ذلك ، نريد أن نطلب من Excel حساب الرصيد تلقائيًا في كل فترة ، لذلك بدلاً من مانو أدخل "$ 20،000" ، أدخل "= B1" ، بالإشارة إلى الجدول أعلاه لحساب الدفع. بالنسبة إلى الشهر 1 ، نظرًا لأنه يتم حساب الرصيد عن طريق طرح رصيد الشهر السابق من خلال الدفع الأساسي للشهر الحالي ، أدخل "= E8-D9" في الخلية E9. سيتم احتساب الرصيد تلقائيا. اكتمال جدول إطفاء الخاص بك الآن لشهر 1 ويجب أن ننظر بالضبط مثل هذا:

يمكن ملء كافة البيانات للأشهر المتبقية ببساطة عن طريق تحديد الخلايا B9-E9 ، نسخ التحديد ، ثم لصق التحديد في الخلايا B10-E10. نظرًا لأننا أدخلنا صيغًا لكل خلية وأغلقنا الخلايا التي سيتم استخدامها في كل عملية حساب ، يتم حساب قيم الفائدة والرئيسي والتوازن تلقائيًا. رؤية هذا العمل لشهر 2 ، يمكن إكمال الجدول بأكمله ببساطة عن طريق تحديد الأعمدة B-E خلال الشهر 48 ولصق المعلومات المنسوخة بالفعل. يحسب Excel الآن كل قيمة لجدول الاستهلاك باستخدام الصيغ التي أدخلناها. يجب أن يظهر جدولك المكتمل بالشكل التالي:

السنة الأولى من القرض:

السنة الأخيرة من القرض:

ملاحظة: تحتوي هذه الجداول على السنوات الأولى والأخيرة فقط من القرض. جدول الاستهلاك. سيكون العامان 2 و 3 بين هذين الجدولين اللذين يشكلان جدول إطفاء مستمر لكل 48 شهرًا.

إذا كنت مهتمًا بحساب دفعات الرهن العقاري لرهن ثابت ، يمكنك عمل جدول إطفاء بنفسك باستخدام هذه الخطوات ، أو يمكنك استخدام الآلة الحاسبة المالية لدينا للقيام بذلك نيابة عنك. سوف تظهر لك حاسبة الرهن العقاري دفعتك الشهرية بالإضافة إلى جدول الإطفاء الكامل.