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

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

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

PPMT(rate, per, nper, pv, [fv], [type])

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

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

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

مثال ۱: عملکرد تابع PPMT در اکسل

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

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

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

پنجره Function Arguments باز می‌شود. در کادر Rate کلیک کرده و B1/B3 (برای اینکه مبلغ هر قسط را در هر ماه به دست آورید.) را تایپ کنید. سپس در کادر Per کلیک کرده و سلول B2 را انتخاب کنید. حالا در کادر Nper کلیک کرده و سلول B3 را انتخاب کنید. سپس در کادر Pv کلیک کرده و سلول B4 را انتخاب کنید. در نهایت روی OK کلیک کنید.

PPMT(B1/B3,B2,B3,B4)

برای محاسبه مبلغ اصلی قسط دوم باید در سلول B2 عدد ۲ را وارد کنید تا ماه دوازدهم که باید عدد ۱۲ را وارد کنید که خوب کار زمان‌بری است. برای صرفه‌جویی در وقت خود از لیست‌های کشویی استفاده کنید.

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

می‌توانید با استفاده از لیست‌های کشویی کار را راحت‌تر کنید، تا با تغییر داده‌های اصلی مبلغ موردنظر برای شما به صورت خودکار محاسبه شود. ابتدا باید منابع داده‌ای موردنیاز برای قسمت Source لیست‌هایی کشویی را آماده کنیم. برای این کار شماره قسط‌ها را از ۱ تا ۱۲ در سلول‌های F2 تا F13 تایپ کنید. سپس در سلول K1 کلیک کنید.

خوب حالا در سلول k1 یک لیست کشویی ایجاد کنید. از سربرگ Data گزینه Data validation را انتخاب کنید. (آموزش ایجاد لیست‌های کشویی به طور کامل در آموزش تابع CHOOSE آمده است.)

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

خوب لیست کشویی شما آماده است.

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

PPMT(B1/B2,K1,B2,B3)

محاسبه سود هر قسط

خوب همان‌طور که در تصویر زیر می‌بینید من در یک فایل اکسل مبلغ اصلی هر قسط را در ستون G برای ماه‌های ۱ تا ۱۲ قرار داده‌ام (با استفاده از تابع PPMT مبلغ اصلی هر قسط را به دست آورده‌ام.) در سلول G14 با استفاده از تابع SUM جمع مبالغ را حساب کرده‌ام. می‌بینید که جمع مبالغ اصلی قسط‌ها برابر با مبلغ کل وام است. در ستون H مبلغ کل هر قسط را در ماه‌های ۱ تا ۱۲ قرار داده‌ام )با استفاده از تابع PMT مبلغ کل هر قسط را به دست آورده‌ام.) سپس با استفاده از تابع SUM جمع مبالغ ستون H را در سلول H14 محاسبه کرده‌ام.

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

سود هر قسط= مبلغ کل هر قسط -مبلغ اصلی قسط

همان‌طور که می‌بینید سود هر قسط هرچه به ماه‌های آخر نزدیک می‌شویم کمتر می‌شود. سپس مجموع سود اقساط را با استفاده از فرمول SUM به دست ‌آورده‌ام.

مجموع مبالغ اصلی قسط‌ها برابر مبلغ کل وام که ۱۰۰۰۰۰۰۰ است و مجموع سود اقساط برابر ۳۸۳۲۱۰ است.

آموزش تابع ppmt در اکسل

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

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

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

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

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

-PPMT(B1/B3,B2,B2*B3,B4)

یا

PPMT(B1/B3,B2,B2*B3,-B4)

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

1. به یاد داشته باشید که مبلغ اصلی قابل پرداخت  هر قسط یک عدد منفی است، زیرا این مبلغ از حساب بانکی شما کم می‌شود.
2. تابع PPMT مبلغ اصلی وام را برای شما محاسبه می‌کند.
3. همه آرگومان های تابع PPMT باید عددی باشند، در غیر این صورت خطای #VALUE! رخ می‌دهد.

جمع‌بندی

در این آموزش تابع PPMT در اکسل و برخی نکات مربوط به آن را گفتیم. در آموزش‌های بعدی سایر توابع مالی را توضیح خواهیم داد. پس با ما همراه باشید.