PMT Function Errors: Why your loan amortization doesn’t match the bank statement.

When an Excel loan amortization schedule fails to match a commercial bank statement, financial analysts often assume someone entered the wrong interest rate or loan balance. In reality, the error usually comes from Excel’s native =PMT() function itself. The =PMT() function is a theoretical annuity formula that assumes every payment period is identical in length. Real-world commercial loans, however, accrue interest daily based on actual calendar days, leap years, and specific banking conventions.

Quick Answer

Excel’s =PMT(rate/12, nper, pv) calculates theoretical monthly payments assuming 12 identical 30.42-day periods. Commercial banks do not calculate interest this way; they accrue interest daily using calendar conventions like Actual/360 or Actual/365. To match the bank statement, build a daily accrual schedule or use =YEARFRAC() instead of relying on =PMT().

---

The 4 Reasons Excel’s =PMT Misses the Bank Statement

The standard Excel payment formula looks straightforward:

=PMT(Annual_Rate / 12, Total_Months, -Loan_Principal)

While this formula works for simple academic exercises, it routinely diverges from commercial bank statements for four distinct structural reasons:

Sources of Variance Between PMT() and Bank Statements:
   ├── 1. Day-Count Mismatches ────> Bank uses Actual/360 or Actual/365; PMT assumes uniform 1/12th year.
   ├── 2. The "Odd First Period" ──> Loan closes mid-month; Day 1 has 10 to 45 days of interim interest.
   ├── 3. Payment Timing ([type]) ──> Payments due in advance (type 1) vs. arrears (type 0).
   └── 4. Monthly Penny Rounding ──> Bank rounds interest to 2 decimals monthly; Excel leaves floating cents.

1. Uniform Periods vs. Daily Day-Count Conventions

Excel’s PMT function divides the annual interest rate by 12, assuming every month has exactly $365.25 / 12 = 30.4167$ days.

In commercial lending, banks calculate interest on a daily basis using specific legal conventions, most commonly Actual/360 or Actual/365:

• In January (31 days), the bank charges 31 days of interest.
• In February (28 days, or 29 in a leap year), the bank charges 28 or 29 days of interest.

Because month lengths vary, the interest portion of a bank’s payment fluctuates every single month. Because =PMT() smooths these differences into a static average, the split between principal and interest in Excel will never match the bank statement. For a detailed breakdown of these conventions, see Date-Value Discrepancies: Fixing 30/360 vs. Actual/365 interest day-count errors..

2. The “Odd First Period” (Stub Days)

A standard =PMT() calculation assumes the first payment occurs exactly one full period after loan origination. For instance, if a loan closes on March 1, the first payment is assumed to be April 1.

In practice, loans rarely close on the first day of a month. If a loan funds on March 18 and the first regular payment is due May 1:

• The borrower incurs 44 days of interim interest on the first cycle.
• Many banks bill this interim “stub interest” separately at closing or roll it into the first invoice.
• Because Excel’s =PMT() cannot account for this irregular first period, every downstream balance in the Excel amortization schedule drifts out of sync from day one.

3. Payment Timing: Arrears vs. Advance (type)

The fifth argument in the function, [type], dictates when payments are due:

• 0 (or omitted): Payments are made at the end of the period (in arrears). This is standard for mortgages and term debt.
• 1: Payments are made at the beginning of the period (in advance). This is standard for equipment leases and real estate rentals.

If a loan agreement requires payments on the first day of each period covering that upcoming month, leaving the [type] argument blank causes =PMT() to calculate interest as if the borrower had the principal for an extra 30 days. On a $500,000 loan at 7% over 10 years, forgetting to set type = 1 creates a $38.25 monthly discrepancy.

4. Monthly Rounding and Floating-Point Drift

Excel calculates values to 15 digits of floating-point precision. If the calculated interest in Month 1 is $2,450.3284, Excel carries those partial mills forward.

A bank’s billing system, however, rounds interest to two decimal places ($2,450.33) on every monthly ledger run. The principal reduction is then calculated as:

$\text{Principal Reduction} = \text{Stated Payment} – \text{Rounded Interest}$

Over a 360-month mortgage or 120-month commercial facility, failing to round monthly interest inside the Excel schedule causes ending balances to drift by tens or hundreds of dollars. For methods to control this, review Precision as Displayed: Why 0.01 differences in Balance Sheets occur (and how to fix them)..

---

Step-by-Step Diagnostic & Remediation

To build an amortization schedule that matches your bank statement to the penny, abandon =PMT() for monthly rows and build a dynamic schedule based on actual dates.

Step 1: Check Loan Terms for Day-Count Basis (e.g., Actual/360, 30/360)
   └── Found? ──> Step 2: Establish Exact Payment Dates (Account for Holidays/Weekends)
                     └── Step 3: Calculate Monthly Interest via YEARFRAC()
                                   └── Step 4: Apply Explicit ROUND() to Monthly Interest

Step 1: Set Up Exact Calendar Dates

Create a column containing the exact payment dates specified in the promissory note, accounting for weekends and bank holidays if the agreement follows a “Modified Following Business Day” rule.

Step 2: Calculate Interest Using YEARFRAC

Instead of multiplying the beginning balance by Rate / 12, calculate interest using the exact day fraction between payment dates:

=ROUND(Beginning_Balance * Annual_Rate * YEARFRAC(Previous_Date, Current_Date, Basis_Code), 2)

• Use Basis_Code = 2 for Actual/360 (most commercial debt).
• Use Basis_Code = 3 for Actual/365 (consumer credit lines and select term notes).
• Use Basis_Code = 0 for US 30/360 (fixed-rate agency mortgages).

Step 3: Calculate Principal and Balance

Calculate principal repayment as the fixed payment minus the rounded interest:

=Stated_Payment - Current_Period_Interest

Then update the ending balance:

=Beginning_Balance - Current_Period_Principal

If your loan allows variable drawdowns or has terms where initial payments fail to cover monthly interest, standard schedule formulas will fail. In those scenarios, proceed to Negative Amortization Errors: Handling loan models where interest exceeds payments..