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/3601: Actual/actual2: Actual/3603: Actual/3654: 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.