Spreadsheet checks before running interest in Singapore

Sanity-checking a spreadsheet before you run interest on it is usually faster than fixing a bad result afterwards—especially in Singapore, where rate changes, compounding, and court practice can all matter.

This post walks through how to use a simple “spreadsheet checker” mindset before you plug anything into DocketMath’s Singapore interest calculator, and what kinds of problems you can catch early.

What the checker catches

Think of the checker as a short pre-flight list you run on your spreadsheet. You’re not deciding what interest rules apply (that’s a legal question); you’re checking whether the data is clean enough that any Singapore interest calculation you run will behave as expected.

Below are common issues and how they affect interest outputs.

1. Date problems

Interest in Singapore almost always turns on dates: when sums became due, when they were paid, and the period the court or contract specifies.

Key checks:

• All dates are real calendar dates

  • Watch for: 31/11/2023, 2023-13-01, or text like "TBA".
  • Why it matters: invalid dates may be treated as text or converted incorrectly, shifting the interest period.

• Date formats are consistent

  • Common clash: DD/MM/YYYY vs MM/DD/YYYY.
  • In mixed datasets (e.g., exports from different systems), confirm:

    • A value like 03/04/2023 is really 3 April 2023, not 4 March 2023.
  • In Excel/Sheets, sort the date column. Abrupt jumps (e.g., 2023 → 2024 → 2023 again) can reveal mis-parsed dates.

• No unintended time components

  • If you see values like 2023-05-01 18:32, confirm whether the time is relevant.
  • Many interest models treat days as whole units; stray times can create off‑by‑one‑day effects in some setups.

2. Amount and currency issues

Next, confirm that the principal amounts you’re about to run interest on actually match what you think they are.

Checks:

• Numeric, not text

  • Cells should be numbers, not "1,000.00" as text.
  • Quick test: change number format (e.g., to 4 decimal places). If nothing changes, it’s probably text.

• Currency consistency

  • Identify whether the sheet mixes:

    • SGD and foreign currencies
    • Pre- and post-conversion amounts
  • If there’s a currency column, scan it for unexpected codes (e.g., USD in a mostly SGD sheet).

• Sign direction (debits vs credits)

  • Decide your convention:

    • Option A: claims positive, payments negative
    • Option B: separate columns for “claim” and “payment”
  • Then verify:

    • No payments accidentally entered as positive “additional claim”
    • No claim amounts accidentally entered as negative

A quick pivot table or subtotal by party or invoice can reveal if totals look plausible before you run interest.

3. Duplicates and gaps

Interest calculations in Singapore often depend on when money was actually outstanding. Duplicates and missing rows distort that story.

Checks:

• Obvious duplicates

  • Filter or use conditional formatting on:

    • Invoice/reference number
    • Date + amount combination
  • Confirm whether repeated entries are:

    • Genuine (e.g., instalments with the same amount), or
    • Accidental copy-pastes.

• Missing rows for known events

  • If you know of:

    • Part payments
    • Waivers
    • Reversals
    • Settlement payments but they’re not in the sheet, decide whether they should be captured before interest is run.

4. Interest-related flags and categories

Your spreadsheet may need to distinguish between amounts that follow different interest logic, for example:

• Pre‑judgment vs post‑judgment amounts
• Contractual interest vs default statutory interest under Singapore law
• Sums that do not attract interest (e.g., certain costs, or as agreed by parties)

Useful structural checks:

• Is there a clear “interest-eligible?” column?

  • e.g., Yes/No, Y/N, 1/0.

• Are categories mutually exclusive?

  • If you have a “Type” column (e.g., Principal, Costs, Interest already charged), confirm:

    • Each row has exactly one type.
    • No row is both “Principal” and “Interest already charged”.

This structure lets you segment your data before feeding it into DocketMath, so you can run different scenarios cleanly.

5. Rate, period, and compounding fields

Even if you don’t store all legal assumptions in the spreadsheet, you may have columns that hint at how interest should run.

Common fields:

• “Interest rate”
• “From date” / “To date”
• “Judgment date”
• “Payment date”
• “Compounding frequency”

Checks:

• Rate format is clear

  • Decide: is 5 equal to 5% or 0.05?
  • Standardise: either convert all to decimals or all to percentages, and label the header accordingly.

• Interest periods make sense

  • From date should not be after To date.
  • No negative durations when you compute To – From.

• Compounding flags are consistent

  • If you have a “Compounded?” column:

    • Confirm only rows that should be compounded are marked.
    • If Singapore law or the contract disallows compounding for a certain period, ensure that’s reflected in your flags or segmentation.

Remember: DocketMath will not decide which interest regime applies—that’s a legal and factual question. The checker’s role is to make sure your spreadsheet can support whichever Singapore interest scenario you ultimately choose.

When to run it

You don’t need a heavy checklist for every tiny claim, but there are clear points in a Singapore workflow where a quick spreadsheet sanity check pays off.

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. Before you upload or paste into DocketMath

Run the checker when you:

• Export data from:

  • Accounting systems
  • Case management tools
  • Bank statements
• Are about to:

  • Upload a CSV
  • Paste rows into DocketMath’s /tools/interest interface
  • Share a workbook with colleagues as the “source of truth” for interest

At this point, small corrections (e.g., fixing dates or signs) are cheap and visible.

2. After major edits or merges

Run the checker again if you:

• Merge:

  • Multiple matters into one workbook
  • Old and new system exports
• Perform bulk updates:

  • Global find‑and‑replace on dates or amounts
  • Mass formula changes

These operations are where subtle formula misalignments and off‑by‑one row errors often appear.

3. Before sharing figures externally

If you plan to:

• Provide an interest schedule to opposing counsel
• Support a Singapore court filing or affidavit with interest workings
• Circulate a settlement calculation internally for sign-off

…run the checker. The goal is not to certify legal correctness, but to:

• Ensure the spreadsheet is internally coherent
• Make it easier to explain how DocketMath produced the final interest numbers

Try the checker

You can apply the ideas above in any spreadsheet tool (Excel, Google Sheets, Numbers). A lightweight, reusable pattern looks like this:

1. Create a “Checks” sheet

   • Add formula-based tests, such as:

     • =IF(ISNUMBER(A2),"","Check: date not numeric")
     • =IF(B2<0,"","Check: amount expected negative")
   • Use conditional formatting to highlight:

     • Non-dates
     • Negative durations
     • Unexpected currencies

2. Add a “Status” column to your data

   • Example values:

     • OK
     • Check date
     • Check amount sign
   • Filter to only non‑OK rows before running interest.

3. Segment your data for scenarios

   • Use filters or helper columns to create:

     • A view of amounts that should follow one Singapore interest rule
     • A separate view for another rule (e.g., different periods or rates)
   • Run each segment through DocketMath’s interest calculator separately, documenting:

     • The input range
     • The assumptions used (rate, period, compounding, jurisdiction = SG)

4. Document what you did

   • Add a “Notes” column or a separate “Methodology” sheet:

     • How dates were interpreted (e.g., DD/MM/YYYY)
     • How rates were expressed (e.g., 5 = 5% p.a.)
     • Which rows were excluded from interest and why

This makes it much easier to revisit or justify your figures later, especially if Singapore interest rules or factual assumptions are challenged.

To test your cleaned data with jurisdiction-aware logic, run it through DocketMath’s Singapore‑aware interest calculator. The tool will respect the dates and amounts you’ve prepared; the checker just helps ensure those inputs behave as you expect.

Related reading

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

• Why we started the DocketMath blog

• Why we started the DocketMath blog