توابع وام در اکسل – راهنمای کاربردی

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

قدرت و کارایی اکسل، در انجام محاسبات نهفته است. فرمول نویسی و استفاده از توابع درونی اکسل، انجام بسیاری از کارها را ساده‌تر و البته سریع‌تر می‌کند. برای دسترسی به توابع مالی در اکسل، کافی است از برگه Formula گزینه Financial را انتخاب کنید. لیست طولانی از توابع مالی در اکسل ظاهر خواهد شد که شامل ۵۵ تابع مختلف است.

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

$$\large \begin{cases}pv \times (1+rate)^{nper}+pmt(1+rate\times type)\times \dfrac{(1+rate)^{nper}-1}{rate}+fv=0 & rate \neq 0\\(pmt\times nper)+pv+fv=0 & rate =0\end{cases}$$

رابطه ۱

محاسبه اقساط یک وام با تابع PMT

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

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

کلیک کنید

به طور دقیق‌تر می‌توان گفت که این تابع قابلیت محاسبه میزان یا مقدار قسط (Payment) یک وام با بهره یا سود ثابت و البته مبلغ بازپرداخت ثابت را دارد. شکل و پارامترهای این تابع به صورت زیر است.

$$\large PMT\;(rate\; ,nper\; ,pv\; ,[fv]\; ,[type]\;)$$

استفاده از پارامترهای fv و type برای محاسبه این تابع اختیاری است و اکسل مقدارهای پیش‌فرض برای آن‌ها در نظر گرفته است ولی اگر مقدار آن‌ها را تعیین کنید، ممکن است نحوه محاسبات تغییر کند.

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

برای این که یاد بگیرید چگونه قسط وام را در اکسل با تابع PPMT محاسبه کنید و با فرمول‌ها و مثال‌های کاربردی آشنا شوید، در ویدیوی کوتاه زیر آموزش آن را مشاهده کنید.

در ادامه پارامترهای این تابع را معرفی و در مورد خصوصیات هر یک توضیحاتی ارائه خواهیم کرد.

به منظور واضح‌تر شدن خصوصیات و ویژگی‌های هر یک از این پارامترها، توضیحاتی نیز در ادامه آمده است.

• نرخ بهره وام-سرمایه‌گذاری (rate): در این قسمت باید نرخ بهره وام دریافتی یا مبلغ سرمایه‌گذاری را مشخص کنید. البته توجه داشته باشید که اگر می‌خواهید اقساط ماهانه وام را به وسیله این تابع محاسبه کنید، باید این نرخ را هم به صورت ماهانه وارد کنید. برای مثال اگر نرخ بهره یک وام ۱۸٪ در سال است، برای محاسبه اقساط وام ماهانه باید نرخ بهره سالانه را بر ۱۲ تقسیم کنید تا نرخ بهره ماهانه بدست آید. واضح است که این عدد باید به صورت درصدی یا اعشاری وارد شود. در صورتی که از این تابع به منظور تعیین درآمدهای در یک طرح سرمایه‌گذاری استفاده می‌کنید، این مقدار می‌تواند نرخ سود یک سرمایه‌گذاری نیز باشد که قرار است سود آن به صورت ماهانه پرداخت شود.
• دوره بازپرداخت (nper): این پارامتر بیانگر طول دوره بازپرداخت وام است. به این معنی که باید تعداد سال یا ماه‌هایی که وام مستهلاک می‌شود را در این پارامتر وارد کنید. باز هم توجه داشته باشید که اگر می‌خواهید اقساط را برحسب ماه محاسبه کنید باید دوره بازپرداخت را هم ماهانه وارد کنید تا اکسل در انجام محاسبات دچار مشکل نشود. واضح است که این مقدار نیز عددی نامنفی است. باز هم اگر از این تابع برای سرمایه‌گذاری استفاده کرده‌اید، این پارامتر، دوره سرمایه‌گذاری را مشخص می‌کند.
• مبلغ وام یا میزان سرمایه‌گذاری جاری (pv): در این پارامتر مبلغ وام را وارد می‌کنیم. توجه داشته باشید که اگر می‌خواهید قسط وام را برحسب ریال دریافت کنید، باید این مقدار را نیز برحسب ریال ثبت کنید. البته از یاد نبرید که مقدار مثبت برای این پارامتر باعث ایجاد مقدار منفی برای مبلغ قسط یا سود ماهانه وام خواهد شد. زیرا وام را دریافت ولی قسط را پرداخت می‌کنید. از طرفی اگر هدف سرمایه‌گذاری است باید مقدار این پارامتر را در تابع منفی وارد کنید، زیرا مبلغی است که از دارایی شما کسر می‌شود. به این ترتیب نتیجه محاسبه تابع pmt‌ مثبت شده و به معنی دریافت مبلغ حاصل از سرمایه‌گذاری است.
• ارزش آتی سرمایه (fv): اگر لازم باشد می‌توانید ارزش این سرمایه‌گذاری (مبلغ وام که در pv ثبت شده) را در پایان دوره بازپرداخت مشخص کنید. پارامتر fv ارزش آتی سرمایه دریافتی را پس از گذشت nper واحد زمانی مشخص می‌کند. اگر می‌خواهید بالانس مثبت داشته باشید ملبغ مورد نظر را در fv وارد کنید. در صورتی که این مقدار وارد نشود، اکسل به طور پیش‌فرض مقدار صفر را در نظر گرفته و حساب را تسویه می‌کند. برای مثال فرض کنید صندوقی از شما می‌خواهد که در آن سرمایه‌گذاری کنید. این صندوق تضمین می‌کند با سود مشخص در پایان دوره سرمایه‌گذاری، ۱۰ میلیون تومان به شما پرداخت خواهد کرد. به این ترتیب برای پیدا کردن مبلغ پرداختی ماهانه در این صندوق از تابع PMT استفاده کرده و مقدار pv را صفر و fv را همان ۱۰ میلیون تومان در نظر می‌گیریم. همچنین اگر می‌خواهید با دریافت وامی که در pv مشخص شده در پایان دوره بازپرداخت وام، ذخیره‌ای هم در بانک وام دهنده داشته باشید، آن مبلغ را به عنوان پارامتر fv وارد می‌کنید.
• نوع بازپرداخت (type): اگر مبلغ اقساط را در پایان هر دوره (مثلا در پایان هر ماه) پرداخت می‌کنید می‌توانید این پارامتر را صفر در نظر بگیرید. البته اگر مقداری برای type وارد نکنید به طور خودکار صفر در نظر گفته شده و مشخص می‌شود که اقساط در پایان هر دوره پرداخت می‌شوند. اگر می‌خواهید شیوه محاسبه بازپرداخت براساس آغاز دوره انجام شود، مقدار این پارامتر را برابر با ۱ در نظر بگیرید. به این ترتیب مشخص می‌کنید که هر قسط در ابتدای ماه پرداخت می‌شود. واضح است که با این کار به محض دریافت وام، قسط اول از آن کسر خواهد شد.

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

مبلغ قسط ماهانه در سطر ششم و با رنگ نارنجی مشخص شده است. فرمول مربوط به این بخش به صورت زیر است.