قبل أخذ القرض، سيكون من الجيد حساب جميع المدفوعات عليها. سيوفر المقترض في المستقبل من مختلف المشاكل غير المتوقعة وخيبة الأمل عندما اتضح أن المبالغ الزائدة كبيرة جدا. مساعدة على هذا الحساب يمكن لأدوات برنامج Excel. دعونا معرفة كيفية حساب مدفوعات الأقساط على القرض في هذا البرنامج.
حساب الدفع
بادئ ذي بدء، يجب القول أن هناك نوعان من مدفوعات الائتمان:- متباينة؛
- دخل سنوي.
مع مخطط متباين، يجلب العميل حصة مساوية شهرية من المدفوعات على جسم القرض بالإضافة إلى المدفوعات إلى الفائدة. انخفض حجم مدفوعات الفائدة كل شهر، حيث يتم تقليل جسم القرض الذي يتم فيه حساب منه. وبالتالي، يتم تقليل الدفعة الشهرية الشاملة أيضا.
يستخدم مخطط العمة آن نهجا مختلفا قليلا. يجعل العميل مبلغا واحدا من إجمالي الدفعة الشهرية، والتي تتكون من مدفوعات على أساس قرض ودفع الفائدة. في البداية، يتم ترقيم مساهمات الفوائد على كامل القرض، ولكن كما ينخفض الجسم، يتم تقليل الفائدة والاهتمام الاستحقاق. لكن المبلغ الإجمالي للدفع يظل دون تغيير بسبب زيادة شهرية في مقدار المدفوعات التي قدمها جسم القرض. وبالتالي، مع مرور الوقت، نسبة الاهتمام بإجمالي سقوط الدفع الشهري، وتنمو وزن نسبة الجسم. في الوقت نفسه، فإن الدفعة الشهرية العامة نفسها لا تتغير طوال فترة الائتمان.
فقط على حساب الدفع الأقساط، وسوف نتوقف. خاصة، هذا مناسب، لأن معظم البنوك تستخدم الآن هذا المخطط المحدد. إنه مناسب للعملاء، لأنه في هذه الحالة لا يتغير المبلغ الإجمالي للدفع، المتبقية ثابتة. يعرف العملاء دائما كم تحتاج إلى الدفع.
الخطوة 1: حساب المساهمة الشهرية
لحساب مساهمة شهرية عند استخدام دائرة الأقساط في Excele، هناك وظيفة خاصة - PPT. يشير إلى فئة المشغلين الماليين. صيغة هذه الميزة هي كما يلي:
= PPT (معدل؛ KPER؛ PS؛ BS؛ نوع)
كما نرى، تتمتع الوظيفة المحددة بعدد كبير إلى حد ما من الحجج. صحيح، آخر اثنين منهم ليس إلزاميا.
تشير وسيطة "المعدل" إلى معدل مئوية لفترة محددة. إذا تم استخدام المعدل السنوي، على سبيل المثال، فإن دفع القرض شهريا، فيجب تقسيم السعر السنوي إلى 12 وتستخدم النتيجة كحجة. إذا تم تطبيق نوع ربع سنوي من الدفع، في هذه الحالة، يجب تقسيم الرهان السنوي إلى 4، إلخ.
"CPER" تعني إجمالي عدد فترات مدفوعات القروض. وهذا هو، إذا تم أخذ القرض لمدة عام مع الدفعة الشهرية، فإن عدد الفترات تعتبر 12 عاما، إذا كان عامين، ثم عدد الفترات - 24. إذا تم أخذ القرض لمدة عامين مع دفع ربع سنوي، ثم عدد الفترات 8.
يشير "PS" إلى القيمة الحالية في الوقت الحالي. التحدث بكلمات بسيطة، وهذا هو المبلغ الإجمالي للقرض في بداية الإقراض، وهذا هو، المبلغ الذي اقترضته، باستثناء الفائدة وغيرها من المدفوعات الإضافية الأخرى.
"BS" هي تكلفة مستقبلة. ستكون هذه القيمة هيئة قرض في وقت الانتهاء من اتفاقية القرض. في معظم الحالات، هذه الحجة هي "0"، لأن المقترض في نهاية فترة الائتمان يجب أن يستقر بالكامل مع المقرض. الوسيطة المحددة ليست إلزامية. لذلك، إذا كان ينحدر، فإنه يعتبر صفرا.
تحدد الوسيطة "النوع" وقت الحساب: في النهاية أو في بداية الفترة. في الحالة الأولى، يستغرق القيمة "0"، وفي الثانية - "1". تستخدم معظم المؤسسات المصرفية الخيار بالضبط مع الدفع في نهاية الفترة. هذه الحجة اختيارية أيضا، وإذا تم حذفها، فمن المعتقد أنها صفر.
الآن حان الوقت للانتقال إلى مثال محدد لحساب مساهمة شهرية باستخدام وظيفة PL. لحساب، نستخدم طاولة مع البيانات المصدر، حيث يشار إلى معدل الفائدة على القرض (12٪)، قيمة القرض (500000 روبل) وفترة القرض (24 شهرا). في الوقت نفسه، يتم الدفع شهريا في نهاية كل فترة.
- حدد العنصر الموجود في الورقة التي سيتم عرض نتيجة النتيجة، وانقر فوق أيقونة "إدراج وظيفة"، وضعت بالقرب من صف الصيغة.
- يتم إطلاق نافذة معالج النافذة. في فئة "مالية" تخصيص اسم "PLT" وانقر على زر "موافق".
- بعد ذلك، يفتح نافذة الوسائط لمشغل PL.
في حقل "معدل"، يجب عليك إدخال القيمة النسبة المئوية للفترة. يمكن القيام بذلك يدويا، فقط وضع نسبة مئوية، ولكن يتم الإشارة إليها في خلية منفصلة على الورقة، لذلك سنقدم رابطا إليه. قم بتثبيت المؤشر في الحقل، ثم انقر فوق الخلية المقابلة. ولكن، كما نتذكر، لدينا معدل الفائدة السنوي في طاولتنا، وفترة الدفع تساوي الشهر. لذلك، نقسم الرهان السنوي، بل رابطا للخلية التي تضم فيها الرقم 12، مما يتوافق مع عدد الأشهر في السنة. الانقسام يركض مباشرة في حقل نافذة الوسيطة.
في حقل cper، تم تعيين الإقراض. إنه يساوي 24 شهرا. يمكنك تطبيقها في الحقل رقم 24 يدويا، ولكن في الحالة السابقة، حدد رابطا إلى موقع هذا المؤشر في الجدول المصدر.
في الحقل "PS" يشير إلى قيمة القرض الأولي. يساوي 500000 روبل. كما هو الحال في الحالات السابقة، نحدد رابطا لعنصر الأوراق، والذي يحتوي على هذا المؤشر.
في الحقل "BS" يشير إلى حجم القرض، بعد الدفع الكامل. كما تتذكر، هذه القيمة هي دائما تقريبا صفر. تثبيت في هذا الحقل الرقم "0". على الرغم من أن هذه الحجة يمكن حذفها عموما.
في حقل "نوع"، نحدد في البداية أو في نهاية الشهر يتم إجراء الدفع. نحن، كما هو الحال في معظم الحالات، يتم إنتاجها في نهاية الشهر. لذلك، وضعنا الرقم "0". كما هو الحال في الحجة السابقة، من الممكن إدخال أي شيء لهذا الحقل، ثم يفترض البرنامج الافتراضي أنه صفر يساوي ذلك.
بعد إدخال جميع البيانات، اضغط على زر "موافق".
- بعد ذلك، في الخلية التي خصصناها في الفقرة الأولى من هذا الدليل، يتم عرض نتيجة الحساب. كما ترون، فإن حجم الدفع العام الشهري على القرض هو 23536.74 روبل. تتيح لك عدم الخلط بين العلامة "-" قبل هذا المبلغ. لذلك تشير المنفى إلى أن هذا هو تدفق الأموال، أي خسارة.
- من أجل حساب المبلغ الإجمالي للدفع لفترة القرض بأكملها، مع مراعاة سداد جسم القرض والفائدة الشهرية، بدلا من ذلك مضاعفة مبلغ الدفع الشهري (23536.74 روبل) لعدد أشهر (24 شهرا) ). كما ترون، كان المبلغ الإجمالي للمدفوعات لفترة القرض بأكملها في قضيتنا 564881.67 روبل.
- الآن يمكنك حساب مقدار الزائد على القرض. للقيام بذلك، من الضروري الاستفادة من المبلغ الإجمالي للمدفوعات حول القرض، بما في ذلك الفائدة وجسم القرض، والمبلغ الأولي المطالب به. لكننا نتذكر أن أول هذه القيم بالفعل مع علامة "-". لذلك، في حالة تحديد حالنا، اتضح أنهم بحاجة إلى مطوية. كما نرى، كان إجمالي المبالغ الزائدة من القرض خلال الفترة بأكملها 64881.67 روبل.
درس: ماجستير في الوظائف في Excel
المرحلة 2: تفاصيل الدفع
والآن، بمساعدة مشغلي Excel الآخرين، نقدم تفاصيل شهرية مدفوعات لمعرفة مقدار شهر معين ندفعه من خلال نص القرض، وكم هو مقدار الفائدة. لهذه الأغراض، حداد في طاولة المنفى، والتي سنزيل البيانات. ستكون خطوط هذا الجدول مسؤولة عن الفترة المقابلة، أي الشهر. بالنظر إلى أن فترة الإقراض هي 24 شهرا، سيكون عدد الصفوف مناسبا أيضا. أشارت الأعمدة إلى مجموعة قرض ومدفوعات الفائدة، ودفع شهري إجمالي شهري، وهو مجموع الأعمدة السابقة، وكذلك المبلغ المتبقي للدفع.
- لتحديد مقدار الدفع عن طريق نص القرض، استخدم وظيفة OSP، والتي مخصصة لهذه الأغراض. نحن نؤسس المؤشر في الخلية، الموجودة في الخط "1" وفي العمود "الدفع عن طريق نص القرض". انقر فوق الزر "لصق وظيفة".
- انتقل إلى ماجستير الوظائف. في فئة "المالية"، نلاحظ اسم "OSPLT" وانقر فوق الزر "موافق".
- تبدأ حجج حجج المشغل OSP. لديها بناء الجملة التالي:
= osscult (معدل؛ فترة؛ KPER؛ PS؛ BS)
كما نرى، تتزامن حجج هذه الميزة تماما تقريبا مع حجج مشغل PLT، بدلا من الوسيطة الاختيارية "النوع" إضافة وسيطة إلزامية "فترة". يشير إلى عدد فترة الدفع، وفي حالتنا الخاصة في عدد الشهر.
املأ حجج وسيطات وظيفة OSR مألوفة بالفعل لنا بنفس البيانات، والتي تم استخدامها لوظيفة PL. فقط بالنظر إلى حقيقة أنه في المستقبل، سيتم استخدام نسخ صيغة من خلال علامة ملء، تحتاج إلى القيام بجميع الروابط في الحقول المطلقة بحيث لا تتغير. يتطلب هذا وضع علامة الدولار قبل كل قيمة للإحداثيات الرأسية والأفقية. ولكن من الأسهل القيام بذلك، ما عليك سوى اختيار الإحداثيات والنقر فوق مفتاح الدالة F4. سيتم وضع علامة الدولار في الأماكن الصحيحة تلقائيا. ونحن أيضا لا ننسى أن الرهان السنوي يجب تقسيمه إلى 12.
- ولكن لدينا حجة جديدة أخرى، والتي لم تكن من وظيفة الثابتة الثابتة. هذه الحجة "الفترة". في الحقل المناسب، قم بتعيين مرجع إلى الخلية الأولى من عمود "الفترة". يحتوي عنصر الورقة هذا على الرقم "1"، والذي يدل على عدد الشهر الأول من الإقراض. ولكن على عكس الحقول السابقة، نترك الارتباط قريب في الحقل المحدد، ولا تفعل مطلقا منه.
بعد إدخال جميع البيانات التي تحدثنا عنها أعلاه، اضغط على زر "موافق".
- بعد ذلك، في الخلية، التي سبق أن تخصصنا، ستظهر مقدار الدفع بجسم القرض في الشهر الأول. سيكون 18536.74 روبل.
- ثم، كما هو مذكور أعلاه، يجب علينا نسخ هذه الصيغة إلى خلايا الأعمدة المتبقية باستخدام علامة التعبئة. للقيام بذلك، قم بتعيين المؤشر إلى الزاوية اليمنى السفلى من الخلية، والذي يحتوي على الصيغة. يتم تحويل المؤشر إلى الصليب، والذي يسمى علامة التعبئة. انقر فوق زر الماوس الأيسر واسحبه إلى نهاية الجدول.
- نتيجة لذلك، يتم ملء جميع أعمدة الخلايا. الآن لدينا مخطط دفع قرض شهري. كما ذكر أعلاه، يزيد مقدار الدفع على هذه المقالة مع كل فترة جديدة.
- الآن نحن بحاجة إلى إجراء حساب دفع شهري عن طريق الفائدة. لهذه الأغراض، سوف نستخدم مشغل PRT. نحن نخصص أول خلية فارغة في العمود "نسبة المدفوعات". انقر فوق الزر "لصق وظيفة".
- في وظائف ماجستير المهام في الفئة "المالية"، ننتج أسماء NAMP. قم بإجراء انقر فوق الزر "موافق".
- تبدأ نافذة الوسائط لدالة TRP. بناء الجملة الخاص به يبدو هذا:
= PRT (معدل؛ الفترة؛ وحدة المعالجة المركزية؛ PS)
كما نرى، فإن حجج هذه الوظيفة متطابقة تماما عناصر مماثلة لمشغل OSP. لذلك، ما عليك سوى إدخال نفس البيانات في النافذة التي أدخلناها في النافذة السابقة للحجج. نحن لا ننسى أن المرجع في مجال "الفترة" يجب أن يكون قريب، وفي جميع المجالات الأخرى يجب إحضار الإحداثيات إلى الشكل المطلق. بعد ذلك، انقر فوق الزر "موافق".
- ثم يتم عرض نتيجة حساب مقدار الدفع مقابل الفائدة للحصول على قرض للشهر الأول في الخلية المقابلة.
- تطبيق علامة التعبئة، وجعل نسخ الصيغة في العناصر المتبقية من العمود، بهذه الطريقة تلقي جدول شهري للنسب النسب المئوية للقرض. كما نرى، كما قيل في وقت سابق، من شهر إلى شهر تقليل قيمة هذا النوع من الدفع.
- الآن علينا حساب الدفع الشهري العام. لهذا الحساب، لا ينبغي للمرء أن يلجأ إلى أي مشغل، حيث يمكنك استخدام الصيغة الحسابية البسيطة. قمنا بتطوي محتويات الخلايا في الشهر الأول من الأعمدة "الدفع عن طريق نص القرض" و "الفائدة الكاملة". للقيام بذلك، قم بتعيين علامة "=" في أول خلية فارغة في العمود "إجمالي الدفع الشهري". ثم انقر فوق العنصرين أعلاه عن طريق تعيين علامة "+" بينهما. انقر فوق مفتاح ENTER.
- بعد ذلك، باستخدام علامة التعبئة، كما هو الحال في الحالات السابقة، قم بملء عمود البيانات. نظرا لأننا نرى، في جميع أنحاء العمل بأكمله للعقد، فإن مبلغ الدفعة الشهرية الإجمالية، والذي يتضمن الدفع من قبل هيئة القرض ودفع الفائدة، سيكون 23536.74 روبل. في الواقع، نحسب بالفعل هذا المؤشر قبل استخدام PPT. ولكن في هذه الحالة، يتم عرضها بشكل واضح، بدقة كقوة الدفع بجسم القرض والفائدة.
- الآن تحتاج إلى إضافة بيانات إلى العمود، حيث يتم عرض ما تبقى من مبلغ القرض شهريا، والتي لا تزال هناك حاجة إلى الدفع. في الخلية الأولى من العمود "التوازن على الدفع" الحساب سيكون أسهل. نحن بحاجة إلى أن تؤخذ بعيدا عن حجم القرض الأولي، الذي تم تحديده في الجدول مع البيانات الأولية، ودفع من قبل الهيئة من القرض في الشهر الأول في الجدول محسوبة. ولكن، نظرا لحقيقة أن واحدا من الأرقام نذهب بالفعل مع علامة "-"، ثم أنها لا ينبغي أن تؤخذ بعيدا، ولكن لأضعاف. نحن تجعل من وانقر على زر ENTER.
- لكن حساب التوازن في الأجر بعد أشهر الثانية واللاحقة ستكون أكثر تعقيدا نوعا ما. للقيام بذلك، ونحن بحاجة إلى اتخاذ بعيدا عن الجسم للقرض إلى بداية الإقراض بمبلغ إجمالي المدفوعات من قبل الهيئة للقرض للفترة السابقة. تثبيت "=" علامة في الخلية الثانية من العمود "قصر على الدفع". بعد ذلك، تحديد ارتباط إلى الخلية التي تحتوي على مبلغ القرض الأولي. نحن تجعل من المطلق، وتسليط الضوء على الضغط على مفتاح F4. ثم نضع علامة "+"، لأن لدينا المعنى الثاني وهكذا سلبية. بعد ذلك، انقر على الزر "إدراج وظيفة".
- يتم تشغيل الماجستير من الوظائف، والتي تحتاج للانتقال إلى فئة "الرياضية". هناك ونحن تخصيص "مبالغ" نقش واضغط على زر "موافق".
- يبدأ نافذة الحجج الحجج وظيفة. يقدم مشغل محدد لتلخيص البيانات في الخلايا التي نحن بحاجة إلى أداء في "الدفع عن طريق القرض الجسم" العمود. لديها بناء الجملة التالي:
= المبالغ (NUMBER1، NUMBER2؛ ...)
كوسائط، ما يشير إلى الخلايا التي ترد الأرقام. وضعنا المؤشر في حقل "NUMBER1". ثم يعلقون على زر الماوس الأيسر واختر خليتين الأولى من العمود الاعتماد الجسم على الورقة. في هذا المجال، كما نرى، رابط لمجموعة ظهر. وهو يتألف من جزأين مفصولة القولون: ما يشير إلى النطاق الأول للمجموعة وعلى آخر. من أجل أن تكون قادرة على نسخ الصيغة المحددة في المستقبل من خلال علامة التعبئة، يمكننا أن نجعل من الحلقة الأولى إلى مجموعة المطلق. نسلط الضوء عليها والضغط على مفتاح وظيفي F4. الجزء الثاني من إشارة والإجازات المتعلقة. الآن، عند استخدام علامة التعبئة، سيتم تحديد النطاق الأول للمجموعة، وهذا الأخير سوف تمتد وهو يتحرك إلى أسفل. وهذا أمر ضروري بالنسبة لنا لتحقيق الأهداف. بعد ذلك، انقر فوق الزر "موافق".
- لذلك، يتم الإعفاء نتيجة للتوازن الائتمان والديون بعد الشهر الثاني في الخلية. الآن، بدءا من هذه الخلية، التي نتخذها نسخ الصيغة إلى عناصر العمود فارغة باستخدام علامة التعبئة.
- تم إجراء حساب شهري من بقايا الدفع على القرض لفترة الائتمان بأكملها. كما ينبغي أن يكون، في نهاية الموعد النهائي، هذا المبلغ صفر.
وبالتالي، لم نقم ببساطة بحساب دفع القرض، لكننا نظمت نوعا من حاسبة الائتمان. والتي سوف تعمل على مخطط الأقساط. إذا كان في الجدول المصدر، على سبيل المثال، تغيير مبلغ القرض وسعر الفائدة السنوي، ثم في الجدول النهائي سيكون هناك إعادة حساب البيانات التلقائية. لذلك، يمكن استخدامها ليس فقط مرة واحدة فقط لحالة معينة، ولكن للتطبيق في مختلف المواقف لحساب خيارات الائتمان على مخطط الأقساط.
درس: الوظائف المالية في Excel
كما ترون، باستخدام برنامج Excel في المنزل، يمكنك بسهولة حساب دفعة القروض الشهرية الشاملة على مخطط الأقساط، باستخدام مشغل PL لهذه الأغراض. بالإضافة إلى ذلك، بمساعدة وظائف OSR و PRT، من الممكن حساب مقدار المدفوعات من قبل هيئة القرض والنسب المئوية للفترة المحددة. تطبيق كل هذه الأمتعة وظائف الأمتعة معا، من الممكن إنشاء آلة حاسبة ائتمانية قوية يمكن استخدامها أكثر من مرة لحساب الدفع الأقساط.