Excel YEARFRAC function

Summary

The Excel YEARFRAC function calculates the fraction of a year between two dates as a decimal. This function is useful for applications like calculating age from a birthdate or determining the annualized rate on a financial instrument.
Syntax
				
					=YEARFRAC(start_date, end_date, [basis])
				
			
  • start_date: The starting date.
  • end_date: The ending date.
  • basis: [Optional] The day count basis to use.
Return value
A decimal representing the fraction of a year between the two dates.

How to use

YEARFRAC computes fractional years between two dates. You can specify the method for counting days via the optional ‘basis’ parameter. The basis parameter has five options:

  • 0 (or omitted): US (NASD) 30/360
  • 1: Actual/actual
  • 2: Actual/360
  • 3: Actual/365
  • 4: European 30/360
 

The choice of ‘basis’ affects how the days between the start and end dates are counted, influencing the calculated fraction of a year.

Examples

YEARFRAC for Age Calculation
Calculating Age from Birthdate:
				
					=INT(YEARFRAC(birthdate, TODAY()))
				
			
This formula calculates the age as a whole number based on a given birthdate.
YEARFRAC for Financial Calculations
Annualized Return Calculation:
				
					=YEARFRAC(start_date, end_date, 1) * annual_rate

				
			
This example calculates the annualized return on an investment, considering the actual number of days in the year.
YEARFRAC for Year Progress
Percentage of Year Completed:
				
					=YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY())
				
			
This formula finds the percentage of the current year that has elapsed.

Additional Notes

  • Be aware of the ‘basis’ parameter when comparing YEARFRAC results across different calculations or scenarios.
  • YEARFRAC is essential in financial modeling for prorating amounts over part of a year.
  • The function will return a #VALUE! error if either the start_date or end_date are not valid Excel dates.

Related Functions

Excel NETWORKDAYS function

The Excel NETWORKDAYS function calculates working days between two dates, excluding weekends, useful for project timelines.

Excel DAYS360 function

The Excel DAYS360 function calculates days between dates using a 360-day year, ideal for financial and accounting calculations.

Excel DATEDIF function

The Excel DATEDIF function calculates the difference between two dates, useful for age, time spans, and precise date calculations.

Excel DAYS function

The Excel DAYS function calculates the difference in days between two dates, essential for tracking durations and time spans in data analysis.

Content Navigation