نحوه محاسبه نرخ داخلی بازده (IRR) در اکسل

توسط Svetlana Cheusheva ، به روز شده در 15 مارس 2023

این آموزش نشان می دهد که چگونه می توان IRR یک پروژه را در اکسل با فرمول ها و ویژگی های جستجوی هدف محاسبه کرد. شما همچنین یاد خواهید گرفت که چگونه می توانید یک الگوی بازده داخلی ایجاد کنید تا تمام محاسبات IRR را به صورت خودکار انجام دهید.

هنگامی که نرخ بازده داخلی یک سرمایه گذاری پیشنهادی را می دانید ، ممکن است فکر کنید تمام آنچه را که برای ارزیابی آن نیاز دارید دارید - هرچه IRR بزرگتر باشد بهتر است. در عمل ، این ساده نیست. Microsoft Excel سه عملکرد مختلف را برای یافتن نرخ بازده داخلی ارائه می دهد و درک واقعی آنچه در واقع با IRR محاسبه می کنید بسیار مفید خواهد بود.

IRR چیست؟

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

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

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

IRR چه فاش می کند؟

در بودجه بندی سرمایه ، IRR به طور گسترده ای برای ارزیابی سودآوری یک سرمایه گذاری آینده نگر و رتبه بندی چندین پروژه استفاده می شود. اصل کلی به همین سادگی است: هرچه نرخ بازده داخلی بالاتر باشد ، پروژه جذاب تر است.

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

محدودیت های IRR

اگرچه IRR یک روش بسیار محبوب برای ارزیابی پروژه های سرمایه است ، اما دارای نقص های ذاتی است که ممکن است منجر به تصمیمات زیر حد شود. مشکلات اصلی IRR عبارتند از:

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

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

محاسبه IRR در اکسل

از آنجا که نرخ بازده داخلی نرخ تخفیف است که در آن ارزش خالص فعلی یک سری از جریان های نقدی برابر با صفر است ، محاسبه IRR براساس فرمول سنتی NPV است:

اگر با نماد جمع بندی خیلی آشنا نیستید ، ممکن است شکل گسترده فرمول IRR درک آسانتر باشد:

• CF₀- سرمایه گذاری اولیه (که توسط یک عدد منفی نشان داده شده است)
• CF₁، cf₂… CF_n- جریان های نقدی
• من - شماره دوره
• n - دوره های کل
• IRR - نرخ بازده داخلی

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

مثال: شما اکنون 1000 دلار سرمایه گذاری می کنید و در 2 سال آینده 500 دلار و 660 دلار پس می گیرید. چه نرخ تخفیف باعث می شود ارزش خالص فعلی صفر باشد؟

به عنوان اولین حدس ما ، بیایید نرخ 8 ٪ را امتحان کنیم:

• اکنون: pv = -1000 دلار
• سال 1: PV = 500 دلار / (1+0. 08) 1 = 462. 96 دلار
• سال 2: PV = 660 دلار / (1+0. 08) 2 = 565. 84 دلار

با افزودن این موارد ، NPV را برابر با 28. 81 دلار می گیریم:

اوه ، حتی نزدیک به 0 نیست. شاید حدس بهتر ، مثلاً 10 ٪ ، می تواند چیزها را تغییر دهد؟

• اکنون: pv = -1000 دلار
• سال 1: PV = 500 دلار / (1+0. 1) 1 = 454. 55 دلار
• سال 2: PV = 660 دلار / (1+0. 1) 2 = 545. 45 دلار
• NPV: -1000 + 454. 55 $ + 545. 45 $ = 0. 00 دلار

خودشه! با نرخ تخفیف 10 ٪ ، NPV دقیقاً 0 است. بنابراین ، IRR برای این سرمایه گذاری 10 ٪ است:

اینگونه است که نرخ داخلی بازده را به صورت دستی محاسبه می کنید. Microsoft Excel ، سایر برنامه های نرم افزاری و ماشین حساب های مختلف IRR آنلاین نیز به این روش آزمایش و خطا اعتماد دارند. اما بر خلاف انسان ، رایانه ها می توانند چندین تکرار را خیلی سریع انجام دهند.

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

Microsoft Excel 3 کارکرد برای یافتن نرخ بازده داخلی ارائه می دهد:

