YEARFRAC(...,0) with StartDate = 28-Feb-2025 and EndDate = 31-Mar-2025 does not match standard 30/360 variants — by design?

Question

Hello,

I am trying to understand the exact behavior of Excel YEARFRAC with basis = 0 (US (NASD) 30/360) for a month-end February edge case.

I am testing:

=YEARFRAC(DATE(2025;2;28);DATE(2025;3;31);0)

with:

• StartDate = 28-Feb-2025
• EndDate = 31-Mar-2025

My issue is that the result does not seem to correspond cleanly to any of the standard 30/360 conventions I would expect, for example:

• 30/360 Bond Basis / 30/360 ISDA
• 30U/360 EOM
• 30/360 PSA

For this date pair, those conventions give different numerators depending on the February EOM treatment, but Excel YEARFRAC(...,0) appears to behave differently.

My questions are:

1. What is the exact rule Excel uses internally for YEARFRAC(...,0) in this case?
2. Is the behavior for 28-Feb-2025 to 31-Mar-2025 considered:
   • intended,
   • a legacy compatibility behavior,
   • or a known bug/limitation?
3. Should YEARFRAC(...,0) be expected to match DAYS360(...,FALSE)/360, or are there edge cases where they intentionally diverge?
4. Is there an official Microsoft description of the precise algorithm used when the start date is the last day of February?

For reference, this is the kind of comparison I am trying to reconcile:

=YEARFRAC(DATE(2025;2;28);DATE(2025;3;31);0)

I would appreciate clarification on the intended Excel logic here, especially for February month-end cases.

Thank you.

Answer 1

Excel’s behavior here is driven by the documented limitation of the US (NASD) 30/360 basis in YEARFRAC, not by a fully specified financial-standard algorithm.

From the available documentation:

• YEARFRAC(start_date, end_date, 0) uses US (NASD) 30/360 as its day-count basis when basis = 0 or omitted.
• Microsoft explicitly notes that YEARFRAC may return an incorrect result when using the US (NASD) 30/360 basis and the start_date is the last day in February.

“The YEARFRAC function may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February.”

Given that, the key points for the specific questions are:

1. Exact rule used for basis = 0 in this case
   The documentation only specifies that basis = 0 means US (NASD) 30/360 and that all arguments are truncated to integers. It does not provide a step‑by‑step algorithm for how the 30/360 transformation is applied, nor how February end‑of‑month is handled internally. For the “start date is last day of February” case, Microsoft only flags that the result may be incorrect; the exact internal adjustment rule is not documented in the provided sources.
2. Intended vs legacy vs bug for 28‑Feb‑2025 to 31‑Mar‑2025
   For a start_date that is the last day of February, Microsoft classifies the behavior under basis = 0 as a known limitation:
   • It is explicitly documented that the result “may be incorrect” in this scenario.
   • That implies the behavior is not guaranteed to be a faithful implementation of any particular 30/360 market convention for this edge case. So this is best treated as a known limitation/bug in the US (NASD) 30/360 implementation for YEARFRAC when the start date is the last day of February, rather than a deliberate attempt to match a specific financial standard.
3. Relationship to DAYS360(..., FALSE)/360
   The documentation does not state that YEARFRAC(..., 0) is defined as DAYS360(..., FALSE)/360, nor that they are guaranteed to match in all cases.
   For most dates, they will conceptually be using the same 30/360 US (NASD) basis, but for the documented edge case (start date is last day of February) YEARFRAC may return an incorrect result. The sources do not state that this limitation also applies to DAYS360, and they do not state that the two functions are required to be identical. Therefore, divergence in such edge cases must be assumed possible and is not ruled out by the documentation.
4. Official description of the algorithm when start date is last day of February
   The only official statement in the provided material is that YEARFRAC may return an incorrect result when using US (NASD) 30/360 and the start date is the last day in February.
   No further breakdown of the internal algorithm or special‑case rules for February month‑end is given in the referenced documentation.

Practical implication: for precise financial 30/360 work, especially around February month‑end, YEARFRAC(..., 0) should not be relied upon to match a specific 30/360 convention (Bond Basis, ISDA, PSA, 30U/360 EOM, etc.) in all edge cases. A custom implementation or an alternative basis (1, 2, 3, or 4) may be preferable where exact convention matching is required.

---

References:

• YEARFRAC function
• WorksheetFunction.YearFrac method (Excel)
• WorksheetFunction.YearFrac(Object, Object, Object) Method