محاسبه اقساط وام؛ فرمول، اکسل و جدول قسط ۱۴۰۵

برای محاسبه اقساط وام فقط سه عدد لازم دارید، مبلغ وام، نرخ سالانه و تعداد ماه‌ها، و ماشین‌حساب گوشی هم برای کنترل جواب کافی است. مثلاً وام ۲۰ میلیون تومانی با نرخ ۲۲ درصد و ۳۶ قسط ماهانه، ماهی حدود ۷۶۳٬۸۰۹ تومان قسط دارد و روی هم حدود ۷٫۵ میلیون تومان سود به بانک می‌پردازید. در ادامه فرمول قسط ثابت را قدم‌به‌قدم حساب می‌کنیم، همان را در اکسل با تابع PMT می‌نویسیم و می‌بینیم قسط ثابت با قسط کاهشی چه فرقی دارد.

اگر هنوز نوع وام را انتخاب نکرده‌اید، شرایط هر نوع در انواع وام بانکی در ایران آمده است. اگر بیشتر دنبال سود کل وام‌های ۱۰۰ تا ۵۰۰ میلیونی هستید، جدولش در محاسبه سود وام بانکی هست؛ این‌جا سراغ خودِ قسط می‌رویم.

فرمول محاسبه اقساط وام با قسط ثابت

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

PMT = P × r × (1 + r)ⁿ ÷ [(1 + r)ⁿ − 1]

• P مبلغ وام است.
• r نرخ ماهانه است. نرخ سالانه را بر ۱۲ تقسیم می‌کنید و چون درصد است، آن را به عدد اعشاری هم برمی‌گردانید. ۲۲ درصد در سال مثلاً می‌شود ۰٫۰۱۸۳۳ در هر ماه.
• n تعداد قسط‌ها به ماه است.

قرارداد بانک‌ها و بسیاری از فایل‌های اکسل به ریال نوشته می‌شود، برای همین مثال را ریالی می‌زنیم و تومانش را کنارش می‌آوریم. یک صفر که حذف شود، ۲۰۰ میلیون ریال همان ۲۰ میلیون تومان است. با نرخ ۲۲ درصد، (۱ + r) به توان ۳۶ حدود ۱٫۹۲۳۳ می‌شود. وقتی این عدد و r را در فرمول بگذارید، قسط ۷٬۶۳۸٬۰۹۱ ریال به دست می‌آید که همان حدود ۷۶۳٬۸۰۹ تومان است. ما ۲۲ درصد را فقط برای مثال انتخاب کرده‌ایم. در گزارش‌های این روزها نرخ مصوب تسهیلات ۲۳ درصد است و در بخش‌های بعدی با همان کار می‌کنیم.

جمع ۳۶ قسط حدود ۲۷۵ میلیون ریال می‌شود و اگر اصل وام را از آن کم کنید، سود کل ۷۴٬۹۷۱٬۲۶۳ ریال یا حدود ۷٫۵ میلیون تومان درمی‌آید.

محاسبه اقساط وام در اکسل با تابع PMT

اگر فرمول را دستی نمی‌زنید، اکسل و گوگل شیت همان را در یک تابع دارند. قالبش این است.

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

سه عدد اول را حتماً بدهید. rate نرخ هر دوره است و برای قسط ماهانه باید نرخ سالانه را بر ۱۲ تقسیم کنید، nper تعداد قسط‌هاست و pv مبلغ وام. دو عدد آخر داخل کروشه‌اند و برای وام معمولی لازم نیستند؛ اگر fv را ننویسید اکسل صفر می‌گیرد و وام در پایان کاملاً تسویه می‌شود، و اگر type را ننویسید فرض می‌کند قسط‌ها آخر هر ماه پرداخت می‌شوند. بسته به تنظیمات ویندوز ممکن است به‌جای ویرگول لازم باشد نقطه‌ویرگول بگذارید.

برای همان وام ۲۰۰ میلیون ریالی فرمول این‌طور نوشته می‌شود.

=PMT(22%/12, 36, 200000000)

جواب ۷٬۶۳۸٬۰۹۱ ریال است ولی با علامت منفی، چون در توابع مالی پولی که از جیب شما بیرون می‌رود منفی و پولی که به شما می‌رسد مثبت نوشته می‌شود. اگر مبلغ وام را منفی بنویسید، قسط مثبت درمی‌آید. ورودی‌های این مثال را در تصویر زیر می‌بینید.

جدول محاسبه اقساط وام با IPMT و PPMT

