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

حساب الدفع

بادئ ذي بدء ، يجب أن أقول أن هناك نوعين من مدفوعات القروض:

  • متباينة.
  • المعاش.

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

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

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

الخطوة 1: حساب القسط الشهري

لحساب الرسم الشهري عند استخدام نظام الأقساط في Excel ، هناك وظيفة خاصة - PLT . إنه ينتمي إلى فئة المشغلين الماليين. الصيغة لهذه الوظيفة هي كما يلي:

=ПЛТ(ставка;кпер;пс;бс;тип)

كما ترون ، هذه الوظيفة لديها الكثير من الحجج. صحيح أن آخر اثنين منهما ليسا إلزاميين.

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

يشير "Kper" إلى العدد الإجمالي لفترات سداد القروض. بمعنى ، إذا تم أخذ قرض لمدة عام واحد مع دفع شهري ، فإن عدد الفترات يعتبر 12 ، إذا كان لمدة عامين ، فإن عدد الفترات هو 24 . إذا تم أخذ القرض لمدة سنتين مع دفعة ربع سنوية ، فإن عدد الفترات يكون 8 .

تشير "Ps" إلى القيمة الحالية. بكلمات بسيطة ، هذا هو المبلغ الإجمالي للقرض في بداية القرض ، أي المبلغ الذي تقترضه ، دون الأخذ في الاعتبار الفائدة والمدفوعات الإضافية الأخرى.

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

تحدد الوسيطة "النوع" وقت الحساب: في النهاية أو في بداية الفترة. في الحالة الأولى ، يأخذ القيمة "0" ، وفي الثانية - "1" . تستخدم معظم المؤسسات المصرفية هذا الخيار مع الدفع في نهاية الفترة. هذه الحجة اختيارية أيضاً ، وإذا تم حذفها ، فمن المفترض أنها صفر.

وقد حان الوقت الآن للانتقال إلى مثال ملموس لحساب الرسوم الشهرية باستخدام وظيفة معاهدة قانون البراءات. للحساب ، نستخدم الجدول مع البيانات الأولية ، والذي يبين سعر الفائدة على القرض ( 12 ٪ ) ، وقيمة القرض ( 500،000 روبل ) وفترة القرض ( 24 شهرا ). في هذه الحالة ، يتم الدفع شهريًا في نهاية كل فترة.

  1. حدد العنصر في الورقة التي سيتم عرض نتيجة الحساب لها ، وانقر فوق الرمز "Insert function" الموجود بالقرب من شريط الصيغة.
  2. انتقل إلى معالج الدالة في Microsoft Excel

  3. يتم تشغيل نافذة معالج الوظائف . في فئة "Financial" ، نختار الاسم "PLT" ونضغط على زر "OK" .
  4. انتقل إلى نافذة الوسائط لوظيفة PLC في Microsoft Excel

  5. بعد ذلك ، تفتح نافذة الوسيطات الخاصة بمشغل PLT .

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

    في حقل "Kper" ، يتم تعيين فترة الاعتماد. لدينا لمدة 24 شهرا. يمكنك إدخال الرقم 24 يدويًا ، ولكننا ، كما في الحالة السابقة ، نشير إلى رابط إلى موقع هذا المؤشر في الجدول المصدر.

    في الحقل "Ps" ، يشار إلى القيمة الأصلية للقرض. يساوي 500،000 روبل . كما في الحالات السابقة ، نشير إلى رابط عنصر الورقة الذي يحتوي على هذا المؤشر.

    يشير الحقل "Bs" إلى مبلغ القرض بعد سداده بالكامل. كما تتذكر ، هذه القيمة هي دائما تقريبا الصفر. لقد قمنا بتعيين الرقم "0" في هذا الحقل. على الرغم من أن هذه الحجة يمكن حذفها تماما.

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

    بعد إدخال جميع البيانات ، انقر على زر "موافق" .

  6. نافذة الوسيطة الخاصة بوظيفة PLT في Microsoft Excel

  7. بعد ذلك ، يتم عرض نتيجة الحساب في الخلية التي حددناها في الفقرة الأولى من هذا الدليل. كما ترون ، فإن قيمة الدفعة الشهرية الإجمالية للقرض هي 23،536.74 روبل . لا تخلط بين علامة "-" قبل هذا المبلغ. لذا يشير أكسل إلى أن هذا التدفق النقدي ، أي خسارة.
  8. نتيجة حساب الدفع الشهري في Microsoft Excel

  9. من أجل احتساب المبلغ الإجمالي للسداد لكامل فترة القرض ، مع الأخذ في الاعتبار سداد مبلغ القرض والفوائد الشهرية ، يكفي مضاعفة قيمة الدفعة الشهرية ( 23536.74 روبل ) بعدد الأشهر ( 24 شهراً ). كما ترون ، كان المبلغ الإجمالي للمدفوعات لفترة القرض بأكملها في حالتنا 564881.67 روبل .
  10. المبلغ الإجمالي للدفعات في Microsoft Excel

  11. الآن يمكنك حساب مبلغ الدفع الزائد على القرض. للقيام بذلك ، فمن الضروري أن تأخذ من المبلغ الإجمالي للمدفوعات على القرض ، بما في ذلك الفائدة وهيئة القرض ، المبلغ الأولي المقترض. لكننا نتذكر أن أول هذه القيم لديها بالفعل علامة "-" . لذلك ، في حالتنا الخاصة ، اتضح أنها بحاجة إلى أن تكون مطوية. كما ترون ، كان المبلغ الإجمالي للدفع الزائد للقرض للفترة كلها 64881.67 روبل .

