تابع IPMT در اکسل — محاسبه میزان بهره در هر قسط به‌صورت دقیق

تابع IPMT در اکسل ابزاری ساده و دقیق برای محاسبه مقدار بهره مربوط به یک قسط مشخص از وام یا سرمایه‌گذاری است. این تابع مخصوص زمانی است که می‌خواهیم بدانیم در دورهٔ nام چه مقدار از پرداخت ماهانه یا دوره‌ای صرف بهره شده و چه مقدار صرف بازپرداخت اصل بدهی می‌شود.

ساختار و پارامترهای تابع

نحو کلی تابع:

=IPMT(rate, per, nper, pv, [fv], [type])

توضیح پارامترها:

نکات مهم درباره ورودی‌ها و علامت‌ها

• اگر pv را مثبت وارد کنید، اکسل معمولاً نتایج پرداخت (PMT) را منفی برمی‌گرداند؛ برای نمایش ارقام مثبت معمولاً pv را منفی می‌زنند (مثلاً -200000).
• در پرداخت‌های ماهانه، نرخ سالیانه را بر 12 تقسیم و تعداد دوره‌ها را در 12 ضرب کنید.
• اگر type=1 انتخاب شود، پرداخت‌ها در ابتدای دوره صورت می‌گیرند و بهرهٔ دورهٔ اول ممکن است صفر یا کمتر شود؛ دقت کنید.

مثال عملی: وام 200,000 با نرخ سالیانه 5% و دورهٔ بازپرداخت 20 سال (ماهانه)

فرض کنیم: مبلغ وام 200,000، نرخ سالیانه 5%، دوره 20 سال با پرداخت‌های ماهانه.

=IPMT(5%/12, 1, 20*12, -200000)

این فرمول میزان بهرهٔ قسط اول را برمی‌گرداند. توجه کنید که pv را منفی وارد کردیم تا مقدار بهره به‌صورت مثبت نمایش داده شود.

برای محاسبهٔ قسط ماهیانه از تابع PMT استفاده می‌کنیم:

=PMT(5%/12, 20*12, -200000)

این فرمول مبلغ کل قسط ماهیانه را نشان می‌دهد که شامل بهره و بازپرداخت اصل است.

نمونه جدول استهلاک (سه ماه اول)

با استفاده از PMT، IPMT و PPMT می‌توانیم جدول استهلاک ایجاد کنیم. اعداد تقریباً به‌صورت زیر خواهند بود:

این اعداد نشان می‌دهد که با گذر زمان سهم بهره از قسط کاهش و سهم بازپرداخت اصل افزایش می‌یابد.

مثال‌های کاربردی و سناریوها

• محاسبه بهرهٔ یک قسط خاص برای گزارش‌دهی مالی یا تولید جدول بدهی.
• مقایسهٔ هزینهٔ بهره در بازپرداخت‌های با دوره‌های پرداخت متفاوت (ماهانه، فصلی، سالانه).
• تحلیل تاثیر پرادخت زودهنگام (prepayment) یا تغییر دورهٔ پرداخت با تغییر آرگومان type یا fv.

خطاهای معمول و راه‌حل‌ها

• #NUM!: معمولاً وقتی per خارج از بازه 1 تا nper باشد یا nper صفر باشد. شمارهٔ دوره را بررسی کنید.
• #DIV/0!: اگر نرخ نرخ برابر صفر و یا اشتباه وارد شده باشد و باعث تقسیم بر صفر شود.
• نتایج غیرمنتظره: احتمالاً علامت pv یا نوع پرداخت (type) اشتباه است—قواعد علامت‌گذاری را بررسی کنید.

تکنیک‌های پیشرفته و نکات کارشناسی

• برای وام‌هایی با نرخ متغیر می‌توانید از ستون‌های کمکی برای نرخ هر دوره استفاده کرده و به‌جای IPMT از محاسبات مرحله‌ای بهره بگیرید یا از توابع مالی در VBA بهره ببرید.
• برای محاسبه مجموع بهره در بازهٔ زمانی مشخص به‌جای جمع کردن IPMT‌های تک به تک، می‌توانید از تابع CUMIPMT استفاده کنید که مجموع بهره بین دو دوره را بازمی‌گرداند.
• دقت گرد کردن: در جدول‌های بلند، جمع قسط‌ها و جمع اصل و بهره ممکن است به‌علت گرد کردن اندکی اختلاف داشته باشد؛ برای گزارش نهایی از دقت دو رقم اعشار استفاده و ستون جمع بگیرید.

نمونه فرمول ترکیبی برای ساخت جدول در اکسل

=IPMT($B$1/12, A2, $B$2*12, -$B$3)

توضیح: فرض کنید در سلول B1 نرخ سالیانه، در B2 تعداد سال‌ها و در B3 مبلغ وام قرار دارد. ستون A شامل شمارهٔ دوره (1،2،3,…). این فرمول به‌صورت دینامیک بهرهٔ هر دوره را محاسبه می‌کند.

خلاصه و جمع‌بندی

تابع IPMT ابزار قدرتمندی برای تحلیل اثر بهره در هر قسط است و به‌خصوص برای تهیه جدول‌های استهلاک، تحلیل هزینهٔ کل بهره و تصمیم‌گیری درباره بازپرداخت زودهنگام مفید است. با رعایت قواعد ورودی‌ها (نرخ دوره‌ای، تعداد دوره‌ها، علامت pv و نوع پرداخت) می‌توانید نتایج دقیق و قابل اتکایی در اکسل به‌دست آورید.

اگر نیاز به نمونه فایل اکسل با جدول استهلاک آماده دارید، می‌توانم فرمت نمونه و فرمول‌ها را به‌صورت مرحله‌به‌مرحله برایتان آماده کنم.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال