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

در جدول و نمودار زیر نرخ بهره موثر با لحاظ دوره های پرداخت متفاوت محاسبه شده است که می توان مشاهده کرد؛

۱۱- محاسبه نرخ بهره موثر با تابع effect

متخصصان مالی برای محاسبه نرخ بهره موثر از نرخ بهره اسمی از فرمول های موجود در اکسل استفاده می کنند که فرمول EFFECT در بین آنها از محبوبیت خاصی برخوردار است.

همچنین برای محاسبه نرخ بهره سالانه (APR) و درصد بهره سالانه (APY)، محاسبه وام خودرو، مسکن و همچنین وام های کوچک تر از نرخ اسمی استفاده می کنند که توسط وام دهنده تعیین می گردد.

تابع EFFECT در اکسل:

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

(EFFECT (nominal_ rate , npery=

نرخ بهره اسمی: در این آرگومان مقدار نرخ بهره اسمی وارد می گردد که بایستی بین صفر و یک باشد.

تعداد دوره: در این نحوه محاسبه بهره مرکب در اکسل آرگومان تعداد دوره های مرکب شدن در سال وارد می گردد که بایستی عددی صحیح و مثبت باشد.

لازم به ذکر است که نرخ موثر سالانه معیاری از نرخ بهره می باشد که به منظور مرکب سازی نرخ بهره از آن استفاده می گردد و در مقایسه های مالی از قبیل پروژه ها، وام ها، سودآوری و… مورد استفاده زیادی قرار دارد که فرمول آن به صورت زیر می باشد:

خطاهای معمول:

• اگر مقدار نرخ بهره اسمی عددی بین صفر و یک نباشد، خطای#NUM! رخ می دهد. دقت داشته باشید که صفر می تواند شامل شود اما یک نه.
• اگر مقادیر وارد شده در تابع، عددی (اسکالر) نباشند، خطای #VALUE! رخ خواهد داد.

مثال: محاسبه EFFECT برای دوره های مختلف

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

مدت دوره	تعداد دفعات مرکب شدن	نرخ بهره اسمی	نرخ بهره موثر
n	npery	rate	effect
سالیانه	1	12%	12%
شش ماهه	2	12%	12.36%
چهار ماهه	3	12%	12.4864%
فصلی	4	12%	12.550881%
دو ماهه	6	12%	12.616241926400%
ماهیانه	12	12%	12.682503013197%
روزانه	365	12%	12.747461563839%
ساعتی	8760	12%	12.749592487823%
دقیقه ای	525600	12%	12.749683614621%
ثانیه ای	31536000	12%	12.749685143060%

برای شرکت در دوره های آموزشی امکان سنجی با اکسل می توانید با شماره 09127976106 تماس حاصل فرمایید .

اشتراک گذاری:

https://comfar.co/?p=1482

دکتر ساعی نیک حسین ساعی نیک نحوه محاسبه بهره مرکب در اکسل هستم دکتری اقتصاد از دانشگاه علامه طباطبایی از ۱۵ سال گذشته در زمینه انجام انواع طرحهای توجیهی و آموزش نرم افزار کامفار و اکسل به صورت مداوم با سازمانها و نهادهای دولتی و خصوصی و دانشگاهها همکاری می کنم. بعد از اجرای ۴۰۰۰ ساعت آموزشی قصد دارم شیوه نوینی از آموزشهای امکان سنجی را از طریق این وب سایت و شبکه های اجتماعی پر مخاطب ارائه نمایم. با من همراه باشید.

موضوع: کمک برای محاسبه بهره مرکب در اکسل

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

اخطار: این یک موضوع قدیمی است به دلیل قدیمی بودن موضوع، ممکن است برخی فایل های ضمیمه به درستی کار نکنند. لطفا در صورت عدم ضرورت، از بالا آوردن موضوعات قدیمی خودداری نمایید.

تصاویر کوچک فایل پیوست

گروه تخصصی پرسش و پاسخ اکسل ایران در تلگرام

سپاس ها (2)

نوشته اصلی توسط amir_ts

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

با سلام
شما اگر ردیف آخر رو انتخاب کنید گوشه سمت راست یک علامت بعلاوه ضخیم به وجود میاد که با کشیدن اون به پایین تا هر جا که بخواهید میتونید جدولتون رو بسط بدید.

سپاس ها (1)

نوشته اصلی توسط amir_ts

با سلام
شما اگر ردیف آخر رو انتخاب کنید گوشه سمت راست یک علامت بعلاوه ضخیم به وجود میاد که با کشیدن اون به پایین تا هر جا که بخواهید میتونید جدولتون رو بسط بدید.

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

اینجا وقتی مقدار 100 و 1 درصد و 240 رو قرار میدیم عدد 98,925.54
رو به ما میده
فرمولش رو هم اینجا اورده
Understanding The Time Value Of Money | Investopedia

در هر دو حالت هم روزی 1 درصد به اصل اضافه میشه

سپاس ها (1)

سپاس ها (1)

البته اگر تخصصي بخواهيد مي توانيد از تابع fv (فيوچر وليو) هم استفاه کنيد.
آرگومانهاي آن که در اين حالت مورد استفاده قرار خواهند گرفت عبارتند از rate (نرخ سود به درصد) و nper (تعداد دفعات محاسبه سود) و pv (سرمايه اوليه) که pv را بايد به صورت منفي وارد کنيد.
مثلا" فرمول زير ارزش آتي 100 ريال را براي يک دوره 240 تايي با نرخ بهره 1 درصد (0.01) به ما مي دهد:

سپاس ها (2)

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

نوشته اصلی توسط alibati

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

با سلام
من فرمول های محاسباتی اون سایت رو دیدم.

با این فرمول ها باز به همون عدد تابع معرفی شده FV آقای پارسا گرامی میرسیم.فقط مورد مبهم اون سایت اشاره به 4.5% بود و همچنین نداشتن پارامتر(Periodic Deposit (PMT که جای تامل داره والا با فرمول های سایتی که معرفی کردید باز به همین عدد میرسیم.
ضمنا من یک سایت دیگه پیدا کردم که آنلاین محاسبه نحوه محاسبه بهره مرکب در اکسل رو انجام میده که جوابش با تابع معرفی شده و جدول طراحی شده برابر است.این سایت رو هم امتحان کنید.
Future Value Calculator
فقط قسمت دوره سپرده (Periodic Deposit (PMT را صفر بزارید و جواب ها رو تست کنید.

نرخ بهره موثر با تابع EFFECT

از نرخ بهره Interest Rate جهت محاسبه سود سپرده و یا بهره وام Loan دریافتی از سیستم بانکی و یا هر منبع مالی تامین کننده دیگر استفاده می شود. زمانی که شما سپرده گذار باشید این نرخ تحت عنوان نرخ سود سپرده گذاری می باشد و زمانی که شما وام گیرنده باشید تحت عنوان نرخ بهره وام خوانده می شود. صرف نظر از اینکه شما سپرده گذار و یا وام گیرنده باشید، معمولا هر دوی این نرخ نحوه محاسبه بهره مرکب در اکسل ها را تحت عنوان نرخ بهره موثر می خوانیم. در ادامه مقاله حاضر به توضیح و تفسیر نرخ بهره موثر می پردازیم.

نرخ بهره موثر:

نرخ بهره مؤثر (Effective interest rate)، گفته می شود. نرخ صرفا مؤثر نرخ بهره وام یا محصول مالی است که از نرخ بهره اسمی باقی می ماند و در صورت بهره مرکب به عنوان «نرخ بهره معادل» بیان می شود. سالانه به صورت معوقه پرداخت می شود.

درادامه توضیحاتی همراه با مثال برای درک بهتر نحوه محاسبه بهره مرکب در اکسل این مبحث ارائه می دهیم؛ برای سود بانکی متعلق به رقم سپرده گذاری شده در بانک ارائه می شود که همین قاعده در خصوص نرخ بهره وام دریافتی که جهت تأمین پروژه های سرمایه گذاری مورد استفاده قرار می گیرد.

معمولا نرخ بهره سپرده که از سوی بانک ها و یا موسسات مالی و اعتباری اعلام می گردد به صورت سالیانه می باشد. به این صورت که تعیین نرخ بهره R درصد نشان از آن دارد که با سپرده گذاری A ریال نحوه محاسبه بهره مرکب در اکسل در بانک سالیانه به اندازه R درصد به سپرده شما سود تعلق خواهد گرفت.

سود سالیانه سپرده = A × R

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

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

در این حالت نرخ بهره سالیانه که R درصد اعلام شده است به جهت تعیین دوره بازپرداخت سود تحت تأثیر قرار گرفته و تغییر می نماید. نرخ بهره اعلام شده بعنوان نرخ بهره نرمال نامگذاری می شود اما نرخ واقعی که با در نظر گرفتن دوره زمانی بازپرداخت سود در محاسبات لحاظ می شود تحت عنوان نرخ بهره موثر Effective Rate خوانده می شود.

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

سود سالیانه سپرده = A × Re

نکات مهم و قابل توجه:

نرخ موثر سالانه معیاری از نرخ بهره است که برای مرکب سازی نرخ بهره استفاده شده و در مقایسات مالی (پروژه ها، وام ها و سود آوری و…) استفاده زیادی دارد. فرمول ریاضی آن به صورت زیر است:

لازم به ذکر است که نرخ موثر سالانه معیاری از نرخ بهره می باشد که به منظور مرکب سازی نرخ بهره از آن استفاده می گردد و در مقایسه های مالی از قبیل پروژه ها، وام ها، سودآوری و… مورد استفاده زیادی قرار دارد که فرمول آن به صورت زیر می باشد:

فرمول محاسبه نرخ بهره موثر بدین صورت است:

Re= Effective Rate

m = Periods per year

محاسبه نرخ بهره موثر در نرم افزار اکسل Excel با فرمول زیر انجام می شود:

متخصصان مالی برای محاسبه نرخ بهره موثر از نرخ بهره اسمی از فرمول های موجود در اکسل استفاده می کنند که فرمول EFFECT در بین آنها از محبوبیت خاصی برخوردار است.

همچنین برای محاسبه نرخ بهره سالانه (APR) و درصد بهره سالانه (APY)، محاسبه وام خودرو، مسکن و همچنین وام های کوچک تر از نرخ اسمی استفاده می کنند که توسط وام دهنده تعیین می گردد.

شرح تابع EFFECT:

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

EFFECT(nominal_rate, npery) =

(تعداد دوره، نرخ بهره اسمی) EFFECT =

تابع EFFECT یک مقدار که نمایانگر مقدار نرخ بهره موثر سالانه است را باز می گرداند.

ورودی های این تابع به شرح زیر هستند:

• نرخ بهره اسمی: در این آرگومان مقدار نرخ بهره اسمی وارد می گردد که بایستی بین صفر و یک باشد.
• تعداد دوره: در این آرگومان تعداد دوره های مرکب شدن در سال وارد می گردد که بایستی عددی صحیح و مثبت باشد.

مثال 1:

در جدول و نمودار زیر نرخ بهره موثر با لحاظ دوره های پرداخت متفاوت محاسبه شده است که می توان مشاهده کرد؛

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

در این محاسبات npery نیز از فرمول زیر این گونه به دست آمده است:

npery = 365 / days

همانطور که در نمودار و محاسبات گفته شده مشاهده می کنید هر چقدر که دوره پرداخت بهره کوتاه تر باشد، نرخ بهره موثر بزرگ تری حاصل می شود. به طوری که اگر دوره پرداخت بهره روزانه باشد در این صورت نرخ بهره موثر 22.13 درصد محاسبه می شود که نسبت به نرخ بهره نرمال 20.00% در حدود 2.13% افزایش خواهد داشت.

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

به طور مثال: اگر مبلغ سپرده گذاری در بانک رقم 100،000،000 تومان باشد درصورتی که سپرده گذاری با نرخ بهره 20% و دوره پرداخت یک ساله باشد، سود متعلق به آن در انتهای سال 20،000،000 تومان خواهد بود و اگر سپرده گذاری با نرخ بهره 20% روزشمار (دوره پرداخت روزانه) باشد، سود متعلق به آن در انتهای سال 22.130،00 تومان خواهد بود.

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

حال اگر شرایط بازپرداخت وام به صورت مثلا ماهیانه توافق شده باشد، در طول دوره ساخت که سرمایه گذار بازپرداخت بهره وام ندارد، مطابق رویه ای که در بالا بدان اشاره شد جهت محاسبه کل بهره متعلقه در طول یک سال باید از نرخ نحوه محاسبه بهره مرکب در اکسل بهره موثر ماهیانه استفاده شود. لذا اگر نرخ بهره نرمال وام دریافتی 20% باشد، محاسبه بهره در انتهای دوره احداث با نرخ بهره موثر 21.94% خواهد بود. که در این حالت به نفع وام گیرنده نخواهد بود.

مثال 2:

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

خطاهای معمول:

• اگر مقدار نرخ بهره اسمی عددی بین صفر و یک نباشد، خطای !NUM# رخ می دهد. دقت داشته باشید که صفر می تواند شامل شود اما یک نه.
• اگر مقادیر وارد شده در تابع، عددی (اسکالر) نباشند، خطای !VALUE# رخ خواهد داد.

نگارش: ساناز یوسفی

به منظور کسب اطلاعات بیشتر، مطالعه مقاله جریان نقدی با تابع xnpv را به شما عزیزان پیشنهاد می نماییم.

نرخ هزینه فرصت اسمی و موثر در اکسل

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

تفاوت نرخ هزینه فرصت اسمی (درصد) و موثر سالانه

برای روشن شدن تفاوت این دو نرخ به مثال زیر توجه کنید:

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

با لحاظ پرداخت ماهانه:

در ماه اول سودی معادل با ۱٫۵ درصد (با لحاظ پرداخت ماهانه و تقسیم سود سالانه ۱۸ درصد بر ۱۲ ماه) بر روی سرمایه اولیه خود (۲۰۰ میلیون) به دست خواهید آورد. بنابراین در پایان ماه اول، سرمایه شما ۲۰۳ میلیون تومان خواهد شد.

در ماه دوم هم شما سودی معادل ۱٫۵ درصد بر سرمایه ابتدای ماه دوم که اکنون ۲۰۳ میلیون است، به دست خواهید آورد و در پایان ماه دوم سرمایه شما معادل ۲۰۶,۰۴۵,۰۰۰ تومان خواهد شد.

به همین منوال می توان مقدار سود و سرمایه را برای تمام ماه ها بدست آورد.

همان طور که در جدول زیر می بینید، رشد سرمایه شما در طی یک سال برابر با ۱۹٫۵۶ درصد است که این همان واقعی یا نرخ موثر سالانه (EAR) است. می بینیم که این رقم، بالاتر از نرخ اسمی سالانه ۱۸درصد است. دلیل این تفاوت این است که در هر دوره مالی بر روی کل سرمایه خود، که شامل اصل و سود حاصل از دوره های قبل است، مجددا سودی معادل ۱.۵درصد بدست می آورید. در واقع، نرخ موثر سالانه در برگیرنده اثرات تجمعی ثروت است.

نرخ سود اسمی سالانه را معمولا با APR که همان Annual Percentage Rate است، هم نشان می دهند.

با لحاظ پرداخت سه ماه یک بار

در یک سال، چهار دوره سه ماهه داریم. بنابراین نرخ سود دوره (سه ماهه) برابر است با ۴.۵ درصد (۱۸ درصد تقسیم بر ۴). همان گونه که ملاحظه می کنید، در این حالت نرخ بهره موثر سالانه برابر با ۱۹٫۲۵ درصد است.

با لحاظ پرداخت شش ماه یک بار

در یک سال، دو دوره شش ماهه داریم. بنابراین نرخ سود دوره (شش ماهه) برابر است با ۹ درصد (۱۸ درصد تقسیم بر ۲). در این حالت که تجمیع شش ماهه صورت می گیرد، نرخ بهره موثر سالانه برابر با ۱۸٫۸۱ درصد است.

نکته: هرچه تعداد دفعات تحقق پرداخت نقدی در یک سال کمتر شود، نرخ موثر سالانه و اسمی سالانه به یکدیگر نزدیک می شوند

با لحاظ پرداخت سالانه

در نهایت اگر جریانات نقد به صورت سالانه صورت پذیرند، این دو نرخ با یکدیگر برابر خواهند شد.

چگونه نرخ موثر سالانه (Effective Annual Rate) را محاسبه کنیم؟

فرمول محاسبه نرخ موثر سالانه (EAR) برابر است با:

توجه کنیم تعداد دفعاتی که سود در سال نحوه محاسبه بهره مرکب در اکسل مرکب میشود، معمولا ماهانه یا فصلی است. در جدول زیر تمام دوره های مالی ممکن در سال نشان داده شده است:

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

البته توجه کنیم بانک ها به طور معمول نرخ بهره اسمی سالانه را به جای نرخ بهره موثر تبلیغ می کنند.

تابع EFFECT

اگر نرخ بهره یا هزینه فرصت سالانه را داشته باشیم، برای بدست آوردن نرخ هزینه فرصت موثر سالانه در نرم افزار اکسل می توان از تابع EFFECT استفاده کرد. نحوه ورود اطلاعات مربوط به این تابع به صورت زیر است:

اولین ورودی مورد نیاز این تابع، نرخ بهره اسمی سالانه است.
دومین ورودی، تعداد دفعات تحقق جریان نقدی در یک سال (تعداد دوره در یک سال) است.

تابع NOMINAL

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

اولین ورودی این تابع، نرخ بهره موثر سالانه است.

دومین ورودی نیز تعداد دفعات تحقق جریان نقدی در طی سال را از ما سوال می کند.

برای مثال اگر نرخ هزینه فرصت موثر سالانه برابر با ۲۲ درصد باشد، نرخ هزینه فرصت دوره، در حالتی که جریانات نقدینگی به صورت هفتگی باشد، چه مقدار خواهد شد؟

برای حل این مثال ابتدا باید نرخ اسمی سالانه را محاسبه کنیم و سپس این نرخ را بر تعداد دفعات تحقق جریان نقدی در طی سال تقسیم کنیم. طبق مثال جریانات نقدی به صورت هفتگی یعنی ۵۲ نوبت در سال صورت می پذیرد.

امیدواریم این مطلب، راهنمای مفید و کاربردی برای درک مفاهیم نرخ اسمی و موثر و نحوه محاسبه آنها در اکسل برای شما باشد.

درباره الناز پورمحمد

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

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

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

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

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

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

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

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

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

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

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

مخفف:

Payment during Maturity Time

شرح اجزاء:

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

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

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

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

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

ملاحظات:

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

تابع 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: مقدار ارزش فعلی یا مبلغ تسهیلات دریافتی

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

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

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

مخفف:

Interest Payment during Maturity Time

شرح:

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

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

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

برای دریافت 5 ویدئو زیر بر روی درباره کاربرد توابع مالی در محاسبه قسط و وام در اکسل به صفحه زیر مراجعه کنید.