Date-Value Discrepancies: Fixing 30/360 vs. Actual/365 interest day-count errors.

When corporate loan schedules, bond investments, or treasury models built in Excel fail to reconcile with bank statements or trustee notices, the problem is almost always an incorrect day-count convention. Formulas that calculate interest using simple day subtractions like (End_Date - Start_Date) / 365 or naive assumptions like thirty days per month ignore the strict conventions governing financial contracts. Over a multi-million dollar borrowing facility, confusing 30/360 with Actual/365 or Actual/360 routinely causes thousands of dollars in interest variances.

Quick Answer

Stop subtracting dates manually. Use Excel’s dedicated day-count function: =YEARFRAC(start_date, end_date, [basis]). Set basis = 0 for US 30/360 (bonds), basis = 1 for Actual/Actual (Treasuries), basis = 2 for Actual/360 (commercial loans), or basis = 3 for Actual/365 (consumer debt). Multiply the resulting fraction directly by the principal and annual interest rate.

---

The Major Day-Count Conventions Explained

Every debt instrument specifies a day-count convention in its legal credit agreement or indenture. The convention determines two things:

1. Numerator: How many days are assumed to pass between two dates.
2. Denominator: How many days are assumed to be in a full calendar year.

Common Financial Conventions:
   ├── US 30/360 (Basis 0) ────> Corporate Bonds, Agency Debt
   ├── Actual/Actual (Basis 1) ─> US Treasury Bonds & Notes
   ├── Actual/360 (Basis 2) ───> Commercial Bank Loans, Syndicated Credit Facilities
   └── Actual/365 (Basis 3) ───> Consumer Credit, Auto Loans, UK Gilts

Basis Code	Convention Name	Numerator Rule	Denominator Rule	Typical Usage
0 or omitted	US (NASD) 30/360	Every full month is 30 days. Adjustments applied to 31st and Feb.	Exactly 360 days.	Corporate bonds, municipal bonds.
1	Actual/Actual	Exact calendar days elapsed.	Actual days in year (365 or 366 for leap year).	US Treasury securities.
2	Actual/360	Exact calendar days elapsed.	Exactly 360 days.	Commercial lines of credit, money market notes.
3	Actual/365 Fixed	Exact calendar days elapsed.	Exactly 365 days (ignores leap years).	Commercial paper, international loans.
4	European 30/360	Every month is 30 days. No special February 31st rule.	Exactly 360 days.	Eurobonds, ISMA/ICMA debt.

---

The “Actual/360” Commercial Lending Trap

The most frequent source of modeling error occurs in commercial banking debt. Borrowers often assume an agreed “6.00% annual interest rate” will cost $60,000 on a $1,000,000 loan over a normal year.

However, commercial bank credit facilities almost universally run on an Actual/360 convention (basis = 2).

• In a normal 365-day year, an Actual/360 schedule charges for 365 days while dividing by a 360-day year.
• The true effective annual rate is: $\text{Effective Rate} = 6.00\% \times \left(\frac{365}{360}\right) = 6.0833\%$

Over a year, the borrower incurs an extra $833.33 per million dollars of debt compared to a naive /365 formula. In leap years, that discrepancy grows to an extra $1,000.00 per million. If your model does not use the correct day-count basis, your cash flow forecasts will consistently underestimate debt service obligations.

---

Step-by-Step Diagnostic & Implementation

Step 1: Check the Loan Document / Indenture for Day-Count Terms
   └── Found? ──> Step 2: Replace Manual Math with =YEARFRAC()
                     └── Discrepancy Persists? ──> Step 3: Check Leap Year / End-of-Month Rules

Step 1: Replace Manual Date Division

Audit your interest formulas. If you see logic like:

=Balance * Rate * ((End_Date - Start_Date) / 360)   <-- RISK: Incompatible with 30/360 rules

Or:

=Balance * Rate * ((End_Date - Start_Date) / 365)   <-- RISK: Ignores Actual/360 banking terms

Replace them with =YEARFRAC():

=Principal * Annual_Rate * YEARFRAC(Start_Date, End_Date, Basis_Code)

Step 2: Handle Month-End Discrepancies in 30/360

Under the US 30/360 rule (basis = 0), dates ending on the 31st require special handling:

• If the starting date is the 31st of a month, it is converted to the 30th.
• If the ending date is the 31st of a month and the starting date is earlier than the 30th, the ending date remains the 31st (resulting in a 31-day span for that month).

If you calculate days by subtracting months manually using =MONTH(End) - MONTH(Start), you bypass these bond market rules, producing broken coupon accruals. =YEARFRAC(Start, End, 0) executes these complex NASD day-adjustment algorithms automatically.

Step 3: Address Leap Year Day-Count Drift

When loans cross a leap year, day counts introduce non-linear accrual patterns.

• Under Actual/Actual (basis = 1), if an interest period spans both 2023 (365 days) and 2024 (366 days), Excel automatically proportions the fraction based on how many days fell into each specific calendar year.
• Under Actual/365 (basis = 3), the denominator remains locked at 365, meaning an extra day of interest accrues during February 2024.

For detailed strategies on managing leap year cycles within multi-tier repayment schedules, see Amortization Schedules: Handling “Leap Year” errors in daily interest accruals..

---

Auditing Bank Amortization Discrepancies

If your =YEARFRAC formulas match the bank’s day count, but the regular payment still misses the bank statement:

1. Check the Base Formula: Excel’s native =PMT() function assumes uniform, equal periodic cycles (exactly 1/12 of a year per month). Real loans accrue on irregular daily calendars. Review the underlying limitations of the function at PMT Function Errors: Why your loan amortization doesn’t match the bank statement..
2. Check Holiday Calendars: If an interest payment date falls on a weekend or bank holiday, many agreements use “Modified Following” business day conventions. The interest calculation window stretches to the next valid business day, shifting days into the following period.