YEARFRAC
Returns the fraction of a year between two dates.
YEARFRAC(<start_date>, <end_date> [, <basis>]). Basis 0 (the default) is US 30/360. Basis 1 is actual/actual. Basis 2 is actual/360. Basis 3 is actual/365. Basis 4 is European 30/360.Returns: A decimal number, such as 0.5 for half a year.
What it does
YEARFRAC gives the part of a year that falls between two dates. Finance teams use it to accrue interest for a part of a year, to prorate an annual fee or insurance premium, and to match the day count rules written into a loan or bond. The basis argument chooses the day count convention, and the choice changes the answer, so it must match the contract.
Syntax and arguments
- start_date: the starting date. Required.
- end_date: the ending date. Required.
- basis: the day count convention, from 0 to 4 as listed above. Optional. Use 3 (actual/365) for many simple accrual calculations, and check the contract for the basis that it states.
Example
A note carries interest at 6% a year. Interest was last paid on September 15, 2026, and the report date is October 30, 2026. The controller wants the interest accrued to the report date.
Accrued Interest =
VAR ReportDate = MAX('Calendar'[Date])
RETURN
SUMX(
'Notes',
'Notes'[Principal] * 'Notes'[AnnualRate] * YEARFRAC('Notes'[LastPaidDate], ReportDate, 3)
)
For 500,000 at 6% with 45 actual days since the last payment, the year fraction on actual/365 is 45 divided by 365. The accrued interest is 500,000 times 0.06 times 45 divided by 365, which is about 3,698.63. The basis 3 choice gives exactly that result. A different basis would give a different figure, which is why the contract matters.
Example: a second, different use
A customer pays an annual service fee of 120,000 for a contract that starts on July 1. At the end of December, about half the service has been delivered, so the controller wants the fee earned to date for the contract.
Fee Earned to Date =
SUMX(
'Contracts',
'Contracts'[AnnualFee] * YEARFRAC('Contracts'[ServiceStart], MAX('Calendar'[Date]), 3)
)
From July 1 to December 31 is 183 actual days, so the year fraction on actual/365 is 183 divided by 365, about 0.501. The fee earned is about 60,164 out of 120,000. Revenue recognition rules may require a different method, so confirm the method with the accounting policy before you use this for reporting.
Things to watch
- The default basis is 30/360, which treats every month as 30 days. Results differ from actual/365 by a small amount, and the difference is larger for odd periods.
- Check the day count convention in the loan or bond terms before you choose a basis.
- The function returns a fraction of a year, not a number of days. Multiply by the annual amount to get the period amount.
- For a count of whole days, use DATEDIFF with the DAY interval.
In business terms
YEARFRAC supports accrued interest and interest income schedules, prorated subscriptions and insurance, lease and fee accruals, and any calculation where a contract defines the day count. Auditors check the basis when they recalculate interest, so document the basis in the model.