يمكننا استخدام صيغ Excel PMT و IPMT و IF لإنشاء جدول ديون. أولاً ، نحتاج إلى إنشاء النموذج عن طريق إدخال بعض افتراضات الديون. في هذا المثال ، نفترض أن الدين هو 5،000،000 دولار ، ومدة السداد هي 5 سنوات ، وسعر الفائدة سعر الفائدة. نسبة من رأس المال. أن تكون 4.5٪.
1. الرصيد الافتتاحي في جدول الديون الخاص بنا يساوي مبلغ القرض البالغ 5 ملايين دولار ، لذا في الخلية E29 ندخل = B25 لربطه بإدخال الافتراض. بعد ذلك ، يمكننا استخدام صيغة PMT لحساب إجمالي الدفعة للفترة الأولى = PMT ($ B $ 27 ، $ B $ 26 ، $ B $ 25) . تحسب الصيغة مبلغ الدفع باستخدام مبلغ القرض ومدة القرض وسعر الفائدة المذكور في قسم الافتراض.
2. في الخلية E28 ، أدخل الفترة التي نحن فيها ، وهي 1. في الخلية E29 ، أدخل = E28 + 1 واملأ الصيغة إلى اليمين. بعد ذلك ، استخدم صيغة IPMT لمعرفة مدفوعات الفائدة للفترة الأولى = IPMT ($ B $ 27 ، E28 ، $ B $ 26 ، $ B $ 25) .
3. الدفعة الأساسية هي الفرق بين إجمالي السداد ودفع الفائدة ، وهو = E30-E31 . الرصيد الختامي هو الرصيد الافتتاحي بالإضافة إلى الدفعة الأساسية التي يتم إجراؤها ، وهي = E29 + E32 . الرصيد الافتتاحي للفترة 2 هو الرصيد الختامي للفترة 1 ، وهو = E33 .
4. انسخ جميع الصيغ من الخلية E29 إلى E33 إلى العمود التالي ، ثم انسخ كل الصيغ إلى اليمين. تحقق مما إذا كان الرصيد الختامي للفترة 5 = 0 لضمان استخدام الصيغ والأرقام الصحيحة.
5. لاحظ أن هناك بعض رسائل الخطأ تبدأ من الفترة 6 لأن الرصيد الافتتاحي هو 0. هنا يمكننا استخدام وظيفة IF لتنظيف الأخطاء. في الخلية E30 ، اكتب = IF (E29> 0 ، PMT ($ B $ 27 ، $ B $ 26 ، $ B $ 25) ، 0) . تنص الصيغة على أنه إذا كان الرصيد الافتتاحي أقل من 0 ، فسيتم عرض قيمة الدفع الإجمالية على أنها 0.
6. في الخلية 31 ، اكتب = IF (E29> 0 ، IPMT ($ B $ 27 ، E28 ، $ B $ 26 ، $ B $ 25) ، 0) . تشبه هذه الصيغة الصيغة السابقة ، والتي تنص على أنه إذا كان الرصيد الافتتاحي أقل من 0 ، فسيتم عرض دفعة الفائدة على أنها 0.
7. انسخ الخلية E30 و E31 ، واضغط على SHIFT + السهم الأيمن ثم CTRL + R للتعبئة إلى اليمين. يجب أن ترى أن جميع رسائل الخطأ تظهر الآن على أنها 0.
XNPV و XIRR بوظائف DATE و IF
يمكننا حساب NPV و IRR بناءً على تواريخ محددة باستخدام وظائف Excel XNPV و XIRR مع دالتي DATE و IF.
8. انتقل إلى الخلية E6 وأدخل = DATE (E5،12،31) لعرض التاريخ. نسخ إلى اليمين. سترى خطأ #VALUE! رسالة بعد 2021. يمكننا إصلاح ذلك باستخدام الدالة IFERROR = IFERROR (DATE (E5،12،31)، "") .
9. الآن يمكننا البدء في حساب NPV و IRR. أولاً ، نحتاج إلى إدخال مبالغ التدفق النقدي الحر. نفترض أن مبالغ FCF من الفترة 1 إلى 5 هي -1000 ، 500 ، 600 ، 700 ، 900. في الخلية C37 ، سنقوم بإدخال معدل خصم 15٪. في الخلية B37 ، احسب NPV باستخدام صيغة XNPV = XNPV (C37، E35: I35، E6: I6) .
10. في الخلية B38 ، احسب IRR باستخدام صيغة XIRR = XIRR (E35: I35، E6: I6) .
إضافة OFFSET إلى XNPV و XIRR
يمكننا التغيير إلى صيغ XNPV و XIRR لعمل صيغ أكثر ديناميكية باستخدام وظيفة OFFSET.
11. في الخلية B42 ، قم بتغيير الصيغة إلى = XNPV (C42، E40: OFFSET (E40،0، $ F $ 3-1)، E6: I6) . تكون الصيغة أكثر ديناميكية لأنه إذا زاد عدد الفترات ، فستزيد فترات التدفق النقدي الحر أيضًا. لا نحتاج إلى تغيير صيغة NPV إذا كانت فترة التنبؤ أطول. بالنسبة لوظيفة IRR ، قم بتغييرها إلى = XIRR (E40: OFFSET (E40،0، $ F $ 3-1)، E6: I6) .
12. بعد تعديل صيغة عدد الفترات ، يجب أن نعوض التواريخ. في الخلية B42 ، قم بتغيير الصيغة إلى = XNPV (C42، E40: OFFSET (E40،0، $ F $ 3-1)، E6: OFFSET (E6،0، $ F $ 3-1)) . يسمح هذا لصيغتي NPV و IRR باختيار العدد الصحيح من التدفق النقدي الحر مع التغيير في عدد الفترات.
ملخص صيغ جدول الديون الرئيسية
- صيغة PMT لحساب مبلغ سداد الديون: = PMT (معدل الفائدة ، عدد الشروط ، القيمة الحالية)
- صيغة IPMT لحساب دفع الفائدة: = IPMT (معدل الفائدة ، الفترة ، عدد الشروط ، القيمة الحالية)
- صيغة XNPV لإيجاد صافي القيمة الحالية: = XNPV (معدل الخصم ، التدفقات النقدية الحرة ، التواريخ)
- صيغة XIRR لإيجاد معدل العائد الداخلي: = XIRR (التدفقات النقدية المجانية ، التواريخ)
- صيغة OFFSET لحساب NPV الديناميكي: = XNPV (معدل الخصم ، 1st FCF: OFFSET (1st FCF ، 0 ، # فترات - 1) ، التاريخ الأول: OFFSET (التاريخ الأول ، 0 ، # فترات - 1))
- صيغة OFFSET لحساب IRR الديناميكي: = XIRR (1st FCF: OFFSET (1st FCF، 0، # period - 1)، 1st date: OFFSET (1st date، 0، # period - 1))
مصادر أخرى
شكرًا لك على قراءة دليل Finance حول جدول الديون مع صيغ PMT و IPMT و IF. لمواصلة التعلم والتقدم في حياتك المهنية ، ستكون الموارد المالية التالية مفيدة:
- صيغ Excel الأساسية صيغ Excel الأساسية يعد إتقان صيغ Excel الأساسية أمرًا بالغ الأهمية للمبتدئين ليصبحوا بارعين في التحليل المالي. يعتبر Microsoft Excel قطعة برمجية قياسية في تحليل البيانات. يعد برنامج جداول بيانات Microsoft أيضًا أحد أكثر البرامج المفضلة من قبل المصرفيين الاستثماريين
- أفضل ممارسات النمذجة المالية أفضل ممارسات النمذجة المالية تهدف هذه المقالة إلى تزويد القراء بمعلومات حول أفضل ممارسات النمذجة المالية ودليل سهل المتابعة ، خطوة بخطوة لبناء نموذج مالي.
- قائمة وظائف Excel قائمة بأهم وظائف Excel للمحللين الماليين. تغطي ورقة الغش هذه مئات الوظائف التي تعتبر بالغة الأهمية لمعرفتها كمحلل Excel
- نظرة عامة على اختصارات Excel نظرة عامة على اختصارات Excel نظرة عامة على اختصارات Excel هي طريقة يتم التغاضي عنها لزيادة الإنتاجية والسرعة في Excel. تقدم اختصارات برنامج Excel للمحلل المالي أداة قوية. يمكن لهذه الاختصارات أداء العديد من الوظائف. بسيطة مثل التنقل داخل جدول البيانات لملء الصيغ أو تجميع البيانات.