YEARFRAC

What it does

YEARFRAC(start_date, end_date, [basis])

YEARFRAC converts the interval between two dates into a fraction of a year, under a selectable day-count convention. It is the time axis of fixed-income arithmetic: every accrual, every discount factor, every bill price ultimately asks "how much of a year is this", and this function is where Excel answers.

Structurally it is a quotient:

YEARFRAC(start, end, basis) = (days between, counted by basis) / (days in a year, per basis)

Both the numerator and the denominator depend on the basis, and — this is the part that surprises people — for one basis the denominator is not a constant.

The two 30/360 bases are the conventions documented verbatim on the DAYS360 page — basis 0 is the US (NASD) method with its asymmetric, start-date-dependent end-date rule, and basis 4 is the symmetric European method that adjusts only the 31st. YEARFRAC(a, b, 0) and DAYS360(a, b, FALSE)/360 are the same quantity on the documented reading of both pages.

Basis 1 is the hard one. "Actual/actual" does not name a single algorithm. The denominator must be some notion of "the length of the year the interval sits in", and when an interval spans several years, or a leap day, or starts and ends in years of different lengths, there are several defensible constructions — averaging the lengths of the spanned years, using the length of the year containing the start, using 365 or 366 according to whether a leap day falls inside the period, and others. Excel's exact rule is not stated on the documentation page, and this Handbook has not established it. That gap is the single most important open question about this function, because basis 1 is the one regulated instruments most often specify.

Arguments

YEARFRAC(start_date, end_date, [basis]) — two required arguments and one optional.

start_date — "a date that represents the start date".

end_date — "a date that represents the end date".

basis — optional; the day-count convention, per the table above. Omitted means 0, the US (NASD) 30/360 convention. Microsoft states a general conversion rule for this function that the rest of the category does not state so plainly: "All arguments are truncated to integers." So fractional serials contribute no fractional days, and a fractional basis truncates.

The misunderstood argument is basis, and specifically its default. A workbook written against an Actual/365 instrument that omits basis silently gets 30/360 answers, which are close enough to look right and wrong enough to matter over a portfolio.

Try it

Quirks

Result and edge cases

Returns a Number: a fraction of a year, which may exceed 1 for intervals longer than a year. It is not negative when end_date precedes start_date: reversed dates give the same answer as in order (FN-YEARFRAC-001).

• Microsoft documents a defect in this function. Verbatim: YEARFRAC "may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February". That is the vendor stating that basis 0 is wrong on a named input class. The Handbook records it as documented; it has not been characterized here — nobody has determined which February end dates misbehave, by how much, or whether the same inputs affect DAYS360, which implements the same adjustment rules.
• Basis 1 across a leap day is where the undocumented denominator rule becomes visible. An interval of exactly one calendar year should give 1 under any sensible actual/actual construction; an interval spanning 29 February is where constructions diverge.
• Equal dates give 0.
• Reversed intervals give the same fraction as in order (FN-YEARFRAC-001); unlike DATEDIF, no error is documented for them.
• Floating point. The result is a quotient of small integers by 360, 365, 366 or an actual/actual denominator. Division by 360 and 365 is inexact in binary, so the last bits of the result depend on the order in which the quotient is formed — which makes YEARFRAC one of the few functions in this category with genuine last-bit content, and one where an excel-bitexact implementation has something real to match.

Errors

As documented on the Microsoft page:

• #VALUE! when start_date or end_date is not a valid date.
• #NUM! when basis is less than 0 or greater than 4.

Note that the invalid-date condition is #VALUE! here, where several siblings in this category document #NUM! for an out-of-range date. The Handbook records the family's inconsistent documented error values rather than harmonizing them; see claim language, rule 5.

Ordinary coercion failures and error propagation follow the engine rules in coercion and lifting.

How to implement

Notes for implementers

1. Basis 1 needs a written-down definition before it needs code. Pick a construction, state it precisely (which years, how leap days are counted, what happens for multi-year intervals), and do not claim compatibility until it has been checked. This is the one place in this assignment where "we implemented what seemed right" is guaranteed to diverge from something.
2. Share the 30/360 adjustment code with DAYS360. Bases 0 and 4 are exactly that function's two methods; two implementations of one rule will eventually disagree, and Microsoft's own documented caveat suggests Excel may already contain such a divergence.
3. Truncate every argument to an integer first, per the documented rule, including basis.
4. Form the quotient in a fixed, documented order. The numerator is a small integer and the denominator is 360, 365, 366 or a computed value; different groupings give different last bits. For a portable-reproducible flavour this must be pinned explicitly.
5. Validate basis against 0…4 before anything else, since it is the one documented #NUM!.

