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

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

إنشاء جدول الاستهلاك في Excel

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

قم بإنشاء جدول استهلاك مع دفعات إضافية

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

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

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

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

• وظيفة PMT: يتم استخدام هذه الوظيفة لحساب إجمالي الدفعة لكل فترة لقرض على أساس دفعات ثابتة وسعر فائدة ثابت.
• وظيفة IPMT: تقوم هذه الوظيفة بحساب جزء الفائدة من الدفعة لفترة معينة.
• وظيفة PPMT: يتم استخدام هذه الوظيفة لحساب الجزء الرئيسي من الدفعة لفترة محددة.

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

إنشاء جدول الاستهلاك في Excel

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

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

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

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

⭐️ النتيجة:

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

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

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

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

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

⭐️ الخطوة 3: قم بعمل ملخص القرض

● احصل على العدد المقرر للدفعات:

=B2:B3

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

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

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

=SUM(C10:C369)

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

=SUM(F10:F369)

⭐️ النتيجة:

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

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

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

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