صيغة لحساب دفعة سنوية في Excel

تسديد مدفوعات القروض أسهل وأسرع في الحساب باستخدام Microsoft Office Excel. يتم قضاء المزيد من الوقت في الحساب اليدوي. ستركز هذه المقالة على مدفوعات الأقساط السنوية وميزات حسابها ومزاياها وعيوبها.

ما هو الدفع السنوي

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

علاوة على ذلك ، فإن الفائدة على القرض مدرجة بالفعل في المبلغ الإجمالي المدفوع للبنك.

الأقساط التصنيف

يمكن تقسيم الأقساط إلى الأنواع التالية:

1. مُثَبَّت. المدفوعات التي لا تتغير لها معدل ثابت بغض النظر عن الظروف الخارجية.
2. عملة. القدرة على تغيير مبلغ السداد في حالة هبوط سعر الصرف أو ارتفاعه.
3. مفهرسة. المدفوعات حسب المستوى ، مؤشر التضخم. خلال فترة القرض ، غالبًا ما يتغير حجمها.
4. المتغيرات. المعاش ، والتي قد تتغير تبعا لحالة النظام المالي والأدوات.

انتبه! المدفوعات الثابتة هي الأفضل لجميع المقترضين ، لأنها قليلة المخاطر.

مزايا وعيوب مدفوعات الأقساط

لفهم الموضوع بشكل أفضل ، من الضروري دراسة السمات الرئيسية لهذا النوع من مدفوعات القروض. لها المزايا التالية:

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

ليس بدون عيوب:

• تصنيف عالي. سيدفع المقترض مبلغًا أكبر من المال مقارنة بالدفع التفاضلي.
• المشاكل الناشئة عن الرغبة في سداد الديون قبل الموعد المحدد.
• لا إعادة حساب للمدفوعات المبكرة.

ما هو دفع القرض؟

يتكون دفع الأقساط من المكونات التالية:

• الفائدة التي يدفعها الشخص عند سداد القرض.
• جزء من المبلغ الأساسي.

نتيجة لذلك ، يتجاوز إجمالي مبلغ الفائدة دائمًا المبلغ الذي يساهم به المقترض لتخفيض الدين.

صيغة دفع الأقساط الأساسية في Excel

كما هو مذكور أعلاه ، في Microsoft Office Excel يمكنك العمل مع أنواع مختلفة من المدفوعات للقروض والسلف. المعاش ليست استثناء. بشكل عام ، الصيغة التي يمكنك من خلالها حساب مساهمات الأقساط بسرعة هي كما يلي:  

المهم! من المستحيل فتح أقواس في مقام هذا التعبير لتبسيطه.

يتم فك رموز القيم الرئيسية للصيغة على النحو التالي:

• AP - دفع الأقساط السنوية (يتم اختصار الاسم).
• س - حجم أصل الدين للمقترض.
• PS - سعر الفائدة المطروح على أساس شهري من قبل بنك معين.
• C هو عدد الأشهر التي يستغرقها القرض.

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

أمثلة على استخدام دالة PMT في Excel

نعطي شرط بسيط للمشكلة. من الضروري حساب دفعة القرض الشهرية إذا قدم البنك فائدة بنسبة 23 ٪ ، والمبلغ الإجمالي 25000 روبل. سيستمر الإقراض لمدة 3 سنوات. تم حل المشكلة وفقًا للخوارزمية:

1. قم بإنشاء جدول بيانات عام في Excel بناءً على بيانات المصدر.

2. قم بتنشيط وظيفة PMT وأدخل الوسائط الخاصة بها في المربع المناسب.
3. في حقل "الرهان" ، أدخل الصيغة "B3 / B5". سيكون هذا هو معدل الفائدة على القرض.
4. في السطر "Nper" ، اكتب القيمة بالصيغة "B4 * B5". سيكون هذا هو إجمالي عدد الدفعات لكامل مدة القرض.
5. املأ حقل "PS". هنا تحتاج إلى تحديد المبلغ الأولي المأخوذ من البنك ، كتابة القيمة "B2".

