«تابع IRR در اکسل» (Internal Rate of Return) یکی از توابع مالی است که از آن برای محاسبه نرخ بازده داخلی یک جریان نقدی مشخص استفاده می‌کنیم. برای انجام محاسبه با داشتن مبلغ سرمایه‌گذاری اولیه و مقادیر درآمد خالص می‌توانیم به‌راحتی نرخ بازده داخلی سرمایه‌گذاری را با این تابع به‌دست آوریم. مدیران کسب و کارها و سرمایه‌گذاران از تابع IRR برای مقایسه و رتبه‌بندی میزان سودآوری چند پروژه‌ سرمایه‌گذاری مختلف استفاده می‌کنند. برای آشنایی با نحوه استفاده از تابع IRR در این مطلب از مجله فرادرس نحوه فرمول‌نویسی با آن را همراه دو مثال یاد می‌گیریم.

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

ساختار کلی فرمول تابع IRR در اکسل صرف‌نظر از نوع پروژه یا سرمایه گذاری به شرح زیر است.

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

• values  (اجباری): آرایه‌ای از مجموعه جریان‌های نقدی شامل مبلغ سرمایه‌گذاری اولیه و مقدار درآمد یا بازده خالص در جدول اکسل
• guess ( اختیاری): مقدار حدس اولیه برای نرخ بازده داخلی

نکات مهم در ساختار فرمول تابع IRR

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

• در صورتی‌که مقدار آرگومان guess را در فرمول وارد نکنیم، اکسل به‌صورت پیش‌فرض آن را معادل «۱۰٪» در نظر می‌گیرد.
• برای انجام محاسبه، لازم است آرگومان values حداقل شامل یک مقدار مثبت (جریان نقدی ورودی) و یک مقدار منفی (جریان نقدی خروجی) باشد.
• تابع IRR مقادیر جریان نقدی وارد شده در جدول را با توجه به ترتیب قرارگیری آن‌ها تحلیل می‌کند. بنابراین لازم است پرداخت‌ها یا دریافت‌های مالی آرگومان values را به ترتیب زمانی آن در جدول وارد کنیم.
• اگر مقادیر وارد شده در جدول، سلول خالی، عبارت متنی و مقادیر منطقی «True» یا «False» باشند، این موارد از محاسبه حذف می‌شوند.

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

مثال محاسبه نرخ بازده داخل با تابع IRR در اکسل

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

مثال اول: محاسبه ساده نرخ بازده داخلی با تابع IRR

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

با توجه به مقادیر جدول فرمول محاسبه نرخ بازده داخلی =IRR(B3:B13) خواهد بود. که بعد از نوشتن آن در سلول دلخواه، نتیجه به شکل تصویر زیر خواهد بود. مثبت بودن مقدار IRR نشان‌دهنده سودآور بودن آن است.

برای درک بهتر تحلیل جریان‌های نقدی و بررسی مثال‌های بیشتر پیشنهاد می‌کنیم فیلم آموزش رایگان توابع مالی NPV و IRR در اکسل از فرادرس را مشاهده کنید.

مثال دوم: محاسبه نرخ رشد مرکب سالانه با تابع IRR

«نرخ رشد مرکب سالانه» (CAGR | Compound Annual Growth Rate) میانگین سرعت رشد سالانه یک سرمایه‌گذاری در یک دوره زمانی است. این شاخص برای محاسبه بازده سرمایه‌گذاری‌هایی به کار می‌رود که ارزش پروژه در طول زمان نوسان دارد. CAGR معیار مناسبی برای تعیین مقدار ریسک سرمایه‌گذاری نیست، اما برای مقایسه عملکرد سرمایه‌گذاری‌های مختلف به‌خصوص در حوزه سهام و تحلیل رشد درآمد، سود یا ارزش شرکت در چند سال بسیار کاربرد دارد.

لازم است توجه داشته باشیم که مقدار CAGR را نمی‌توانیم به‌صورت مستقیم با تابع IRR محاسبه کنیم و نیاز به تغییراتی در داده‌ها داریم. به‌عنوان مثال جدول زیر میزان درآمد حاصل از یک سرمایه‌گذاری در سال‌های مختلف است.

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

در این مرحله با نوشتن فرمول =IRR(B3:B9) مقدار نرخ رشد مرکب سالانه برابر ۱۲٪ محاسبه می‌شود.

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

=(سرمایه‌گذاری اولیه-ارزش نهایی سرمایه‌گذاری)^(تعداد دوره سرمایه‌گذاری/۱)-۱

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

البته در نرم‌افزار اکسل تابع XIRR و تابع MIRR هم برای محاسبه نرخ بازده داخلی استفاده می‌شوند که نکات تکمیلی در این خصوص را در مطلب زیر از مجله فرادرس توضیح داده‌ایم.

یادگیری فرمول‌نویسی با سایر توابع مالی اکسل در فرادرس

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

اما در گام اول فیلم‌های منتخب آموزشی زیر پیشنهاد اصلی برای شروع است.

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

رایج‌ترین پیام خطا در فرمول‌نویسی با تابع IRR در اکسل

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

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

• مقادیر جریان‌های نقدی یا آرگومان value حداقل شامل یک مقدار منفی و یک مقدار نیست.
• تابع IRR هنگام محاسبه بعد از ۲۰ بار تکرار نتواند نرخی را پیدا کند که نتیجه قابل قبول برای محاسبه باشد، به این نتیجه می‌رسد که حل عددی وجود ندارد یا بسیار پیچیده است. این حالت نیز زمانی اتفاق می‌افتد که به عنوان مثال همه مقادیر جریان نقدی یا مثبت و یا منفی هستند. همچنین اگر الگوی جریان نقدی به شکل چند سرمایه‌گذاری و چند برداشت در طول مدت زمان انجام پروژه باشد، اکسل دچار اشتباه می‌شود. البته اگر مقدار آرگومان guess نیز فاصله بسیاری از جواب واقعی داشته باشد، اکسل در ۲۰ بار تکرار به نتیجه نمی‌رسد و این پیام را به عنوان خروجی نمایش می‌دهد.

جمع بندی پایانی

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