إنشاء جدول سداد قرض في Excel – دليل تدريجي خطوة بخطوة

يمكن أن تكون مهارة إنشاء جدول استهلاك القرض في Excel مفيدة للغاية، إذ تتيح لك تصور سدادات قرضك وإدارتها بكفاءة. يعرض جدول الاستهلاك كل دفعة دورية لقرضك (غالبًا رهن عقاري أو قرض سيارة)، ويفصّل كل دفعة إلى مكونَيْها: الفائدة والأصل، مع عرض الرصيد المتبقي بعد كل دفعة. دعونا نتعمق الآن في دليلٍ خطوة بخطوة لإنشاء هذا الجدول في Excel.

ما هو جدول الاستهلاك؟

أنشئ جدول استهلاك في Excel

أنشئ جدول استهلاك لعدد متغير من الفترات

أنشئ جدول استهلاك مع دفعات إضافية

أنشئ جدول استهلاك (مع دفعات إضافية) باستخدام قالب Excel

ما هو جدول الاستهلاك؟

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

لإنشاء جدول استهلاك القرض في Excel، تُعد الدوال المدمجة PMT وPPMT وIPMT ضرورية فعليًّا. دعونا نستعرض ما تقوم به كل دالة:

• دالة PMT: تُستخدم لحساب المبلغ الإجمالي المدفوع لكل فترة من فترات القرض، بناءً على مدفوعات ثابتة وسعر فائدة ثابت.
• دالة IPMT: تحسب هذه الدالة الجزء المخصص للفائدة من الدفعة خلال فترة زمنية معيّنة.
• دالة PPMT: تُستخدم لحساب الجزء المخصص لأصل القرض (الرَّأس مال) من الدفعة خلال فترة زمنية محددة.

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

أنشئ جدول استهلاك في Excel

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

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

⭐️ الخطوة 1: إعداد معلومات القرض وجداول الاستهلاك

1. أدخل معلومات القرض ذات الصلة، مثل سعر الفائدة السنوي، ومدة القرض بالسنوات، وعدد الدفعات سنويًا، ومبلغ القرض في الخلايا كما هو موضح في لقطة الشاشة التالية:
   
2. بعد ذلك، أنشئ جدول استهلاك في Excel مع العناوين التالية في الخلايا A7:E7: الفترة، الدفعة، الفائدة، الأصل، والرصيد المتبقي.
3. في عمود الفترة، أدخل أرقام الفترات. في هذا المثال، يبلغ إجمالي عدد الدفعات 24 شهرًا (سنتين)، لذا أدخل الأرقام من 1 إلى 24 في عمود الفترة. انظر لقطة الشاشة:
   
4. بمجرد إعداد الجدول بالعناوين وأرقام الفترات، يمكنك المتابعة بإدخال الصيغ والقيم في أعمدة الدفعة والفائدة والأصل والرصيد وفقًا لتفاصيل قرضك.

⭐️ الخطوة 2: حساب إجمالي مبلغ الدفعة باستخدام دالة PMT

صيغة دالة PMT هي:

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

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ الخطوة 3: حساب الفائدة باستخدام دالة IPMT

في هذه الخطوة، ستستخدم دالة IPMT في Excel لحساب الفائدة المُستحقة لكل فترة دفع.

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

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ الخطوة 4: حساب الأصل باستخدام دالة PPMT

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

صيغة دالة IPMT هي:

صيغة ومعاملات دالة PPMT مطابقة تمامًا لتلك المستخدمة في دالة IPMT التي ناقشناها سابقًا.

أدخل الصيغة التالية في الخلية D7، ثم اسحب مقبض التعبئة لأسفل العمود لملء الأصل لكل فترة. راجع لقطة الشاشة:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ الخطوة 5: حساب الرصيد المتبقي

بعد حساب الفائدة والأصل لكل دفعة، تأتي الخطوة التالية في جدول الاستهلاك: حساب الرصيد المتبقي للقرض بعد كل دفعة—وهو عنصرٌ جوهري يُظهر بوضوح كيف يتقلّص رصيد القرض تدريجيًّا مع مرور الوقت.

1. في الخلية الأولى من عمود الرصيد – E7، أدخل الصيغة التالية، والتي تعني أن الرصيد المتبقي سيكون مبلغ القرض الأصلي ناقص الجزء الخاص بالأصل من الدفعة الأولى:

   =B4-D7

   
2. بالنسبة للفترة الثانية وما يليها، احسب الرصيد المتبقي بطرح دفعة الأصل الحالية من رصيد الفترة السابقة. طبِّق الصيغة التالية في الخلية E8:

   =E7-D8

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

