Freelancer invoice and income tracker: how to see who owes you and what you earn

If you work for clients on your own, three questions come back every month: who still owes me money, what did I actually receive, and what did an hour of work bring in. This page lists what a simple invoice and income tracker should record, shows the formulas, and walks through one invoice with invented numbers.

What to record for every invoice

One invoice per row keeps the log easy to filter and to compare with your bank statement.

• Invoice number and client. So each payment can be matched to a request.
• Project. Useful as soon as one client has more than one job.
• Issue date and amount. The date you sent it and the total.
• Payment terms. The number of days the client has to pay. A default in one settings cell saves typing.
• Paid date and amount received. Leave both empty until the money arrives. Enter a smaller amount for a part payment and the rest stays outstanding.
• Credit notes. Enter them as a negative amount so totals stay correct.

What else to record

Two short logs complete the picture. A time log holds one row per work session with date, client, project, hours and a billable flag. An expense log holds date, category, description and amount. Keep the category list short and editable, for example software and tools, equipment, and office or coworking.

The formulas

Every status and total in a tracker comes from a few steps.

• Due date = issue date + payment terms in days.
• Status = Paid if a paid date exists, Overdue if the report date is after the due date, Due soon if the due date falls inside your chosen window, otherwise Open.
• Days overdue = report date minus due date.
• Days to pay = paid date minus issue date.
• Profit (cash basis) = money received in the month minus expenses in the month.
• Received per billable hour = money received / billable hours.

Use a report date cell. Type a fixed date to look at a past moment, or =TODAY() to see the current day.

Worked example (invented numbers)

These figures are made up for illustration. One invoice of 800 is issued on 1 September with 30 days terms.

1. Due date: 1 September plus 30 days is 1 October.
2. Report date 15 November: days overdue = 45.
3. Ageing bucket: 45 days falls into 31 to 60 days.

Now a small month. You received 3,000, logged 40 billable hours and had expenses of 400, with a set-aside of 25 percent as an editable example.

1. Profit: 3,000 minus 400 = 2,600.
2. Set-aside: 25 percent of 2,600 = 650.
3. Received per billable hour: 3,000 / 40 = 75.

Change the percentage to see what a different rule would keep apart.

Why ageing beats a plain overdue list

A list of overdue invoices treats a three day delay like a three month delay. Buckets separate them. Money in the first bucket often needs a polite reminder. Money in the oldest bucket needs a call and a decision. When you also track average days to pay per client, you can see which clients pay close to terms and which always run late.

Looking at irregular income

Invoiced money and received money differ by weeks. A month with many invoices can be a month with little cash. A cash basis view counts invoices in the month they are issued and money in the month it arrives, so the monthly line shows what really came in. Pair it with a set-aside percentage that you choose, and slow months become easier to plan.

Common mistakes

• Tracking only invoiced money. It hides late payers.
• No report date. Without one, overdue days cannot be measured.
• Mixing part payments into one number. Record the amount received and let the rest stay outstanding.
• Skipping the time log. Without hours you cannot see what a project paid per hour.
• Waiting until year end. Five minutes after each invoice or payment is easier than a weekend of catching up.

Build it yourself or use a ready template

The formulas above fit into a plain spreadsheet, and building your own is a fair way to start. A ready template saves the setup time.

The Freelancer Dashboard is an Excel workbook with 9 tabs: Instructions, Settings, Clients, Projects, Invoices, Time_Log, Expenses, Monthly and Dashboard. The invoice tab holds 400 rows with due date, status, days overdue, days to pay and ageing buckets, and it handles part payments and credit notes. The client tab covers 20 clients with invoiced, received, outstanding, billable hours and average days to pay. The project tab covers 100 projects with agreed fee, estimated and logged hours and received per logged hour. The time log holds 1,000 entries and the expense log 400 rows with 8 editable categories. Monthly and Dashboard show received, expenses, profit, set-aside, hours, ageing, the project pipeline and a chart. Example data is included and marked, so you can delete it before real use. A one page Quick Start PDF is included. It has no macros and opens in Excel and LibreOffice.

What it does not model

Version 1 does not model taxes or VAT of any kind, multiple currencies, payroll, recurring invoices or accrual accounting. It does not create invoice documents. It was recalculated in LibreOffice without errors and has not been tested in Google Sheets.

*This page is general information and a description of a record-keeping tool. It gives no financial, tax, accounting or legal advice. Microsoft Excel is a trademark of its owner. Size Run Studio has no affiliation with Microsoft.*

See the Freelancer Dashboard

Frequently asked questions

What should a freelance income tracker record?

For every invoice: number, client, issue date, amount, payment terms and the date the money arrived. Add a time log and a short expense log. From these a tracker can show what is paid, open or overdue, what you received per hour and your profit by month.

What is invoice ageing?

Invoice ageing sorts unpaid invoices by how long they are past their due date, for example not yet due, 1 to 30 days, 31 to 60 days and 61 days or more. It shows at a glance which money needs a reminder first.

How do I handle irregular income in a spreadsheet?

Look at received money by month, not only invoiced money, and decide on a set-aside percentage of monthly profit that you keep apart. The percentage is your own choice. A tracker only does the arithmetic.

Is this tax or legal advice?

No. A tracker is a record-keeping aid. Ask a qualified adviser how to handle VAT, income tax and invoice requirements in your country.