Your Invoice Table Is a Cash-Flow Dashboard: Computing AR Turnover and DSO in SQL + JS

Here's a pattern almost every product with invoicing eventually hits: revenue is up, the growth chart looks fantastic, and yet the bank balance is oddly thin. The founder asks, "Where's the money?" and somebody opens a spreadsheet.

The answer is almost always sitting in your own database. Customers have been invoiced, but they haven't paid yet. That unpaid pile is called accounts receivable (AR), and two numbers derived from it tell you how healthy your collections are:

• AR turnover ratio: how many times per period you collect your average receivables balance.
• DSO (days sales outstanding): the average number of days it takes to get paid after a sale.

If you build billing, invoicing, or any B2B SaaS, you can ship these as dashboard metrics in an afternoon. This post walks through the formulas, the SQL, the JavaScript, and the gotchas that make the naive version wrong.

The formulas

Average AR   = (Beginning AR + Ending AR) / 2
AR Turnover  = Net Credit Sales / Average AR
DSO          = Days in Period / AR Turnover

A few definitions worth being precise about:

• Net credit sales means sales made on credit (invoiced, not paid at the point of sale), minus returns and allowances. Cash sales don't belong in the numerator, because they never create a receivable.
• Beginning AR is the receivables balance at the start of the period; Ending AR is the balance at the end.
• We average the two so a single unusual day-end balance doesn't distort the picture.

A quick worked example. A retailer has $600,000 of annual net credit sales. AR was $45,000 at the start of the year and $55,000 at the end:

Average AR  = (45,000 + 55,000) / 2 = 50,000
Turnover    = 600,000 / 50,000      = 12 times per year
DSO         = 365 / 12              ≈ 30.4 days

Twelve turns a year means the company collects its average receivables balance roughly monthly. Customers take about 30 days to pay.

You can check numbers like these without writing any code using an accounts receivable turnover calculator. It also supports multiple currencies and a side-by-side period comparison, which is handy for sanity-checking whatever you build below.

Step 1: a minimal schema

You don't need much. Assume a Postgres-flavored schema like this:

CREATE TABLE invoices (
  id            BIGSERIAL PRIMARY KEY,
  customer_id   BIGINT NOT NULL,
  issued_on     DATE   NOT NULL,
  due_on        DATE   NOT NULL,
  amount_cents  BIGINT NOT NULL CHECK (amount_cents >= 0),
  paid_on       DATE            -- NULL means still unpaid
);

CREATE TABLE credit_notes (
  id            BIGSERIAL PRIMARY KEY,
  invoice_id    BIGINT NOT NULL REFERENCES invoices(id),
  issued_on     DATE   NOT NULL,
  amount_cents  BIGINT NOT NULL CHECK (amount_cents >= 0)
);

Two choices to notice. Money is stored as integer cents, because floats and money don't mix. And paid_on is nullable, so "unpaid" is simply paid_on IS NULL. This is a simplification: it ignores partial payments. If you take partial payments, you'll want a separate payments table and an outstanding balance per invoice, and I'll flag where that changes the queries.

Step 2: net credit sales for a period

-- Net credit sales between :start and :end (inclusive)
SELECT
  COALESCE((
    SELECT SUM(amount_cents)
    FROM invoices
    WHERE issued_on BETWEEN :start AND :end
  ), 0)
  -
  COALESCE((
    SELECT SUM(amount_cents)
    FROM credit_notes
    WHERE issued_on BETWEEN :start AND :end
  ), 0) AS net_credit_sales_cents;

Gross invoiced amount minus credit notes issued in the same window. Simple, and it matches the "minus returns and allowances" part of the definition.

Step 3: AR balance as of a date

This is the query that trips people up, because AR is a snapshot, not a flow. You're asking: "As of this date, which invoices had been issued but not yet paid?"

-- Outstanding receivables as of :as_of
SELECT COALESCE(SUM(amount_cents), 0) AS ar_cents
FROM invoices
WHERE issued_on <= :as_of
  AND (paid_on IS NULL OR paid_on > :as_of);

The paid_on > :as_of clause is the important part. An invoice that's paid today was still receivable last month. If you only filter on paid_on IS NULL, you'd compute today's AR and wrongly call it historical AR.

For a period starting on :start and ending on :end:

• Beginning AR = the query above with :as_of = :start - 1 day
• Ending AR = the query above with :as_of = :end

