YEARFRAC

Definition of YEARFRAC

Returns the number of years, including fractional years, between two dates using a specified day count convention.

Sample Usage

YEARFRAC(DATE(1969,7,16),DATE(1969,7,24),1)

YEARFRAC(A2,A3)

Syntax

YEARFRAC(start_date, end_date, [day_count_convention])

• start_date - The start date to consider in the calculation. Must be a reference to a cell containing a date, a function returning a date type, or a number.
• end_date - The end date to consider in the calculation. Must be a reference to a cell containing a date, a function returning a date type, or a number.
• day_count_convention - [ OPTIONAL - 0 by default ] - An indicator of what day count method to use.
  
  • 0 indicates US (NASD) 30/360 - This assumes 30 day months and 360 day years as per the National Association of Securities Dealers standard, and performs specific adjustments to entered dates which fall at the end of months.
    
  • 1 indicates Actual/Actual - This calculates based upon the actual number of days between the specified dates, and the actual number of days in the intervening years. Used for US Treasury Bonds and Bills, but also the most relevant for non-financial use.
  • 2 indicates Actual/360 - This calculates based on the actual number of days between the speficied dates, but assumes a 360 day year.
  • 3 indicates Actual/365 - This calculates based on the actual number of days between the specified dates, but assumes a 365 day year.
  • 4 indicates European 30/360 - Similar to 0, this calculates based on a 30 day month and 360 day year, but adjusts end-of-month dates according to European financial conventions.
    

Notes

• This function is mostly used in a financial setting, for calculation involving fixed-income securities. Because the most common calculations performed use the NASD standard calendar, this is the default behavior. However, for use in non-financial settings, option 1, Actual/Actual, is most likely the correct choice.
• Ensure that the inputs to the function are either references to cells containing dates, functions which return date objects such as DATE, DATEVALUE or TO_DATE, or date serial numbers of the type returned by the N function.
• YEARFRAC does not autoconvert number formats. Therefore, YEARFRAC(10/10/2000,10/10/2001) is interpreted as YEARFRAC(0.005,0.00499750124938), the quotients of 10 divided by 10 divided by 2000 and 2001, respectively.

See Also

TO_DATE: Converts a provided number to a date.

N: Returns the argument provided as a number.

DAYS360: Returns the difference between two days based on the 360 day year used in some financial interest calculations.

DATEVALUE: Converts a provided date string in a known format to a date value.

DATE: Converts a provided year, month, and day into a date.

In order to use the YEARFRAC formula, start with your edited Excellentable:

Then type in the YEARFRAC Formula in the area you would like to display the outcome:

By adding the value you would like to calculate the YEARFRAC formula for, Excellentable will generate the outcome: