Spreadsheet checks before running interest in Canada

What the checker catches

Before you calculate Canadian interest in a spreadsheet, the biggest risk is usually less about the math and more about the inputs and the structure around the math. DocketMath’s spreadsheet-checker workflow is meant to sanity-check key assumptions—so you don’t accidentally charge (or credit) the wrong amount due to a date, rate, or period-definition issue.

Here are the most common problems the checker is designed to catch, along with what they typically look like in a spreadsheet:

• Date logic errors

  • Symptom: A “days” column goes negative, is zero for non-identical dates, or jumps unexpectedly.
  • Typical cause: Mixing date formats (e.g., YYYY-MM-DD vs DD/MM/YYYY), or using text instead of true date cells.

• Day-count convention mismatches

  • Symptom: Two rows that differ only by day-count basis (e.g., 365 vs 360) produce materially different results.
  • Typical cause: Hard-coded denominators or inconsistent conventions across worksheets/tabs.

• Compounding vs simple interest drift

  • Symptom: Interest grows far faster (or slower) than expected, or the “period” column doesn’t align with the compounding frequency.
  • Typical cause: Applying a rate per year but treating it as per period (or vice versa), or using formulas that compound at an unintended cadence.

• Rate scaling mistakes

  • Symptom: A rate entered as 6 instead of 0.06, causing interest to balloon ~100×.
  • Typical cause: Percent formatting inconsistency (e.g., storing “6%” in one place and “6” in another), or reading values as raw numbers rather than percentages.

• Sign and direction problems

  • Symptom: Results show interest as negative where you expect a positive charge.
  • Typical cause: Amount sign conventions (receivable vs payable, payments vs charges) not applied consistently across rows.

• Row alignment and carry-forward issues

  • Symptom: Payment dates and principal amounts don’t correspond to the right periods.
  • Typical cause: Sorting one column but not others, or using mismatched row ranges (e.g., dates from rows 10–20 paired with principal from rows 9–19).

• Hidden rounding and precision loss

  • Symptom: The total differs slightly from the sum of the visible per-row results.
  • Typical cause: Rounding inside intermediate steps (e.g., rounding a daily rate) instead of rounding at the end.

• Mixed time zones / timestamp artifacts

  • Symptom: Day counts are off by 1 (or more) around boundary dates.
  • Typical cause: Using datetime values with time components, not just dates (so “same calendar day” subtracts to less than 24 hours).

Pitfall to watch for: If your spreadsheet treats dates as text, functions that “subtract dates” may return #VALUE! or—worse—silently convert in an unexpected way. The checker helps by validating that date types are behaving like dates and that day differences are reasonable.

In practice, the checker is most useful for verifying input consistency (dates, rates, principal, period definitions) before you compute interest.

When to run it

A spreadsheet-checker approach pays off at predictable points in your workflow. Instead of doing an “end-of-file review,” run checks at the stages below, where errors are most likely to be introduced.

1. After importing data

   • Right after you paste CSV exports or pull values from an accounting system, dates and numeric fields are most likely to be mis-typed.

2. After you change any rate or day-count setting

   • If you update a rate (e.g., an annual percentage) or adjust the day-count basis, rerun immediately so you can confirm the schedule still behaves consistently.

3. Before you propagate formulas across periods

   • A common failure mode: formulas copy down, but one column references the wrong range. A checker workflow can validate that outputs row-by-row match the expected pattern.

4. Before calculating totals shown to stakeholders

   • Especially if you report a single combined “interest total,” verify the sum matches the recomputed structure. This catches rounding and reference issues early.

5. After sorting or filtering

   • Sorting can break row alignment. If you sort, rerun checks to confirm principal, start/end dates, and rates still match the intended periods.

If you maintain multiple tabs (e.g., Inputs, Schedule, Totals), treat the checker as a gate—not just a quick glance. You’re validating that the schedule is internally coherent.

What “coherent” means in practice

Use these quick items each time you run the checker:

(Gentle reminder: spreadsheet behavior can vary by locale and import method, so treat these checks as sanity checks rather than a guarantee. When results materially differ from expectations, pause and investigate the inputs.)

Try the checker

You can use the workflow in DocketMath to keep interest calculations grounded in verified inputs. Start by using the interest tool entry point, then apply the checker concept to validate the schedule.

• Use this link to begin: **Run interest in DocketMath

Here’s how to sanity-check typical spreadsheet inputs for Canadian interest calculations before you run the full computation:

1) Validate dates (start/end) and day counts

• Confirm your start and end values are actual dates (not text).
• Check that day differences behave smoothly across rows.
• If you have a “days” helper column, ensure it matches what your formula expects.

Expected behavior:

• For consecutive periods, day counts should change predictably.
• For a fixed period length, days should repeat.

2) Confirm rate scaling and units

Canadian interest workflows often involve an annualized rate. Ensure your spreadsheet uses the same unit everywhere:

• Annual rate as 0.06 (6%) or 6%, consistently
• If your calculator expects a decimal, entering 6 instead of 0.06 typically creates a ~100× error

Quick test:

• Temporarily isolate a single row with a small principal and a short date range.
• Compare the spreadsheet result to an estimate like:
  principal × rate × (days / day-count basis)
  (Use the basis your sheet claims to follow.)

3) Verify period structure vs compounding

If you have compounding, confirm:

• whether compounding happens per day, per month, or per period row
• that your “period boundaries” align with your compounding frequency

Rule of thumb:

• Simple interest should scale linearly with time.
• Compounded interest should grow faster than linear when rates are positive.

4) Ensure totals match the schedule

Before treating outputs as final:

• Sum computed interest per row and compare it to the sheet’s “total interest.”
• If totals differ:

  • small discrepancies often point to rounding strategy
  • large discrepancies usually mean your row inputs aren’t aligned (or formulas point to the wrong ranges)

5) Use one “known-good” row as a calibration anchor

Pick a row where you can manually estimate reliably:

• short time window
• round numbers for principal and rate
• clean date difference

Then compare your spreadsheet output to the estimate (or to DocketMath output for that row). If it matches, you’ve confirmed the mechanics. If it doesn’t, fix the inputs/formulas before scaling to the whole schedule.

A practical input/output mental model

When inputs change, outputs should shift in a predictable direction:

Related reading

• Interest rule lens: Maine — The rule in plain language and why it matters
• Common interest mistakes in Rhode Island — Common errors and how to avoid them
• Worked example: interest in Maine — Worked example with real statute citations