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