(If you track partial payments, replace SUM(amount_cents) with the sum of each invoice's outstanding balance as of that date, which means joining payments with paid_on <= :as_of.)

Step 4: the metrics in JavaScript

Pull those three numbers out of the database and let a small pure function do the math:

function arMetrics({ netCreditSales, beginningAR, endingAR, daysInPeriod = 365 }) {
  const averageAR = (beginningAR + endingAR) / 2;

  // No receivables at all: ratios are undefined, not zero or Infinity.
  if (averageAR === 0) {
    return { averageAR, turnover: null, dso: null, arPctOfSales: null };
  }

  const turnover = netCreditSales / averageAR;
  const dso = daysInPeriod / turnover;
  const arPctOfSales = (averageAR / netCreditSales) * 100;

  return { averageAR, turnover, dso, arPctOfSales };
}

const result = arMetrics({
  netCreditSales: 60_000_000, // $600,000 in cents
  beginningAR: 4_500_000,
  endingAR: 5_500_000,
});

console.log(result.turnover.toFixed(1)); // "12.0"
console.log(result.dso.toFixed(1));      // "30.4"

Keep this function pure and free of database access. It's trivially unit-testable:

import { test, expect } from "vitest";

test("12 turns and ~30 day DSO for the retail example", () => {
  const r = arMetrics({
    netCreditSales: 60_000_000,
    beginningAR: 4_500_000,
    endingAR: 5_500_000,
  });
  expect(r.turnover).toBeCloseTo(12, 5);
  expect(r.dso).toBeCloseTo(30.42, 2);
});

test("zero receivables does not divide by zero", () => {
  const r = arMetrics({ netCreditSales: 1_000, beginningAR: 0, endingAR: 0 });
  expect(r.turnover).toBeNull();
});

Gotcha #1: quarterly and monthly periods

This is the mistake I'd bet is most common. If you compute turnover over a quarter, the ratio is "per quarter," so you can't divide 365 by it.

Say a quarter has $150,000 of net credit sales and a $50,000 average AR:

Turnover (this quarter) = 150,000 / 50,000 = 3
DSO = 91 / 3 ≈ 30.3 days      ✅ use days in the period
DSO = 365 / 3 ≈ 121.7 days    ❌ wrong

Either use the actual number of days in the period (the daysInPeriod parameter above), or annualize the turnover first (3 turns a quarter is about 12 a year, then 365 / 12 ≈ 30.4). Both give you the same answer; mixing them gives you a number four times too big.

Gotcha #2: averaging two points hides spikes

The "(beginning + ending) / 2" formula is the textbook version, and it's fine for a classroom or a rough read. But if your business is seasonal, two snapshots can be badly unrepresentative. A retailer with a huge December and quiet January–November could show a misleading average depending on which two dates you pick.

In a real dashboard, compute AR at the end of each month and average all of those:

const monthEndBalances = [4_500_000, 4_700_000, 5_200_000 /* ... */];
const averageAR =
  monthEndBalances.reduce((a, b) => a + b, 0) / monthEndBalances.length;

You can then pass that averageAR in place of the two-point average. It costs a few more queries and makes the metric much harder to game accidentally.

Gotcha #3: a high ratio isn't automatically good

A higher turnover means faster collection, which usually means healthier cash flow. But an extremely high ratio can also mean your credit terms are so strict you're turning away customers. Turnover is a signal to investigate, not a score to maximize.

Context matters too. Rough rules of thumb you'll see quoted: retail often runs around 10-15 turns a year, manufacturing around 6-10, and healthcare around 5-8 (insurance and payer delays drag it down). Treat any benchmark table as a sanity check rather than a target; your own trend over time is a far better comparison than someone else's average.

Step 5: aging buckets, the report finance actually uses

Turnover and DSO are averages. They won't tell you which invoices are the problem. For that, finance teams use an aging report, which groups unpaid invoices by how overdue they are:

SELECT
  CASE
    WHEN CURRENT_DATE <= due_on            THEN 'current'
    WHEN CURRENT_DATE - due_on <= 30       THEN '1-30 days late'
    WHEN CURRENT_DATE - due_on <= 60       THEN '31-60 days late'
    WHEN CURRENT_DATE - due_on <= 90       THEN '61-90 days late'
    ELSE '90+ days late'
  END AS bucket,
  COUNT(*)          AS invoices,
  SUM(amount_cents) AS outstanding_cents
FROM invoices
WHERE paid_on IS NULL
GROUP BY bucket
ORDER BY MIN(CURRENT_DATE - due_on);

Now your dashboard can show both the headline ("DSO is 41 days, up from 33") and the explanation ("most of the increase is two invoices in the 90+ bucket"). That's a far better product experience than a single number.

A practical tip: render the buckets as a horizontal stacked bar. Anything past 60 days should be visually loud.

Gotcha #4: the boring data-quality traps

These won't show up in the textbook formula but will absolutely show up in production:

• Disputed invoices. An invoice under dispute is technically receivable but practically doubtful. Consider a status column so you can include or exclude them deliberately.
• Time zones. issued_on and paid_on as plain DATE avoids a lot of off-by-one-day boundary bugs at period edges. If you store timestamps, normalize to one zone before comparing.
• Multi-currency. Never add EUR cents to USD cents. Convert at a documented rate, or compute the metrics per currency.
• Write-offs. If you write off bad debt, it should leave AR. Otherwise your turnover will look permanently terrible.
• Backdated payments. If users can record a payment with a past date, your historical AR snapshots will change after the fact. Decide whether that's acceptable, or snapshot month-end balances into their own table.

Putting it together

The whole feature is smaller than it looks:

1. Two SQL queries: net credit sales, and AR as of a date.
2. One pure function: arMetrics.
3. One aging query for the "why."
4. A handful of tests, including the zero-receivables and partial-period cases.

When you're verifying your output against a known-good reference, this free AR turnover and DSO calculator lets you enter net credit sales and the beginning and ending balances, then shows turnover, DSO, average AR, and AR as a percentage of sales. If your code and an independent tool disagree, you've found a bug (usually Gotcha #1) before your customers do.

Takeaways

• AR turnover = net credit sales / average AR. DSO = days in the period / turnover.
• AR is a snapshot as of a date, so filter on paid_on > :as_of, not just paid_on IS NULL.
• Use the days in your period when computing DSO, not a hardcoded 365.
• Averages hide problems; pair them with an aging report.
• Keep the math pure, store money as integers, and test the zero case.

Your turn

Do you track DSO in your product today, or is collections still a spreadsheet exercise on someone's laptop? And if you've built an aging report, what's the nastiest edge case you hit (partial payments, credit notes, multi-currency)? Drop it in the comments.

If this helped, a ❤️ or 🦄 makes it easier for other devs to find. Follow along for more practical "finance meets code" breakdowns.