احتمالا برای شما هم پیش آمده که حداقل یکبار از بانک وام گرفته باشید. معمولا زمان گرفتن وام حس کنجکاوی و حسابگری، ذهن شما را درگیر این سوالات می‌کند که مبلغ هر قسط چقدر می‌شود؟ یا اینکه اگر نرخ سود کمتر یا بیشتر باشد، مبلغ هر قسط چقدر تغییر می‌کند؟! خب برای اینکار می‌توانید به راحتی از تابع PMT در اکسل استفاده کنید.  به کمک این تابع می‌توانید قبل از گرفتن وام، مبلغ دوره‌های مختلف پرداخت وام را با هم مقایسه کنید تا موردی را پیدا کنید که برای شما مناسب‌تر باشد. تابع PMT در دسته توابع مالی (Financial) قرار دارد و  مبلغ قسط را بر اساس نرخ بهره، تعداد اقساط و مبلغ وام محاسبه می‌کند. در این آموزش نحوه استفاده از تابع PMT در اکسل را توضیح می‌دهیم.

نکته: “PMT” مخفف “Payment” به معنی پرداخت است.

پیشنهاد مطالعه: تحلیل متغیرها با کمک Data Table

چند نکته برای عملکرد صحیح تابع PMT در اکسل

1. مبلغ قابل پرداخت یک عدد منفی است، زیرا این مبلغ از حساب بانکی شما کم می‌شود.
2. نتیجه تابع PMT بر اساس مبلغ اصلی وام و میزان بهره است و هیچ‌گونه هزینه، مالیات یا پرداختی را که ممکن است با وام همراه باشد، شامل نمی‌شود.
3. فرمول PMT در اکسل می‌تواند مبلغ پرداخت وام را برای حالت‌های مختلف پرداخت مانند هفتگی، ماهانه یا سالانه محاسبه کند.

ساختار تابع PMT در اکسل

ساختار تابع PMT به شکل زیر است:

PMT(rate, nper, pv, [fv], [type])

1.  Rate: نرخ بهره در هر دوره را در این قسمت وارد کنید. عدد مربوط به این قسمت می‌تواند به صورت درصد یا اعشاری باشد.
2. Nper: تعداد اقساط وام را در این قسمت وارد کنید.
3. Pv: مبلغ قابل پرداخت، یعنی کل مبلغ وام را در این قسمت وارد کنید.
4. Fv: مقدار مانده نقدی است که می‌خواهید بعد از آخرین پرداخت، پرداخت کنید. در صورت وارد نکردن مقداری در این قسمت، مقدار (۰) فرض می‌شود.
5. Type: زمان پرداخت وام را در این قسمت تعیین کنید.

• • اگر عدد ۰ را در این قسمت وارد کنید یا هیچ عددی وارد نکنید. پرداخت‌ در پایان هر دوره انجام می‌شود.
  • اگر عدد ۱ را وارد کنید، پرداخت در ابتدای هر دوره انجام می‌شود.

نکته: ۳ آرگومان اول ضروری و دو آرگومان آخر اختیاری هستند.

عملکرد تابع PMT در اکسل

فرض کنید شما متقاضی دریافت وام خودروی ۵ ساله با نرخ بهره ۷٪ و مبلغ وام ۱۰۰۰۰۰۰۰ هستید، فرمول PMT می‌تواند به شما بگوید مبلغ هر کدام از اقساط شما چقدر خواهد بود.  ابتدا داده‌های خود را در یک فایل اکسل وارد کنید.

یک سلول را به عنوان سلول نتیجه (D2) در نظر بگیرید. سپس در نوار فرمول نرم‌افزار روی Fx کلیک کنید. (یا اینکه کلیدهای Shift+F3 را فشار دهید.)

پنجره Inert Function باز می‌شود. از دسته توابع مالی (Financial) تابع PMT را انتخاب کنید و روی OK کلیک کنید.

پنجره Function Argumentes باز می‌شود. در کادر Rate کلیک کرده و سلول B3 را انتخاب کنید. سپس در کادر Nper کلیک کرده و سلول B2 را انتخاب کنید. حالا در کادر Pv کلیک کرده و سلول B1 را انتخاب کنید. در نهایت روی OK کلیک کنید.

فرمول PMT به شکل زیر است:

PMT(0.07, 5,10000000)

تابع pmt در اکسل

محاسبه مبلغ قابل پرداخت ماهانه وام

برای محاسبه مبلغ قابل پرداخت ماهانه برای همین وام، از این فرمول استفاده کنید:

PMT(0.07/12, 5*12, 10000000)

برای این کار باید عدد مربوط به آرگومان Rate (نرخ بهره)  را بر عدد ۱۲ (تعداد ماه‌های سال) تقسیم کنید و عدد مربوط به آرگومان Nper (تعداد اقساط وام) را در عدد ۱۲ (تعداد ماه‌های سال) ضرب کنید.

