貸款與房貸還款試算表

Sheet layout — tab "Invoices"

Column	Header	Filled by
A	Invoice #	You
B	Client	You (drop-down from tab "Clients")
C	Issue date	You
D	Amount	You
E	Payment terms (days)	Formula
F	Due date	Formula
G	Paid on	You (leave empty until paid)
H	Status	Formula
I	Days overdue	Formula

Formulas (excerpt, 6 of 10)

E2 — payment terms from the client list =IFERROR(VLOOKUP(B2, Clients!A:C, 3, FALSE), 30) Looks up the client's terms in the "Clients" tab; if the client isn't there, uses 30 days.

F2 — due date =IF(C2="", "", C2 + E2) Adds the terms to the issue date. Stays empty for blank rows.

H2 — status =IF(C2="", "", IF(G2<>"", "Paid", IF(TODAY()>F2, "Overdue", "Open"))) Paid if a payment date is entered; otherwise Overdue after the due date, else Open.

I2 — days overdue =IF(H2="Overdue", TODAY() - F2, "")

Dashboard!B2 — total outstanding =SUMIFS(Invoices!D:D, Invoices!H:H, "<>Paid", Invoices!C:C, "<>")

Dashboard!A5 — balance by client (one formula fills the table) =QUERY(Invoices!B:H, "select B, sum(D) where H <> 'Paid' and B is not null group by B order by sum(D) desc label sum(D) 'Owed'", 1)

Where to paste

• Put E2, F2, H2 and I2 in row 2 of "Invoices", then drag down to row 500
• Paste the two Dashboard formulas once; they update by themselves
• Conditional formatting on H2:H500: text is "Overdue" → light red fill

Edge cases handled

• Partly paid invoice: add a row with the remaining amount and a new number like INV-041b
• Client renamed: change it only in "Clients"; the drop-down keeps rows consistent
• Excel instead of Sheets: all formulas work except QUERY; use a PivotTable on columns B, D, H instead

Check example

Invoice INV-038, client Northside Dental, issued Sep 1, terms 14 days → due Sep 15. Today Oct 20, not paid → Overdue, 35 days.