1. Calculation functions
  2. All Functions
  3. Financial Functions
  4. YEARFRAC

Use the YEARFRAC function to calculate the fraction of a year between two dates (inclusive of the start date, exclusive of the end date).

The function uses a basis (day-count convention) to count the number of days between these dates, and then divide that number by the basis.

Syntax

YEARFRAC(start, end[, basis])

The YEARFRAC function has the following arguments:

Argument Data type Description
start (required) Date The start date.
end (required) Date The end date.
basis (optional) Number

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US 30/360, Actual/360, and EUR 30/360 are used;
  • 365 days when basis Actual/365 is used; and
  • 365 or 366 days when Actual/Actual is used.

US 30/360 is the default basis for DURATION. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual,
  • 2 for  Actual/360,
  • 3 for  Actual/365, or
  • 4 for European 30/360.

Learn about the conventions used to calculate the day count for basis.

 

Returns
Number

Constraints

The YEARFRAC function has the following constraints:

  • the start and end values must represent a valid date;
  • the start and maturity dates must represent a valid date between 01/01/1900 and 12/31/2399; and
  • the basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Examples

The following tables show some example formulas using the YEARFRAC function.

You can reference line items or list properties in your formula.

Formula Description Result
YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30), 1)

This example shows a YEARFRAC calculation that includes a basis.

The start date is given as 01/15/2015, the end date is given as 04/30/2018, and the basis is given as 1 (Actual/Actual).

3.28767123
YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30))

In this example YEARFRAC is calculated without specifying a basis. As a result, it uses the default basis of US 30/360.

The start date is given as 01/15/2015 and the end date is given as 04/30/2018.

3.29166666

Excel equivalent