جدول المحتويات
- فهم الرهن العقاري الخاص بك
- حساب الدفع الشهري
- حساب سعر الفائدة السنوي
- تحديد طول القرض
- تحلل القرض
- حساب القرض في إكسل
- إطفاء القرض
- إنشاء جدول القرض
إن سداد القرض هو عملية سداد الأموال التي سبق اقتراضها من أحد المقرضين ، وعادةً من خلال سلسلة من الدفعات الدورية التي تشمل الفائدة الرئيسية زائد. هل تعلم أنه يمكنك استخدام برنامج Excel لحساب سداد القرض الخاص بك؟
هذه المقالة هي دليل خطوة بخطوة لإعداد حسابات القروض.
الماخذ الرئيسية
- استخدم برنامج Excel للحصول على رهن عقاري من خلال تحديد دفعتك الشهرية ، ومعدل الفائدة الخاص بك ، والجدول الزمني للقرض الخاص بك. يمكنك إلقاء نظرة أكثر تفصيلاً على تفاصيل القرض مع التفوق وإنشاء جدول سداد مناسب لك.هناك حسابات متوفرة لكل خطوة يمكنك تعديلها لتلبية احتياجاتك المحددة. إن تفحص ودراسة قرضك خطوة بخطوة يمكن أن يجعل عملية السداد أقل إحباطًا وأكثر قابلية للإدارة.
فهم الرهن العقاري الخاص بك
باستخدام Excel ، يمكنك الحصول على فهم أفضل للرهن العقاري الخاص بك في ثلاث خطوات بسيطة. الخطوة الأولى تحدد الدفع الشهري. الخطوة الثانية تحسب سعر الفائدة ، والخطوة الثالثة تحدد جدول القرض.
يمكنك إنشاء جدول في Excel يخبرك بسعر الفائدة وحساب القرض طوال مدة القرض وتحلل القرض والإطفاء والدفع الشهري.
حساب الدفع الشهري
أولاً ، إليك كيفية حساب الدفعة الشهرية للرهن العقاري. باستخدام معدل الفائدة السنوي والمدير والمدة ، يمكننا تحديد المبلغ الواجب سداده شهريًا.
الصيغة ، كما هو موضح في الصورة أعلاه ، مكتوبة كما يلي:
= -PMT (معدل؛ طول. القيمة_الحالية ؛؛)
علامة الطرح أمام PMT ضرورية لأن الصيغة تُرجع رقماً سالباً. الوسائط الثلاث الأولى هي معدل القرض وطول القرض (عدد الفترات) والأصل المقترض. الوسيطتان الأخيرتان اختيارية ، القيمة الافتراضية المتبقية إلى صفر ؛ الدفع مقدما (لأحد) أو في النهاية (مقابل الصفر) ، هو أيضا اختياري.
صيغة Excel المستخدمة لحساب الدفع الشهري للقرض هي:
= -PMT ((1 + B2) ^ (1/12) -1 ؛ B4 * 12 ؛ B3) = PMT ((1 + 3،10٪) ^ (1/12) -1 ؛ 10 * 12 ؛ 120000)
Explanation: بالنسبة للسعر ، نستخدم السعر الشهري (فترة السعر) ، ثم نقوم بحساب عدد الفترات (120 لمدة 10 سنوات مضروبة في 12 شهرًا) ، وأخيرا ، نشير إلى المبلغ الرئيسي المقترض. سيكون الدفع الشهري لدينا $ 1،161.88 على مدى 10 سنوات.
حساب سعر الفائدة السنوي
لقد رأينا كيفية إعداد حساب الدفع الشهري لرهن عقاري. لكن قد نرغب في تعيين الحد الأقصى للدفع الشهري الذي يمكننا تحمله والذي يعرض أيضًا عدد السنوات التي يتعين علينا خلالها سداد القرض. لهذا السبب ، نود أن نعرف سعر الفائدة السنوي المقابل.
كما هو موضح في لقطة الشاشة أعلاه ، نحسب أولاً معدل الفترة (شهريًا ، في حالتنا) ، ثم السعر السنوي. ستكون الصيغة المستخدمة هي RATE ، كما هو موضح في لقطة الشاشة أعلاه. هو مكتوب على النحو التالي:
= RATE (NPER، PMT، القيمة_الحالية ؛؛)
الحجج الثلاث الأولى هي طول القرض (عدد الفترات) ، الدفع الشهري لسداد القرض ، والرئيس المقترض. الوسائط الثلاث الأخيرة اختيارية ، والقيمة الافتراضية المتبقية إلى صفر ؛ مصطلح وسيطة لإدارة الاستحقاق مقدما (لأحد) أو في النهاية (للصفر) هو أيضا اختياري. أخيرًا ، تكون وسيطة التقدير اختيارية ولكن يمكنها تقديم تقدير مبدئي للمعدل.
صيغة Excel المستخدمة لحساب معدل الإقراض هي:
= معدل (12 * B4 ؛ -B2 ؛ B3) = معدل (12 * 13 ؛ -960 ؛ 120000)
ملاحظة: يجب أن تُعطى البيانات المقابلة في الدفع الشهري علامة سلبية. هذا هو السبب في وجود علامة الطرح قبل الصيغة. فترة معدل 0.294 ٪.
نستخدم المعادلة = (1 + B5) هي 12-1 ^ = (1 + 0.294٪) ^ 12-1 للحصول على المعدل السنوي لقرضنا ، وهو 3.58٪. بمعنى آخر ، لاقتراض 120،000 دولار على مدى 13 عامًا لدفع 960 دولارًا شهريًا ، يجب أن نتفاوض على قرض بمعدل سنوي أقصى قدره 3.58٪.
يعد استخدام Excel طريقة رائعة لتتبع ما تدين به والتوصل إلى جدول زمني للسداد يقلل من أي رسوم قد تصل إليها نتيجة لذلك.
تحديد طول القرض
سنرى الآن كيفية تحديد طول القرض عندما تعرف المعدل السنوي ، والمبلغ المقترض ، والدفع الشهري الذي يتعين سداده. بمعنى آخر ، كم من الوقت سوف نحتاج إلى سداد رهن عقاري بقيمة 120،000 دولار بمعدل 3.10٪ ودفع شهري قدره 1100 دولار؟
الصيغة التي سنستخدمها هي NPER ، كما هو موضح في الصورة أعلاه ، وتتم كتابتها على النحو التالي:
= NPER (معدل، PMT، القيمة_الحالية ؛؛)
الحجج الثلاث الأولى هي السعر السنوي للقرض ، الدفع الشهري اللازم لسداد القرض ، والرئيس المقترض. الوسيطتان الأخيرتان اختياريتان ، القيمة الافتراضية المتبقية إلى صفر. مصطلح وسيطة الدفع مقدما (لأحد) أو في النهاية (لصفر) هو أيضا اختياري.
= NPER ((1 + B2) ^ (1/12) -1 ؛ -B4 ؛ B3) = NPER ((1 + 3،10٪) ^ (1/12) -1 ؛ -1100 ؛ 120000)
ملاحظة: يجب أن تُعطى البيانات المقابلة في الدفع الشهري علامة سلبية. هذا هو السبب لدينا علامة ناقص قبل الصيغة. يبلغ طول فترة السداد 127.97 (أشهر في حالتنا).
سنستخدم المعادلة = B5 / 12 = 127.97 / 12 لعدد السنوات لاستكمال سداد القرض. وبعبارة أخرى ، لاقتراض 120.000 دولار ، بمعدل سنوي قدره 3.10 ٪ ودفع 1100 دولار شهريًا ، يجب علينا سداد آجال الاستحقاق لمدة 128 شهرًا أو 10 سنوات و 8 أشهر.
تحلل القرض
يتكون قرض الدفع من أصل والفائدة. يتم احتساب الفائدة لكل فترة - على سبيل المثال ، ستمنحنا الدفعات الشهرية التي تزيد عن 10 سنوات 120 فترة.
يوضح الجدول أعلاه تفصيل القرض (إجمالي المدة يساوي 120) باستخدام صيغتي PPMT و IPMT. وسيطات الصيغتين متشابهة ويتم تقسيمها على النحو التالي:
= -PPMT (معدل، num_period؛ طول. الرئيسي ؛؛)
الحجج هي نفسها بالنسبة لمعادلة PMT التي تمت مشاهدتها بالفعل ، باستثناء "num_period" ، والتي تتم إضافتها لإظهار الفترة التي يتم خلالها تحطيم القرض نظرًا لرأس المال والفائدة. إليك مثال:
= -PPMT ((1 + B2) ^ (1/12) -1 ؛ 1 ؛ B4 * 12 ؛ B3) = PPMT ((1 + 3،10٪) ^ (1/12) -1 ؛ 1 ؛ 10 * 12؛ 120000)
تظهر النتيجة في لقطة الشاشة أعلاه "تحلل القرض" خلال الفترة التي تم تحليلها ، وهي "واحدة ؛" وهذا هو ، الفترة الأولى أو الشهر الأول. نحن ندفع 1،161.88 $ مقسمة إلى 856.20 دولار الرئيسية وفائدة 305.68 دولار.
حساب القرض في إكسل
من الممكن أيضًا حساب أصل الدين وسداد الفوائد لعدة فترات مثل أول 12 شهرًا أو أول 15 شهرًا.
= -CUMPRINC (معدل؛ طول، الرئيسي، تاريخ_البدء. END_DATE، نوع)
نجد الوسيطات والمعدل والطول والرئيسية والمصطلح (وهي إلزامية) التي رأيناها بالفعل في الجزء الأول بصيغة PMT. ولكن هنا ، نحتاج إلى وسيطات "start_date" و "end_date" أيضًا. يشير "start_date" إلى بداية الفترة المراد تحليلها ، ويشير "end_date" إلى نهاية الفترة المراد تحليلها.
إليك مثال:
= -CUMPRINC ((1 + B2) ^ (1/12) -1، B4 * 12؛ B3، 1، 12؛ 0)
تظهر النتيجة في لقطة شاشة "Cumul 1st year" ، وبالتالي فإن الفترات التي تم تحليلها تتراوح من سنة إلى 12 من الفترة الأولى (الشهر الأول) إلى الثاني عشر (الشهر الثاني عشر). على مدى عام ، سندفع 1019195 دولار أمريكي في رأس المال و 3522.99 دولار في الفائدة.
إطفاء القرض
تسمح لنا الصيغ السابقة بإنشاء فترة جدولنا الزمني حسب الفترة ، ومعرفة المبلغ الذي سندفعه شهريًا في رأس المال والفائدة ، ومعرفة المبلغ المتبقي للدفع.
إنشاء جدول القرض
لإنشاء جدول قرض ، سنستخدم الصيغ المختلفة التي تمت مناقشتها أعلاه ونوسعها خلال عدد الفترات.
في عمود الفترة الأولى ، أدخل "1" كالفترة الأولى ثم اسحب الخلية لأسفل. في حالتنا ، نحتاج إلى 120 فترة منذ دفع القرض لمدة 10 سنوات مضروبًا في 12 شهرًا يساوي 120.
العمود الثاني هو المبلغ الشهري الذي نحتاج إلى دفعه كل شهر - وهو ثابت على جدول القرض بأكمله. لحساب المبلغ ، أدخل الصيغة التالية في خلية الفترة الأولى لدينا:
= -PMT (TP-1 ؛ B4 * 12 ؛ B3) = -PMT ((1 + 3،10٪) ^ (1/12) -1 ؛ 10 * 12 ؛ 120000)
العمود الثالث هو الأساس الذي سيتم سداده شهريًا. على سبيل المثال ، خلال الفترة الأربعين ، سنقوم بسداد مبلغ 945.51 دولارًا أمريكيًا على أساس إجمالي المبلغ الشهري البالغ 1،161.88 دولارًا.
لحساب المبلغ الرئيسي المسترد ، نستخدم الصيغة التالية:
= -PPMT (TP ؛ A18 ؛ $ B $ 4 * 12 ؛ $ B $ 3) = -PPMT ((1 + 3،10٪) ^ (1/12) ؛ 1 ؛ 10 * 12 ؛ 120000)
العمود الرابع هو الفائدة ، التي نستخدم فيها الصيغة لحساب أصل المبلغ على المبلغ الشهري لاكتشاف مقدار الفائدة الواجب دفعها:
= -INTPER (TP ؛ A18 ؛ $ B $ 4 * 12 ؛ $ B $ 3) = -INTPER ((1 + 3،10٪) ^ (1/12) ؛ 1 ؛ 10 * 12 ؛ 120000)
يحتوي العمود الخامس على المبلغ المتبقي للدفع. على سبيل المثال ، بعد الدفعة الأربعين ، يتعين علينا دفع 83،994.69 دولارًا على 120،000 دولار.
الصيغة هي كما يلي:
= $ B $ 3 + CUMPRINC (TP؛ $ B $ 4 * 12؛ $ B $ 3؛ 1؛ A18، 0)
تستخدم الصيغة مزيجًا من أصل أقل من فترة ما قبل الخلية التي تحتوي على الأصل المقترض. تبدأ هذه الفترة في التغيير عندما نسحب الخلية وسحبها لأسفل. يوضح الجدول أدناه أنه في نهاية 120 فترة يتم سداد قرضنا.
