Income and work

Invoice aging report for freelancers: 0-30, 31-60 and 60+ days

Build a simple invoice aging report in a sheet: balance and age per invoice, three buckets (0-30, 31-60, 60+ days), totals and a sample layout.

Editorial illustration for Invoice aging report for freelancers: 0-30, 31-60 and 60+ days

An invoice aging report groups what clients still owe you by how long it has been outstanding. For a freelancer, three buckets are enough: 0-30 days, 31-60 days and 60+ days. List each unpaid invoice with its balance and age, assign it to a bucket, and total each bucket. The result shows at a glance how much of your money is getting old.

What columns does the invoice list need?

  • A: Invoice number
  • B: Client
  • C: Invoice date, entered as a real date
  • D: Invoice amount
  • E: Amount received so far
  • F: Balance, which is D minus E
  • G: Age in days
  • H: Bucket

Choose one starting point for age and keep it. Counting from the invoice date is simplest. Counting from the due date is gentler and tells you how overdue something is. Both are fine as long as you do not mix them.

Which formulas make the buckets work?

With the first invoice in row 2, use these and fill down. They work in Excel and Google Sheets.

  • Balance in F2: =D2-E2
  • Age in G2: =IF(F2>0,TODAY()-C2,"")
  • Bucket in H2: =IF(F2<=0,"Paid",IF(G2<=30,"0-30",IF(G2<=60,"31-60","60+")))
  • Total for a bucket: =SUMIFS($F$2:$F$200,$H$2:$H$200,"0-30")

Repeat the total formula with the other two bucket labels. A grand total of =SUM($F$2:$F$200) should equal the three bucket totals added together. If it does not, an invoice has fallen into no bucket.

TODAY() changes every day, so a saved report from last month will show different ages tomorrow. To keep a history, copy the three bucket totals as plain values into a small table each week, with the date beside them.

What does a sample report look like?

Hypothetical balances, with ages counted at the day you run the report:

InvoiceClientBalance (₹)Age (days)Bucket
INV-114Client A45,000120-30
INV-113Client B18,000270-30
INV-110Client C30,0004431-60
INV-108Client A25,0005831-60
INV-104Client D60,0007360+
INV-101Client C15,0009560+

Bucket totals: 0-30 days is 45,000 plus 18,000, which is ₹63,000. The 31-60 bucket is 30,000 plus 25,000, which is ₹55,000. The 60+ bucket is 60,000 plus 15,000, which is ₹75,000. The grand total is ₹1,93,000, and the 60+ bucket is about 39 per cent of it.

How do I see which client sits where?

Sort the list by client, or build a pivot table with client as rows, bucket as columns and the sum of balance as values. In the sample, Client C appears in two buckets, 31-60 and 60+, which means a pattern and not a one-off. Client A has a fresh invoice and an invoice nearly in the 60+ bucket, which is worth a message today.

A variation is to age from the due date instead and use buckets of not yet due, 1-30 days overdue and more than 30 days overdue. That version tells you how late rather than how old, and some freelancers find it easier to act on.

How often should I run it?

Weekly is enough for most freelancers. Pick one day, update the received amounts first, then read the three totals. Write them in a small history table so you can see whether the 60+ bucket is growing. If your bank statement is the source of what has been received, importing it into SpendixAI gives you dated credits to copy from, while the invoice list itself stays in your sheet.

What to do with the report

  1. Send reminders for everything in 31-60.
  2. Phone the contact for every invoice in 60+.
  3. Stop accepting new work from any client with a growing 60+ balance until you have spoken.
  4. Use the 60+ total to adjust your cash plan. Treat it as late, not lost, but do not spend it.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports