Spreadsheet checks before running interest in Vermont

Before you calculate prejudgment or post‑judgment interest in Vermont, your spreadsheet needs to be clean, consistent, and jurisdiction‑aware. A small date error or mislabeled rate column can quietly swing your Vermont interest result by thousands of dollars.

This guide walks through how to sanity‑check your spreadsheet before you run Vermont interest calculations with DocketMath’s interest tool, and what the built‑in checker is actually looking for.

What the checker catches

DocketMath’s spreadsheet checker focuses on three things for Vermont (US‑VT):

1. Dates and timelines
2. Interest rates and rate changes
3. Principal, payments, and balances

Here’s how it approaches each.

• 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. Dates and timelines

Vermont interest depends heavily on when things happen: judgment date, accrual start, payment dates, and any statutory rate changes.

The checker looks for:

• Unparseable dates

  • Cells that look like dates but aren’t valid (e.g., 13/32/2023, 2023-00-10).
  • Mixed formats that Excel/Sheets interpret inconsistently.

• Out‑of‑order events

  • Payment dates that come before the alleged start date.
  • Accrual end dates that are earlier than the start date.
  • Events that fall outside the Vermont interest period you specify.

• Suspicious gaps or overlaps

  • Long unexplained gaps with no balance or activity.
  • Multiple events on the same date that don’t add up (e.g., two different “starting balances” on the same day).

Quick self‑check before you upload (no need to use literal checkboxes):

• Confirm all date columns use a single, consistent format (e.g., YYYY-MM-DD).
• Make sure no date cell contains text like "N/A", "tbd", or comments.
• Verify that the start date ≤ every event date ≤ end date.

2. Interest rates and Vermont‑specific rules

Vermont law uses different interest rates depending on context and time period (for example, statutory judgment interest vs. a contract rate). DocketMath doesn’t decide which rate applies—that’s a legal question—but once you choose the rate, the checker verifies that your spreadsheet is internally consistent.

The checker looks for:

• Missing or conflicting rate columns

  • A rate column with some blank cells in the middle of an active period.
  • Two rate columns (e.g., Rate and Judgment Rate) both populated for the same rows.

• Implausible rate values

  • Rates that look like 250 when you probably meant 2.50%.
  • Negative rates.
  • Mixed notation like 0.12 in some rows and 12 in others for the same period.

• Rate change alignment

  • If you specify Vermont rate changes by date (e.g., a new statutory rate effective on a certain date), the checker verifies that:

    • No two “effective” dates overlap.
    • There are no gaps where no rate is defined while the balance is non‑zero.

How to structure your rate data:

Use a clear, explicit pattern. For example:

The checker expects:

• One primary rate column in percent or decimal (you choose, but be consistent).
• Any rate changes to appear as rows with new rates, not silent changes mid‑range.

3. Principal, payments, and balances

Interest in Vermont is typically calculated on a principal balance that changes over time. The checker aims to ensure those changes are arithmetically coherent.

It looks for:

• Mismatched signs

  • Payments recorded as positive when they should be negative (or vice versa).
  • Refunds or chargebacks that don’t match the sign convention used elsewhere.

• Impossible balances

  • Balances that jump without a corresponding principal change row.
  • Balances that go negative when your configuration assumes no negative balances.

• Missing required columns

  • For DocketMath’s interest calculator, you’ll usually want:

    • A date column
    • A principal change (or “transaction amount”) column
    • Optionally, a running balance column (the checker can recompute this to cross‑check)
    • An interest rate column (if not using a single global rate)

Simple Vermont‑ready layout:

A: Date
B: Description
C: Principal Change (positive = increase, negative = payment)
D: Interest Rate (annual, %)
E: (optional) Running Balance

The checker will:

• Reconstruct the implied running balance from column C.
• Compare it to any balance you provide in column E.
• Flag rows where the difference exceeds a small tolerance.

When to run it

You’ll get the most value from the checker before you run any Vermont interest calculation and whenever you change your spreadsheet’s structure, rate assumptions, or date ranges.

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.

Use it before:

• First‑time Vermont interest runs

  • When you’re adapting an existing spreadsheet from another state to Vermont (US‑VT).
  • When you’ve just imported data from a case management or billing system.

• Switching from “flat” to time‑aware interest

  • Moving from a single “days × rate × principal” line to a timeline of events.
  • Adding multiple rate periods (e.g., pre‑judgment vs. post‑judgment).

• Producing work product for others

  • Before sharing a Vermont interest schedule with:

    • Opposing counsel
    • A client
    • A court or mediator

Run it again after:

• Editing date ranges

  • Changing the claimed start date or end date.
  • Adding or removing payments.

• Changing Vermont rate assumptions

  • Updating to a new statutory rate.
  • Switching from a contract rate to a statutory rate or vice versa.

• Re‑using a template

  • Copying a prior matter’s spreadsheet to a new Vermont case and overwriting rows.
  • Adding new columns for notes, categories, or allocations.

Try the checker

You can use the checker directly inside the DocketMath interest tool for Vermont (US‑VT). The flow is:

1. Prepare your spreadsheet

   • Make sure you have:

     • One date column.
     • One principal change (or transaction) column.
     • Either:

       • A single Vermont rate you’ll enter in the tool, or
       • A rate column with values per row.
   • Save as .xlsx or .csv.

2. Upload and map columns

   • In DocketMath’s interest calculator, select Vermont (US‑VT).
   • Upload your spreadsheet.
   • Map:

     • Date → your date column.
     • Principal change → your transaction column.
     • Rate → either:

       • A global Vermont rate, or
       • The rate column in your sheet.

3. **Run the checker (before calculating)

   • Use the checker option to scan:

     • Date validity and order.
     • Rate consistency and coverage.
     • Principal changes and any provided balances.
   • Review the issues list; typical outputs include:

     • Rows where dates couldn’t be parsed.
     • Rows with missing or conflicting rates.
     • Rows where the implied balance doesn’t match the sheet.

4. Fix and iterate

   • Back in your spreadsheet:

     • Correct flagged dates (typos, wrong year, wrong format).
     • Standardize rate entries (all in %, or all in decimals).
     • Fix sign conventions on payments.
   • Re‑upload and re‑run the checker until:

     • No blocking errors remain.
     • Any warnings are understood and acceptable for your use.

5. **Then run the Vermont interest calculation

   • Once the checker is clean:

     • Run the calculation for the Vermont jurisdiction.
     • Use Explain++ in DocketMath to see step‑by‑step interest accruals and confirm the numbers match your expectations.

For a more robust workflow—especially when documenting Vermont‑specific assumptions—pair the checker with DocketMath’s step‑by‑step breakdowns and your own written notes in the file.

Related reading

• Introducing Explain++: step-by-step calculation breakdowns

• A practical workflow for jurisdiction-aware legal calculations (and how to document them)

• Why we started the DocketMath blog