YEARFRAC and DAYS360 Equivalents in Python

Interest accruals, bond coupons, pro-rata fees and leases in Excel often rely on YEARFRAC and DAYS360. Both look simple — the fraction of a year between two dates, the days between two dates on a 360-day calendar — but each hides a day-count convention with specific rules for month ends, February and leap years. Moving such a workbook to Python means reproducing those rules exactly, or the figures drift by a few days' interest on exactly the dates where people check them: the end of February and months with 31 days. Python's standard library has no YEARFRAC, and naive (end - start).days / 365 matches only one of the five bases. This page implements all five YEARFRAC bases and both DAYS360 methods, applies them to an accrual table in pandas, and checks every result against a spreadsheet's own calculation. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

The functions use only the standard library; pandas applies them to columns. The checks write the same formulas into a workbook and recalculate it with LibreOffice (soffice), whose results for these functions follow the conventions described below.

The five bases and two methods

YEARFRAC(start, end, basis) divides a day count by a year length, and the basis picks both:

DAYS360(start, end, method) returns the 30/360 day count itself: the US method by default (FALSE) or the European method (TRUE). The 30/360 rules all pretend every month has 30 days, so that monthly interest is the same every month; they differ only in how a 31st and the end of February are treated.

Step 1 — DAYS360, US and European

Both methods compute 360 × years + 30 × months + days from adjusted day numbers. The adjustments are the whole difference:

import calendar
from datetime import date

def is_month_end(d):
    return d.day == calendar.monthrange(d.year, d.month)[1]

def days360(start, end, european=False):
    d1, d2 = start.day, end.day
    if european:
        d1 = min(d1, 30)
        d2 = min(d2, 30)
    else:
        if is_month_end(start):
            d1 = 30
        if d2 == 31 and d1 >= 30:
            d2 = 30
    return 360 * (end.year - start.year) + 30 * (end.month - start.month) + (d2 - d1)

for a, b in [(date(2024, 1, 31), date(2024, 3, 31)), (date(2025, 2, 28), date(2025, 3, 31)),
             (date(2024, 2, 29), date(2025, 2, 28)), (date(2024, 2, 28), date(2024, 3, 31))]:
    print(a, "→", b, "US:", days360(a, b), "EU:", days360(a, b, european=True))

The European method simply caps both days at 30. The US method treats a start date on the last day of any month — including 28 February in a common year or 29 February in a leap year — as the 30th, and turns an end date of the 31st into the 30th only if the start has become 30 or more. That is why 28 February 2025 to 31 March 2025 counts 30 days in the US method but 32 in the European one, and why 28 February 2024 (not a month end, because 2024 is a leap year) to 31 March 2024 counts 33. These February cases are exactly the ones to test.

Step 2 — YEARFRAC bases 0, 2, 3 and 4

Bases 2, 3 and 4 are straightforward. Basis 0 is a 30/360 count, but not identical to DAYS360's US method: when both dates are the last day of February, the end becomes 30 too, and an end date of 31 is reduced only when the original start day was 30 or 31:

def yearfrac_30360_us(start, end):
    d1, d2 = start.day, end.day
    feb_end = lambda d: d.month == 2 and is_month_end(d)
    if feb_end(start) and feb_end(end):
        d2 = 30
    orig_d1 = d1
    if feb_end(start):
        d1 = 30
    if d2 == 31 and orig_d1 >= 30:
        d2 = 30
    if d1 == 31:
        d1 = 30
    return (360 * (end.year - start.year) + 30 * (end.month - start.month) + (d2 - d1)) / 360

def yearfrac(start, end, basis=0):
    if start > end:
        start, end = end, start
    actual = (end - start).days
    if basis == 0:
        return yearfrac_30360_us(start, end)
    if basis == 1:
        return actual / year_length_actual(start, end)
    if basis == 2:
        return actual / 360
    if basis == 3:
        return actual / 365
    if basis == 4:
        return days360(start, end, european=True) / 360
    raise ValueError("basis must be 0–4")

