تابع XIRR در اکسل — محاسبه نرخ بازده داخلی برای جریان‌های نقدی نامنظم

تابع XIRR در اکسل ابزاری قدرتمند برای محاسبه نرخ بازده داخلی (IRR) زمانی است که جریان‌های نقدی در فواصل زمانی نامنظم رخ می‌دهند. برخلاف تابع IRR که فرض می‌کند پرداخت‌ها و دریافت‌ها به فواصل مساوی (مثلاً هر ماه یا هر سال) اتفاق می‌افتند، XIRR با استفاده از تاریخ‌های واقعی، بازده سالانه معادل را محاسبه می‌کند.

فرمت و ورودی‌ها

سینتکس تابع XIRR به صورت زیر است:

=XIRR(values, dates, [guess])

توضیح پارامترها:

• values: آرایه یا محدوده مقادیر نقدی (پرداخت‌ها منفی، دریافت‌ها مثبت).
• dates: آرایه یا محدوده تاریخ‌های متناظر با هر مقدار در values.
• guess (اختیاری): حدس اولیه برای نرخ (مثلاً 0.1 یعنی 10%). اگر خالی باشد اکسل مقدار پیش‌فرض را استفاده می‌کند.

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

• تعداد عناصر در ranges مقادیر و تاریخ‌ها باید برابر باشد.
• حداقل یک مقدار منفی (سرمایه‌گذاری) و یک مقدار مثبت (بازگشت) لازم است.
• تاریخ‌ها باید تاریخ‌های معتبر اکسل باشند؛ رشته‌های متنی یا تاریخ‌های نادرست باعث خطای #VALUE! می‌شوند.
• XIRR نرخ سالیانه بازده را برمی‌گرداند؛ برای تبدیل به نرخ ماهانه از تبدیل مرکب استفاده کنید.

مثال عملی با جدول نمونه

فرض کنید مجموعه‌ای از تراکنش‌های سرمایه‌گذاری با تاریخ‌های نامنظم داریم:

برای محاسبه نرخ بازده داخلی سالیانه این جریان‌ها، در سلولی فرمول زیر را وارد کنید:

=XIRR(B2:B5, A2:A5)

در اینجا فرض شده ستون A تاریخ‌ها و ستون B مقادیر نقدی را در بر دارند. نتیجه عددی که اکسل برمی‌گرداند، نرخ بازده سالیانه است (مثلاً 0.03 به معنای 3٪).

استفاده از آرگومان guess

در برخی موارد تابع ممکن است برای پیدا کردن جواب نیاز به حدس اولیه داشته باشد یا به جواب محلی (Local solution) برسد. می‌توانید مقدار guess را به صورت زیر مشخص کنید:

=XIRR(B2:B5, A2:A5, 0.1)

این فرمول با فرض حدس اولیه 10٪ جستجو را شروع می‌کند. تغییر guess ممکن است به یافتن راه‌حل درست در موارد چندجوابه کمک کند.

تفاوت XIRR با IRR

• IRR فرض فواصل مساوی زمانی بین جریان‌ها را دارد (مثلاً هر ماه یا هر سال).
• XIRR بر اساس تاریخ‌های واقعی هر جریان محاسبه می‌کند و مناسب سرمایه‌گذاری‌هایی با واریانس زمانی بالاست (سرمایه‌گذاری‌های خصوصی، بوت‌کمپ‌ها، بازگشت‌های میان‌دوره‌ای غیر یکنواخت).

موارد خطا و رفع آنها

• #NUM!: معمولاً وقتی که تابع نتواند نرخ را پیدا کند یا مقادیر مناسب (حداقل یک مثبت و یک منفی) وجود نداشته باشد. مطمئن شوید که حداقل یک جریان منفی و یکی مثبت دارید و در صورت نیاز guess را تغییر دهید.
• #VALUE!: مقادیر تاریخ یا مقادیر نقدی نادرست هستند؛ مطمئن شوید تاریخ‌ها به شکل تاریخ اکسل وارد شده‌اند.
• ترتیب تاریخ‌ها می‌تواند نامنظم باشد؛ XIRR ترتیب را خودِ تابع مدیریت می‌کند ولی بهتر است داده‌ها را از قدیمی‌ترین به جدیدترین مرتب کنید تا خواناتر باشد.

استفاده‌های پیشرفته و نکات تخصصی

• برای مقایسه بازده سرمایه‌گذاری‌هایی با زمان‌بندی متفاوت نقدینگی، XIRR معیار دقیق‌تری نسبت به IRR یا نرخ‌های ساده فراهم می‌کند.
• برای تبدیل نرخ سالانه XIRR به نرخ ماهانه می‌توانید از تبدیل مرکب استفاده کنید:

=(1 + XIRR(B2:B5, A2:A5))^(1/12) - 1

این فرمول نرخ معادل ماهانه را به شما می‌دهد. توضیح: ابتدا نرخ سالیانه +1 گرفته می‌شود، ریشه دوازدهم استخراج می‌شود تا نرخ ماهانه به دست آید، سپس 1 کم می‌شود.

• در مدل‌های جریان‌های متعدد (مثلاً صندوق‌های سرمایه‌گذاری مشترک) از XIRR برای محاسبه بازده واقعی سرمایه‌گذار استفاده کنید، نه از بازده حسابداری ساده.
• در صورتی که داده‌های بسیار زیادی دارید و محاسبات کند می‌شود، می‌توانید حدود‌های اولیه (guess) مناسب قرار دهید تا همگرایی سریع‌تر شود یا از ابزار Solver برای اهداف بهینه‌سازی استفاده کنید.

مثال کاربردی در تحلیل سرمایه‌گذاری

فرض کنید شما چند واریز و برداشت در طول زمان در یک پروژه انجام داده‌اید. XIRR به شما می‌گوید که در صورت احتساب زمان وقوع هر جریان، سرمایه‌گذاری شما به طور متوسط سالیانه چقدر بازده داشته است. این برای تصمیم‌گیری درباره پذیرش یا رد پروژه، مقایسه با هزینه سرمایه (WACC) یا رتبه‌بندی پروژه‌ها بسیار کاربردی است.

چک‌لیست قبل از محاسبه

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

خلاصه

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

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

ارسال