إنشاء جدول استهلاك قرض في 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)

⭐️ النتيجة:

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

اكتشف سحر Excel مع 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، واستخدم مقبض التعبئة لسحب وتوسيع هذه الصيغ لأسفل عبر مجموعة فترات الدفع بالكامل. بالنسبة لأي فترات غير مستخدمة، ستظهر الخلايا 0s. انظر لقطة الشاشة:

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

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

=B2:B3

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

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

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

=SUM(C10:C369)

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

=SUM(F10:F369)

⭐️ النتيجة:

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

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

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

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