6. تأكد من أنه بعد النقر على "موافق" في الجدول المصدر ، تم حساب قيمة "الدفعة الشهرية".

معلومات إضافية! يشير الرقم السالب إلى أن المقترض ينفق المال.

مثال على حساب مبلغ الدفعة الزائدة على قرض في Excel

في هذه المشكلة ، تحتاج إلى حساب المبلغ الذي سيدفعه الشخص الذي اقترض 50000 روبل بمعدل فائدة 27 ٪ لمدة 5 سنوات. في المجموع ، يقوم المقترض بسداد 12 دفعة سنويًا. المحلول:

1. تجميع جدول البيانات الأصلي.

2. من المبلغ الإجمالي للمدفوعات ، اطرح المبلغ الأولي وفقًا للصيغة «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». يجب إدراجه في شريط الصيغة أعلى القائمة الرئيسية للبرنامج.
3. نتيجة لذلك ، سيظهر مبلغ المدفوعات الزائدة في السطر الأخير من اللوحة التي تم إنشاؤها. سيدفع المقترض مبلغ 41606 روبل في الأعلى.

معادلة حساب السداد الأمثل للقرض الشهري في Excel

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

1. قم بإنشاء جدول بيانات بناءً على البيانات الأصلية.

2. أدخل الصيغة في سطر إدخال Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» واضغط على "أدخل" من لوحة المفاتيح. ستختلف الحروف حسب الخلايا التي يوضع فيها الجدول.
3. تأكد من احتساب مبلغ المساهمة تلقائيًا في السطر الأخير من الجدول.

انتبه! وبالتالي ، لكي يجمع العميل 4 روبل بمعدل 2000000 ٪ في 11 عامًا ، فإنه يحتاج إلى إيداع 28188 روبل شهريًا. يشير ناقص المبلغ إلى أن العميل يتكبد خسائر من خلال إعطاء أموال للبنك.

ميزات استخدام دالة PMT في Excel

بشكل عام ، تتم كتابة هذه الصيغة على النحو التالي: = PMT (rate؛ nper؛ ps؛ [bs]؛ [type]). تحتوي الوظيفة على الميزات التالية:

1. عند احتساب الاشتراكات الشهرية ، يؤخذ في الاعتبار المعدل السنوي فقط.
2. عند تحديد سعر الفائدة ، من المهم إعادة الحساب بناءً على عدد الأقساط في السنة.
3. بدلاً من الوسيطة "Nper" في الصيغة ، يشار إلى رقم معين. هذه هي فترة الدفع.

حساب الدفع

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

المرحلة الأولى: احتساب القسط الشهري

لكي تحسب في Excel المبلغ الذي يتعين عليك دفعه كل شهر على قرض بسعر ثابت ، يجب عليك:

1. قم بتجميع الجدول المصدر وحدد الخلية التي تريد عرض النتيجة فيها وانقر فوق الزر "إدراج وظيفة" في الأعلى.

2. في قائمة الوظائف ، حدد "PLT" وانقر فوق "موافق".

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

4. عندما يتم ملء جميع الوسائط ، ستتم كتابة الصيغة المناسبة في السطر لإدخال القيم ، وستظهر نتيجة الحساب بعلامة ناقص في حقل جدول "الدفع الشهري".

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

المرحلة الثانية: تفاصيل الدفع

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

1. أنشئ جدول بيانات لمدة 24 شهرًا.

2. ضع المؤشر في الخلية الأولى من الجدول وأدخل وظيفة "OSPLT".

3. املأ وسيطات الدالة بنفس الطريقة.

4. عند ملء حقل "الفترة" ، تحتاج إلى الرجوع إلى الشهر الأول في الجدول ، مع الإشارة إلى الخلية 1.

5. تأكد من ملء الخلية الأولى في العمود "دفعة من نصيب القرض".
6. لملء جميع صفوف العمود الأول ، تحتاج إلى تمديد الخلية إلى نهاية الجدول

