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.

YEARFRAC(Start, End[, Basis])

 Argument Data type Description Start (required) Date The bond settlement date: The date the bond is traded to the buyer. End (required) Date The bond maturity date: The date when the bond expires. Basis Number The basis determines how many days exist in a year. A full year has: 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used 365 days when basis Actual/365 is used 365 or 366 days when Actual/Actual is used US 30/360 is the default basis for COUPDAYS. 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 4 for European 30/360 Learn about the conventions used to calculate the day count for basis.

The YEARFRAC function returns a number.

• The start and end dates must be valid dates between 01/01/1900 and 12/31/2399.
• The basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

YEARFRAC

This example shows a YEARFRAC calculation that includes a basis.

 Formula Description Result YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30), 1) This formula uses: a start date of as 01/15/2015 an end date of 04/30/2018 a basis of 1 (Actual/Actual) 3.28767123

This example shows a YEARFRAC calculation does not use a basis. As a result, the basis defaults to US 30/360.

 Formula Description Result YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30)) This formula uses: a start date of 01/15/2015 an end date of 04/30/2018 3.29166666