• IRR - متداول ترین کاربردی برای محاسبه نرخ داخلی بازده برای یک سری از جریان های نقدی که در فواصل منظم رخ می دهد.
• XIRR - IRR را برای یک سری جریان نقدی که در فواصل نامنظم رخ می دهد ، پیدا می کند. از آنجا که تاریخ دقیق پرداخت ها را در نظر می گیرد ، این عملکرد دقت محاسبه بهتری را ارائه می دهد.
• MIRR - نرخ بازده داخلی اصلاح شده را باز می گرداند ، که نوعی IRR است که هم هزینه وام و هم سود مرکب دریافت شده در سرمایه گذاری مجدد جریان های نقدی مثبت را در نظر می گیرد.

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

فرمول IRR برای محاسبه نرخ بازده داخلی

فرض کنید شما در حال سرمایه گذاری 5 ساله با جریان نقدی در B2: B7 هستید. برای استفاده از IRR ، از این فرمول ساده استفاده کنید:

= IRR (B2: B7)

توجه داشته باشید. برای اینکه فرمول IRR به درستی کار کند ، لطفاً اطمینان حاصل کنید که جریان نقدی شما حداقل یک منفی (خروجی) و یک مقدار مثبت (جریان) دارد و تمام مقادیر به ترتیب زمانی ذکر شده است.

برای اطلاعات بیشتر ، لطفاً به عملکرد Excel IRR مراجعه کنید.

فرمول XIRR برای یافتن IRR برای جریان های نقدی نامنظم

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

با جریان نقدی در B2: B7 و تاریخ آنها در C2: C7 ، فرمول به شرح زیر است:

= XIRR (B2: B7 ، C2: C7)

• اگرچه عملکرد XIRR لزوماً به ترتیب زمانی نیازی به تاریخ ندارد ، اما تاریخ اولین جریان نقدی (سرمایه گذاری اولیه) باید ابتدا در آرایه باشد.
• تاریخ ها باید به عنوان تاریخ های معتبر اکسل ارائه شوند. تهیه تاریخ در قالب متن ، اکسل را در معرض خطر سوء تفسیر آنها قرار می دهد.
• عملکرد اکسل XIRR از فرمول متفاوتی برای رسیدن به نتیجه استفاده می کند. فرمول XIRR پرداخت های بعدی را بر اساس یک سال 365 روز تخفیف می دهد ، در نتیجه ، XIRR همیشه نرخ بازده داخلی سالانه را برمی گرداند.

برای اطلاعات بیشتر ، لطفاً به عملکرد Excel XIRR مراجعه کنید.

فرمول آینه ای برای استفاده از IRR اصلاح شده

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

جایی که B2: B7 جریان نقدی است ، E1 نرخ مالی (هزینه وام گرفتن پول) و E2 نرخ سرمایه گذاری مجدد (بهره دریافت شده در سرمایه گذاری مجدد درآمد) است.

توجه داشته باشید. از آنجا که عملکرد آینه اکسل علاقه مرکب را بر سود محاسبه می کند ، نتیجه آن ممکن است از نظر عملکردهای IRR و XIRR متفاوت باشد.

IRR ، XIRR و MIRR - کدام یک بهتر است؟

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

به طور کلی ، در نظر گرفته می شود که:

• XIRR دقت محاسبه بهتری نسبت به IRR ارائه می دهد زیرا تاریخ دقیق جریان های نقدی را در نظر می گیرد.
• IRR غالباً ارزیابی بی خوشی از سودآوری پروژه را ارائه می دهد ، در حالی که MIRR تصویری واقع بینانه تر می دهد.

ماشین حساب IRR - الگوی اکسل

اگر به طور منظم نیاز به محاسبه IRR در اکسل دارید ، تنظیم نرخ داخلی بازده می تواند زندگی شما را بسیار ساده تر کند.

ماشین حساب ما شامل هر سه فرمول (IRR ، XIRR و MIRR) خواهد بود تا دیگر نگران نباشید که نتیجه آن معتبرتر است اما می توانید همه آنها را در نظر بگیرید.

