The annual loan rate is only part of the interest calculation

Two calculations using the same principal and annual rate can produce different interest because they use different day-count conventions. A model that divides every annual rate by twelve may therefore disagree with a loan statement even when the rate itself is correct. Record the convention and the accrual dates before changing the rate to force a reconciliation.

ISDA's day-count reference material distinguishes Actual/360 from Actual/365 Fixed, among other conventions. The first uses actual elapsed days over 360; the second uses actual elapsed days over 365. These definitions illustrate why the denominator matters. The actual loan agreement determines which convention and date rules apply to the business.

Keep the annual rate, day-count fraction, relevant balance and rounding rule as separate inputs. A single hard-coded interest amount may match one statement while failing the next month because the number of days or the balance changed.

Assume an illustrative $250,000 balance outstanding for 30 actual days at an 8% simple annual rate. Under Actual/360, interest is $250,000 × 8% × 30/360, or $1,666.67 before applying any specified rounding convention. Under Actual/365 Fixed, it is approximately $1,643.84.

The difference is about $22.83 for that period. Neither amount can be selected merely because it is closer to a monthly budget. The difference comes from the contractual basis, and the applicable basis should be supported by the agreement or the lender's confirmed calculation.

For 31 actual days on the same unchanged balance and rate, the amounts become approximately $1,722.22 and $1,698.63. Dividing 8% by twelve would still produce $1,666.67, so it would miss the day variation. Use a periodic-rate assumption only when that assumption matches the intended calculation.

The word Fixed matters: its denominator remains 365 under that definition. Other actual/actual conventions can depend on calendar years or coupon periods. Do not infer the calculation from an abbreviated label that omits the relevant variant.

A period spanning a leap day or year end is a useful targeted check. Determine whether the agreement uses a fixed denominator, splits the period or refers to a coupon-based fraction. Preserve the supported interpretation in the model rather than silently switching every denominator to 366 in a leap year.

This is also why a generic spreadsheet year-fraction function needs its basis argument reviewed. Its default may not match the contract. Compare at least one ordinary period and one relevant boundary period with a calculation supplied or confirmed by the lender.

A 30/360 convention does not simply mean actual elapsed days divided by 360. It uses an adjusted day count based on thirty-day months, and different variants can treat month-end dates differently. Identify the particular method before substituting a spreadsheet function.

Microsoft's DAYS360 documentation distinguishes a U.S. method from a European method and describes different adjustments for endpoint dates. The existence of that choice is the practical lesson: a function returning a number without an error does not establish that it implements the loan's named convention.

Keep dates as actual date values and inspect the start and end order. Test month ends and February when those dates occur in the schedule. Preserve the selected function arguments with the model notes so a later reviewer can reproduce the calculation rather than rely on a remembered default.

For a numerical illustration, keep the 8% rate and Actual/360 basis but assume $250,000 is outstanding for the first 15 days and $150,000 for the next 15 after a repayment. Interest is about $833.33 plus $500, or $1,333.33. Using the $150,000 closing balance for all 30 days would produce only $1,000. That $333.33 difference comes from balance timing, not from a different annual rate or a missing bank fee.

A constant-balance formula is insufficient when a draw or repayment occurs partway through the accrual period. Divide the period into segments with the appropriate balance and rate, calculate interest for each and add the results. Use the agreement's effective-date treatment for the transaction.

The same applies to a rate reset. A period may contain days at the old rate and days at the new one. Applying the closing rate to the whole period can overstate or understate the statement amount. Retain the reference rate date, contractual margin and any applicable floor or cap separately.

For a product using compounded daily rates, the calculation may require the stated compounding formula rather than a simple annual rate multiplied by a fraction. Identify that mechanism before using the simple examples above. The model should reproduce the actual interest structure, not merely its annualized label.

An interest payment can occur after the accrual period closes. A nonbusiness-day adjustment can also move the cash date without necessarily changing the calculation period in the same way. Confirm the agreement's treatment instead of assuming every payment delay adds an identical number of accrual days.

For the cash forecast, show when interest will be paid. For the interest schedule, show the period over which it accrued. The two should reconcile through accrued interest or another appropriate accounting category rather than be forced into the same monthly column.

Check whether the bank debit also includes principal, fees or other charges. A difference between the debit and the calculated interest may have nothing to do with day count. Reconcile the statement components before altering a correct accrual formula.

A focused reconciliation checks principal dates, rate dates, day-count basis, included and excluded endpoints, rounding and payment components. Work through those inputs in that order and quantify each confirmed change. Avoid a permanent unexplained adjustment added merely to match the latest statement.

Keep the agreement reference, supported convention, lender calculation and model version with the schedule. The next forecast can then change for a real reason, such as an additional day or a draw, while preserving the same agreed method.