هل تعلم أنه يمكنك استخدام إكسيل لحساب تسديد القرض الخاص بك؟ سترشدك هذه المقالة من خلال جميع الخطوات اللازمة للقيام بذلك. الآلات الحاسبة الرهن العقاري: كيف تعمل .

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

حساب القرض للإيجار الشهري

أولا، دعونا نرى كيفية تنفيذ حساب الدفع الشهري للرهن العقاري. وبعبارة أخرى، باستخدام معدل إينتيريست السنوي، مدير والمدة، يمكننا تحديد المبلغ الذي سيتم سداده شهريا.

الصيغة، كما هو موضح في الصورة أعلاه، مكتوبة على النحو التالي:

= - بمت (ريت؛ لينغث؛ present_value؛ [future_value]؛ [تايب])

علامة ناقص أمام بمت ضرورية، حيث ترجع الصيغة رقما سالبا. والحجج الثلاثة الأولى هي معدل القرض وطول القرض (عدد الفترات) والمقترض الرئيسي. الوسيتان الأخيرتان اختياريتان، القيمة الافتراضية المتبقية إلى 0، تدفع مقدما (لل 1) أو في النهاية (لل 0)، هي اختيارية أيضا.

- <->

صيغة إكسيل المستخدمة لحساب القسط الشهري للقرض هي:

= - بمت ((1 + B2) ^ (1/12) -1؛ B4 * 12؛ B3) = بمت ((1 + 3، 10٪) ^ (1/12) -1؛ 10 * 12؛ 120000)

إكسلاناتيون: بالنسبة للمعدل نستخدم فترة المعدل، وهو المعدل الشهري، عدد الفترات (أشهر هنا 120 لمدة 10 سنوات مضروبة في 12 شهرا) وأخيرا نشير إلى أصل المقترض. سيكون لدينا الدفع الشهري $ 1، 161. 88 أكثر من 10 سنوات.

حساب الرهن العقاري لأسعار الفائدة

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

حساب الفائدة على القرض

كما هو مبين في الصورة أعلاه، نقوم أولا بحساب معدل الفترة (شهريا في حالتنا)، ثم المعدل السنوي. سوف تكون الصيغة المستخدمة ريت كما هو موضح في الصورة أعلاه مكتوبة على النحو التالي:

= ريت (نبر؛ بمت؛ present_value؛ [future_value]؛ [تايب])

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

صيغة إكسيل المستخدمة لحساب سعر الإقراض هي:

