توابع XIRR, IRR و MIRR

14 مهر 1400

دقیقه

سؤال‌هایی که در این فصل به آنها پاسخ داده می‌شود: چگونه می‌توان نرخ بازده داخلی (IRR) یک جریان نقدی را به دست آورد؟ آیا یک پروژه همواره IRR یگانه‌ای دارد؟ آیا شرایطی هست که تضمین کند پروژه همواره نرخ بازده داخلی یگانه‌ای داشته باشد؟ اگر دو پروژه هرکدام یک نرخ بازده داخلی داشته باشند چگونه...

آخرین به‌روزرسانی: 19 بهمن 1401

سؤال‌هایی که در این فصل به آنها پاسخ داده می‌شود:

  • چگونه می‌توان نرخ بازده داخلی (IRR) یک جریان نقدی را به دست آورد؟
  • آیا یک پروژه همواره IRR یگانه‌ای دارد؟
  • آیا شرایطی هست که تضمین کند پروژه همواره نرخ بازده داخلی یگانه‌ای داشته باشد؟
  • اگر دو پروژه هرکدام یک نرخ بازده داخلی داشته باشند چگونه می‌توان از نرخ بازده پروژه استفاده نمود؟
  • چگونه می‌توان نرخ بازده داخلی جریان نقدی نامنظمی را پیدا کرد؟
  • MIRR چیست و چگونه می‌توان آن را محاسبه کرد؟

ارزش خالص فعلی (NPV) یک بخش از جریان نقدی به نرخ بهره (r) مورداستفاده بستگی دارد. مثلاً اگر جریان نقدی پروژه یک و دو (کاربرگ IRR در فایل IRR.Xlsx در تصویر 1-9) را بررسی کنید، درمی‌یابید که با مقدار r=0.2  پروژه دو دارای ارزش خالص فعلی بالاتری خواهد شد و با مقدار r=0.01 پروژه یک دارای ارزش خالص فعلی بالاتری خواهد شد. وقتی‌که از NPV برای رتبه‌بندی سرمایه‌گذاری‌ها استفاده می‌کنیم، خروجی ما می‌تواند به نرخ بهره بستگی داشته باشد. اشخاص ذاتاً دوست دارند همه چیز را در یک رقم خلاصه کنند. نرخ بازده داخلی (IRR) یک پروژه را خیلی ساده می‌توان نرخ بهره‌ای دانست که میزان NPV پروژه را برابر با صفر می‌کند. اگر پروژه‌ای IRR یگانه‌ای داشته باشد، آن IRR تفسیر بسیار جالبی خواهد داشت. مثلاً اگر پروژه‌ای IRR برابر با 15 داشته باشد، آن‌وقت بازده سالیانه 15 درصدی برای جریان‌ نقدی سرمایه‌گذاری شده دریافت خواهد شد. در مثال‌های این فصل می‌بینید که پروژه شماره یک دارای IRR برابر با 47.5 درصد است. یعنی سرمایه گذاری 400 دلاری شما در زمان یک 47.5 درصد  نرخ بازده داخلی تولید خواهد کرد. هرچند برخی اوقات ممکن است پروژه‌ای بیش از یکIRR داشته باشد و حتی اصلاً IRR نداشته باشد. در این موارد صحبت درباره میزانIRR‌ پروژه امری بی‌فایده است.

تصویر 1-9 مثال‌هایی از تابع IRR

پاسخ به سؤال‌های این فصل:

چگونه می‌توان نرخ بازده داخلی (IRR) یک جریان نقدی را به دست آورد؟

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