The difference between basis 0 and DAYS360 / 360 is a known quirk: from 29 February 2024 to 28 February 2025, YEARFRAC(...,0) gives exactly 1, because both dates are February month ends, while DAYS360(...)/360 gives 358/360. If a workbook mixes the two, reproduce each separately rather than deriving one from the other. Like Excel, the function swaps the dates if they are in reverse order, so the result is never negative.

Step 3 — Basis 1, actual/actual

Basis 1 divides actual days by a year length that depends on the dates. Excel's rule: if the period is at most one year, the year has 366 days when it includes a 29 February (or lies within a leap year), otherwise 365; if the period is longer, the year length is the average over every calendar year it touches:

def year_length_actual(start, end):
    one_year_later = start.replace(year=start.year + 1) if not (start.month == 2 and start.day == 29) \
        else date(start.year + 1, 3, 1)
    if end <= one_year_later:
        if start.year == end.year:
            return 366 if calendar.isleap(start.year) else 365
        leap_days = [date(y, 2, 29) for y in range(start.year, end.year + 1) if calendar.isleap(y)]
        return 366 if any(start <= d <= end for d in leap_days) else 365
    years = range(start.year, end.year + 1)
    return sum(366 if calendar.isleap(y) else 365 for y in years) / len(years)

print(round(yearfrac(date(2024, 2, 29), date(2025, 2, 28), 1), 6))
print(round(yearfrac(date(2024, 1, 15), date(2026, 9, 30), 1), 6))

From 29 February 2024 to 28 February 2025 is 365 days within a period that includes a 29 February, so the year counts as 366: 0.997268. From 15 January 2024 to 30 September 2026 is more than a year, so the 989 days are divided by the average length of 2024, 2025 and 2026 — (366 + 365 + 365) / 3 — giving 2.707117. This basis is not the ISDA actual/actual convention used in many bond markets, which splits the period by calendar year; if a contract specifies ISDA, implement that instead of copying Excel.

Step 4 — Check against a spreadsheet's own results

Day-count code is only trustworthy once it agrees with the spreadsheet on awkward dates. Write the formulas into a workbook, recalculate, and compare every basis:

import subprocess
from openpyxl import Workbook, load_workbook

pairs = [(date(2024, 1, 31), date(2024, 3, 31)), (date(2024, 2, 29), date(2025, 2, 28)), (date(2025, 2, 28), date(2025, 3, 31)),
         (date(2023, 12, 31), date(2024, 12, 31)), (date(2024, 1, 15), date(2026, 9, 30)), (date(2025, 1, 30), date(2025, 2, 28)),
         (date(2024, 2, 28), date(2024, 3, 31)), (date(2024, 7, 1), date(2025, 6, 30))]
wb = Workbook()
ws = wb.active
for i, (a, b) in enumerate(pairs, start=1):
    ws.cell(i, 1, a); ws.cell(i, 2, b)
    for basis in range(5):
        ws.cell(i, 3 + basis, f"=YEARFRAC(A{i},B{i},{basis})")
    ws.cell(i, 8, f"=DAYS360(A{i},B{i},FALSE)")
    ws.cell(i, 9, f"=DAYS360(A{i},B{i},TRUE)")
