محاسبه بهره مرکب در اکسل – توضیح فرمول + مثال

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

بهره مرکب چیست؟

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

این موضوع را با یک مثال مورد بررسی قرار می‌دهیم:

فرض کنید می‌خواهید مبلغ ۱٬۰۰۰٬۰۰۰ تومان را با نرخ ۵٪ بهره مرکب سالانه سرمایه‌گذاری کنید. در این حالت شما در سال اول مبلغ ۵۰٬۰۰۰ تومان سود دریافت می‌کنید و سرمایه اصلی شما به مبلغ ۱٬۰۵۰٬۰۰۰ تومان افزایش می‌یابد. در سال بعد، با توجه به این‌که برای سرمایه‌گذاری سود مرکب در نظر گرفته شده و هیچ مبلغی هم از سرمایه اصلی برداشت نکرده باشید، ۵٪ بهره به کل ۱٬۰۵۰٬۰۰۰ تومان تعلق می‌گیرد.

با این حساب سود سال دوم معادل ۵۲٬۵۰۰ تومان بوده و سرمایه اصلی به ۱٬۱۰۲٬۵۰۰ تومان می‌رسد. روند مذکور برای سال‌های بعد هم تکرار شده و در سال سوم، بهره ۵ درصدی به ۱٬۱۰۲٬۵۰۰ تومان تعلق گرفته و به همین ترتیب، سالانه رقم سرمایه اصلی به‌صورت نمایی افزایش می‌یابد.

محاسبه با استفاده از فرمول در اکسل

حالا که با مفهوم بهره مرکب آشنا شدید، به نحوه محاسبه آن با استفاده از فرمول در اکسل می‌پردازیم.

$$P^{'} = (1+\frac{R}{N})^{(NT)}$$

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

1=P(1+R/N)^NT

• 'P: ارزش آینده یا همان مبلغ ناخالص (شامل سرمایه اصلی و سود)
• P: سرمایه اصلی
• R: نرخ بهره
• N: دفعات محاسبه بهره در سال
• T: تعداد سال‌های سرمایه‌گذاری

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

کلیک کنید

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

1. ابتدا جدولی از مقادیر فرمول را در اکسل ایجاد کنید:

2. فرمول محاسبه بهره مرکب را در سلول B5 وارد کنید (در ابتدای فرمول علامت = استفاده می‌شود).

3. در این مثال، سرمایه اصلی در سلول B1، نرخ بهره در سلول B2، دفعات محاسبه بهره در سال در سلول B3 و تعداد سال‌های سرمایه‌گذاری در سلول B4 درج شده‌اند. بنابراین، فرمول نهایی به این شکل خواهد بود:

4. پس از نوشتن فرمول در سلول موردنظر، دکمه Enter کیبورد را می‌زنیم. نتیجه حاصل، مبلغ ناخالص است که در واقع همان ارزش آینده سرمایه اولیه پس از ۵ سال را نشان می‌دهد.

حالا شما محاسبه‌گر بهره مرکب خود را ساخته‌اید و می‌توانید تنها با تغییر دادن مقادیر ستون B، ارزش آینده سرمایه‌گذاری را برای سال‌های مختلف با بهره‌های متفاوت محاسبه کنید.

محاسبه با استفاده از تابع FV

روش بعدی برای محاسبه نرخ مرکب، استفاده از تابع FV است که به شکل زیر نوشته می‌شود:

1=FV(rate,nper,pmt,[pv],[type])

• rate: نرخ بهره
• nper: تعداد سال‌های سرمایه‌گذاری
• pmt: پرداختی ثابت به ازای هر دوره؛ این مقدار، یک مبلغ ثابت است که به ازای هر دوره به اصل سرمایه اضافه می‌شود
• pv: سرمایه اصلی
• type: برای نشان دادن زمان محاسبه در اول یا آخر دوره است؛ عدد ۰ برای محاسبه در انتهای هر دوره و عدد ۱ برای محاسبه در ابتدای هر دوره

حالا از این تابع طی مراحل زیر برای محاسبه مثال قبل استفاده می‌کنیم:

1. در اکسل عبارت =FV    را نوشته و پرانتز باز کنید (می‌توانید پس از تایپ عنوان تابع، دکمه Tab کیبورد را بزنید تا به‌صورت خودکار پرانتز باز شود).
2. برای مقدار rate، که همان نرخ بهره است، رقم ۵٪ را وارد کنید. دقت داشته باشید که این مقدار را حتما به شکل درصد یا اعشار (0.05) وارد کنید. پس از وارد کردن هر مقدار، باید یک کاما (,) انگلیسی تایپ کرده و بدون فاصله مقدار بعدی را وارد کنید.
3. از آنجا که در مثال، تعداد سال‌های سرمایه‌گذاری ۵ سال بود، برای مقدار nper عدد ۵ را وارد کنید.
4. در مثال هیچ مبلغ پرداختی اضافه‌ای در نظر گرفته نشده، به همین خاطر مقدار pmt را ۰ قرار دهید.
5. سرمایه اصلی ۱٬۰۰۰٬۰۰۰ تومان بوده پس pv هم رقم ۱۰۰۰۰۰۰ خواهد بود.
6. در نهایت از آنجا که می‌خواهیم ارزش آینده سرمایه‌گذاری را در انتهای دوره محاسبه کنیم، برای type عدد ۰ را وارد کنید.
7. حالا پرانتز را ببندید و دکمه Enter را بزنید (می‌توانید دو بار دکمه Tab کیبورد را فشار دهید). ارزش آینده سرمایه‌گذاری اولیه پس از پنج سال محاسبه شده است:

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

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

$$P^{'} = (1+\frac{R}{N})^{(NT)}$$

در این فرمول، N نشان دهنده تعداد دفعاتی است که باید در یک سال بهره مرکب لحاظ شود. در نتیجه اگر بخواهیم بهره را به‌صورت ماهانه محاسبه کنیم (درون‌سالی)، برای این مقدار عدد ۱۲ را در نظر می‌گیریم. همچنین برای محاسبه بهره روزانه، عدد ۳۶۵ در نظر گرفته می‌شود. با توجه به این توضیحات، بهره مرکب ماهانه برای مثال قبل، به شکل زیر محاسبه می‌شود:

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

کلیک کنید

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

یکی از توابع پرکاربرد اکسل تابع EFFECT است که می‌توانیم از آن برای محاسبه بهره مرکب درون‌سالی استفاده کنیم. شکل تابع در اکسل به این صورت است:

1=P+(P*EFFECT(EFFECT(R,N)*T,T))

• P: سرمایه اصلی
• R: نرخ بهره
• N: دفعات محاسبه بهره در سال
• T: تعداد سال‌های سرمایه‌گذاری

با توجه به مثال قبلی، محاسبه بهره مرکب با استفاده از تابع EFFECT در این گام‌ها انجام می‌شود:

1. سرمایه اصلی ۱٬۰۰۰٬۰۰۰ تومان بود پس P معادل ۱۰۰۰۰۰۰ است.
2. نرخ بهره ۵ درصد بود در نتیجه R هم ۵٪ است.
3. از آنجایی که در حال محاسبه بهره درون‌سالی ماهانه هستیم، برای N عدد ۱۲ را در نظر می‌گیریم.
4. در نهایت محاسبات برای ۵ سال انجام می‌شود پس T هم برابر با ۵ است.
5. با زدن دکمه Enter، نتیجه نمایش داده خواهد شد:

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