Day Count Convention (YEARFRAC)

Highlighted
Previous Contributor

Day Count Convention (YEARFRAC)

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

3 REPLIES 3
Highlighted
Regular Contributor

RE: Day Count Convention (YEARFRAC)

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
Highlighted
New Contributor

Re: RE: Day Count Convention (YEARFRAC)

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

 

Highlighted
New Contributor

Re: RE: Day Count Convention (YEARFRAC)

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