Practical Guide · Research-based spreadsheet design. Google Sheets formula documentation checked 30 October 2026. The numbers below are fictional illustrations, not a report of client results or a claim that a downloadable template has been tested.
A $3,000 project can look better on an invoice than it feels on your calendar. If the client needs a second round of interviews, two more revisions and a weekend handoff, the agreed fee stays the same while the hours keep moving. A freelance project profitability tracker makes that gap visible without turning your business into a bookkeeping exercise.
This guide builds a lean Google Sheets system for fixed-fee client work. It records the fee you agreed, every hour you actually worked, direct cash costs and payments received. The result is a project-level contribution figure and an effective hourly earning rate. Neither is a substitute for tax accounts, a full profit-and-loss statement or a billable-hours invoice.
What a freelance project profitability tracker should answer
When you review a project, five questions matter more than a decorative dashboard: How much was the fee? What did you spend directly to deliver it? How much of your time did it consume? How much money has the client actually paid? Would you quote the same work at the same price again?
Those questions describe three different views. Project economics compare the contracted fee with the costs and time attributable to that engagement. Cash flow shows money received. Monthly workload shows when you did the work. One figure cannot accurately stand in for all three.
For a first version, avoid tracking every possible business metric. Use four tabs called Projects, Time, Costs and Payments. Add a small Monthly view only after the underlying entries work.
Set up the Projects tab without confusing revenue and cash
Make one row per engagement, not one row per client. A client might commission a website in January and another deliverable in March; treating these as separate projects lets you see which type of work is sustainable.
| Column | Header | What belongs here |
|---|---|---|
| A | Project ID | A unique code such as ACME-01; never recycle it. |
| B | Client | A short display name. |
| C | Agreed fee | The current contracted amount, excluding taxes you collect for authorities. |
| D | Status | Quoted, active, completed or cancelled. |
| E | Total hours | Calculated from the Time tab. |
| F | Direct costs | Calculated from Costs; do not include your own working time here. |
| G | Contribution | Fee minus direct costs, before your own labor and shared overhead. |
| H | Effective hourly earnings | Contribution divided by your recorded hours. |
| I | Contribution margin | Contribution divided by agreed fee. |
The project ID is the quiet hero of this design. Use exactly the same ID on each time, cost and payment entry. A client name might be spelled three ways. A controlled project ID reduces accidental mismatches.
If a project is still only a proposal, keep it out of realized-work comparisons or label it clearly. A quote is not revenue. If the scope changes and both sides agree to a different fee, update the agreed-fee field and keep the amendment alongside your contract. Our freelance scope-of-work template helps define what the original fee includes.
Record every hour, including work you cannot invoice separately
On the Time tab, create six columns: Date | Project ID | Task | Hours | Billable under contract? | Note. Enter decimal hours: 0.5 for 30 minutes, 1.25 for an hour and fifteen minutes. If you prefer start and end times, calculate duration in a separate column before mixing formats.
For fixed-fee work, the “billable” flag describes whether the task was included in the commercial scope, not whether it creates an extra invoice. Kickoff calls, status updates, quality checks and revisions still consume time. Include them in Total hours even when the client would never see those line items.
A sample row might read: 2026-10-12 | ACME-01 | Final revisions | 2.5 | Yes | Second feedback pass. Another could be: 2026-10-13 | ACME-01 | Unplanned handoff call | 0.75 | No | Requested after sign-off. Both hours count in the project economics.
Update time at the end of a work block rather than reconstructing it after a month. If you already use a timer, export entries to the same columns. Do not force a second time-tracking tool into your workflow just to create this report.
Separate direct project costs from business overhead
On the Costs tab, use Date | Project ID | Type | Amount | Note. Direct costs might include a subcontractor hired only for that engagement, purchased project assets or a client-specific service charge. Enter each amount once, in the same currency as the project’s fee, excluding recoverable tax where appropriate.
A general software subscription that supports many clients is different. Put shared subscriptions, rent and other overhead in a separate business expense record; do not silently charge a whole month’s subscription to each active project. If a tool is purchased exclusively for one project, document that exception. The IRS recordkeeping guidance is one useful example of why receipts and source documents should stay connected to financial records; local accounting and tax rules still take precedence.
Your own work hours do not appear here as an extra cash expense in this starter model. They are captured through the effective hourly earnings calculation. Subtracting an arbitrary wage for those same hours and then interpreting earnings per hour without care creates a confusing metric. If you later build a true labor-cost model, label it separately and specify the internal cost rate you chose.
If the client reimburses expenses on top of the fee, track that reimbursement separately and decide whether you are comparing pass-through money or retained project revenue. For a simple first pass, use the net amount the business actually keeps as the fee base and avoid treating a reimbursement as margin.
Paste these Google Sheets formulas into row 2
With the column order above, use the following formulas in Projects. These formulas are examples for Google Sheets, not a plugin or a paid template; copy them down for additional project rows.
| Cell | Formula | Meaning |
|---|---|---|
| E2 | =SUMIF(Time!$B:$B,$A2,Time!$D:$D) |
Add hours for this project ID. |
| F2 | =SUMIF(Costs!$B:$B,$A2,Costs!$D:$D) |
Add direct costs for this ID. |
| G2 | =C2-F2 |
Contribution before your time and overhead. |
| H2 | =IFERROR(G2/E2,"") |
Contribution per hour; blank until hours exist. |
| I2 | =IFERROR(G2/C2,"") |
Contribution margin; format as percent. |
In some Google Sheets locales, formula argument separators differ. If your sheet reports a parse error, check its locale and adjust separators rather than changing the economic logic. The official Google Sheets function list documents functions including SUMIF, SUMIFS, IFERROR and EDATE.
Do a basic check before relying on the file: make one fake project worth $1,000, enter four hours, add $100 of direct costs and confirm that contribution is $900 and effective hourly earnings are $225. Then add one more hour and see whether the effective rate falls to $180. This tests the relationship between your own inputs and the output, not every edge case in an accounting system.
A worked example: two projects, two different lessons
Consider two fictional fixed-fee projects completed in one month. Project A has a $3,000 fee, $300 in direct costs and 36 hours of your time. Project B has a $1,800 fee, $100 in direct costs and 14 hours.
| Metric | Project A | Project B |
|---|---|---|
| Agreed fee | $3,000 | $1,800 |
| Direct costs | $300 | $100 |
| Contribution | $2,700 | $1,700 |
| Time spent | 36 hours | 14 hours |
| Effective hourly earnings | $75.00 | $121.43 |
| Contribution margin | 90.0% | 94.4% |
Project A produced more contribution dollars, but Project B used your time more efficiently. That does not mean you should automatically reject the next Project A. Perhaps it helped you enter a market or build reusable work. It does mean that the two deserve different pricing or scope questions the next time they appear.
Neither figure tells you your personal take-home pay. Income taxes, shared overhead, the time spent winning projects and business development work are still outside this simple project calculation. The measure is useful because its limitations are clear.
Add a Payments tab so an unpaid invoice does not look like cash
Use Payment date | Project ID | Amount received | Invoice reference. Record actual receipts, including partial payments, not the date an invoice was sent. In a separate Projects column J called Cash received, use =SUMIF(Payments!$B:$B,$A2,Payments!$C:$C). In K, Unreceived fee, use =C2-J2.
“Unreceived fee” is a rough monitoring flag, not a legally correct accounts-receivable balance if you have not yet invoiced, have issued credits, or have tax and reimbursements outside the quoted fee. For formal accounting, reconcile to invoices and your books. An overdue amount calls for a documented follow-up process, not an automatic judgment about the client’s profitability; our proposal follow-up guide illustrates how to write a concise, professional follow-up, although invoice reminders need their own facts and dates.
Why keep payments separate? A profitable project can still create a cash squeeze when payment arrives late. Conversely, a deposit can arrive this month for work you will perform next month. Labeling both as this month’s “profit” hides the timing difference.
Get monthly hours without inventing monthly profit
Create a Monthly tab with an actual date in cell A1, such as 2026-10-01, formatted as “October 2026.” Add three lines: Hours worked, Direct costs entered and Cash collected. For each metric, sum events whose dates fall on or after A1 but before the first day of the next month.
Hours worked: =SUMIFS(Time!$D:$D,Time!$A:$A,">="&$A$1,Time!$A:$A,"<"&EDATE($A$1,1))
Direct costs logged: =SUMIFS(Costs!$D:$D,Costs!$A:$A,">="&$A$1,Costs!$A:$A,"<"&EDATE($A$1,1))
Cash received: =SUMIFS(Payments!$C:$C,Payments!$A:$A,">="&$A$1,Payments!$A:$A,"<"&EDATE($A$1,1))
Copy A1 to the next row using =EDATE(A1,1) if you later build a month-by-month history; adjust formula references to that row. The three totals help with capacity planning and cash awareness. They are not a monthly profit-and-loss statement because project fees, work and payments can fall in different months.
If you need recognized revenue by month or a formal tax summary, use the method your accountant requires. The IRS small-business tax guide describes the distinction between cash and accrual methods in the U.S.; rules vary elsewhere. The tracker is an internal decision aid, not accounting advice.
Handling multiple currencies and subcontractors without breaking the totals
Never add $1,000, €1,000 and ₩1,000,000 together as though they shared a unit. For cross-border projects, choose one reporting currency and enter the exchange rate actually used for each transaction or a documented period rate appropriate to your reporting purpose. Preserve the original amount, original currency, conversion date or rate source and converted amount. Do not quietly recalculate old earnings using today’s exchange rate.
A subcontractor’s invoice belongs in the Costs tab if it is attributable to that project. Do not include the subcontractor’s hours in your personal effective hourly earnings denominator. If you want an agency-wide productivity measure later, add a separate field for external labor hours and define it explicitly. Mixing your own hours with someone else’s makes the number hard to interpret.
For remote client work, date differences matter too. Use a consistent date convention for payments and timesheets, especially if a bank posts a transfer in a different time zone. Our guide to working across time zones with clients covers how to agree on handoff times without constant availability.
Review it once a week and after every finished project
Five minutes on Friday is enough to catch missing hours and invoices while they are still fresh. Then, once a project ends, compare its contribution and effective hourly earnings with your initial estimate. A simple closeout note might read: “Two extra revision cycles; 5.5 hours beyond estimate; raise next proposal by a fixed revision allowance or narrow the included review rounds.”
That note is where the tracker becomes a business system rather than another spreadsheet. The best next step may be a clearer contract, fewer simultaneous clients or a better handoff, not a more elaborate dashboard. A client offboarding checklist gives the project a clear finish line so final delivery and access removal are not forgotten.
Weekly review checklist: Are all work sessions logged? Does every cost have a project ID and receipt? Did any agreed fee change? Are partial payments recorded only once? Are any nonbillable requests signaling scope drift? Which finished project would I quote differently next time?
Finally, keep the file simple enough to use on a busy day. A tracker that you update with honest numbers will teach you more than a sophisticated template that you abandon after the first week.
Common mistakes to catch before you trust the result
“My margin is 90%, so I keep 90%.” No. The displayed contribution margin is before your own labor cost and shared business overhead. It is not net profit.
“An invoice was sent, so the money arrived.” No. Track deposits on their actual payment date and reconcile credits and refunds separately.
“Only billable hours matter.” Not for fixed-fee project efficiency. Scoping, meetings and revisions all consume capacity even when they are not separate line items.
“A bigger project must be a better project.” Compare contribution dollars, effective hourly earnings and strategic value together. One metric should not make the decision for you.
“The formula proves my records are right.” A formula accurately summarizes whatever you entered, including omissions and duplicates. Check a small fictional example before making pricing decisions with real data.
Start with one recent completed engagement, not every project you have ever accepted. Build the five tabs, validate the simple arithmetic, and review the outcome. If the next quote changes because you finally saw where the hours went, the tracker has done its job.
