📌 Stage 2 · The Invoice Register · Invoicing & AR · NET 30 Terms Math · 30–40 min

NET 30 by formula, not by memory

Learning goals

  • Compute a due date as invoice date plus the customer's terms in days
  • Explain NET 30 and how NET 15 differs, using the live Blue Fern Bakery invoices
  • Describe why the due date column feeds status, aging, and the entire dunning cadence

Concepts

Terms as data

A due date typed from memory is a guess that drifts. The register computes it: invoice date plus the Days column of that customer's row. Excel dates are serial numbers under the hood (Sep 1, 2026 is 46266), so adding 30 is ordinary arithmetic that lands on a real date and formats itself as one.

The VLOOKUP reaches into the Customers sheet for the days - column 4 of the B5:E104 range - and FALSE keeps it an exact match, so an unknown code surfaces as #N/A instead of silently borrowing a stranger's terms.

=$C5+VLOOKUP($D5,Customers!$B$5:$E$104,4,FALSE)

$C5 is the invoice date. VLOOKUP finds this row's customer code ($D5) in the Customers master range and returns column 4, the numeric terms (30 for NET 30, 15 for NET 15). Date plus number equals the due date: May 26, 2026 + 30 = Jun 25, 2026.

NET 30 and its cousins

NET 30 is the US default: payment due 30 days after the invoice date, giving the customer's AP department one monthly check run. Five of Cedar & Co.'s six customers run on it - INV-1082 dated May 26, 2026 comes due Jun 25, 2026.

Blue Fern Bakery LLC runs NET 15: tighter terms for a small, thin-margin business. Both of its live invoices show the 15-day math: INV-1113 dated Aug 27, 2026 is due Sep 11, 2026, and INV-1116 dated Sep 8, 2026 is due Sep 23, 2026 - which is why a invoice raised mid-September is already 7 days past due at the close.

Two variants you will meet in the wild, handled outside this workbook: '2/10 NET 30' offers a 2% discount for payment within 10 days; 'due on receipt' means days = 0.

💡 Tips:

  • A NET 30 due date that lands on a weekend or bank holiday is still legally the due date; some businesses state 'next business day' terms - a policy choice, not a formula change.
  • Changing a customer's Days value affects only invoices raised afterward whose due dates have not been computed yet - in a live file, existing due-date cells keep their cached results until recalculated.

The column everything reads

Due Date looks like a courtesy column. It is actually the spine of the workbook. The status ladder compares it to the as-of date (Lesson 6). Days Overdue subtracts it from the as-of date (Lesson 9). The aging buckets are ranges of days measured from it (Lesson 10). The dunning cadence anchors every reminder and escalation to it (Lesson 12).

That is also why the due date must be a formula: if terms or dates were ever wrong, fixing the master data and recalculating repairs the entire downstream chain at once, with no stale typed dates hiding in the middle.

Practice

Open on Invoices. Cell I5 (Due Date of INV-1082) is blank - retype the terms math and let the whole column's pattern click.

  1. Type the due-date formula — Cell I5 is blank. Type =$C5+VLOOKUP($D5,Customers!$B$5:$E$104,4,FALSE) and press Enter. I5 returns Jun 25, 2026: May 26 plus C05 Marigold Interiors' 30 NET 30 days.
  2. Check the NET 15 rows — Find INV-1113 (row 12): Aug 27, 2026 + 15 = Sep 11, 2026. Find INV-1116 (row 14): Sep 8, 2026 + 15 = Sep 23, 2026. Same formula, different terms - because the days live in master data, not in the formula.
  3. Check a future due date — INV-1119 (row 17) is dated Sep 24, 2026 and due Oct 24, 2026. At the Sep 30 as-of date it is not yet due - the Days Overdue column will say -24 in Lesson 9.
  4. Read the column top to bottom — Scan I5 through I17: every due date is a Wednesday-to-whenever real date, no two customers share a rhythm, and the six Overdue rows in column L all have due dates before Sep 30, 2026.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the terms formula into I5 and got Jun 25, 2026 for INV-1082
  • ☐ Verified Blue Fern Bakery's NET 15 invoices: INV-1113 due Sep 11, 2026 and INV-1116 due Sep 23, 2026
  • ☐ Confirmed INV-1119 dated Sep 24, 2026 is due Oct 24, 2026 - not yet due at the September close
  • ☐ Noted the baseline: 11 of 13 invoices run NET 30 math; the two Blue Fern Bakery rows run NET 15