wb.save("daycount.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "daycount.xlsx"], check=True, capture_output=True)
sheet = load_workbook("calc/daycount.xlsx", data_only=True).active

mismatches = []
for i, (a, b) in enumerate(pairs, start=1):
    for basis in range(5):
        if abs(sheet.cell(i, 3 + basis).value - yearfrac(a, b, basis)) > 1e-9:
            mismatches.append((a, b, f"basis {basis}"))
    if sheet.cell(i, 8).value != days360(a, b) or sheet.cell(i, 9).value != days360(a, b, True):
        mismatches.append((a, b, "DAYS360"))
print(f"{len(pairs) * 7} comparisons, mismatches: {mismatches or 'none'}")

All 56 comparisons — five bases and two DAYS360 methods for eight date pairs chosen around month ends, February and leap years — agree to within floating-point precision. Keep a test like this next to the functions: if someone "simplifies" the February rules later, it fails on exactly the dates that matter. Recalculating workbooks for tests is covered in Recalculate Excel Formulas Without Excel in Python.

Step 5 — Accrued interest in pandas

With the scalar functions tested, apply them to a table of loans to compute accrued interest to a reporting date:

import pandas as pd

loans = pd.DataFrame({
    "Loan": ["L-01", "L-02", "L-03", "L-04"],
    "Principal": [250_000, 80_000, 1_200_000, 45_000],
    "Rate": [0.052, 0.061, 0.0475, 0.07],
    "Last payment": pd.to_datetime(["2026-06-30", "2026-08-31", "2026-02-28", "2026-09-15"]),
    "Basis": [0, 4, 2, 1],
})
as_of = date(2026, 9, 30)
loans["Year fraction"] = [yearfrac(d.date(), as_of, b) for d, b in zip(loans["Last payment"], loans["Basis"])]
loans["Accrued"] = (loans["Principal"] * loans["Rate"] * loans["Year fraction"]).round(2)
print(loans.round({"Year fraction": 6}).to_string(index=False))

Each loan uses the basis its contract specifies, stored in a column so the table documents itself. The accrued interest is principal × rate × year fraction, exactly as the Excel formula =Principal*Rate*YEARFRAC(LastPayment, AsOf, Basis) would compute it. A list comprehension over the scalar function is perfectly fast for thousands of loans; for millions, vectorise the actual/360 and actual/365 bases with plain date arithmetic, which needs no loop. Related financial functions are covered in PMT, NPV and IRR in Python.

Common pitfalls

• days / 365 for everything. That is only basis 3; use the basis the contract specifies.
• Treating YEARFRAC basis 0 as DAYS360/360. They differ when both dates are February month ends.
• Forgetting leap-year month ends. 28 February 2024 is not a month end; 29 February is.
• Assuming basis 1 is ISDA actual/actual. Excel's rule averages year lengths for long periods.
• No test on February and 31st dates. Compare with spreadsheet results on those dates.

Conclusion

Excel's day-count functions are reproducible in a few lines once their rules are written down: DAYS360 adjusts start and end days for 30-day months — capping at 30 in the European method, and in the US method treating any month-end start as the 30th and an end on the 31st as the 30th only when the start is 30 or later. YEARFRAC basis 0 is a slightly different US 30/360 count, bases 2 and 3 divide actual days by 360 or 365, basis 4 is European 30/360, and basis 1 uses 365 or 366 for periods up to a year and an average year length beyond. Test the functions against spreadsheet results on February and month-end dates before trusting them.

Frequently asked questions

What is the Python equivalent of Excel's YEARFRAC? There is no built-in function. Implement the day-count basis you need: actual/360 and actual/365 divide the day difference by 360 or 365, 30/360 bases count 30-day months with month-end rules, and actual/actual uses 365 or 366 days per year by Excel's rule.

How do I calculate DAYS360 in Python? Compute 360 times the year difference plus 30 times the month difference plus the difference of adjusted days. In the European method, cap both days at 30; in the US method, set a month-end start to 30 and an end of 31 to 30 only when the start day is 30 or more.

Why does YEARFRAC with basis 0 not equal DAYS360 divided by 360? YEARFRAC basis 0 adds a rule for February: when both dates are the last day of February, the end day also becomes 30, so the two results differ for such dates.

Is Excel's actual/actual the same as ISDA actual/actual? No. Excel divides by 365 or 366 for periods up to a year and by an average year length for longer periods; ISDA splits the period by calendar year.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• EOMONTH and EDATE Equivalents in pandas — month-end date arithmetic.
• XIRR and XNPV Equivalents in Python — dated cash flows, actual/365.
• Calculate Age and Tenure from Dates with pandas — whole years and months between dates.