Day Count Convention (YEARFRAC)

PrevContributor

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

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

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 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

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