7. حدد وظيفة "PRPLT" لملء العمود الثاني من الجدول.
8. املأ جميع الحجج في النافذة المفتوحة وفقًا للصورة أدناه.

9. احسب إجمالي الدفعة الشهرية عن طريق إضافة القيم في العمودين السابقين.

10. لحساب "الرصيد المستحق الدفع" ، تحتاج إلى إضافة سعر الفائدة إلى السداد الموجود في جسم القرض وتمديده حتى نهاية اللوحة لملء جميع أشهر القرض.

معلومات إضافية! عند حساب الباقي ، يجب تعليق علامات الدولار على الصيغة حتى لا تتحرك عند التمدد.

حساب مدفوعات الأقساط على قرض في Excel

تعد وظيفة PMT مسؤولة عن حساب الأقساط السنوية في Excel. مبدأ الحساب بشكل عام هو القيام بالخطوات التالية:

1. تجميع جدول البيانات الأصلي.
2. بناء جدول سداد الديون لكل شهر.
3. حدد الخلية الأولى في العمود "المدفوعات على القرض" وأدخل صيغة الحساب “PLT ($ B3 / 12 ؛ $ B $ 4 ؛ $ B $ 2)”.
4. تمتد القيمة الناتجة لجميع أعمدة اللوحة.

الحساب في MS Excel سداد المبلغ الأصلي للديون

يجب أن يتم دفع الأقساط الشهرية بمبالغ ثابتة. وسعر الفائدة لا يتغير.

حساب رصيد المبلغ الأساسي (مع BS = 0 ، اكتب = 0)

لنفترض أنه تم أخذ قرض بقيمة 100000 روبل لمدة 10 سنوات بنسبة 9 ٪. من الضروري حساب مبلغ الدين الأساسي في الشهر الأول من السنة الثالثة. المحلول:

1. قم بتجميع ورقة بيانات وحساب الدفعة الشهرية باستخدام صيغة PV أعلاه.
2. احسب حصة الدفع المطلوبة لسداد جزء من الدين باستخدام الصيغة «= -PMT- (PS-PS1) * item = -PMT- (PS + PMT + PS * item)».
3. احسب مبلغ الدين الأساسي لمدة 120 فترة باستخدام صيغة معروفة.
4. باستخدام عامل تشغيل HPMT ، ابحث عن مقدار الفائدة المدفوعة للشهر الخامس والعشرين.
5. تحقق نتيجة.

حساب مبلغ رأس المال الذي تم دفعه بين فترتين

من الأفضل إجراء هذا الحساب بطريقة بسيطة. تحتاج إلى استخدام الصيغ التالية لحساب المبلغ في الفاصل الزمني لفترتين:

• = «- BS (عنصر ؛ con_period ؛ plt ؛ [ps] ؛ [نوع]) / (1 + نوع * عنصر)».
• = "+ BS (المعدل ؛ start_period-1 ؛ plt ؛ [ps] ؛ [النوع]) / IF (start_period = 1 ؛ 1 ؛ 1 + النوع * المعدل)".

انتبه! يتم استبدال الأحرف الموجودة بين قوسين بقيم محددة.

السداد المبكر بمدة مخفضة أو سداد

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

لتقليل المدفوعات ، تحتاج إلى إعادة حساب المساهمة لكل شهر سابق.

آلة حاسبة للقرض مع مدفوعات غير منتظمة

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

1. أدخل أيام الشهر التي يتم سداد الدفعات لها ، وحدد رقمها.
2. تحقق من المبالغ السالبة والموجبة. يفضل السلبية.
3. عد الأيام بين تاريخين تم إيداع الأموال فيهما.

حساب الدفع الدوري ببرنامج MS Excel. الودائع

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

وفي الختام

وبالتالي ، فإن دفع الأقساط أسهل وأسرع وأكثر فاعلية لحساب Excel. مشغل PMT مسؤول عن حسابها. يمكن العثور على أمثلة أكثر تفصيلاً أعلاه.