المبلغ الزائد للقرض في Microsoft Excel

الدرس: معالج الدالة في Excel

الخطوة 2: تفاصيل المدفوعات

والآن وبمساعدة من مشغلي أكسل الآخرين ، سنقوم بعمل تفاصيل الدفع الشهرية لنرى كم في شهر معين ندفعه على هيئة القرض ، وكم هو الفائدة. لهذا الغرض ، نرسم جدولاً في Excel ، والذي سنملأه بالبيانات. سوف تتوافق صفوف هذا الجدول مع الفترة المقابلة ، أي الشهر. بالنظر إلى أن فترة التسديد بالنسبة لنا هي 24 شهراً ، فإن عدد الصفوف سيكون مناسبًا أيضًا. تشير الأعمدة إلى دفع مبلغ القرض ، ودفع الفائدة ، وإجمالي الدفعة الشهرية ، وهو مجموع العمودين السابقين ، والمبلغ المتبقي المطلوب سداده.

جدول العوائد في Microsoft Excel

  1. لتحديد مبلغ الدفع الخاص بجسم القرض ، نستخدم وظيفة OSPLT ، والتي تم تصميمها فقط لهذه الأغراض. اضبط المؤشر في الخلية الموجودة في الصف "1" وفي العمود "الدفع حسب نص القرض" . انقر على زر "إدراج وظيفة" .
  2. إدراج وظيفة في مايكروسوفت إكسل

  3. انتقل إلى معالج الدالة . في الفئة "المالية" ، نحتفل باسم "OSPLT" ، ثم نضغط على الزر "موافق" .
  4. انتقل إلى نافذة الوسائط للدالة OSPLT في Microsoft Excel

  5. يتم إطلاق نافذة حجج المشغل OSPLT. يحتوي على بناء الجملة التالي:

    =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

    كما ترى ، تتطابق حجج هذه الوظيفة تقريبًا تمامًا مع حجج مشغل PLT ، فقط تمت إضافة الوسيطة الإلزامية "Period" بدلاً من الوسيطة "Type" الاختيارية. يشير إلى عدد فترة الدفع ، وفي حالة خاصة بنا ، رقم الشهر.

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

  6. إطار الوسيطة للدالة OBSFT في Microsoft Excel

  7. ولكن لا يزال لدينا حجة جديدة أخرى لم تكن لها وظيفة PLT . هذه الحجة هي "الفترة" . في الحقل المقابل ، عيّن المرجع إلى أول خلية في عمود "الفترة" . يحتوي هذا العنصر من الورقة على الرقم "1" ، والذي يشير إلى عدد الشهر الأول من تسجيل الأرصدة. ولكن على عكس الحقول السابقة ، في الحقل المحدد ، نترك المرجع نسبيًا ، ولا نجعل منه مطلقًا.

    بعد إدخال جميع البيانات التي تحدثنا عنها أعلاه ، انقر على الزر "موافق" .

  8. وسيطة Period في نافذة الوسائط الدالة OBSF في Microsoft Excel

  9. بعد ذلك ، في الخلية ، التي خصصناها سابقًا ، سيتم عرض مبلغ الدفع على هيكل القرض للشهر الأول. سيكون 18536.74 روبل .
  10. نتيجة حساب الدالة OSPLT في Microsoft Excel

  11. ثم ، كما ذكرنا أعلاه ، يجب أن ننسخ هذه الصيغة إلى باقي الخلايا في العمود باستخدام مقبض التعبئة. للقيام بذلك ، قم بتعيين المؤشر إلى الزاوية اليمنى السفلى من الخلية التي تحتوي على الصيغة. يتم تحويل المؤشر إلى صليب ، والذي يسمى علامة التعبئة. ثبّت زر الماوس الأيسر واسحبه لأسفل حتى نهاية الطاولة.
  12. حشو في مايكروسوفت اكسل

  13. وكنتيجة لذلك ، يتم تعبئة كافة الخلايا في العمود. الآن لدينا جدول سداد القروض على أساس شهري. كما ذكر أعلاه ، يزيد مبلغ الدفع لهذه المادة مع كل فترة جديدة.
  14. مبلغ الدفع لجسم القرض شهريًا في Microsoft Excel

  15. الآن نحن بحاجة إلى إجراء حساب شهري لمدفوعات الفائدة. لهذه الأغراض ، سوف نستخدم مشغل PRPLT . حدد أول خلية فارغة في عمود "دفع الفائدة" . انقر على زر "إدراج وظيفة" .
  16. انتقل إلى معالج الدالة في Microsoft Excel

  17. في نافذة المعالجات في فئة "المالية" ، نختار اسم PRPLT . انقر فوق "موافق" زر.
  18. انتقل إلى نافذة الوسيطة للدالة PRPLT في Microsoft Excel

  19. يتم تشغيل نافذة الوسيطة الخاصة بوظيفة PRPLT . تركيبه كما يلي:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

  20. نافذة من الحجج من وظيفة PRPLT في Microsoft Excel

  21. ثم يتم عرض نتيجة حساب مبلغ الفائدة المدفوعة للقرض للشهر الأول في الخلية المقابلة.
  22. نتيجة حساب وظيفة PRPLT في Microsoft Excel

  23. بتطبيق علامة التعبئة ، نقوم بنسخ الصيغة إلى العناصر المتبقية في العمود ، وبالتالي نحصل على جدول دفع شهري للفائدة على القرض. وكما نرى من قبل ، فإن قيمة هذا النوع من الدفع تقل من شهر لآخر.
  24. جدول المدفوعات على الفائدة للقرض في Microsoft Excel

  25. الآن يجب علينا حساب مجموع المدفوعات الشهرية. بالنسبة لهذا الحساب ، يجب ألا يلجأ المرء إلى أي مشغل ، حيث يمكن للمرء استخدام صيغة حسابية بسيطة. نضيف محتويات الخلايا في الشهر الأول من الأعمدة "الدفع بواسطة هيكل القرض" و "دفعة الفائدة" . للقيام بذلك ، قم بتعيين علامة "=" على أول خلية فارغة من عمود "إجمالي الدفعة الشهرية" . ثم انقر على العنصرين أعلاه ، مع وضع علامة "+" بينهما. اضغط على مفتاح Enter .
  26. مجموع الدفعة الشهرية الإجمالية في Microsoft Excel

  27. علاوة على ذلك ، باستخدام علامة التعبئة ، كما في الحالات السابقة ، نقوم بتعبئة العمود بالبيانات. كما نرى ، خلال كامل مدة العقد ، فإن مبلغ الدفعة الشهرية الإجمالية بما في ذلك السداد على هيكل القرض ودفع الفائدة سيبلغ 23536.74 روبل . في الواقع ، لقد قمنا بالفعل بحساب هذا الرقم بمساعدة معاهدة قانون البراءات . ولكن في هذه الحالة ، يتم تقديمها بشكل أكثر وضوحًا ، تمامًا كمبلغ الدفعة المقدمة إلى هيئة القرض والفائدة.
  28. مجموع الدفعة الشهرية في Microsoft Excel

  29. الآن تحتاج إلى إضافة البيانات إلى العمود ، حيث سيتم عرض رصيد مبلغ القرض شهريا ، والتي لا تزال هناك حاجة إلى أن تدفع. في الخلية الأولى من عمود "الرصيد إلى العائد" ، سيكون الحساب هو الأبسط. نحن بحاجة إلى الاستغناء عن مبلغ القرض الأصلي ، المشار إليه في الجدول مع البيانات الأولية ، والدفع على هيئة القرض للشهر الأول في جدول التسوية. ولكن ، بالنظر إلى حقيقة أن أحد الأرقام التي لدينا بالفعل مع علامة "-" ، لا ينبغي أن تؤخذ بعيدا ، ولكن مطوية. افعل هذا واضغط على زر Enter .
  30. يجب أن يتم دفع الرصيد بعد أول شهر من تسجيل الدائن في Microsoft Excel

  31. لكن حساب رصيد السداد بعد الثاني والشهور اللاحقة سيكون أكثر تعقيدًا بعض الشيء. للقيام بذلك ، نحن بحاجة إلى الاستغناء عن جسم القرض في بداية القرض المبلغ الإجمالي للمدفوعات على هيئة القرض للفترة السابقة. عيّن علامة "=" في الخلية الثانية من عمود "الرصيد إلى العائد" . بعد ذلك ، نشير إلى الرابط الخاص بالخلية ، والذي يحتوي على مبلغ القرض الأصلي. نجعلها مطلقة عن طريق اختيار والضغط على مفتاح F4 . ثم نضع علامة "+" ، لأن القيمة الثانية ستكون سلبية بالنسبة لنا. بعد ذلك ، انقر فوق الزر "إدراج وظيفة" .
  32. إدراج وظيفة في مايكروسوفت إكسل

  33. يبدأ معالج الدالة ، الذي من الضروري الانتقال إلى الفئة "رياضيات" . هناك نختار النقش "SUM" وننقر على زر "OK" .
  34. انتقل إلى نافذة الوسائط الدالة SUM في Microsoft Excel

  35. يتم فتح نافذة الوسيطة الدالة SUM . يعمل المشغل المحدد على تلخيص البيانات في الخلايا ، والتي نحتاج إلى تنفيذها في العمود "الدفع حسب نص القرض" . يحتوي على بناء الجملة التالي:

    =СУММ(число1;число2;…)

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

  36. نافذة الوسيطة للدالة SUM في Microsoft Excel

  37. لذلك ، يتم عرض نتيجة رصيد دين الائتمان بعد الشهر الثاني في الخلية. الآن ، بدءًا من هذه الخلية ، نقوم بنسخ الصيغة إلى عناصر العمود الفارغة باستخدام مقبض التعبئة.
  38. حشو في مايكروسوفت اكسل

  39. يتم احتساب الأرصدة الشهرية للدفع على القرض طوال فترة الائتمان. كما هو متوقع ، في نهاية الفترة هذا المبلغ صفر.

حساب الرصيد لسداد القرض في Microsoft Excel

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

تم تغيير البيانات الأصلية في Microsoft Excel

الدرس: الوظائف المالية في Excel

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