IRR(range of cash flows,[guess]) به شکلی که guess آرگومانی انتخابی است. اگر برای محاسبه IRR ، آرگومان guess (یا حدس ) را وارد نکنید، اکسل این تابع را با فرض بر آنکه مقدار آرگومان  guess  برابر با 10 درصد است محاسبه می‌کند و بعد مقدار تقریبی IRR را تغییر می‌دهد تا نرخ بهره‌ای را پیدا کند که مقدار NPV پروژه را مساوی صفر می‌کند،اگر اکسل نتواند مقداری را بیابد که NPV پروژه را صفر کند، خطای #NUM را به ما خواهد داد. در سلول شماره B5 از کاربرگIRR در فایلی بنام IRR.Xlsx فرمول IRR(C2:I2) را وارد کرده‌ایم تا مقدار IRR‌پروژه شماره یک را محاسبه کنیم. اکسل عدد 47.5 درصد را به ما می‌دهد؛ بنابراین اگر از نرخ بهره سالیانه‌ای برابر با 47.5 درصد استفاده شود، NPV پروژه شماره یک برابر با صفر خواهد بود. در مثالی مشابه می‌توان دید که پروژه شماره دو IRR برابر با 80.1 درصد دارد.

حتی اگر تابع IRR مقدار IRR را پیدا کند، یک پروژه ممکن است بیش از یک IRR  داشته باشد. برای بررسی اینکه آیا پروژه دارای بیش از یک IRR‌ است می‌توان میزان حدس اولیه IRR پروژه را تغییر داد.( مثلاً از 90 درصد به 90 درصد) در اینجا میزان حدس اولیه (guess) را برای پروژه شماره یک با کپی کردن فرمول IRR($C$2:$I$2,A8) در سلول B8 به محدوده B9:B17 تغییر می‌دهیم. از آنجا که تمامی حدس های IRR‌پروژه یک عدد 47.5 ‌را به ما می‌دهد، با اطمینان می‌توان گفت که پروژه شماره 1 مقدار IRR یگانه‌ای برابر با 47.5 درصد دارد. به همان شکل می‌توان اطمینان داشت که پروژه شماره 2 نیز IRR یگانه‌ای برابر با 80.1 درصد داراست.

آیا یک پروژه هموارهIRR یگانه‌ای دارد؟

در کاربرگ Multiple IRR در فایل IRR.Xlsx (تصویر 2-9) می‌توانید ببینید که پروژه شماره سه (دارای جریان نقدی 20، 82، 60- ، 2) دو IRR دارد. میزان guess  مربوط به IRR پروژه را با کپی‌کردن فرمول IRR($B$4:$E$4,B8) از سلول C8 به محدوده C9:C17 از 90 درصد به 90 درصد تغییر می‌دهیم.

تصویر 2-9 پروژه‌ای با بیش از یکIRR

توجه کنید وقتی مقدار guess  سی درصد یا کمتر است، مقدار IRR برابر9.6 – درصد می شود. برای مقادیر guess  ‌های دیگرIRR برابر 216.1 درصد است. پروژه شماره سه برای این دو نرخ بهره،NPV‌ برابر با صفر دارد.

در کاربرگ  No IRR در فایل IRR.xlsx (تصویر 3-9) می توان دید که هر حدسی برای   IRR‌ پروژه شماره چهار انتخاب شود،  پیام خطای #NUM را به دنبال خواهد داشت. این پیام نشان می‌دهد که پروژه شماره چهار دارای IRR نیست.

وقتی پروژه‌ای چندین IRR داشته باشد یا هیچ IRR نداشته باشد، مفهوم IRR عملاً تمامی معنی خود را از دست می‌دهد. هرچند با وجود این مشکل، بسیاری از شرکت‌ها از IRR به‌عنوان ابزار اساسی رتبه‌بندی سرمایه‌گذاری‌هایشان استفاده می‌کنند.

تصویر 3-9  پروژه‌های بدون IRR

آیا شرایطی هست که تضمین کند پروژه همواره نرخ بازده داخلی یگانه‌ای داشته باشد؟