تابع PMT فقط قسط کل را می‌دهد. IPMT می‌گوید چقدر از یک قسط مشخص سود است، که در اصطلاح بانکی «فرع» هم می‌گویند، و PPMT می‌گوید چقدر از همان قسط اصل وام را کم می‌کند، یعنی همان پولی که قرض گرفته‌اید. جمع این دو همیشه برابر PMT است. هر دو تابع یک ورودی بیشتر از PMT دارند و آن شماره قسط است؛ مثلاً سود قسط پنجم را این‌طور می‌گیرید.

=IPMT(22%/12, 5, 36, 200000000)

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

جدول ۳۶ قسطی کامل، با جمع هر ستون در ردیف آخر، در تصویر بعدی آمده است. جمع ستون اصل دقیقاً ۲۰۰ میلیون ریال است و جمع ستون سود همان ۷۴٬۹۷۱٬۲۶۳ ریال.

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

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

محاسبه اقساط وام به روش ثابت یا کاهشی

در اقساط کاهشی اصل وام هر ماه به یک اندازه کم می‌شود و سود روی مانده بدهی می‌نشیند؛ قسط اول سنگین‌ترین است و از آن به بعد هر ماه سبک‌تر می‌شود. برای وام ۱۰۰ میلیون تومانی با ۲۳ درصد و ۳۶ ماه، دو روش نتیجه‌های زیر را می‌دهند.

با روش کاهشی در این مثال حدود ۳٫۹ میلیون تومان سود کمتری می‌دهید، ولی ماه اول ۸۲۳ هزار تومان بیشتر از جیبتان می‌رود. در وام‌های بانکی رایج معمولاً قسط ثابت می‌بینید و کاهشی کمتر پیش می‌آید. وام شما کدام را دارد، در قرارداد و جدول بازپرداخت نوشته شده است.

محاسبه سریع اقساط وام با ضریب هر یک میلیون تومان

برای تخمین سریع لازم نیست هر بار فرمول را بنویسید. قسط وام با مبلغش متناسب است، پس اگر قسط یک میلیون تومان را بدانید، کافی است آن را در تعداد میلیون‌های وام ضرب کنید. جدول زیر این ضریب را برای نرخ ۲۰ و ۲۳ درصد نشان می‌دهد. نرخ ۲۳ درصد را مبنا گرفته‌ایم چون در گزارش‌های مهر ۱۴۰۵ همین نرخ مصوب تسهیلات است و تغییری هم اعلام نشده، ولی نرخ وام خودتان را باید از بانک بگیرید.

مثلاً وام ۸۰ میلیون تومانی با نرخ ۲۳ درصد و ۴۸ ماه را در نظر بگیرید. ۸۰ را در ۳۲٬۰۵۱ ضرب کنید تا قسط حدود ۲٬۵۶۴٬۰۰۰ تومان در بیاید.

با قسط مشخص چه وامی می‌شود گرفت

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

=PV(23%/12, 36, -5000000)

جواب حدود ۱۲۹ میلیون تومان است. یعنی اگر ماهی ۵ میلیون تومان قسط بدهید، با نرخ ۲۳ درصد و ۳۶ ماه می‌توانید حدود ۱۲۹ میلیون تومان وام بگیرید. اگر مدت را به ۴۸ ماه برسانید، با همان قسط وام بزرگ‌تری می‌گیرید، ولی سود کل هم بیشتر می‌شود.

دو تابع دیگر هم به کار می‌آیند. NPER می‌گوید با این مبلغ و این قسط، وام در چند ماه تسویه می‌شود. RATE نرخ را پیدا می‌کند، پس اگر در قرارداد فقط مبلغ وام و قسط آمده، با آن نرخ پنهان را درمی‌آورید. چون جواب این تابع نرخ ماهانه است، در مثال زیر ضرب در ۱۲ شده تا نرخ سالانه درآید. =RATE(36, -3870972, 100000000)*12 همان ۲۳ درصد را برمی‌گرداند.

ارزش فعلی و ارزش آتی اقساط مساوی

در کتاب‌ها و کلاس‌های مالی همین محاسبه را با چند اصطلاح توضیح می‌دهند. جدول زیر اصطلاح‌ها را به ورودی‌های اکسل وصل می‌کند.

ارزش فعلی و آتی مجموعه‌ای از اقساط مساوی هم با دو فرمول کوتاه حساب می‌شود. A همان قسط ثابت و r نرخ هر دوره است.

PV = A × [1 − (1 + r)⁻ⁿ] ÷ r

FV = A × [(1 + r)ⁿ − 1] ÷ r

