Freelancer profit and loss (P&L) in a spreadsheet: a one-page layout

A P&L answers one question: after costs, did the business make money this month? If you already keep a list of transactions, building it takes one formula copied into a grid.

The layout

Months across the top, a year-total column at the end, and three blocks down the side:

1. Income: one row per income category, then a total.
2. Expenses: one row per expense category, then a total.
3. Net profit (income minus expenses) and margin % (net profit divided by income).

Keep it to one page. If you need more than about ten rows per block you have too many categories.

Fill it from your transaction list

Assume a sheet called Transactions with Date in column A, Type (Income or Expense) in B, Category in C and Amount in F, and that the P&L has the first day of each month in row 4 (B4 = 1 Jan, C4 = 1 Feb, and so on) and category names in column A. One formula, copied across and down:

=SUMIFS(Transactions!$F:$F,
        Transactions!$B:$B, "Income",
        Transactions!$C:$C, $A6,
        Transactions!$A:$A, ">="&B$4,
        Transactions!$A:$A, "<"&EDATE(B$4,1))

Use "Expense" in the expense block. The dollar signs matter: they let you copy the formula across months and down categories without editing it.

Total income    =SUM(B6:B9)
Net profit      =B11-B24
Margin %        =IF(B11=0, 0, B26/B11)

The IF(B11=0, ...) guard stops a #DIV/0! error in a month with no income.

Add an "Other" row so the totals never lie

The classic bug: you add a transaction with a category that isn't on the P&L, and the monthly totals quietly stop matching your records. Fix it with a catch-all row that is calculated as everything in that month minus the rows listed above it:

Other income  =SUMIFS(Transactions!$F:$F, Transactions!$B:$B, "Income",
                      Transactions!$A:$A, ">="&B$4,
                      Transactions!$A:$A, "<"&EDATE(B$4,1))
               - SUM(B6:B9)

Now a new or misspelled category lands in Other instead of disappearing, and the total always equals what's in Transactions.

What this P&L is, and isn't

It records money in the month it moved, so a December invoice paid in January shows up in January. That's the simplest way to see your real month-to-month position, but it isn't the only accounting method, and the method you must use for official filings depends on where you live and your situation. Treat this as a management view of your business, not a filing document.

Read it in three passes: is net profit positive, is the margin stable, and which single expense category moved the most compared with last month.

General information about using spreadsheets, not financial, tax or accounting advice. Which accounting method and records are required for tax filings depends on where you live; check with your tax authority or a qualified professional.