اگر مواردی از جریان نقدی تنها حاوی یک تغییرعلامت (علامت مثبت و منفی) باشد، تضمین می‌شود که پروژه حتما دارای یک IRR است. مثلاً در پروژه شماره دو در سلول های C3:I3 از کاربرگ IRR علامت های جریان نقدی مورد نظر عبارتند از + + + + + – . می بینیم تنها یک تغییر علامت  وجود دارد( بین زمان یک و زمان دو) بنابر این پروژه شماره دو می بایست یک IRR یگانه داشته باشد. در پروژه شماره سه، در سلولهای B4:E4 از کاربرگ Multiple IRR علامت های جریان نقدی عبارتند از – + – +  . از آنجا که علامت جریان نقدی سه بار تغییر کرده است نمی توان تضمین داد کهIRR یگانه‌ای وجود داشته باشد. پروژه شماره چهار در سلول های B5:D5 از کاربرگ Multiple IRR، علامت های جریان نقدی عبارتند از + – +. از آنجا که علامت های جریان‌ نقدی دوبار تغییر کرده اند، برای این مورد نیز IRR یگانه‌ای تضمین نمی شود. بیشتر پروژه های وجوه سرمایه‌گذاری ( مثل ساخت یک مجموعه ساختمانی) با جریان نقدی منفی آغاز شده و بدنبال آن جریان نقدی مثبتی حاصل می گردد. بنابراین بیشتر پروژه های وجوه سرمایه‌گذاری دارای IRR یگانه‌ای هستند.

اگر دو پروژه هرکدام یک نرخ بازده داخلی داشته باشند چگونه می‌توان از نرخ بازده داخلی استفاده نمود؟

اگر پروژه‌ای دارای IRR یگانه‌ای باشد، می‌توان این را گفت که پروژه ارزش شرکت را بالا می‌برد آن هم تنها به این شرط که IRR پروژه از هزینه سالیانه سرمایه بیشتر باشد. مثلاً اگر هزینه سرمایه یک شرکت 15درصد باشد، پروژه یک و پروژه دو ارزش شرکت را بالا می‌برند.

فرض کنید دو پروژه در نظر گرفته‌اید (هر دو دارای IRR یگانه‌ای هستند) اما تنها می‌توانید حداقل یک پروژه را انجام دهید. باور اینکه می‌بایست پروژه‌ای با IRR بالاتر را انتخاب کنید، باور بسیار وسوسه‌کننده‌ای است. برای دیدن اینکه این باور چگونه می‌تواند به تصمیم‌های نادرستی منجر شود، نگاهی به تصویر 4-9 و کاربرگwhich Project در فایلIRR.xlsx بیندازید. پروژه شماره 5 مقدار IRR برابر با 40 درصد دارد و پروژه شماره 6 نیز IRR برابر با 50 درصد داراست. اگر پروژه ها را بر اساس میزان IRR رتبه‌بندی کرده و تنها بتوانید یک پروژه را انتخاب کنید، آن‌وقت پروژه شماره 6 را انتخاب خواهید کرد.هرچند بهتر است به خاطر داشته باشید که  NPV یک پروژه میزان ارزشی که پروژه به شرکت می افزاید را اندازه گیری می‌کند. مشخص است که پروژه شماره 5 ( با هر هزینه سرمایه ای) دارای NPV‌ بزرگ تر از پروژه شماره 6 است. بنابراین اگر قرار باشد که تنها یک پروژه انتخاب شود، آن پروژه شماره 5 خواهد بود. IRR بسیار دردسر ساز است چرا که مقیاس پروژه را در نظر نمی گیرد. با اینکه پروژه شماره 6 از نظر سرمایه‌گذاری دلاری بهتر از پروژه شماره 5 است، مقیاس وسیع تر پروژه شماره 5 آن را برای شرکت با ارزش تر از پروژه شماره 6 می‌کند. IRR مقیاس و وسعت یک پروژه را انعکاس نمی دهد درحالی که NPV این کار را می‌کند.