محاسبه مبلغ قابل پرداخت هفتگی وام

برای محاسبه مبلغ قابل پرداخت هفتگی برای همین وام از این فرمول استفاده کنید:

PMT(0.07/52, 5*52, 10000000)

نکته: عدد ۵۲ نشان‌دهنده تعداد هفته‌های یک سال است. برای همین عدد آرگومان Pv را بر ۵۲ تقسیم می‌کنیم و عدد آرگومان Nper را در ۵۲ ضرب می‌کنیم.

تغییر فرمت عدد در سلول نتیجه

به طور پیش فرض، اکسل نتیجه تابع PMT را با فرمت Currency با ۲ رقم اعشار، به رنگ قرمز و به صورت عدد مثبت و در پرانتز نشان می‌دهد. می‌توانید فرمت سلول نتیجه را تغییر دهید. برای این کار از سربرگ Home روی فلش کوچک موجود در قسمت پایین و سمت راست گروه Number کلیک کنید.

پنجره Format cells باز می‌شود. در سربرگ Number و از لیست کشویی Category گزینه Currency و در قسمت Decimal places عدد ۰ و از لیست کشویی Symbol گزینه Persian را انتخاب کنید و از قسمت Negative numbers رنگ قلم مشکی و عدد منفی را انتخاب کنید، تا نتیجه به صورت عدد منفی و با رنگ قلم مشکی و با واحد پولی ریال نمایش داده شود.

 در قسمت سمت راست تصویر زیر حالت پیش فرض و در سمت چپ تصویر زیر نتیجه مشابه را با فرمت موردنظر می‌بینید.

اگر مایل هستید مبلغ قابل پرداخت را به عنوان عدد مثبت مشاهده کنید، قبل از کل فرمول PMT یا قبل از آرگومان pv (مبلغ وام) یک علامت منفی بگذارید:

-PMT(B1 ,B2, B3)

یا

PMT(B1 ,B2 ,-B3)

استفاده از لیست‌های کشویی

می‌توانید با استفاده از لیست‌های کشویی مقداری کار را راحت‌تر کنید، تا با تغییر داده‌های اصلی مبلغ موردنظر برای شما به صورت خودکار محاسبه شود. ابتدا باید منابع داده‌ای موردنیاز برای قسمت Source لیست‌هایی کشویی را آماده کنیم. برای این کار در ۳ سلول از فایل اکسل (J1, J2,J3)  کلمات هفتگی و ماهانه و سالانه را تایپ کنید.

سپس روبروی آنها در سلول اول (K1) فرمول B2/52= را تایپ کنید. در سلول پایین تر فرمول (K2) B2/12= را تایپ کنید. در سلول پایین تر (K3) کلیک کرده و سلول B2 را انتخاب کنید. سپس در سلول‌های روبروی این ۳ مورد در سلول اول (L1) فرمول B3*52 را تایپ کنید. در سلول پایین‌تر (L2) فرمول B3*12 را تایپ کنید. در سلول پایین‌تر ((L3 کلیک کرده و سلول  B3را انتخاب کنید.

خوب حالا  در سلول G1 یک لیست کشویی شامل ۳ گزینه هفتگی، ماهانه، سالانه ایجاد کنید. یعنی از سربرگ Data گزینه Data validation را انتخاب کنید.

در پنجره Data validation از لیست کشویی Allow گزینه List و در قسمت Source سلول‌های j1 تا j3 را انتخاب کنید. در نهایت روی OK کلیک کنید.

سپس در سلول G2 یک لیست کشویی شامل سه گزینه ایجاد کنید. برای این کار همانند مرحله قبل عمل کنید، فقط در قسمت Source سلول‌های K1 تا k3 را انتخاب کنید.

 و در سلول G3 هم یک لیست کشویی ایجاد کنید. مانند مرحله قبل عمل کنید، فقط در قسمت Source سلول‌های L1 تا L3 را انتخاب کنید.

در نهایت در سلول G4 فرمول زیر را تایپ کنید:

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

چند نکته درباره تابع PMT در اکسل

• اگر آرگومان نرخ بهره عدد منفی باشد یا Nper برابر ۰ باشد، خطای #NUM رخ می‌دهد.
• اگر مقادیر یک یا چند آرگومان متن باشند، خطای #VALUE! رخ می‌دهد.

جمع‌بندی

در این آموزش تابع PMT و برخی نکات بر آن را توضیح دادیم. با استفاده از توابع IFERROR و VLOOKUP و PMT  می‌توان یک ماشین حساب در اکسل برای محاسبه مبلغ قابل پرداخت قسط وام‌ها ایجاد کرد که مربوط به مباحث پیشرفته‌تر اکسل است.