حساب الدفع السنوي في Microsoft Excel
قبل أخذ قرض ، سيكون من الجيد حساب جميع المبالغ المدفوعة له. وهذا من شأنه أن يوفر على المقترض في المستقبل من مختلف المتاعب وخيبات الأمل غير المتوقعة ، عندما يتبين أن الدفع الزائد كبير للغاية. مساعدة في هذا الحساب يمكن أن أدوات إكسل. دعونا معرفة كيفية حساب مدفوعات قرض الأقساط في هذا البرنامج.
حساب الدفع
بادئ ذي بدء ، يجب أن أقول أن هناك نوعين من مدفوعات القروض:
- متباينة.
- المعاش.
مع خطة متباينة ، يدخل العميل في البنك حصة شهرية متساوية من المدفوعات على جسم القرض بالإضافة إلى مدفوعات الفائدة. تنخفض قيمة مدفوعات الفائدة كل شهر ، حيث يتم تخفيض مبلغ القرض الذي تم حسابه منه. وبالتالي ، يتم تخفيض إجمالي المدفوعات الشهرية أيضًا.
مع مخطط الأقساط ، يتم استخدام نهج مختلف قليلا. يقوم العميل بنفس المبلغ من الدفعة الإجمالية كل شهر ، والذي يتكون من دفعات على هيكل القرض ودفع الفائدة. في البداية ، تكون مدفوعات الفائدة لكامل مبلغ القرض ، ولكن مع انخفاض الجسد ، تقل الفائدة أيضًا. لكن المبلغ الإجمالي للسداد يبقى دون تغيير بسبب الزيادة الشهرية في مبلغ المدفوعات على هيئة القرض. وبالتالي ، مع مرور الوقت ، تقل حصة الفائدة في إجمالي الدفعة الشهرية ، ويزداد وزن الدفع المحدد للجسم. في الوقت نفسه ، لا يتغير إجمالي الدفع الشهري على مدى فترة القرض.
فقط عند حساب الدفع السنوي ، سنتوقف. علاوة على ذلك ، هذا حقيقي ، لأن معظم البنوك في الوقت الحالي تستخدم هذا المخطط. كما أنه ملائم للعملاء ، لأنه في هذه الحالة لا يتغير المبلغ الإجمالي للدفع ، ويظل ثابتًا. الزبائن يعرفون دائما كم لدفع.
الخطوة 1: حساب القسط الشهري
لحساب الرسم الشهري عند استخدام نظام الأقساط في Excel ، هناك وظيفة خاصة - PLT . إنه ينتمي إلى فئة المشغلين الماليين. الصيغة لهذه الوظيفة هي كما يلي:
=ПЛТ(ставка;кпер;пс;бс;тип)
كما ترون ، هذه الوظيفة لديها الكثير من الحجج. صحيح أن آخر اثنين منهما ليسا إلزاميين.
تشير حجة "الرهان" إلى سعر الفائدة لفترة محددة. إذا تم ، على سبيل المثال ، استخدام المعدل السنوي ، ولكن يتم سداد القرض شهريًا ، فيجب أن يقسم السعر السنوي على 12 ويتم استخدام النتيجة كحجة. إذا تم استخدام طريقة دفع ربع سنوية ، في هذه الحالة ، يجب تقسيم المعدل السنوي على 4 ، إلخ.
يشير "Kper" إلى العدد الإجمالي لفترات سداد القروض. بمعنى ، إذا تم أخذ قرض لمدة عام واحد مع دفع شهري ، فإن عدد الفترات يعتبر 12 ، إذا كان لمدة عامين ، فإن عدد الفترات هو 24 . إذا تم أخذ القرض لمدة سنتين مع دفعة ربع سنوية ، فإن عدد الفترات يكون 8 .
تشير "Ps" إلى القيمة الحالية. بكلمات بسيطة ، هذا هو المبلغ الإجمالي للقرض في بداية القرض ، أي المبلغ الذي تقترضه ، دون الأخذ في الاعتبار الفائدة والمدفوعات الإضافية الأخرى.
"Bs" هي القيمة المستقبلية. هذا المبلغ ، والذي سيكون جسم القرض في وقت الانتهاء من اتفاق القرض. في معظم الحالات ، تساوي هذه الوسيطة القيمة "0" ، حيث يجب على المقترض التسوية الكاملة مع المقرض في نهاية فترة الاعتماد. الوسيطة المحددة اختيارية. لذلك ، إذا تم حذفه ، فإنه يعتبر صفر.
تحدد الوسيطة "النوع" وقت الحساب: في النهاية أو في بداية الفترة. في الحالة الأولى ، يأخذ القيمة "0" ، وفي الثانية - "1" . تستخدم معظم المؤسسات المصرفية هذا الخيار مع الدفع في نهاية الفترة. هذه الحجة اختيارية أيضاً ، وإذا تم حذفها ، فمن المفترض أنها صفر.
وقد حان الوقت الآن للانتقال إلى مثال ملموس لحساب الرسوم الشهرية باستخدام وظيفة معاهدة قانون البراءات. للحساب ، نستخدم الجدول مع البيانات الأولية ، والذي يبين سعر الفائدة على القرض ( 12 ٪ ) ، وقيمة القرض ( 500،000 روبل ) وفترة القرض ( 24 شهرا ). في هذه الحالة ، يتم الدفع شهريًا في نهاية كل فترة.
- حدد العنصر في الورقة التي سيتم عرض نتيجة الحساب لها ، وانقر فوق الرمز "Insert function" الموجود بالقرب من شريط الصيغة.
- يتم تشغيل نافذة معالج الوظائف . في فئة "Financial" ، نختار الاسم "PLT" ونضغط على زر "OK" .
- بعد ذلك ، تفتح نافذة الوسيطات الخاصة بمشغل PLT .
في حقل "Rate" ، يجب إدخال مقدار الفائدة للفترة. يمكن إجراء ذلك يدويًا ، فقط ضع النسبة المئوية ، ولكننا قمنا بإدراجه في خلية منفصلة على الورقة ، لذلك دعنا نعطيه رابطًا. اضبط المؤشر في الحقل ، ثم انقر فوق الخلية المقابلة. ولكن ، كما نتذكر ، في جدولنا يتم تعيين سعر الفائدة السنوي ، وفترة السداد تساوي شهر واحد. لذلك ، نقوم بتقسيم المعدل السنوي ، أو بالأحرى الإشارة إلى الخلية التي تحتوي عليها ، من خلال الرقم 12 ، المقابل لعدد الأشهر في السنة. يتم تنفيذ التقسيم مباشرة في مجال نافذة الحجج.
في حقل "Kper" ، يتم تعيين فترة الاعتماد. لدينا لمدة 24 شهرا. يمكنك إدخال الرقم 24 يدويًا ، ولكننا ، كما في الحالة السابقة ، نشير إلى رابط إلى موقع هذا المؤشر في الجدول المصدر.
في الحقل "Ps" ، يشار إلى القيمة الأصلية للقرض. يساوي 500،000 روبل . كما في الحالات السابقة ، نشير إلى رابط عنصر الورقة الذي يحتوي على هذا المؤشر.
يشير الحقل "Bs" إلى مبلغ القرض بعد سداده بالكامل. كما تتذكر ، هذه القيمة هي دائما تقريبا الصفر. لقد قمنا بتعيين الرقم "0" في هذا الحقل. على الرغم من أن هذه الحجة يمكن حذفها تماما.
في حقل "النوع" ، نشير في البداية أو في نهاية الشهر. نحن ، كما في معظم الحالات ، يتم إنتاجها في نهاية الشهر. لذلك ، قمنا بتعيين الرقم "0" . كما هو الحال مع الوسيطة السابقة ، لا يمكنك إدخال أي شيء في هذا الحقل ، ثم سيفترض البرنامج افتراضيًا أن له قيمة صفر.
بعد إدخال جميع البيانات ، انقر على زر "موافق" .
- بعد ذلك ، يتم عرض نتيجة الحساب في الخلية التي حددناها في الفقرة الأولى من هذا الدليل. كما ترون ، فإن قيمة الدفعة الشهرية الإجمالية للقرض هي 23،536.74 روبل . لا تخلط بين علامة "-" قبل هذا المبلغ. لذا يشير أكسل إلى أن هذا التدفق النقدي ، أي خسارة.
- من أجل احتساب المبلغ الإجمالي للسداد لكامل فترة القرض ، مع الأخذ في الاعتبار سداد مبلغ القرض والفوائد الشهرية ، يكفي مضاعفة قيمة الدفعة الشهرية ( 23536.74 روبل ) بعدد الأشهر ( 24 شهراً ). كما ترون ، كان المبلغ الإجمالي للمدفوعات لفترة القرض بأكملها في حالتنا 564881.67 روبل .
- الآن يمكنك حساب مبلغ الدفع الزائد على القرض. للقيام بذلك ، فمن الضروري أن تأخذ من المبلغ الإجمالي للمدفوعات على القرض ، بما في ذلك الفائدة وهيئة القرض ، المبلغ الأولي المقترض. لكننا نتذكر أن أول هذه القيم لديها بالفعل علامة "-" . لذلك ، في حالتنا الخاصة ، اتضح أنها بحاجة إلى أن تكون مطوية. كما ترون ، كان المبلغ الإجمالي للدفع الزائد للقرض للفترة كلها 64881.67 روبل .
الخطوة 2: تفاصيل المدفوعات
والآن وبمساعدة من مشغلي أكسل الآخرين ، سنقوم بعمل تفاصيل الدفع الشهرية لنرى كم في شهر معين ندفعه على هيئة القرض ، وكم هو الفائدة. لهذا الغرض ، نرسم جدولاً في Excel ، والذي سنملأه بالبيانات. سوف تتوافق صفوف هذا الجدول مع الفترة المقابلة ، أي الشهر. بالنظر إلى أن فترة التسديد بالنسبة لنا هي 24 شهراً ، فإن عدد الصفوف سيكون مناسبًا أيضًا. تشير الأعمدة إلى دفع مبلغ القرض ، ودفع الفائدة ، وإجمالي الدفعة الشهرية ، وهو مجموع العمودين السابقين ، والمبلغ المتبقي المطلوب سداده.
- لتحديد مبلغ الدفع الخاص بجسم القرض ، نستخدم وظيفة OSPLT ، والتي تم تصميمها فقط لهذه الأغراض. اضبط المؤشر في الخلية الموجودة في الصف "1" وفي العمود "الدفع حسب نص القرض" . انقر على زر "إدراج وظيفة" .
- انتقل إلى معالج الدالة . في الفئة "المالية" ، نحتفل باسم "OSPLT" ، ثم نضغط على الزر "موافق" .
- يتم إطلاق نافذة حجج المشغل OSPLT. يحتوي على بناء الجملة التالي:
=ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)كما ترى ، تتطابق حجج هذه الوظيفة تقريبًا تمامًا مع حجج مشغل PLT ، فقط تمت إضافة الوسيطة الإلزامية "Period" بدلاً من الوسيطة "Type" الاختيارية. يشير إلى عدد فترة الدفع ، وفي حالة خاصة بنا ، رقم الشهر.
نقوم بملء حقول نافذة الحجج الخاصة بدالة OSPLT التي نعرفها بالفعل من خلال نفس البيانات التي تم استخدامها في وظيفة PLT . فقط مع الأخذ بعين الاعتبار حقيقة أنه في المستقبل سيتم نسخ الصيغة باستخدام علامة ملء ، تحتاج إلى جعل جميع الروابط في الحقول المطلقة بحيث لا تتغير. لهذا ، يلزم وضع علامة دولار قبل كل قيمة للإحداثيات على طول الخطوط الرأسية والأفقية. ولكن من الأسهل القيام بذلك ، ببساطة تحديد الإحداثيات والضغط على مفتاح الوظيفة F4 . سيتم وضع علامة الدولار في الأماكن الصحيحة تلقائيا. لا تنس أيضًا أنه يجب تقسيم المعدل السنوي على 12 .
- ولكن لا يزال لدينا حجة جديدة أخرى لم تكن لها وظيفة PLT . هذه الحجة هي "الفترة" . في الحقل المقابل ، عيّن المرجع إلى أول خلية في عمود "الفترة" . يحتوي هذا العنصر من الورقة على الرقم "1" ، والذي يشير إلى عدد الشهر الأول من تسجيل الأرصدة. ولكن على عكس الحقول السابقة ، في الحقل المحدد ، نترك المرجع نسبيًا ، ولا نجعل منه مطلقًا.
بعد إدخال جميع البيانات التي تحدثنا عنها أعلاه ، انقر على الزر "موافق" .
- بعد ذلك ، في الخلية ، التي خصصناها سابقًا ، سيتم عرض مبلغ الدفع على هيكل القرض للشهر الأول. سيكون 18536.74 روبل .
- ثم ، كما ذكرنا أعلاه ، يجب أن ننسخ هذه الصيغة إلى باقي الخلايا في العمود باستخدام مقبض التعبئة. للقيام بذلك ، قم بتعيين المؤشر إلى الزاوية اليمنى السفلى من الخلية التي تحتوي على الصيغة. يتم تحويل المؤشر إلى صليب ، والذي يسمى علامة التعبئة. ثبّت زر الماوس الأيسر واسحبه لأسفل حتى نهاية الطاولة.
- وكنتيجة لذلك ، يتم تعبئة كافة الخلايا في العمود. الآن لدينا جدول سداد القروض على أساس شهري. كما ذكر أعلاه ، يزيد مبلغ الدفع لهذه المادة مع كل فترة جديدة.
- الآن نحن بحاجة إلى إجراء حساب شهري لمدفوعات الفائدة. لهذه الأغراض ، سوف نستخدم مشغل PRPLT . حدد أول خلية فارغة في عمود "دفع الفائدة" . انقر على زر "إدراج وظيفة" .
- في نافذة المعالجات في فئة "المالية" ، نختار اسم PRPLT . انقر فوق "موافق" زر.
- يتم تشغيل نافذة الوسيطة الخاصة بوظيفة PRPLT . تركيبه كما يلي:
=ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)كما ترون ، فإن حجج هذه الوظيفة متطابقة تمامًا مع العناصر المماثلة لعامل OSPLT . لذلك ، نضع في النافذة نفس البيانات التي أدخلناها في نافذة الحجج السابقة. ومع ذلك ، لا ننسى أن الارتباط في حقل "الفترة" يجب أن يكون نسبيًا ، وفي جميع الحقول الأخرى يجب إحضار الإحداثيات إلى النموذج المطلق. بعد ذلك ، انقر على زر "موافق" .
- ثم يتم عرض نتيجة حساب مبلغ الفائدة المدفوعة للقرض للشهر الأول في الخلية المقابلة.
- بتطبيق علامة التعبئة ، نقوم بنسخ الصيغة إلى العناصر المتبقية في العمود ، وبالتالي نحصل على جدول دفع شهري للفائدة على القرض. وكما نرى من قبل ، فإن قيمة هذا النوع من الدفع تقل من شهر لآخر.
- الآن يجب علينا حساب مجموع المدفوعات الشهرية. بالنسبة لهذا الحساب ، يجب ألا يلجأ المرء إلى أي مشغل ، حيث يمكن للمرء استخدام صيغة حسابية بسيطة. نضيف محتويات الخلايا في الشهر الأول من الأعمدة "الدفع بواسطة هيكل القرض" و "دفعة الفائدة" . للقيام بذلك ، قم بتعيين علامة "=" على أول خلية فارغة من عمود "إجمالي الدفعة الشهرية" . ثم انقر على العنصرين أعلاه ، مع وضع علامة "+" بينهما. اضغط على مفتاح Enter .
- علاوة على ذلك ، باستخدام علامة التعبئة ، كما في الحالات السابقة ، نقوم بتعبئة العمود بالبيانات. كما نرى ، خلال كامل مدة العقد ، فإن مبلغ الدفعة الشهرية الإجمالية بما في ذلك السداد على هيكل القرض ودفع الفائدة سيبلغ 23536.74 روبل . في الواقع ، لقد قمنا بالفعل بحساب هذا الرقم بمساعدة معاهدة قانون البراءات . ولكن في هذه الحالة ، يتم تقديمها بشكل أكثر وضوحًا ، تمامًا كمبلغ الدفعة المقدمة إلى هيئة القرض والفائدة.
- الآن تحتاج إلى إضافة البيانات إلى العمود ، حيث سيتم عرض رصيد مبلغ القرض شهريا ، والتي لا تزال هناك حاجة إلى أن تدفع. في الخلية الأولى من عمود "الرصيد إلى العائد" ، سيكون الحساب هو الأبسط. نحن بحاجة إلى الاستغناء عن مبلغ القرض الأصلي ، المشار إليه في الجدول مع البيانات الأولية ، والدفع على هيئة القرض للشهر الأول في جدول التسوية. ولكن ، بالنظر إلى حقيقة أن أحد الأرقام التي لدينا بالفعل مع علامة "-" ، لا ينبغي أن تؤخذ بعيدا ، ولكن مطوية. افعل هذا واضغط على زر Enter .
- لكن حساب رصيد السداد بعد الثاني والشهور اللاحقة سيكون أكثر تعقيدًا بعض الشيء. للقيام بذلك ، نحن بحاجة إلى الاستغناء عن جسم القرض في بداية القرض المبلغ الإجمالي للمدفوعات على هيئة القرض للفترة السابقة. عيّن علامة "=" في الخلية الثانية من عمود "الرصيد إلى العائد" . بعد ذلك ، نشير إلى الرابط الخاص بالخلية ، والذي يحتوي على مبلغ القرض الأصلي. نجعلها مطلقة عن طريق اختيار والضغط على مفتاح F4 . ثم نضع علامة "+" ، لأن القيمة الثانية ستكون سلبية بالنسبة لنا. بعد ذلك ، انقر فوق الزر "إدراج وظيفة" .
- يبدأ معالج الدالة ، الذي من الضروري الانتقال إلى الفئة "رياضيات" . هناك نختار النقش "SUM" وننقر على زر "OK" .
- يتم فتح نافذة الوسيطة الدالة SUM . يعمل المشغل المحدد على تلخيص البيانات في الخلايا ، والتي نحتاج إلى تنفيذها في العمود "الدفع حسب نص القرض" . يحتوي على بناء الجملة التالي:
=СУММ(число1;число2;…)الوسائط هي المراجع إلى الخلايا التي تحتوي على الأرقام. قمنا بتعيين المؤشر في حقل "Number1" . ثم ، اضغط باستمرار على زر الماوس الأيسر وحدد أول خليتين من عمود "العائد حسب هيئة الائتمان" على الورقة. في الحقل ، كما ترى ، تم عرض مرجع للنطاق. وهو يتألف من جزأين يفصل بينهما نقطتان: المراجع إلى الخلية الأولى من النطاق وإلى الأخير. لكي نتمكن من نسخ الصيغة المشار إليها في المستقبل عن طريق علامة التعبئة ، فإننا نصنع الجزء الأول من الإشارة إلى النطاق المطلق. حدده وانقر على مفتاح الوظيفة F7 . يتم ترك الجزء الثاني من المرجع نسبيًا. الآن ، عند استخدام علامة التعبئة ، سيتم إصلاح الخلية الأولى من النطاق ، وسيتم تمديد الخلية الأخيرة أثناء تحركها لأسفل. هذا ما نحتاجه لتحقيق أهدافنا. ثم انقر على زر "موافق" .
- لذلك ، يتم عرض نتيجة رصيد دين الائتمان بعد الشهر الثاني في الخلية. الآن ، بدءًا من هذه الخلية ، نقوم بنسخ الصيغة إلى عناصر العمود الفارغة باستخدام مقبض التعبئة.
- يتم احتساب الأرصدة الشهرية للدفع على القرض طوال فترة الائتمان. كما هو متوقع ، في نهاية الفترة هذا المبلغ صفر.
وبالتالي ، لم نحسب فقط الدفع للقرض ، بل نظمت نوعًا من حاسبة القروض. من سيعمل على مخطط الأقساط. إذا كان في الجدول الأصلي ، على سبيل المثال ، تغيير مبلغ القرض ومعدل الفائدة السنوي ، ثم في الجدول النهائي سيكون هناك إعادة حساب تلقائي للبيانات. لذلك ، يمكن استخدامه ليس فقط مرة واحدة لحالة معينة ، ولكن يمكن استخدامه في حالات مختلفة لحساب خيارات القروض على نظام الأقساط.
الدرس: الوظائف المالية في Excel
كما ترون ، باستخدام برنامج Excel في المنزل ، يمكنك بسهولة حساب إجمالي مبلغ القرض الشهري على نظام الأقساط ، باستخدام هذه الأغراض المشغل PLT . بالإضافة إلى ذلك ، بمساعدة وظائف OPST و MTEF ، يمكن حساب مبلغ المدفوعات على هيئة القرض والفائدة للفترة المحددة. بتطبيق كل هذه الحقائب من الوظائف معًا ، يمكنك إنشاء حاسبة قروض قوية يمكنك استخدامها أكثر من مرة لحساب دفعة سنوية.