تصویر 4-9 IRR می‌تواند به انتخاب نادرست پروژه‌ای که قصد انجامش را داریم منجر شود.

چگونه می‌توان IRR جریان نقدی نامنظمی را به دست آورد؟
جریان‌های نقدی نه فقط در ابتدا و انتهای سال بلکه در تاریخ‌های حقیقی رخ می‌دهند. دستورزبان تابعXIRR عبارت است از: XIRR(cash flow, dates, [guess]). تابع XIRR موردی از جریان نقدی را که در هر دسته از تاریخ های نامنظمی رخ داده است را مشخص می نماید. در اینجا هم مثل تابع IRR آرگومانguess آرگومانی انتخابی است. برای دیدن مثالی از نحوه استفاده از تابع XIRR تصویر5-9 و کاربرگی بنام XIRR در فایل IRR.xlsx را بررسی کنید.


تصویر 5-9 مثالی از تابعXIRR

فرمول XIRR(F4:F7,E4:E7) در سلول D9  نشان می‌دهد که میزانIRR  پروژه شماره 7 48.69درصد است.

MIRR چیست و چگونه می‌توان آن را محاسبه کرد؟

در بسیاری از موقعیت‌ها نرخ وجوهی که شرکت به‌صورت وام دریافت می‌کند با نرخ وجوهی که به‌عنوان سرمایه‌گذاری مجدد دریافت می‌کند متفاوت است. محاسبات تابعIRR به طور ضمنی فرض را بر آن می گیرد که نرخ وام های شرکت و وجوه سرمایه‌گذاری مجدد آن شرکت برای IRR یکسان می باشند. اگر نرخ واقعی وجه وام گرفته شده و نرخ واقعی وجه سرمایه‌گذاری مجدد را بدانیم ، آن‌وقت تابع نرخ داخلی بازده تعدیل شده( MIRR) نرخ تخفیفی را محاسبه می‌کند که باعث می‌شود NPV همه موارد جریان نقدی( شامل بازپرداخت وام ها و سرمایه‌گذاری مجدد تمام عایدی ها با نرخ معین) برابر با صفرشود. دستور زبان تابعMIRR‌ عبارت است از MIRR(cash_flow_values,borrowing_rate,reinvestment_rate). نکته جالب در باره تابع MIRR آن است که مقدار آن همواره یگانه است. کاربرگMIRR  در فایل IRR.xls حاوی مثالی ازتابع MIRR‌ می باشد. ( تصویر6-9)  فرض کنید امروز 120000 دلار قرض کرده و  جریان های نقدی زیر کسب شده اند: سال اول 39000دلار، سال دوم30000دلار، سال سوم:21000دلار، سال چهارم37000دلار، سال پنجم: 46000دلار. فرض کنید که می‌توانید هر سال 10 درصد وام گرفته و هر سال 12 درصد سرمایه‌گذاری مجدد انجام دهید.

بعد از واردکردن این مقدارها در سلول‌های E7:E12  مقدار MIRR در سلول D15 از طریق فرمول MIRR(E7:E12,E3,E4)   به دست می‌آید؛ بنابراین MIRR این پروژه برابر با 12.61 در صد است. در سلولD16 مقدار واقعی IRR 13.07 درصد محاسبه شده است.

تصویر 6-9 مثالی از تابعMIRR

فایلی بنام MIRR.xlsx(تصویر 7-9) جزئیات محاسبه MIRR را  برای اذهان ریاضی پسند نشان می‌دهد. همان‌طور که نشان‌داده‌شده، جریان نقدی در زمان‌های صفر و یک منفی و در زمان‌های 2 و 3 مثبت هستند. ابتدا توجه کنید که اگر مقدار X‌ دلار در زمان صفر سرمایه‌گذاری کنید و  سرمایه‌گذاری شما مقدار Y‌ دلار در زمان 3 به شما بازگرداند، آنگاه میزان IRR‌ این سرمایه‌گذاری ساده برابر است با: (y/x)1/3 – 1. برای سرمایه‌گذاری X  را به‌عنوان ارزش فعلی (با استفاده از نرخ وام) جریان نقدی منفی اولین دوره محاسبه می‌کنیم و y را به‌عنوان ارزش آتی (استفاده از نرخ سرمایه‌گذاری مجدد) جریان نقدی ِمثبت آخرین دوره محاسبه می‌کنیم؛ بنابراین MIRR سرمایه‌گذاری برابر است با: (y/x)1/3 – 1

 