Rust code

23 yearfrac_fn "YEARFRAC" (2, 3) [V "start_date", V "end_date", V "basis"] |s, a, sv| {
    let start = super::kit::checked_date_serial(strict_date_arg(s, a, 0)?)?;
    let end = super::kit::checked_date_serial(strict_date_arg(s, a, 1)?)?;
    let basis = if a.len() > 2 && !a[2].is_missing() { super::kit::num_without_logical(s, a, 2)? } else { 0.0 };
    out(s, k::yearfrac(start, end, basis))
}

/// NASD US (30/360) day count between two 1900 serials.
fn us_30_360(start: i64, end: i64) -> f64 {
    let (sy, sm, mut sd) = ymd_1900(start);
    let (ey, em, mut ed) = ymd_1900(end);
    let feb_end = |y: i64, m: i64| if y == 1900 { 29 } else { days_in_month(y, m) };
    let start_last_feb = sm == 2 && sd == feb_end(sy, sm);
    let end_last_feb = em == 2 && ed == feb_end(ey, em);
    if sd == 31 || start_last_feb {
        sd = 30;
    }
    if ed == 31 {
        if sd < 30 {
            let (ny, nm) = if em == 12 { (ey + 1, 1) } else { (ey, em + 1) };
            return (ny - sy) as f64 * 360.0 + (nm - sm) as f64 * 30.0 + (1 - sd) as f64;
        }
        ed = 30;
    }
    if end_last_feb && start_last_feb {
        ed = 30;
    }
    ((ey - sy) * 360 + (em - sm) * 30 + (ed - sd)) as f64
}

fn eu_30_360(start: i64, end: i64) -> f64 {
    let (sy, sm, mut sd) = ymd_1900(start);
    let (ey, em, mut ed) = ymd_1900(end);
    if sd == 31 {
        sd = 30;
    }
    if ed == 31 {
        ed = 30;
    }
    ((ey - sy) * 360 + (em - sm) * 30 + (ed - sd)) as f64
}

pub(crate) fn yearfrac(start_date: f64, end_date: f64, basis: f64) -> Result<f64, W> {
    if start_date.trunc() < 0.0 || end_date.trunc() < 0.0 {
        return Err(W::Num);
    }
    let serial = |value: f64| -> Result<i64, W> {
        if !value.is_finite() {
            return Err(W::Value);
        }
        let serial = value.trunc() as i64;
        if !(0..=MAX_SERIAL_1900).contains(&serial) {
            return Err(W::Value);
        }
        Ok(serial)
    };
    let (mut start, mut end) = (serial(start_date)?, serial(end_date)?);
    if !basis.is_finite() {
        return Err(W::Num);
    }
    if end < start {
        std::mem::swap(&mut start, &mut end);
    }
    match basis.trunc() as i64 {
        0 => Ok(us_30_360(start, end) / 360.0),
        1 => super::day_count_common::excel_actual_actual_fraction(start, end),
        2 => Ok((end - start) as f64 / 360.0),
        3 => Ok((end - start) as f64 / 365.0),
        4 => Ok(eu_30_360(start, end) / 360.0),
        _ => Err(W::Num),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• DAYS360 — the same two 30/360 conventions, expressed as a day count rather than a year fraction. YEARFRAC(a,b,0) should be DAYS360(a,b,FALSE)/360 and YEARFRAC(a,b,4) should be DAYS360(a,b,TRUE)/360. Testing those two identities is the cheapest way to probe the documented last-day-of-February defect, since it localizes the disagreement to one of the two functions.
• DATEDIF with "Y" — whole elapsed years rather than a fraction. Frequently substituted for YEARFRAC in age calculations, with different answers.
• DAYS — the raw calendar interval that bases 1, 2 and 3 use as their numerator.
• EOMONTH — a common source of the exact end-of-February dates Microsoft's caveat names.

Named variants

No named variants are recorded yet.