How to sanity-check spreadsheets before running interest in Massachusetts

Running prejudgment or postjudgment interest in Massachusetts is unforgiving: one wrong date, one mislabeled column, and your totals can drift far from what Mass. law actually produces.

DocketMath’s interest spreadsheet checker is designed to catch the most common spreadsheet problems before you click “calculate.” This post walks through what it checks, when to use it, and how small tweaks in your inputs can change your outputs.

What the checker catches

The checker focuses on issues that quietly distort Massachusetts interest calculations.

• Date ordering problems (end date before start date).
• Rates entered as whole numbers instead of percentages.
• Missing caps or discounts in the spreadsheet.
• Inconsistent rounding or day-count conventions.

1. Date problems

Massachusetts interest is extremely date‑sensitive, so the checker inspects every date column you map.

It looks for:

• Blank required dates

  • Example: A row is missing “Judgment date” but has a principal amount.
  • Result: That row can’t be included in a judgment‑to‑payment interest run.

• Impossible or suspicious dates

  • Dates far in the past or future (e.g., 1901, 2099).
  • Payment dates before the claim or judgment date.
  • Negative or zero‑length periods where interest shouldn’t accrue.

• Mixed formats that break parsing

  • Some entries as 01/02/2024, others as 2024-01-02, others as text like Jan 2 24.
  • Cells that look like dates but are actually text.

How it affects your outputs:

• A missing start date usually means DocketMath will skip that row or require you to fix it.
• A payment before the start date can generate negative or zero interest, which is rarely what you intend.
• A column that can’t be parsed as dates means no interest will be computed for those rows.

2. Amount and sign issues

The checker validates the numeric columns you map as principal, damages, or payments.

It flags:

• Non‑numeric amounts

  • Cells with $, commas, or stray characters that stop them from parsing as numbers.
  • Example: "1,200.00 " vs. 1200.00.

• Unexpected signs

  • Positive numbers where you’ve mapped a payment column.
  • Negative numbers where you’ve mapped a principal or damages column.

• Zero‑amount rows

  • Rows with all zeros but complete dates.
  • These rows add noise and can confuse later review.

How it affects your outputs:

• A payment column with positive numbers might be treated as additional principal, inflating interest.
• Non‑numeric cells are treated as blanks, so those rows won’t behave as you expect.
• Zero‑amount rows can clutter Explain++ breakdowns and make audits harder.

3. Column mapping mismatches

The checker compares your column mappings to what the Massachusetts interest calculator expects.

It looks for:

• Missing required fields

  • Example: For a simple Mass. judgment interest run, you typically need:

    • A principal or judgment amount column
    • A start date (e.g., judgment date)
    • An end date (e.g., payment date or “as of” date)

• Ambiguous mappings

  • Mapping the same spreadsheet column to two different roles (e.g., “Judgment date” and “Payment date”).
  • Leaving key columns unmapped when they clearly look like amounts or dates.

• Inconsistent usage across rows

  • Some rows using a “Claim date” column, others leaving it blank while using a different date column.

How it affects your outputs:

• Missing or inconsistent mapping can:

  • Exclude rows from the calculation.
  • Apply wrong interest periods.
  • Force you to re‑upload once you notice odd totals.

4. Massachusetts‑specific sanity checks

The checker doesn’t interpret Massachusetts law for you, but it can flag patterns that are unusual for US‑MA interest runs, such as:

• Extremely long interest periods

  • Claims or judgments that appear to run for decades.
• Unusually large principal values

  • Outliers that are orders of magnitude larger than neighboring rows.

When to run it

You can run the checker multiple times as you refine your spreadsheet. In practice, these are the best checkpoints:

Run the checker before importing a spreadsheet into the Interest workflow. It is especially helpful when you have multiple entries or when a teammate provided the inputs.

1. Right after you finish your first draft spreadsheet

Before you share a draft with anyone, run it through the checker:

• ✅ Confirm your date columns are consistent.
• ✅ Confirm principal vs. payment signs are correct.
• ✅ Confirm required columns are present and mapped.

This catches structural mistakes before they spread into multiple versions.

2. After any major data import or merge

If you’ve:

• Combined exports from different systems,
• Copied in data from a PDF or scanned ledger,
• Added rows from a second matter,

run the checker again. Mixed formats and hidden text values almost always appear during merges.

3. Before you rely on totals in negotiations or filings

When you’re close to relying on a number in:

• A settlement discussion,
• A draft demand letter,
• A mediation brief,

run the checker one more time, then:

1. Fix any flagged rows.
2. Re‑run the Massachusetts interest calculator.
3. Review the Explain++ breakdown for a few sample rows to confirm the logic.

Try the checker

Here’s a simple way to use the checker with the Massachusetts interest calculator:

1. Prepare your spreadsheet

   • One row per:

     • Claim,
     • Invoice,
     • Judgment component,
     • Or payment (depending on your workflow).
   • Include at least:

     • A principal or judgment amount column.
     • A start date column (e.g., “Judgment date” or “Breach date”).
     • An end date or payment date column if you’re calculating to specific payments.

2. Upload to DocketMath

   • Go to the interest calculator.
   • Choose Massachusetts (US‑MA) as the jurisdiction.
   • Upload your spreadsheet file.

3. Map your columns

   • Map:

     • Amount columns (principal, damages, or judgment).
     • Date columns (claim, judgment, payment, as‑of).
   • If you have payments, map them as payments (often negative amounts) rather than as additional principal.

4. Run the checker

   • Before you click “Calculate,” run the spreadsheet checker.
   • Review the report:

     • Fix date parsing issues.
     • Clean up non‑numeric cells.
     • Correct sign conventions (e.g., payments as negatives).
     • Add or map any missing required columns.

5. Re‑upload if needed

   • If you edited the spreadsheet in Excel or Sheets:

     • Save a new version.
     • Re‑upload.
     • Re‑map columns.
     • Re‑run the checker until warnings are either resolved or consciously accepted.

6. Calculate and review

   • Once the checker is clean or only has expected warnings:

     • Run the Massachusetts interest calculation.
     • Use Explain++ to drill into:

       • One or two typical rows.
       • Any row with unusually high interest.
   • Confirm:

     • Start and end dates match your expectations.
     • Payments are applied in the right order.
     • Totals align with your internal sense check.

Related reading

• Why we started the DocketMath blog
• Introducing Explain++: step-by-step calculation breakdowns
• How to structure payments and credits for interest calculations
• Common mistakes in prejudgment interest spreadsheets