Emulating Excel's YearFrac in C#

Question

How can I get the same results of the Excel YearFrac function in my C# application?

Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Answer 1

You can use Excel's functionality directly to calculate YearFrac. Microsoft says you are not supposed to use it, but it works very well. If you need a 100% compatibility with Excel, this solution is hard to beat. You need to add to your project a reference to Microsoft.Office.Interop.Excel in order for this code to compile.

static void Main() {
    var excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.WorksheetFunction wsf = excel.WorksheetFunction;
    var start = new DateTime(1999, 11, 1);
    var end = new DateTime(1999, 1, 11);
    for (var basis = 0; basis != 5; basis++) {
        Console.WriteLine(wsf.YearFrac(start, end, basis));
    }
}

Answer 2

Here is a good snippet.

The algorithm for the YearFrac function is in fact very complex. Maybe this article can provide you with more details.

Answer 3

May I suggest:

    public static double Yearfrac(DateTime startDate,DateTime endDate,DayCount daycount=DayCount.ActAct)
    {
        var nbDaysInPeriod = (double)(endDate - startDate).Days;

        switch(daycount)
        {
            case (DayCount.Act360):
                return nbDaysInPeriod / (double)360;
            case (DayCount.Act365):
                return nbDaysInPeriod / (double)365;
            case (DayCount.ActAct):
                return GetActAct(startDate,endDate);
            case (DayCount.Days360):
                var result = (endDate.Year - startDate.Year) * 360.0 + (endDate.Month - startDate.Month) * 30.0 + (Math.Min(endDate.Day, 30.0) - Math.Min(startDate.Day, 30.0));
                return result/360;
            default:
                return nbDaysInPeriod / (double)365;
        }
    }

    public static double GetActAct(DateTime startDate, DateTime endDate)
    {
        // Reproduce Excel Yearfrac as per http://www.dwheeler.com/yearfrac/excel-ooxml-yearfrac.pdf
        var nbDaysInPeriod = (double)(endDate - startDate).Days;
        if(startDate.Year==endDate.Year || (endDate.Year-1==startDate.Year&&(startDate.Month>endDate.Month||startDate.Month==endDate.Month&&(startDate.Day>=endDate.Day))))
        {
            var den =  365.0;
            if (startDate.Year == endDate.Year && DateTime.IsLeapYear(startDate.Year))
            {
                den++;
            }
            else
            {

                if (endDate.Day == 29 && endDate.Month == 2)
                {
                    den++;
                }
                else
                {
                    if (DateTime.IsLeapYear(startDate.Year))
                    {
                        var feb = new DateTime(startDate.Year, 2, 29);
                        if (startDate<=feb && feb<=endDate) den++;
                    }
                    else
                    {
                        if (DateTime.IsLeapYear(endDate.Year))
                        {
                            var feb = new DateTime(endDate.Year, 2, 29);
                            if (startDate <= feb && feb <= endDate) den++;
                        }
                    }
                }
            }
        }
        else
        {
            var nbYears = endDate.Year - startDate.Year+1;
            var den = nbYears * 365.0;
            for (var i=0;i<nbYears;i++)
            {
                if (DateTime.IsLeapYear(startDate.Year + i)) den++;
            }
            den /= nbYears;
            return nbDaysInPeriod / den;
        }
        return nbDaysInPeriod / 365.0;
    }

Answer 4

The signature of YEARFRAC is YEARFRAC(Date startDate, Date endDate, int convention). The method to calculate the YEARFRAC depends on the convention.

For convention = 2, YEARFRAC will calculate the YEARFRAC using ACT/360 method. An implementation of the ACT/360 can be found at svn.finmath.net, specifically DayCountConvention_ACT_360.java

For convention = 3, YEARFRAC will calculate the YEARFRAC using ACT/365 method. An implementation of the ACT/365 can be found at svn.finmath.net, specifically DayCountConvention_ACT_365.java

For convention = 4, YEARFRAC will calculate the YEARFRAC using 30E/360 method. An implementation of the 30E/360 can be found at svn.finmath.net, specifically DayCountConvention_30E_360.java

For convention = 1, the documentation claims that YEARFRAC is calculate using ACT/ACT convention. However, there are a multiple versions of ACT/ACT and I believe the standard for many financial products is ACT/ACT ISDA. I found that YEARFRAC differs by a small amount from the ACT/ACT IDSA convention! An implementation of the ACT/ACT IDSA can be found at DayCountConvention_ACT_ACT_ISDA.java

I haven't checked the other act/act versions yet, but I would not rely on an emulation of YEARFRAC ACT/ACT, when it is not clear what kind of method they implement...