⭐️ الخطوة 6: إعداد ملخص القرض

بعد إعداد جدول الاستهلاك التفصيلي الخاص بك، يُتيح لك إنشاء ملخص للقرض نظرة سريعة على الجوانب الرئيسية لقرضك، ويتضمّن عادةً التكلفة الإجمالية للقرض وإجمالي الفائدة المدفوعة.

● لحساب إجمالي المدفوعات:

=SUM(B7:B30)

● لحساب إجمالي الفائدة:

=SUM(C7:C30)

⭐️ النتيجة:

الآن، تم إنشاء جدول استهلاك القرض بنجاح—بسيطٌ وشامل! راجع لقطة الشاشة التالية:

افتح سحر إكسل مع KUTOOLS AI

• التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية—كل ذلك بأوامر بسيطة.
• الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
• برمجة VBA: اكتب ونفّذ أكواد VBA بسلاسة!
• تفسير الصيغ: افهم الصيغ المعقدة بسهولةٍ تامة!
• ترجمة النصوص: اقتحم حواجز اللغة داخل جداولك الإلكترونية.

عزّز قدراتك في Excel بأدوات مدعومة بالذكاء الاصطناعي.حمّل الآنوجرّب الكفاءة كما لم تفعل من قبل!

أنشئ جدول استهلاك لعدد متغير من الفترات

في المثال السابق، أنشأنا جدول سداد قرض لعدد ثابت من الدفعات — وهي طريقة مثالية للتعامل مع قروض أو رهون عقارية ذات شروط ثابتة لا تتغيّر.

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

⭐️ الخطوة 1: إعداد معلومات القرض وجداول السداد

1. أدخل معلومات القرض ذات الصلة، مثل سعر الفائدة السنوي، ومدة القرض بالسنوات، وعدد الدفعات سنويًا، ومبلغ القرض في الخلايا كما هو موضح في لقطة الشاشة التالية:
   
2. بعد ذلك، أنشئ جدول استهلاك في Excel مع العناوين التالية في الخلايا A7:E7: الفترة، الدفعة، الفائدة، الأصل، والرصيد المتبقي.
3. في عمود «الفترة»، أدخل أكبر عدد من الدفعات التي قد تتخيلها لأي قرض—على سبيل المثال، املأ الأرقام من 1 إلى 360. ويكفي ذلك لتغطية قرض قياسي تمتد مدته 30 عامًا إذا كنت تقوم بسداد دفعات شهرية.
   

⭐️ الخطوة 2: تعديل صيغ الدفع والفوائد وأصل الدين باستخدام دالة IF

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

● صيغة الدفع:

عادةً ما تُستخدم دالة PMT لحساب الدفعة، ولدمجها مع دالة IF، تكون صيغة التركيب كما يلي:

لذا تكون الصيغة كالتالي:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● صيغة الفوائد:

صيغة التركيب هي:

لذا تكون الصيغة كالتالي:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● صيغة أصل الدين:

صيغة التركيب هي:

لذا تكون الصيغة كالتالي:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ الخطوة 3: تعديل صيغة الرصيد المتبقي

بالنسبة للرصيد المتبقي، يمكنك عادةً حسابه بطرح أصل الدين من الرصيد السابق. ومع استخدام دالة IF، يصبح الحساب كما يلي:

● خلية الرصيد الأولى: (E7)

=B4-D7

● خلية الرصيد الثانية: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ الخطوة 4: إنشاء ملخص القرض

بعد إعداد جدول السداد باستخدام الصيغ المعدَّلة، تأتي الخطوة التالية: إنشاء ملخّص للقرض.

● لحساب إجمالي المدفوعات:

=SUM(B7:B366)

● لحساب إجمالي الفائدة:

=SUM(C7:C366)

⭐️ النتيجة:

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

أنشئ جدول استهلاك مع دفعات إضافية

من خلال إجراء مدفوعات إضافية تتجاوز المدفوعات المجدولة، يمكنك سداد قرضك بشكل أسرع. ويُظهر جدول السداد الذي يتضمّن هذه المدفوعات الإضافية—عند إنشائه في Excel—كيف تُسهم هذه المبالغ في تسريع سداد القرض وتقليل إجمالي الفوائد المدفوعة. إليك كيفية إعداده:

⭐️ الخطوة 1: إعداد معلومات القرض وجداول السداد

1. أدخل معلومات القرض ذات الصلة، مثل سعر الفائدة السنوي، ومدة القرض بالسنوات، وعدد الدفعات سنويًا، ومبلغ القرض، والدفعة الإضافية في الخلايا كما هو موضح في لقطة الشاشة التالية:
   
