تهیه کارت تسهیلات

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

مثال ۱: وام یکساله و بازپرداخت ماهیانه

فرض کنید وامی یکساله به مبلغ ۱۰میلیارد ریال با نرخ ۱۸% دریافت کردیم که بازپرداخت آن ماهیانه و اولین سررسید آن در تاریخ ۱۴۰۱/۰۱/۳۱ می‌باشد. برای ساخت کارت تسهیلات این وام، ابتدا جدول فوق را تهیه کرده و سپس مفروضات (مبلغ وام دریافتی، تاریخ سررسید، نرخ و تعداد قسط در سال) را وارد می‌کنیم.

سلول E2 نشاندهنده فرع وام بوده که از جمع مبالغ کارمزد اقساط حاصل می‌شود و برای محاسبه آن از فرمول زیر استفاده می‌کنیم. همچنین سلول F2 نشاندهنده مبلغ کل بازپرداخت‌ها بوده که از جمع دو سلول D2 و E2 حاصل می‌شود.

=SUM(E3:E14)

برای محاسبه مبلغ اقساط از تابع PMT بهره می‌گیریم و فرمول زیر را در سلول F3 می‌نویسیم:

=-PMT($I$1/12,12,$D$2)

توضیح فرمول: در آرگومان اول تابع، نرخ بهره سالانه (سلول I1) را بر عدد ۱۲ تقسیم کرده تا نرخ ماهیانه بدست آید. آرگومان دوم تابع نیز معرف تعداد دوره بازپرداخت است که عدد ۱۲ وارد شد و نهایتا در آرگومان سوم، مبلغ دریافتی وام که در سلول D2 قرار دارد انتخاب گردید.

*در فرمول فوق از کارکتر $ برای ثابت کردن سلول I1 و D2 استفاده گردید.
*به دلیل منفی بودن خروجی تابع PMT، قبل تابع علامت منفی قرار داده شد.

جهت محاسبه مبلغ اصل قسط از تابع PPMT استفاده کرده و فرمول زیر را در سلول D3 می‌نویسیم:

=-PPMT($I$1/12,A3,12,$D$2)

توضیح فرمول: در آرگومان اول تابع، نرخ بهره سالانه (سلول I1) را بر عدد ۱۲ تقسیم کرده تا نرخ ماهیانه بدست آید. آرگومان دوم، مرحله بازپرداخت بوده که برای پویا کردن آن از ستون A (ردیف اقساط) کمک گرفته شد. در آرگومان سوم تابع که معرف تعداد کل اقساط است عدد ۱۲ وارد شده و سرانجام در آرگومان چهارم، مبلغ دریافتی وام که در سلول D2 قرار دارد انتخاب گردید.

*در فرمول فوق از کارکتر $ برای ثابت کردن سلول I1 و D2 استفاده گردید.
*به دلیل منفی بودن خروجی تابع PPMT، قبل تابع علامت منفی قرار داده شد.

در نهایت با کمک تابع تابع IPMT، کارمزد اقساط محاسبه می‌گردد:

=-IPMT($I$1/12,A3,12,$D$2)

توضیح فرمول: در آرگومان اول این تابع، نرخ بهره سالانه (سلول I1) را بر عدد ۱۲ تقسیم کرده تا نرخ ماهیانه بدست آید. آرگومان دوم، مرحله بازپرداخت بوده که برای پویا کردن آن از ستون A (ردیف اقساط) کمک گرفته شد. در آرگومان سوم تابع که معرف تعداد کل اقساط است عدد ۱۲ وارد شده و سرانجام در آرگومان چهارم، مبلغ دریافتی وام که در سلول D2 قرار دارد انتخاب گردید.

*در فرمول فوق از کارکتر $ برای ثابت کردن سلول I1 و D2 استفاده گردید.
*به دلیل منفی بودن خروجی تابع IPMT، از علامت منفی قبل تابع استفاده شد.

با تعمیم دادن فرمول‌های نوشته شده تا ردیف ۱۴، جدول نهایی به شکل زیر می‌شود.

مثال ۲: وام ۳ماهه و بازپرداخت ماهیانه

فرض کنید وامی سه‌ماهه به مبلغ ۱۰میلیارد ریال با نرخ ۱۸% دریافت کردیم که بازپرداخت آن ماهیانه و اولین سررسید آن در تاریخ ۱۴۰۱/۰۱/۳۱ می‌باشد. جهت تهیه کارت تسهیلات این وام، ابتدا جدولی تهیه کرده و مفروضات (مبلغ وام دریافتی، تاریخ سررسید، نرخ و تعداد قسط در سال) را وارد می‌کنیم. جهت محاسبه مبلغ اقساط، اصل و فرع آن همانند مثال فوق به ترتیب از توابع PMT و PPMT و IPMT استفاده می‌کنیم:

*با توجه به ماهیانه بودن پرداخت‌ها، “تعداد قسط در سال” ۱۲ در نظر گرفته می‌شود و نرخ بهره سالیانه بر عدد ۱۲ تقسیم شده تا نرخ ماهیانه در محاسبات اعمال گردد.

با تعمیم فرمول‌های نوشته شده تا ردیف ۵، نتیجه به شکل زیر خواهد شد.

مثال ۳: وام ۱۸ماهه و بازپرداخت ماهیانه

فرض کنید وامی ۱۸ماهه به مبلغ ۱۰میلیارد ریال با نرخ ۱۸% دریافت کردیم که بازپرداخت آن ماهیانه و اولین سررسید آن در تاریخ ۱۴۰۱/۰۱/۳۱ می‌باشد. به منظور ایجاد کارت تسهیلات این وام، ابتدا جدولی تهیه کرده و مفروضات (مبلغ وام دریافتی، تاریخ سررسید، نرخ و تعداد قسط در سال) را وارد می‌کنیم.

در این مثال نیز همانند مثال‌های فوق عمل کرده؛ با این تفاوت که در آرگومان nper، عدد ۱۸ وارد می‌شود.

*با توجه به ماهیانه بودن دوره بازپرداخت‌ها، نرخ بهره بر عدد ۱۲ تقسیم شده است.

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

مثال ۴: وام ۳ساله و بازپرداخت سالیانه

فرض کنید وامی ۳ساله به مبلغ ۱۰میلیارد ریال با نرخ ۱۸% دریافت کردیم که بازپرداخت آن سالیانه و اولین سررسید آن در تاریخ ۱۴۰۱/۰۱/۳۱ می‌باشد. به منظور تهیه کارت تسهیلات این وام، ابتدا جدولی تهیه کرده و مفروضات (مبلغ وام دریافتی، تاریخ سررسید، نرخ و تعداد قسط در سال) را وارد می‌کنیم.

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

مثال ۵: وام ۳ساله و بازپرداخت 6ماهه

فرض کنید وامی ۳ساله به مبلغ ۱۰میلیارد ریال با نرخ ۱۸% دریافت کردیم که بازپرداخت آن هر ۶ماه بوده و اولین سررسید آن در تاریخ ۱۴۰۱/۰۱/۳۱ می‌باشد. روند تهیه کارت تسهیلات این وام با مثال‌های فوق، فرق چندانی ندارد. در این مثال نرخ بهره بر عدد ۲ تقسیم می‌شود.

پی‌نوشت

*جهت روند کردن ارقام، می‌توانید از توابع INT، ROUND و … استفاده کنید.

پیوست

دانلود فایل کارت تسهیلات