محاسبه سود بانکی و اقساط وام در اکسل

نرم افزار اکسل برای طیف وسیعی از کاربران قابل استفاده می باشد از این رو توابع زیادی با دسته بندی های متفاوت طراحی شده اند که نیازهای کاربران را بر طرف می کند. یکی از این دسته بندی ها مربوط به حوزه مالی می باشد که توابع بسیار کاربردی و زیادی در آن گنجانده شده است.

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

در دو تصویر زیر فرمول محاسبه قسط و سود تسهیلات دریافتی در بانک های خصوصی و دولتی را مشاهده می کنید:

فرمول محاسبه قسط و سود در بانک های خصوصی

فرمول محاسبه قسط و سود در بانک های دولتی

برای محاسبه مبلغ قسط وام از تابع PMT باید استفاده کنیم که اجزای آن به صورت زیر می باشد:

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

مخفف:

Payment during Maturity Time

شرح اجزاء:

Rate: نرخ بهره در هر دوره

nper: تعدادکل دوره های پرداخت

pv: مقدار ارزش فعلی یا مبلغ تسهیلات دریافتی

fv: مقدار ارزش آتی یا تراز نقدی که انتظار می رود پس از آخرین پرداخت داشته باشیم. در صورتیکه این متغیر در تابع نیاید مقدار آن صفر در نظر گرفته می شود.

type: زمان انجام پرداخت ها را مشخص می کند (اگر مقدار صفر بگیرد، پرداخت ها در انتهای دوره انجام می شود و اگر مقدار یک بگیرد، پرداخت ها در ابتدای هر دوره انجام می شود)

بیشتر بخوانید : استفاده از conditional formatting و چک باکس در ساخت گزارش

ملاحظات:

۱- مقداری که توسط این تابع برگردانده می شود شامل اصل وام و سود آن می شود.

تابع PPMT میزان پرداخت از محل اصل وام را نشان می دهد و تابع IPMT میزان پرداخت از محل سود تعلق گرفته را محاسبه می کند. به عبارتی، رابطه مقابل بین این سه تابع برقرار است:

PMT = PPMT + IPMT

در صورتیکه مقدار حاصل از این تابع را در nper ضرب کنید، کل پرداخت های صورت گرفته طی دوره بازپرداخت وام را نشان خواهد داد.
توجه داشته باشید واحدهایی که برای rate و nper استفاده می شوند با هم همگون باشند.به عنوان مثال، اگر پرداخت ها به صورت ماهیانه برای یک وام ۴ ساله با نرخ بهره سالیانه ۱۰% صورت می گیرد، بایستی ۱۲÷۱۰% را برای متغیر rate و ۱۲×۴ را برای متغیر nper مدنظر قرار داد. اگر پرداخت ها به صورت سالیانه برای همان وام انجام شود، بایستی نقدار ۱۰% را برای rate و مقدار ۴ را برای nper استفاده کرد.
متغیرهای nper و type بایستی به صورت عدد صحیح وارد شوند.
اگر nper≤ ۰ ; rate ≤ ۰ یا pv ≤ ۰ باشد این تابع مقدار خطای (!NUM#) را برمی گرداند.
اگر متغیر type مقداری غیر از صفر و یک بگیرد این تابع مقدار خطای (!NUM#) را بر می گرداند.
نتیجه محاسبات این تابع مقدار منفی را بر می گرداند. برای نمایش مقدار محاسبه شده به صورت عدد مثبت می توانید قبل از نام تابع علامت منفی را قرار داده یا در متغیر pv قبل از وارد نمودن عدد علامت منفی را وارد کنید تا نتیجه تابع عددی مثبت باشد.

بیشتر بخوانید: چگونه داده های نامنظم را مرتب کنیم؟

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

مخفف:

Payment on the Principal during Maturity Time

شرح:

میزان پرداخت از محل اصل وام را در یک دوره مشخص بر پایه پرداخت های ثابت دوره ای و نرخ بهره ثابت محاسبه می کند.

شرح اجزاء:

Rate: نرخ بهره در هر دوره

per: شماره عددی که می خواهیم مبلغ را در آن دوره محاسبه کنیم که باید عددی بین ۱ تا nper باشد.

nper: تعدادکل دوره های پرداخت

pv: مقدار ارزش فعلی یا مبلغ تسهیلات دریافتی

IPMT( rate ; per ; nper ; pv ; [fv] ; [type] )

مخفف:

Interest Payment during Maturity Time

شرح:

میزان پرداخت از محل فرع وام را در یک دوره مشخص بر پایه پرداخت های ثابت دوره ای و نرخ بهره ثابت محاسبه می کند.

توجه: ملاحضات و شرح اجزاء این تابع مشابه با دو تابع ذکر شده در بالا می باشد.

در این آموزش قصد داریم جدول پرداخت اقساط را با توبع ذکر شده ایجاد کنیم.

ضمن اینکه یک ستون برای پرداخت های متفرقه یا اضافی در نظر گرفته ایم که به واسطه آن تعداد اقساط برای تسهیلات دریافتی مشخص خواهد شد که قاهدتاً در صورت پرداخت های زودهنگام تعداد بازپرداخت کمتر از nper خواهد شد.