2. بعد ذلك، احسب الدفعة المجدولة.
   بالإضافة إلى خلايا الإدخال، ستحتاج إلى خلية مُعرَّفة مسبقًا لحساباتك اللاحقة — وهي مبلغ الدفعة المجدولة. يمثّل هذا المبلغ الدفعة المنتظمة للقرض بافتراض عدم وجود أي دفعات إضافية. طبّق الصيغة التالية في الخلية B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. بعد ذلك، أنشئ جدول استهلاك في Excel:
   • عيّن التسميات المحددة، مثل الفترة، دفعة الجدول الزمني، الدفعة الإضافية، إجمالي الدفعة، الفائدة، رأس المال، الرصيد المتبقي في الخلايا A8:G8؛
   • في عمود «الفترة»، أدخل أعلى عدد من الدفعات التي قد تتخيلها لأي قرض. على سبيل المثال، املأ الأرقام من 0 إلى 360 — وهو ما يغطي قرضًا قياسيًّا مدته 30 عامًا في حال كنت تُجري دفعات شهرية.
   • للفترة 0 (الصف 9 في حالتنا)، استخدم الصيغة=B4 لاسترجاع قيمة الرصيد، والتي تمثل مبلغ القرض الأولي. ويجب ترك جميع الخلايا الأخرى في هذا الصف فارغة.
   • 

⭐️ الخطوة 2: إنشاء الصيغ لجدول السداد مع المدفوعات الإضافية

أدخل الصيغ التالية في الخلايا المقابلة واحدة تلو الأخرى. ولتحسين معالجة الأخطاء، سنُحيط هذه الصيغة وجميع الصيغ المستقبلية بدالة IFERROR، ما يساعد في تجنّب العديد من الأخطاء المحتملة الناتجة عن ترك خلايا الإدخال فارغة أو احتوائها على قيم غير صحيحة.

● حساب الدفع المجدول:

أدخل الصيغة التالية في الخلية B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● حساب الدفع الإضافي:

أدخل الصيغة التالية في الخلية C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● حساب إجمالي الدفع:

أدخل الصيغة التالية في الخلية D10:

=IFERROR(B10+C10, "")

● حساب أصل الدين:

أدخل الصيغة التالية في الخلية E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● حساب الفوائد:

أدخل الصيغة التالية في الخلية F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● حساب الرصيد المتبقي

أدخل الصيغة التالية في الخلية G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

بمجرد الانتهاء من إدخال جميع الصيغ، حدد نطاق الخلايا B10:G10، ثم استخدم مقبض التعبئة لسحبها وتمديدها عبر جميع فترات الدفع. وستعرض الخلايا الخاصة بأي فترات غير مستخدمة القيمة 0. راجع لقطة الشاشة:

⭐️ الخطوة 3: إنشاء ملخص القرض

● الحصول على عدد المدفوعات المجدولة:

=B2:B3

● الحصول على عدد المدفوعات الفعلية:

=COUNTIF(D10:D369,">"&0)

● الحصول على إجمالي المدفوعات الإضافية:

=SUM(C10:C369)

● الحصول على إجمالي الفوائد:

=SUM(F10:F369)

⭐️ النتيجة:

باتباع هذه الخطوات، تُنشئ جدول سداد ديناميكي في Excel يأخذ في الاعتبار المدفوعات الإضافية.

إنشاء جدول سداد باستخدام قالب Excel

يُعد إنشاء جدول سداد في Excel باستخدام قالب طريقةً مباشرة وفعّالة من حيث الوقت. يوفّر Excel قوالب مدمجة تحسب تلقائيًا الفوائد وأصل الدين والرصيد المتبقي لكل دفعة. إليك كيفية إنشاء جدول سداد باستخدام قالب Excel:

1. انقرملف > جديد، ثم اكتب في مربع البحثجدول استهلاك القرض، واضغط مفتاحEnter. بعد ذلك، اختر القالب الأنسب لاحتياجاتك بالنقر عليه. على سبيل المثال، سأختار هنا قالب آلة حاسبة القروض البسيطة. انظر لقطة الشاشة:
   
2. بمجرد اختيار قالب، انقر على زرإنشاءلفتحه كملف ورقة عمل جديدة.
3. بعد ذلك، أدخل تفاصيل قرضك الخاصة، وسيقوم القالب تلقائيًا بحساب الجدول وتعبئته بناءً على مدخلاتك.
4. أخيرًا، احفظ ملف جدول استهلاك القرض الجديد الخاص بك.