Spreadsheet checks before running interest in Australia

Sanity‑checking spreadsheets before you run interest is one of the easiest ways to avoid bad numbers ending up in affidavits, settlement models, or submissions. This guide walks through a practical “checker” mindset you can apply manually, or automate with DocketMath’s interest calculator for Australia.

The focus here is pre‑calculation checks: making sure your spreadsheet is structurally sound and jurisdiction‑ready before you click “calculate”.

What the checker catches

Think of a “spreadsheet checker” as a short list of questions you ask every time you’re about to run interest. Below are the main issues it should catch, and how they show up in practice.

• 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

Interest in Australia is heavily date‑driven (accrual periods, judgment dates, statutory rate changes). Common issues:

• Invalid or text dates

  • Cells formatted as text ("31/02/2024"), or US vs AU order (03/04/2024 meaning 3 April vs 4 March).
  • Hidden time components (e.g. 2024‑06‑01 13:42) that can shift day counts in some formulas.

• Inconsistent date conventions

  • Mixing:

    • claim period dates,
    • judgment dates, and
    • payment dates
      without clearly separating them.

• Out‑of‑range or impossible dates

  • Dates before the cause of action.
  • Dates after the “as at” date you intend to calculate to.

Checks to run:

• Confirm all date columns are:

  • Formatted as dates, not text
  • Using a consistent locale (e.g. DD/MM/YYYY for AU)
  • Within a plausible range for your matter

• In Excel/Sheets, sort by date column:

  • Look for:

    • Blank dates in the middle of data
    • Dates that jump backwards (e.g. 2024 to 1900)

2. Amount and currency issues

For Australian interest calculations, you typically assume AUD unless your matter involves foreign currency. The checker should flag:

• Mixed or missing currency assumptions

  • No clear note that everything is AUD.
  • Some rows in AUD, others in a foreign currency, with no conversion step.

• Non‑numeric amounts

  • Commas as thousands separators in CSV imports that turn into text.
  • Symbols or annotations in amount cells (e.g. 10,000* or 5,000 (estimate)).

• Sign errors

  • Credits entered as positive instead of negative (or vice versa).
  • Reversed cashflows (payments vs charges) in running balance models.

Checks to run:

• Ensure all amount columns:

  • Are numeric (no #VALUE! errors)
  • Use a consistent sign convention (e.g. positive = principal owed; negative = payments)
  • Have a clear currency label (e.g. “Amount (AUD)”)

• Use a quick sum:

  • Sum the principal column and confirm it matches your instructions or pleadings.
  • Sum the payments column and cross‑check against bank statements or ledger totals.

3. Rate and method alignment with Australian rules

DocketMath is jurisdiction‑aware. For Australia, you’ll often need to distinguish between:

• Pre‑judgment interest under relevant state or federal provisions (e.g. Supreme Court Acts, Federal Court of Australia Act).
• Post‑judgment interest where courts specify a rate or refer to a published rate.
• Contractual interest (if any) that may override or sit alongside statutory rates.

The checker should confirm:

• Which rate source applies

  • Statutory rate (and which jurisdiction: NSW, VIC, QLD, etc.).
  • Court‑specified rate.
  • Contractual rate (fixed or variable).

• How the rate is expressed

  • Annual rate (e.g. 6% per annum).
  • Margin over a reference rate (e.g. “3% above the Cash Rate Target”).
  • Whether the rate changes over time.

• Compounding vs simple interest

  • Many statutory regimes in Australia use simple interest.
  • Contractual terms may call for monthly or daily compounding.

Checks to run:

• Confirm in your spreadsheet:

  • A clear “Interest basis” note (e.g. “NSW pre‑judgment simple interest at Supreme Court Act rate”)
  • A column or separate sheet listing rate periods and values if the rate changes over time
  • A flag for whether interest is simple or compounded, and if compounded, at what frequency

4. Period coverage and gaps

Interest calculations are sensitive to when principal is outstanding. The checker should confirm that:

• Every amount has:

  • A clear start date (from when interest runs)
  • A clear end or “as at” date (to when interest runs, or the date of payment)

• There are:

  • No overlapping periods that double‑count interest on the same principal.
  • No unintended gaps if your model assumes continuous accrual.

Practical steps:

• For line‑by‑line items (e.g. invoices, advances):

  • Sort by start date.
  • Ensure:

    • Start date ≤ end date on every row.
    • End dates don’t precede the overall calculation “as at” date without a reason (e.g. fully paid).

• For running balance models:

  • Check that each transaction date is:

    • On or after the previous transaction date.
    • Within the overall claim period.

5. Formula and range errors

Before you feed data into DocketMath or rely on spreadsheet‑only calculations, scan for:

• Broken formulas

  • #DIV/0!, #REF!, #VALUE!, or circular reference warnings.
  • Formulas that stop one row early (e.g. filled down to row 99 when data goes to 120).

• Partial ranges

  • SUM or AVERAGE formulas that exclude new rows added at the bottom.
  • Named ranges that no longer cover all data.

• Hard‑coded “temporary” values

  • Interest rates typed directly into formulas instead of referencing a single cell.
  • “As at” dates typed in multiple places.

Checks to run:

• Use spreadsheet tools:

  • Show formulas and visually scan for inconsistent patterns
  • Use error‑checking (Excel) or explore “inconsistent formula” flags
  • Replace hard‑coded rates and dates with cell references where possible

When to run it

You don’t need a full audit every time you open a file. Use the checker at key points in your workflow:

1. Before first calculation for a new matter

   • When you first convert pleadings, ledgers, or statements into a calculation‑ready spreadsheet.
   • This is where date formats and sign conventions are most likely to go wrong.

2. After major data changes

   • Adding a new batch of invoices, payments, or rate periods.
   • Changing the assumed “as at” date (e.g. updating to the latest hearing date).

3. Before exporting or filing

   • When you’re about to:

     • Generate an interest schedule for an affidavit.
     • Attach a calculation to submissions.
     • Share a model with the other side.

4. When changing jurisdictions or rate bases

   • Moving from pre‑ to post‑judgment interest.
   • Switching from one Australian jurisdiction’s statutory rate to another.
   • Re‑running the model under a contractual rate for comparison.

For many teams, it’s enough to build a short “pre‑run checklist” into your matter template and tick it off before each major recalculation.

Try the checker

You can apply this checker manually in Excel or Google Sheets, but it becomes more powerful when combined with a jurisdiction‑aware calculator.

With DocketMath’s Australian interest tool at /tools/interest, you can:

• Import or paste your cleaned spreadsheet data.

• Specify:

  • Jurisdiction (e.g. NSW, VIC, QLD, Federal).
  • Interest type (pre‑judgment, post‑judgment, or contractual).
  • Rate assumptions and compounding basis.
  • “As at” date for the calculation.

• See how outputs change when you adjust:

  • The “as at” date (e.g. to a new hearing date).
  • The applicable rate or method (e.g. comparing statutory vs contractual interest).
  • Whether payments are treated as reducing principal first or interest first, where that’s relevant to your scenario.

A typical workflow looks like this:

1. Prepare your spreadsheet

   • Clean dates, amounts, and rates using the checks above.
   • Label columns clearly (e.g. Date, Amount (AUD), Type, Notes).

2. Run a first pass in DocketMath

   • Load the data into the /tools/interest calculator.
   • Select your Australian jurisdiction and interest assumptions.
   • Generate an interest schedule and summary.

3. Compare and refine

   • If you already have a legacy spreadsheet model, compare results.
   • Differences often reveal:

     • Incorrect date ranges.
     • Missed transactions.
     • Mis‑applied rates.

4. Document your assumptions

   • Add a short note in your

Related reading

• Interest rule lens: Maine

• Common deadlines mistakes in Australia

• Spreadsheet checks before running deadlines in California

• Why we started the DocketMath blog