= ريت (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٪ الحد الأقصى.

حساب الرهن العقاري على طول القرض

سنرى الآن كيفية الحصول على طول القرض عندما تعرف المعدل السنوي، والمقترض الرئيسي والمبلغ الشهري الذي سيتم سداده. وبعبارة أخرى، كم من الوقت سوف نحتاج لسداد رهن عقاري بقيمة 120 ألف دولار بمعدل 3. 10٪ ودفع شهري قدره $ 1، 100؟

عدد التسديدات للقرض

الصيغة التي سوف نستخدمها هي نبر، كما هو مبين في الصورة أعلاه، وهي مكتوبة على النحو التالي:

= نبر (ريت؛ بمت؛ present_value؛ [future_value]؛ [تايب])

أول ثلاث حجج هي المعدل السنوي للقرض، والمبلغ الشهري المطلوب لسداد القرض، والمبلغ الرئيسي المقترض. إن الوسيتين الأخيرتين اختياريتين، القيمة الافتراضية المتبقية إلى 0، الوسيطة المستحقة الدفع مقدما (لل 1) أو في النهاية (لل 0) اختيارية أيضا.

= نبر ((1 + B2) ^ (1/12) -1؛ -B4؛ B3) = نبر ((1 + 3، 10٪) ^ (1/12) -1؛ -1100؛ 120000)

ملاحظة: يجب أن تعطى البيانات المقابلة في الدفعة الشهرية علامة سلبية. هذا هو السبب في أن لدينا علامة ناقص قبل الصيغة. ويبلغ طول الاسترداد 127. 97 فترة (أشهر في حالتنا).

سوف نستخدم الصيغة = B5 / 12 = 127. 97/12 لعدد السنوات لاستكمال سداد القرض. وبعبارة أخرى، لاقتراض 120،000 $، بمعدل سنوي قدره 10٪ ودفع $ 1، 100 شهريا، يجب علينا سداد آجال الاستحقاق لمدة 128 شهرا أو 10 سنوات و 8 أشهر.

تحلل القرض

ويتكون دفع القرض من أمرين، أصل الدين والفائدة. يتم احتساب الفائدة لكل فترة، على سبيل المثال التسديدات الشهرية على مدى 10 سنوات، سوف تعطينا 120 فترات.

تظهر الصورة أعلاه تفاصيل القرض (فترة إجمالية تساوي 120)، باستخدام صيغ بمت و إبت. حجج الصيغتين هي نفسها ويتم تقسيمها كما يلي:

= - بمت (ريت؛ num_period؛ لينغث؛ برينسيبال؛ [ريسيدوال]؛ [تيرم])

= - إنتبر (ريت؛ num_period؛ [999]> الحجج هي نفسها كما في صيغة بمت ينظر في الجزء الأول، باستثناء num_period الذي يضاف لإظهار الفترة التي لكسر القرض، وإعطاء والمدير والفائدة بالنسبة له. لنأخذ مثالا على ذلك:

= - بمت ((1 + B2) ^ (1/12) -1؛ 1؛ B4 * 12؛ B3) = بمت ((1 + 3، 10٪) ^ ) -1؛ 1؛ 10 * 12؛ 120000)

= - إنتبر ((1 + B2) ^ (1/12) -1؛ 1؛ B4 * 12؛ B3) = إنتبر ((1 + 3، 10 ٪) ^ (1/12) -1؛ 1؛ 10 * 12؛ 120000)

والنتيجة هي واحدة تظهر في لقطة "تحلل القروض"، خلال الفترة التي تم تحليلها والتي هي "1"، وبالتالي فإن الفترة الأولى ، أو الشهر الأول.لهذا واحد، ونحن ندفع 1161 $. 88، مقسمة إلى 856 دولارا، و 20 أصلا و 305 دولارات. 68 الفائدة.

إكسيل لوان كومبوتاتيون

الآن من الممكن أيضا حساب سداد أصل الدين والفائدة لعدة فترات، مثل الأشهر ال 12 الأولى أو الأشهر ال 15 الأولى.

= - كومبرينك (ريت؛ لينغث؛ برينسيبال؛ start_date؛ end_date؛ تايب)

= - كوميبمت (ريت؛ لينغث؛ برينسيبال؛ start_date؛ end_date؛ تايب)

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

= - كومبرينك ((1 + B2) ^ (1/12) -1؛ B4 * 12؛ B3؛ 1؛ 12؛ 0)

= - كومبرينك ((1 + 3، 10٪) ^ (1/12) -1؛ 10 * 12؛ 120000؛ 1؛ 12؛ 0)

= - كوميبمت ((1 + B2) ^ (1/12) -1؛ B4 * 12؛ B3 ؛ 1؛ 12؛ 0)

= - كوميبمت ((1 + 3، 10٪) ^ (1/12) -1؛ 10 * 12؛ 120000؛ 1؛ 12؛ 0)

واحد يظهر في لقطة "كومول 1st السنة"، وبالتالي فإن فترات تحليل تتراوح من 1 إلى 12، من الفترة الأولى (الشهر الأول) إلى الثاني عشر (الشهر ال 12). على مدى عام، سوف ندفع 10 419 $، 55 الرئيسي و 3 522 $ 99 الفائدة.

إطفاء القرض

الصيغ السابقة تسمح لنا بإنشاء فترة الجدول الزمني لدينا حسب الفترة، وكم سندفع شهريا في أصل الدين والفوائد، وكم تبقى لدفع.

إنشاء جدول قروض في إكسيل

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

في العمود الفترة الأولى، ببساطة أدخل "1" كأول فترة، ثم اسحب الخلية إلى أسفل. في حالتنا، نحن بحاجة إلى 120 فترة منذ دفع القرض لمدة 10 سنوات مضروبا في 12 شهرا = 120.

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

= - بمت (تب-1؛ B4 * 12؛ B3) = -PMT ((1 + 3، 10٪) ^ (1/12 ) -1؛ 10 * 12؛ 120000)

العمود الثالث هو الرئيسي الذي سيتم سداده شهريا. على سبيل المثال، في الفترة الأربعين، سنقوم بسداد 945 دولارا. 51 - من حيث المبدأ على المبلغ الإجمالي الشهري البالغ $ 1، 161. 88 - ولحساب المبلغ الأصلي المسترد نستخدم الصيغة التالية:

= - بمت (تب؛ A18؛ $ B $ 4 * 12؛ $ B $ 3) = -PPMT ((1 + 3، 10٪) ^ (1/12)؛ 1؛ 10 * 12؛ 120000)

العمود الرابع هو الفائدة، والتي نقوم بحساب المبلغ الرئيسي على المبلغ الشهري لدينا لاكتشاف كيف (= 1، 3، 10٪) ^ (1/12) = = 999 = = - إنتبر (تب؛ A18؛ $ B $ 4 * 12؛ $ B $ 3) = -INTPER ؛ 1؛ 10 * 12؛ 120000)

العمود الخامس يحتوي على المبلغ المتبقي لدفع. على سبيل المثال، بعد الدفع ال 40 سيكون علينا أن ندفع $ 83، 994. 69 على $ 120، 000. الصيغة هي كما يلي:

= $ B $ 3 + كومبرينك (تب؛ $ B $ 4 * 12؛ $ B $ 3؛ 1؛ A18؛ 0)

= 120000 + كومبرينك ((1 + 3، 10٪) ^ (1/12)؛ 10 * 12؛ 120000؛ 1؛ 1؛ 0)

خلال فترة مقبلة مع الزنزانة التي تحتوي على أصل الدين المقترض. تبدأ هذه الفترة في التغيير عندما نقوم بنسخ واسحب الخلية إلى أسفل.تظهر الصورة أدناه أنه في نهاية 120 فترات يتم سداد القرض لدينا.