عبارت داخل کروشه در فرمول PV وقتی بر r تقسیم شود همان ضریبی است که در جدول‌های ارزش فعلی اقساط مساوی می‌بینید. جدول زیر این ضریب را برای سه نرخ ماهانه نشان می‌دهد. آن را در قسط ماهانه ضرب کنید تا ارزش فعلی درآید، که برای وام همان مبلغی است که می‌گیرید.

وامی که قسط ندارد و یک‌جا برمی‌گردد

گاهی وام قسط ندارد و اصل و سودش در پایان دوره یک‌جا پرداخت می‌شود، و در این حالت فقط ارزش آتی لازم است. فرض کنید ۱۰۰ میلیون ریال با سود ۲۰ درصد سالانه برای ۵ سال گرفته‌اید؛ همه‌چیز سالانه است و بدون تبدیل وارد تابع FV می‌شود.

=FV(20%, 5, 0, -100000000)

جواب ۲۴۸٬۸۳۲٬۰۰۰ ریال است و در پایان ۵ سال باید حدود ۲٫۵ برابر مبلغ وام را پس بدهید.

حالا فرض کنید مدت ۵ ماه است و سود هر ماه به اصل اضافه می‌شود. یک راه این است که نرخ را بر ۱۲ تقسیم کنید و دوره را ۵ بگذارید؛ ارزش آتی در این حالت ۱۰۸٬۶۱۵٬۷۷۹ ریال می‌شود.

راه دوم این است که دوره را سالانه نگه دارید و ۵ ماه را کسری از سال، یعنی همان ۵ ÷ ۱۲، بنویسید. چون سود هر ماه به اصل اضافه می‌شود، ۲۰ درصد سالانه در عمل کمی بیشتر کار می‌کند، و نرخی که این اضافه‌شدن را هم در خود دارد «نرخ مؤثر» نام دارد. فرمولش در تصویر زیر آمده است و برای ۲۰ درصد حدود ۲۱٫۹ درصد درمی‌آید.

اگر این نرخ را با دورهٔ ۵ ÷ ۱۲ در تابع بگذارید، دوباره به همان ۱۰۸٬۶۱۵٬۷۷۹ ریال می‌رسید. دو راه باید یک جواب بدهند و اگر ندادند، معمولاً واحد نرخ و مدت یکی نیست.

محاسبه اقساط وام ازدواج و ودیعه مسکن

دو وامی که خانوارهای زیادی سراغشان می‌روند حسابشان با وام معمولی فرق دارد، و بهتر است پیش از گذاشتن عددهایشان در فرمول بدانید قسطشان چطور تعیین می‌شود. شرایط و مراحل ثبت‌نام هر کدام در مقالهٔ خودش آمده و این‌جا فقط خودِ قسط را می‌بینیم.

ودیعه مسکن مثل یک وام معمولی با سود ۲۳ درصد حساب می‌شود و همین فرمول و تابع PMT برایش جواب می‌دهد. بانک مسکن مسیر دومی هم با سقف ۴۰۰ میلیون تومان در تهران دارد که با اوراق تأمین می‌شود؛ نرخ و مدتش را از بانک بپرسید و در همین فرمول بگذارید. ارقام جدول طبق گزارش‌های رسانه‌ای است و ممکن است عوض شود. متن بخشنامه‌ها را در سایت رسمی بانک مرکزی ببینید.

وام ازدواج قرض‌الحسنه است و بانک به‌جای سود کارمزد می‌گیرد. طبق توضیح منابع رسانه‌ای، کارمزد ۴ درصدی را هر سال روی مانده وام حساب می‌کنند و همراه قسط اول همان سال می‌گیرند. اگر این روش درست باشد، اصل وام ۳۰۰ میلیونی ماهی ۲٫۵ میلیون تومان کم می‌شود و کارمزد سال اول ۱۲ میلیون، سال دوم ۱۰٫۸ و سال سوم ۹٫۶ میلیون تومان درمی‌آید؛ جمع ده سال حدود ۶۶ میلیون تومان می‌شود. اگر ۴ درصد را مثل نرخ سود در PMT بگذارید، قسط حدود ۳٫۰۴ میلیون تومان درمی‌آید که با این روش نمی‌خواند. شیوهٔ دقیق را بانکی که وام را می‌دهد در قرارداد نوشته است.

اگر قسط بانک با عدد شما فرق کرد

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

اگر می‌خواهید وام را کلی‌تر بشناسید، یعنی شرایط و ضمانتش را، مقالهٔ وام بانکی چیست را بخوانید. قدم بعدی ساده است. محاسبه اقساط وامی را که در نظر دارید با PMT شروع کنید، یعنی نرخ و مدت را بگذارید و قسطی را که درمی‌آید با درآمد ماهانه‌تان مقایسه کنید.