تابع IPMT در اکسل برای محاسبه سهم بهره از اقساط وام
این آموزش نحوه استفاده از تابع IPMT در اکسل را برای یافتن بخش بهره یک پرداخت دوره‌ای وام یا رهن نشان می‌دهد.
هر زمان که وامی می‌گیرید، چه وام مسکن، چه وام جعاله یا وام خودرو، باید مبلغ اولیه قرض گرفته شده و بهره آن را بازپرداخت کنید. به عبارت ساده، بهره هزینه استفاده از پول کسی (معمولاً بانک) است.
بخش بهره وام را می‌توان به صورت دستی با ضرب نرخ بهره دوره در مانده بدهی محاسبه کرد. اما مایکروسافت اکسل یک تابع ویژه برای این کار دارد – تابع IPMT. در این آموزش، به طور عمیق به توضیح نحوه آن و ارائه مثال‌های فرمولی در دنیای واقعی خواهیم پرداخت.
تابع IPMT در اکسل – نحو و کاربردهای اساسی
IPMT تابع پرداخت بهره اکسل است. این تابع، مبلغ بهره وام پرداختی را در یک دوره معین برمی‌گرداند، با فرض اینکه نرخ بهره و کل مبلغ پرداختی در تمام دوره‌ها ثابت باشد.
برای اینکه نام تابع را بهتر به خاطر بسپارید، توجه کنید که «I» مخفف «interest» و «PMT» مخفف «payment» است.
سینتکس تابع IPMT در اکسل به صورت زیر است:
که در آن:
- نرخ (الزامی) – نرخ بهره ثابت در هر دوره. می‌توانید آن را به صورت درصد یا عدد اعشاری ارائه دهید. برای مثال، اگر اقساط سالانه وامی با نرخ بهره سالانه ۶ درصد را پرداخت می‌کنید، از ۶٪ یا ۰.۰۶ برای rate استفاده کنید .اگر پرداخت‌های هفتگی، ماهانه یا فصلی انجام می‌دهید، نرخ سالانه را بر تعداد دوره‌های پرداخت در سال تقسیم کنید، همانطور که در این مثال نشان داده شده است . مثلاً اگر پرداخت‌های فصلی برای وامی با نرخ بهره سالانه ۶ درصد انجام می‌دهید، برای نرخ از ۶٪ تقسیم بر ۴ استفاده کنید.
- Per (الزامی) – دوره‌ای که می‌خواهید بهره آن را محاسبه کنید. این مقدار باید یک عدد صحیح در محدوده ۱ تا nper باشد.
- Nper (الزامی) – تعداد کل پرداخت‌ها در طول مدت وام.
- Pv (الزامی) – ارزش فعلی وام یا سرمایه‌گذاری. به عبارت دیگر، این اصل وام است، یعنی مبلغی که قرض گرفته‌اید.
- Fv (اختیاری) – ارزش آتی، یعنی مانده مورد نظر پس از آخرین پرداخت. در صورت حذف، به طور ضمنی صفر (0) در نظر گرفته می‌شود.
- نوع (اختیاری) – مشخص می‌کند که پرداخت‌ها چه زمانی باید انجام شوند:
- ۰ یا حذف شده – پرداخت‌ها در پایان هر دوره انجام می‌شود.
- ۱- پرداخت‌ها در ابتدای هر دوره انجام می‌شود.
برای مثال، اگر وامی به مبلغ 20000 دلار دریافت کرده‌اید که باید آن را به صورت اقساط سالانه طی ۳ سال آینده با نرخ بهره سالانه ۶٪ پرداخت کنید، بخش بهره پرداخت سال اول را می‌توان با این فرمول محاسبه کرد:
=IPMT(6%, 1, 3,200000)
به جای وارد کردن مستقیم اعداد در فرمول، می‌توانید آنها را در سلول‌های از پیش تعریف شده وارد کنید و مانند تصویر زیر به آن سلول‌ها ارجاع دهید.
مطابق با قرارداد علامت جریان نقدی، نتیجه به صورت یک عدد منفی برگردانده می‌شود زیرا شما این پول را پرداخت می‌کنید. به طور پیش‌فرض، همانطور که در قسمت چپ تصویر زیر نشان داده شده است، این عدد با رنگ قرمز برجسته شده و در پرانتز قرار گرفته است ( قالب ارزی برای اعداد منفی). در سمت راست، می‌توانید نتیجه همان فرمول را در قالب عمومی مشاهده کنید .
اگر ترجیح می‌دهید بهره را به صورت یک عدد مثبت دریافت کنید ، قبل از کل تابع IPMT یا آرگومان pv یک علامت منها قرار دهید :
=-IPMT(6%, 1, 3, 20000)
یا
=IPMT(6%, 1, 3, -20000)
مثال‌هایی از استفاده از فرمول IPMT در اکسل
حالا که اصول اولیه را می‌دانید، بیایید ببینیم چگونه از تابع IPMT برای یافتن میزان بهره برای دفعات مختلف پرداخت استفاده کنیم و چگونه تغییر شرایط وام، بهره بالقوه را تغییر می‌دهد.
قبل از اینکه وارد جزئیات شویم، باید توجه داشت که فرمول‌های IPMT بهتر است بعد از تابع PMT که مبلغ کل یک پرداخت دوره‌ای (بهره + اصل پول) را محاسبه می‌کند، استفاده شوند.
فرمول IPMT برای دوره‌های پرداخت مختلف (هفته، ماه، فصل)
برای بدست آوردن بخش بهره وام به درستی، همیشه باید نرخ بهره سالانه را به نرخ دوره مربوطه و تعداد سال‌ها را به تعداد کل دوره‌های پرداخت تبدیل کنید:
- برای بحث نرخ ، نرخ بهره سالانه را بر تعداد پرداخت‌ها در سال تقسیم کنید، با فرض اینکه دومی برابر با تعداد دوره‌های مرکب‌سازی در سال باشد.
- برای آرگومان nper ، تعداد سال‌ها را در تعداد پرداخت‌ها در هر سال ضرب کنید.
جدول زیر محاسبات را نشان می‌دهد:
| فراوانی پرداخت | استدلال نرخ | استدلال نپر |
| هفتگی | نرخ بهره سالانه / ۵۲ | سال * ۵۲ |
| ماهانه | نرخ بهره سالانه / ۱۲ | سال * ۱۲ |
| فصلنامه | نرخ بهره سالانه / ۴ | سال * ۴ |
| نیمسالانه | نرخ بهره سالانه / ۲ | سال * ۲ |
به عنوان مثال، بیایید میزان بهره‌ای را که باید برای یک وام مشابه اما در فواصل زمانی مختلف بپردازید، پیدا کنیم:
- نرخ بهره سالانه: ۶٪
- مدت وام: ۲ سال
- مبلغ وام: 20،000 دلار
- دوره: ۱
مانده حساب پس از آخرین پرداخت باید 0 دلار باشد ( آرگومان fv حذف شده است) و پرداخت‌ها باید در پایان هر دوره انجام شوند ( آرگومان type حذف شده است).
هفتگی :
=IPMT(6%/52, 1, 2*52, 20000)
ماهانه :
=IPMT(6%/12, 1, 2*12, 20000)
فصلنامه :
=IPMT(6%/4, 1, 2*4, 20000)
نیمسالانه :
=IPMT(6%/2, 1, 2*2, 20000)
با نگاهی به تصویر زیر، متوجه می‌شوید که مبلغ بهره با هر دوره بعدی کاهش می‌یابد. دلیل این امر این است که هر پرداختی به کاهش اصل وام کمک می‌کند و این باعث کاهش مانده‌ای می‌شود که بهره بر اساس آن محاسبه می‌شود.
همچنین، لطفاً توجه داشته باشید که کل مبلغ بهره قابل پرداخت برای یک وام یکسان برای اقساط سالانه، شش ماهه و سه ماهه متفاوت است:
شکل کامل تابع IPMT
در این مثال، ما قصد داریم بهره را برای وام یکسان، با تعداد دفعات پرداخت یکسان، اما انواع مختلف مستمری (عادی و مستمری معوق) محاسبه کنیم. برای این کار، باید از فرم کامل تابع IPMT استفاده کنیم.
برای شروع، بیایید سلول‌های ورودی را تعریف کنیم:
- B1 – نرخ بهره سالانه
- B2 – مدت وام به سال
- B3 – تعداد پرداخت‌ها در سال
- B4 – مبلغ وام ( pv )
- B5 – ارزش آتی ( fv )
- B6 – زمان سررسید پرداخت‌ها ( نوع ):
- ۰ – در پایان یک دوره (مستمری منظم)
- ۱ – در ابتدای دوره (پرداخت مستمری)
با فرض اینکه شماره دوره اول در A9 باشد، فرمول بهره ما به شرح زیر است:
=IPMT($B$1/$B$3, A9, $B$2*$B$3, $B$4, $B$5, $B$6)
نکته. اگر قصد دارید از فرمول IPMT برای بیش از یک دوره استفاده کنید، لطفاً به ارجاعات سلول توجه کنید. تمام ارجاعات به سلول‌های ورودی باید مطلق (با علامت دلار) باشند تا به آن سلول‌ها قفل شوند. آرگومان per باید یک ارجاع سلولی نسبی (بدون علامت دلار مانند A9) باشد زیرا باید بر اساس موقعیت نسبی ردیفی که فرمول در آن کپی می‌شود، تغییر کند.
بنابراین، فرمول بالا را در B9 وارد می‌کنیم، آن را برای دوره‌های باقیمانده به پایین می‌کشیم و نتیجه زیر را می‌گیریم. اگر اعداد ستون‌های بهره (مستمری منظم در سمت چپ و مستمری معوق در سمت راست) را مقایسه کنید، متوجه خواهید شد که هنگام پرداخت در ابتدای دوره، بهره کمی کمتر است.
تابع IPMT اکسل کار نمی‌کند
اگر فرمول IPMT شما خطایی ایجاد کند، به احتمال زیاد یکی از موارد زیر است:
- اگر آرگومان per خارج از محدوده ۱ تا nper باشد ، خطای #NUM! رخ می‌دهد.
- اگر هر یک از آرگومان‌ها غیر عددی باشد، خطای #VALUE! رخ می‌دهد.