تصویر 7-9 محاسبه MIRR

این مطلب را با این نکته به پایان می‌رسانیم که اگر جریان نقدی‌ای خالی گذاشته شود، تابع MIRR هم جریان نقدی و هم‌دوره آن را نادیده خواهد گرفت. چنانچه جریان نقدی‌ای خالی گذاشته شود تابعIRR  پیغام خطای #NUM را به ما می‌دهد.

مسئله‌های این فصل:

مقدار IRR جریان نقدی زیر را محاسبه کنید.

پروژه‌ای را با جریان نقدی زیر در نظر بگیرید. مقدار IRR‌پروژه را مشخص کنید. اگر هزینه سرمایه سالیانه 20 درصد باشد ، آیا شما این پروژه راقبول می کنید؟

مقدار IRR های این پروژه را پیدا کنید.

IRR های پروژه‌ای با جریان‌های نقدی در تاریخ های فهرست شده را پیدا کنید.

دو پروژه زیر را بررسی کنید و فرض کنید هزینه سرمایه شرکت 15 درصد است. IRR و NPV آن را به دست آورید. کدام پروژه ارزش شرکت را بالا می‌برد؟ اگر قرار باشد شرکت تنها یک پروژه انتخاب کند، انتخاب آن کدام‌یک خواهد بود؟

مگ پرایور 25 ساله است و می‌خواهد در چهل سال آینده در آغاز هرسال 10000 دلار در صندوق بازنشستگی پس‌انداز کند. فرض کنید در طی هر سی‌سال آینده مگ 15 درصد از سرمایه‌گذاری‌اش کسب می‌کند و در طی ده سال پیش از بازنشستگی سرمایه‌گذاری‌اش 5 درصد بازده خواهد داشت. میزان IRR مرتبط با سرمایه‌گذاری او همچنین وضعیت نهایی سرمایه‌گذاری بازنشستگی‌اش را مشخص نمایید. چگونه درخواهید یافت که IRR این سرمایه‌گذاری یگانه است؟ این IRR یگانه را چگونه تفسیر می‌کنید؟

با ارائه دلایل توضیح دهید چرا پروژه شماره6 (در کاربرگWhich project در فایل xlsx) دارای IRR برابر با 50 درصد است؟

پروژه‌ای دارای جریان نقدی زیر را در نظر بگیرید:

سعی کنید مقدار IRR این پروژه را بدون حدس زدن به دست آورید. چه مشکلاتی پیش می‌آید؟   IRR‌ این پروژه چقدر است؟ آیا پروژه دارای IRR‌ یگانه‌ای است؟

در مورد جریان نقدی مسئله شماره یک فرض کنید که می‌توانید سالیانه 12 درصد وام‌گرفته و 15 درصد سود سرمایه‌گذاری کنید. حالا MIRR این پروژه را محاسبه نمایید.

فرض کنید امروز مبلغ 1000 دلار برای اوراق قرضه ای که در مسئله شماره 9 از فصل هشت ” ارزشیابی سرمایه‌گذاری با استفاده از معیار ارزش خالص فعلی” ذکر شده، پرداخته‌اید. میزان IRR این اوراق قرضه چقدر است؟IRR اوراق قرضه را اغلب بازده آن نیز می‌نامند.

اشتراک گذاری در شبکه های اجتماعی

مایکروسافت اکسل (Excel)

loader

لطفا شکبیا باشید...