Day Count Convention (YEARFRAC)

PrevContributor
PrevContributor
in Modeling

Hi, has anyone found an alternative to Excel's YEARFRAC-Formula? The formula has various day count conventions such as Actual/Actual, US 30/360, Actual/360, etc. I am trying to get the year fraction for different time periods but it's quite complicated in Anaplan. Best, Evgenij

Tagged:

Answers

  • peter_mcanena
    peter_mcanena
    It should be easy enough, depending on what method you want to use. The simplest method is
    (End Date - Start Date + 1) / 365.25
    which should be accurate enough for most purposes.

    Alternatively, if you want to be accurate to the day:
    Year(End Date) - Year(Start Date) + (End Date - Date(Year(End Date), Month(Start Date), Day(Start Date)) + 1)
                                      / IF MOD(Year(End Date), 4) = 0 THEN 366 ELSE 365
  • Paul_Petersen
    Paul_Petersen

    I have the same requirement/problem as Evgenij.  It is a little more complicated than what Peter posted; those calcs are pretty accurate but when converting Excel models to Anaplan the finance types like to use the optional "basis" parameter to the YEARFRAC function, as shown here:

    YEARFRAC Help.JPG

    YEARFRAC calculates the portion of the year slightly differently for each of these different "basis" parameter values.  I was hoping to find someone who had figured out how to do this in Anaplan, but it looks like I might have to blaze that trail...  After I confirm with my client that this is essential, I will spend the time to figure it out and post my solution.

     

    Thanks,

     

    Paul

     

  • david.harding
    david.harding

    Please note that YEARFRAC is now available natively in Anaplan. This was released in May 2019. Please see: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/YEARFRAC.htm

Categories