در یک نگاه
- تابع PMT مبلغ قسط مساوی را از روی مبلغ وام، نرخ سود سالانه و تعداد اقساط محاسبه می‌کند.
- سهم سود هر قسط با IPMT و سهم اصل با PPMT جدا میشود و جدول اقساط از همین دو ستون ساخته میشود.
- برای مقایسهٔ سه روش محاسبه بدون اکسل، ابزار آنلاین محاسبهٔ اقساط همین سایت قابل استفاده است.
پیش از دریافت وام یا ثبت آن در دفاتر، سه پرسش مطرح است: مبلغ هر قسط چقدر است، از هر قسط چه مقدار سود و چه مقدار اصل وام است، و جمع سود پرداختی در پایان دوره چقدر میشود. اکسل هر سه را با چند فرمول ساده پاسخ می‌دهد.
ورودی‌های محاسبه
مثال این مقاله وامی ۱۰۰ میلیون ریالی با نرخ سود سالانهٔ ۱۸ درصد و بازپرداخت ۱۲ ماهه است. ورودی‌ها در سه سلول جدا قرار می‌گیرند تا با تغییر هر کدام، همهٔ محاسبات به‌روز شود.
| سلول | عنوان | مقدار |
|---|---|---|
| B1 | مبلغ وام | ۱۰۰٬۰۰۰٬۰۰۰ |
| B2 | نرخ سود سالانه | ۱۸٪ |
| B3 | تعداد اقساط | ۱۲ |
گام ۱: مبلغ قسط با PMT
فرمول زیر در سلول B4 مبلغ قسط ماهانه را محاسبه می‌کند:
=ROUND(PMT(B2/12,B3,-B1),0)B2/12: نرخ سالانه به نرخ ماهانه تبدیل میشود، چون اقساط ماهانه است.-B1: علامت منفی برای مثبت شدن نتیجه است؛ PMT پرداخت را منفی برمی‌گرداند.ROUND(…,0): مبلغ به ریال کامل گرد میشود.
نتیجه برای مثال بالا ۹٬۱۶۷٬۹۹۹ ریال است.
گام ۲: جدول اقساط
در جدول اقساط، هر ردیف یک ماه است. سود هر ماه از روی ماندهٔ وام در ابتدای همان ماه محاسبه میشود و باقی قسط از اصل وام کم میشود.
| ستون | عنوان | فرمول ردیف اول (ردیف ۷) |
|---|---|---|
| A | شمارهٔ قسط | 1 |
| B | ماندهٔ ابتدای ماه | =$B$1 |
| C | سود | =ROUND(B7*$B$2/12,0) |
| D | اصل | =E7-C7 |
| E | مبلغ قسط | =$B$4 |
| F | ماندهٔ پایان ماه | =B7-D7 |
در ردیف دوم، سلول B8 برابر =F7 قرار میگیرد و سپس ردیف‌ها تا قسط دوازدهم کپی می‌شوند. ماندهٔ پایان ماه در آخرین ردیف باید صفر یا اختلاف چند ریالی ناشی از گرد کردن باشد.
| قسط | مانده | سود | اصل | مانده پس از قسط |
|---|---|---|---|---|
| ۱ | ۱۰۰٬۰۰۰٬۰۰۰ | ۱٬۵۰۰٬۰۰۰ | ۷٬۶۶۷٬۹۹۹ | ۹۲٬۳۳۲٬۰۰۱ |
| ۲ | ۹۲٬۳۳۲٬۰۰۱ | ۱٬۳۸۴٬۹۸۰ | ۷٬۷۸۳٬۰۱۹ | ۸۴٬۵۴۸٬۹۸۲ |
| ۱۲ | ۹٬۰۳۲٬۵۱۴ | ۱۳۵٬۴۸۸ | ۹٬۰۳۲٬۵۱۱ | ۳ |
سه روش رایج محاسبه
قرارداد هر وام روش محاسبهٔ اقساط را مشخص می‌کند. برای همان وام ۱۰۰ میلیون ریالی، سه روش رایج نتایج متفاوتی دارند:
| روش | مبلغ قسط | جمع سود | فرمول قسط |
|---|---|---|---|
| اقساط مساوی | ۹٬۱۶۷٬۹۹۹ در همهٔ ماه‌ها | ۱۰٬۰۱۵٬۹۸۸ | =PMT(B2/12,B3,-B1) |
| اصل ثابت | از ۹٬۸۳۳٬۳۳۳ تا ۸٬۴۵۸٬۳۳۳ | ۹٬۷۵۰٬۰۰۰ | =$B$1/$B$3+B7*$B$2/12 |
| سود ساده | ۹٬۱۴۵٬۸۳۳ در همهٔ ماه‌ها | ۹٬۷۵۰٬۰۰۰ | =B1*(1+B2*(B3+1)/24)/B3 |
- اقساط مساوی: رایج‌ترین روش؛ مبلغ قسط ثابت است و سهم سود در اقساط اول بیشتر است.
- اصل ثابت: سهم اصل در همهٔ اقساط یکسان است و سود روی ماندهٔ کاهشی محاسبه میشود؛ قسط اول بیشترین و قسط آخر کمترین مبلغ را دارد.
- سود ساده: کل سود با فرمول
(n+1)/24محاسبه و به‌طور مساوی میان اقساط تقسیم میشود.
نکته‌های کاربردی
- نرخ ماهانه: اگر قرارداد نرخ را ماهانه اعلام کرده باشد، تقسیم بر ۱۲ حذف میشود.
- کارمزد و بیمه: کارمزد یک‌جای ابتدای دوره در PMT وارد نمی‌شود؛ برای محاسبهٔ هزینهٔ واقعی وام، از مبلغ دریافتی کم میشود.
- حداکثر وام قابل دریافت: اگر سقف قسط ماهانه معلوم باشد، تابع
=PV(B2/12,B3,-قسط)حداکثر مبلغ وام را برمی‌گرداند. - تاریخ سررسید: تابع
EDATEتاریخ میلادی اقساط را می‌سازد. برای تاریخ شمسی اقساط، ابزار آنلاین محاسبهٔ اقساط جدول کامل را با تاریخ شمسی ارائه می‌دهد. - یادآوری سررسید: ثبت اقساط و یادآوری پیش از سررسید در اچ‌آر فایننس انجام میشود.
پرسش‌های پرتکرار
چرا نتیجهٔ تابع PMT منفی است؟
اکسل پرداخت را جریان خروجی وجه و منفی در نظر میگیرد. با منفی وارد کردن مبلغ وام (-B1) یا قرار دادن علامت منفی پیش از PMT، نتیجه مثبت میشود.
کدام روش جمع سود کمتری دارد؟
در مثال این مقاله، روش اصل ثابت و روش سود ساده هر دو ۹٬۷۵۰٬۰۰۰ ریال سود دارند و روش اقساط مساوی ۱۰٬۰۱۵٬۹۸۸ ریال. در روش اصل ثابت، اصل وام سریع‌تر کم میشود و سود روی ماندهٔ کمتری محاسبه میشود.
اختلاف چند ریالی در آخرین قسط از کجاست؟
از گرد کردن مبلغ قسط و سود هر ماه. معمولاً این اختلاف در آخرین قسط اصلاح میشود تا ماندهٔ وام دقیقاً صفر شود.