تابع YEARFRAC در اکسل — محاسبه کسری سال بین دو تاریخ

تابع YEARFRAC در اکسل برای محاسبه نسبت یا کسری از یک سال بین دو تاریخ مشخص به کار می‌رود. این تابع در کاربردهای مالی، محاسبه بهره معوق، تخصیص هزینه‌ها به دوره‌ها و هر جایی که نیاز به تبدیل فاصله زمانی به «سال» داریم، بسیار مفید است.

فرم کلی و پارامترها

تابع به شکل زیر نوشته می‌شود:

=YEARFRAC(start_date, end_date, [basis])

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

• start_date: تاریخ شروع (قابل وارد کردن به‌صورت تاریخ، مرجع سلولی یا تابع DATE).
• end_date: تاریخ پایان.
• basis (اختیاری): روش محاسبه روزها و سال که می‌تواند مقادیری مثل 0 تا 4 بپذیرد. اگر وارد نشود، مقدار پیش‌فرض 0 خواهد بود.

گزینه‌های basis و معنی آن‌ها

مثال‌های عملی

برای اینکه نحوه کارکرد را ببینیم، چند مثال ساده بررسی می‌کنیم.

=YEARFRAC(DATE(2023,1,1), DATE(2023,12,31), 1)

خروجی: تقریباً 1.0 چون از اول ژانویه تا پایان دسامبر یک سال کامل است. استفاده از basis = 1 (Actual/Actual) عدد دقیق برابری می‌دهد.

=YEARFRAC(DATE(2023,1,1), DATE(2023,7,1), 1)

این فرمول کسری از سال بین 1 ژانویه تا 1 جولای 2023 را با مبنای Actual/Actual برمی‌گرداند (تقریباً 0.5). برای تبدیل به ماه کافی است حاصل را در 12 ضرب کنید.

=YEARFRAC(DATE(2023,2,1), DATE(2023,3,1), 0)

با basis = 0 (30/360 آمریکا) خروجی ممکن است با محاسبه روزهای واقعی متفاوت باشد، زیرا به هر ماه 30 روز فرضی اختصاص داده می‌شود. این رفتار برای محاسبه بهره‌هایی که مطابق قراردادهای 30/360 هستند لازم است.

مثال‌های کاربردی در مالی و حسابداری

• محاسبه بهره محاسبه‌نشده (accrued interest): با دانستن نرخ سالیانه، کسری سال را محاسبه و در نرخ ضرب می‌کنیم.
• تقسیم هزینه‌های سالانه بین دوره‌ها: هزینه‌ای که برای یک سال پرداخت شده را براساس حضور کارکنان یا استفاده از دارایی بین دوره‌های کوتاه‌تر تخصیص می‌دهیم.
• مقایسه قراردادها با مبانی متفاوت: بعضی قراردادها از Actual/360 استفاده می‌کنند و بعضی 30/360؛ YEARFRAC کمک می‌کند تاثیر مبنا را محاسبه کنیم.

نکات تخصصی و نکات ویرایشی

• اگر start_date بزرگ‌تر از end_date باشد، YEARFRAC مقدار منفی برمی‌گرداند. بنابراین برای محاسبه فاصله مطلق از ABS استفاده کنید.
• برای تبدیل کسری سال به ماه‌ها معمولاً حاصل را در 12 ضرب می‌کنند؛ برای دقت بیشتر در محاسبات مالی ممکن است از ROUND یا INT استفاده شود.
• برای اعداد کسری بسیار نزدیک به 1 یا 0 به دلیل نحوه نمایش اعشاری و مسائل شناور ممکن است لازم باشد از ROUND استفاده شود.
• تفاوت بین US و European 30/360 در پایان ماه و فوریه می‌تواند نتیجه نهایی را چند روز معادل تغییر دهد؛ بنابراین انتخاب basis باید مطابق قرارداد مالی یا استاندارد سازمانی باشد.

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

فرض کنید مبلغ اصل 100,000 و نرخ سالیانه 5% و بهره از 15 مارس 2024 تا 30 سپتامبر 2024 محاسبه شود. از Actual/365 استفاده می‌کنیم:

=100000 * 0.05 * YEARFRAC(DATE(2024,3,15), DATE(2024,9,30), 3)

این فرمول ابتدا کسری سال بین تاریخ‌ها را با مبنای Actual/365 محاسبه و سپس بهره متناسب را برمی‌گرداند. در عمل باید مقدار حاصل را برای گزارشگری یا فیش پرداختی گرد کنید.

استفاده در VBA

Dim yf As Double
yf = Application.WorksheetFunction.YearFrac(DateSerial(2024,3,15), DateSerial(2024,9,30), 1)

در این مثال VBA مقدار کسری سال را با مبنای Actual/Actual محاسبه کرده و در متغیر yf قرار می‌دهد. برای ادامه محاسبات مالی می‌توان از yf استفاده کرد.

خطاها و محدودیت‌ها

• اگر تاریخ‌ها به‌درستی وارد نشوند (متن نامعتبر) تابع خطا برمی‌گرداند.
• در برخی سناریوها ممکن است به دلیل تفاوت میان سیستم تاریخ‌گذاری (1900 vs 1904 در مک) نتایج متفاوت شود؛ حواستان به تنظیمات فایل اکسل باشد.
• YEARFRAC برای محاسبات دقیق حقوقی یا قراردادی باید مطابق مستندات قرارداد انتخاب basis شود؛ اختلاف چند روز ممکن است پیامد مالی داشته باشد.

جمع‌بندی و توصیه‌ها

تابع YEARFRAC ابزار ساده و در عین حال قدرتمندی برای تبدیل فاصله بین دو تاریخ به جزءی از سال است. برای کاربردهای مالی دقت کنید که basis مناسب را انتخاب کنید و برای گزارشات نهایی از ROUND استفاده کنید. در نهایت، آگاهی از قواعد 30/360 (آمریکایی vs اروپایی) و تأثیر سال کبیسه در مبنای Actual/Actual از اهمیت بالایی برخوردار است.

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

ارسال