1. جریان و تاریخ نقدی را در دو ستون وارد کنید (A و B در مورد ما).
2. در 2 سلول جداگانه ، نرخ مالی و میزان سرمایه گذاری مجدد را وارد کنید. به صورت اختیاری ، این فروش را به ترتیب Finance_Rate و Revest_Rate نامگذاری کنید.
3. دو محدوده تعریف شده پویا با نام Cash_flows و Dates ایجاد کنید. با فرض اینکه برگه شما با نام Sheet1 نامگذاری شده است ، اولین جریان نقدی (سرمایه گذاری اولیه) در سلول A2 است و تاریخ اولین جریان نقدی در سلول B2 است ، محدوده های نامگذاری شده را بر اساس این فرمول ها قرار دهید: Cash_flows: = Offset (Sheett1! $ $ $ $! 2،0،0 دلار ، تعداد (برگه 1! $ a: $ a) ، 1) تاریخ: = افست (برگه 1! $ 2،0،0 $ ، تعداد (برگه 1! $ b: $ b) ، 1) جزئیاتمراحل را می توان در نحوه ایجاد یک محدوده پویا به نام در اکسل یافت.
4. از نام هایی که به تازگی ایجاد کرده اید به عنوان آرگومان فرمول های زیر استفاده کنید. لطفاً توجه داشته باشید که فرمول ها می توانند در هر ستون دیگری غیر از A و B ، که به ترتیب منحصراً برای جریان نقدی و تاریخ ها محفوظ هستند ، وارد شوند.= IRR (Cash_flows) = XIRR (Cash_flows ، Dates) = MIRR (Cash_flows ، Financial_rate ، reinvest_rate)

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

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

= ifError (آینه (cash_flows ، finance_rate ، reinvest_rate) ، "")

لطفاً بخاطر داشته باشید که اگر سلولهای Finance_Rate و/یا reinvest_rate خالی باشند ، عملکرد Excel Mirm فرض می کند که آنها برابر با صفر هستند.

چگونه می توان IRR را در اکسل با هدف جستجو انجام داد

عملکرد Excel IRR فقط 20 تکرار را برای رسیدن به سرعت انجام می دهد و XIRR 100 تکرار انجام می دهد. اگر بعد از آن بسیاری از تکرارها نتیجه ای دقیق در 0. 00001 ٪ پیدا نشود ، #Num! خطا بازگردانده می شود

اگر به دنبال دقت بیشتری برای محاسبه IRR خود هستید ، می توانید Excel را وادار کنید تا با استفاده از ویژگی هدف به دنبال ، بیش از 32،000 تکرار انجام دهید ، که بخشی از تجزیه و تحلیل است.

ایده این است که هدف به دنبال یافتن نرخ درصد باشد که NPV را با 0 برابر کند. در اینجا چگونه:

1. داده های منبع را از این طریق تنظیم کنید:
   • جریان نقدی را در یک ستون وارد کنید (B2: B7 در این مثال).
   • IRR مورد انتظار را در برخی از سلول ها قرار دهید (B9). ارزشی که وارد می کنید در واقع مهم نیست ، شما فقط باید چیزی را به فرمول NPV "تغذیه" کنید ، بنابراین فقط هر درصد را که به ذهن متبادر می شود ، بگویید.
   • فرمول NPV زیر را در سلول دیگری وارد کنید (B10): = NPV (B9 ، B3: B7)+B2

    

• On the Data tab, in the Forecast group, click What if Analysis>هدف جستجو
• در کادر گفتگوی Seek Seek ، سلول ها و مقادیر را برای آزمایش تعریف کنید:
  • تنظیم سلول - مرجع سلول NPV (B10).
  • به مقدار - نوع 0 ، که مقدار مورد نظر برای سلول تنظیم شده است.
  • با تغییر سلول - اشاره به سلول IRR (B9).

   

  پس از اتمام ، روی OK کلیک کنید.

• کادر گفتگوی وضعیت Seek Seek ظاهر می شود و به شما اطلاع می دهد که آیا راه حلی پیدا شده است. در صورت موفقیت ، مقدار موجود در سلول IRR با یک مورد جدید جایگزین می شود که NPV را صفر می کند. برای پذیرش مقدار جدید یا لغو برای بازگشت به نسخه اصلی ، OK را کلیک کنید.

به یک روش مشابه ، می توانید از ویژگی هدف Seek برای یافتن XIRR استفاده کنید. تنها تفاوت این است که شما نیاز به استفاده از فرمول XNPV به جای NPV دارید.

توجه داشته باشید. مقدار IRR که با هدف به دنبال آن است ، استاتیک است ، همانطور که فرمول ها به صورت پویا محاسبه نمی کنند. پس از هر تغییر در داده های اصلی ، برای دریافت یک IRR جدید باید مراحل فوق را تکرار کنید.

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

کتاب کار را برای بارگیری تمرین کنید

شما همچنین ممکن است به آن علاقه مند باشید

• عملکرد MIRR Excel برای دریافت نرخ بازده داخلی اصلاح شده
• عملکرد اکسل XIRR برای محاسبه نرخ بازده داخلی برای جریان نقدی غیر دوره ای
• چگونه NPV را در اکسل پیدا کنیم
• نحوه محاسبه CAGR در اکسل