Blog / Construction Operations

Pay Application Tracking for Subcontractors, Retainage Included

By Keyona Meeks, Founder, ReRev Labs · Oct 31, 2026 · 11 min read

The short answer: Keep one row per subcontractor pay application in a Google Sheet. Type in what the sub billed to date and the retainage percentage that your subcontract says. Formulas work out the retainage held, the net earned to date, what is new this application and a flag for anything past due. A second tab sums it up by sub. This is a tracking sheet. It records what the paperwork says. Your contract, your state and your accountant decide what you owe and when. I tested the formulas in LibreOffice with five made-up applications. I did not run them in Google Sheets.

By the end of this post, you'll be able to:

• Set up a pay application log with retainage worked out for you.
• See at a glance what is waiting, approved or past due.
• Build a one-line-per-sub summary of what is earned, held and paid.
• Know what this sheet does not do.

A sub sends a pay application. It goes to an inbox. Someone opens it, someone else checks the percent complete, and a week later nobody can say whether it was approved, whether the retainage was taken out or whether the check went out. A sub calls to ask. You open six emails to answer.

I sell build work, so you should know I benefit if you hire us. This post is a tracking tool. It is not accounting software and it is not advice on what you owe.

What should a pay application log track?

When I searched "pay application tracking spreadsheet subcontractor" on October 30, 2026, page one was template and spreadsheet sellers, payment and accounts payable software pages, and a few blog posts. Titles only. I did not review them and I name none. A purchased template gives you columns. It does not know your subcontract.

I also do not use the name of any standard industry form as if it were ours. Use whatever form your owner or your company requires. This sheet holds the numbers from it.

For each application, track:

• Who and which one. The sub and the application number.
• Dates. The period it covers, the date you received it and the date payment is due under your subcontract.
• Money. Work completed to date, the retainage percentage and the retainage held.
• Status. Received, In review, Approved, Paid or Disputed.
• Waiver. Whether you received the lien waiver you require.
• A flag. Past due, due soon or no due date.

What does an official source say about retainage and payment timing?

The rules differ by contract, by state and by who the owner is, so I will not tell you what yours says. As one federal example only, here are three clauses.

FAR 52.232-27, the prompt payment clause for construction contracts, says "The due date for making such payments is 14 days after the designated billing office receives a proper payment request," and requires a subcontract payment clause obligating the contractor to pay subcontractors "not later than 7 days from receipt of payment out of such amounts." It also refers to retaining "a specified percentage of each progress payment." Source: FAR 52.232-27, checked October 30, 2026, read through a fetch tool that returned excerpts. I could not tell from the excerpt exactly how paragraph (d) frames that retaining, so read the clause itself.

FAR 52.232-5, payments under fixed-price construction contracts, says "the Contracting Officer may retain a maximum of 10 percent of the amount of the payment." Source: FAR 52.232-5, checked October 30, 2026, same excerpt caveat.

The point is not those numbers. The point is that timing and retainage come from a document, and your sheet should hold the numbers from your document. A private subcontract may say something else entirely. Some states have their own prompt payment and retainage rules, and I did not research them for this post. Check with an attorney, insurer or safety professional as the question requires, and ask your attorney or accountant what applies to you.

How do you set up the Pay Apps tab?

Make a tab named Pay Apps. Put these headers in row 1, spelled exactly like this, in this order:

Use real dates, not text. A date typed as text will not compare to today. Format the date columns as dates, then check that they right-align.

For Status, add a dropdown list with Received, In review, Approved, Paid and Disputed. The formulas look for the exact words Paid and Approved. I did not open Google's help for the dropdown steps this run, so use Google's own help for data validation.

What are the formulas?

Put these in row 2 and fill them down to row 201. Each is wrapped so a blank row stays blank.

G2, Retainage held:

=IF(A2="","",E2*F2)

H2, Net earned to date:

=IF(A2="","",E2-G2)

I2, Previous net earned (the net earned on this sub's previous application number, or 0 for the first one):

=IF(A2="","",IFERROR(SUMIFS(H:H,A:A,A2,B:B,B2-1),0))

J2, This application (net) (the new money since the last application):

=IF(A2="","",H2-I2)

O2, Flag:

=IF(A2="","",IF(K2="Paid","",IF(L2="","No due date",IF(L2<TODAY(),"Past due",IF(L2-TODAY()<=3,"Due soon","")))))

The flag is silent once a row is Paid. A row with no due date says so, because a missing date is the thing most likely to cause a late payment. "Due soon" means within three days. Change the 3 if you want a different window.

Net earned is work to date minus retainage held. This sheet assumes the retainage percentage is a plain share of the work to date. Your subcontract may reduce or release retainage in stages, or compute it differently. If so, type the held amount yourself into column G in place of the formula, or change the formula to match. I do not know what your contract says.

How do you build the Sub Summary tab?

Make a second tab named Sub Summary. Row 1 headers: Sub, Contract amount, Latest app #, Work completed, Retainage held, Percent complete, Net earned, Net paid, Net approved, not paid, Past due apps.

Type each sub's name in column A and the contract amount from the subcontract in column B. Then fill down these from row 2:

C2, Latest app #:

=IF(A2="","",MAXIFS('Pay Apps'!B:B,'Pay Apps'!A:A,A2))

Google's help says MAXIFS "returns the maximum value in a range of cells, filtered by a set of criteria." Source: MAXIFS, checked October 30, 2026.

D2, Work completed:

=IF(A2="","",SUMIFS('Pay Apps'!E:E,'Pay Apps'!A:A,A2,'Pay Apps'!B:B,C2))

E2, Retainage held:

=IF(A2="","",SUMIFS('Pay Apps'!G:G,'Pay Apps'!A:A,A2,'Pay Apps'!B:B,C2))

F2, Percent complete:

=IF(A2="","",IF(B2>0,D2/B2,""))

G2, Net earned:

=IF(A2="","",SUMIFS('Pay Apps'!H:H,'Pay Apps'!A:A,A2,'Pay Apps'!B:B,C2))

H2, Net paid:

=IF(A2="","",SUMIFS('Pay Apps'!J:J,'Pay Apps'!A:A,A2,'Pay Apps'!K:K,"Paid"))

I2, Net approved, not paid:

=IF(A2="","",SUMIFS('Pay Apps'!J:J,'Pay Apps'!A:A,A2,'Pay Apps'!K:K,"Approved"))

J2, Past due apps:

=IF(A2="","",COUNTIFS('Pay Apps'!A:A,A2,'Pay Apps'!O:O,"Past due"))

Format B, D, E, G, H and I as currency and F as a percent.

What does the sheet show with real numbers?

I tested with five made-up applications and three made-up subs. Sub A: application 1 at 10,000 to date with 10 percent retainage, Paid; application 2 at 25,000 to date, Approved. Sub B: application 1 at 5,000 with 5 percent retainage, Received and one day past its due date. Sub C: application 1 at 8,000, In review and due in two days; application 2 at 12,000, Received with no due date.

The Pay Apps tab gave: Sub A application 2 retainage held 2,500, net earned 22,500, previous net earned 9,000 and 13,500 this application. Sub B was flagged Past due. Sub C application 1 was flagged Due soon and application 2 No due date. The Sub Summary gave Sub A latest application 2, work completed 25,000, retainage held 2,500, 50 percent complete, net earned 22,500, net paid 9,000 and 13,500 approved, not paid. I checked every figure by hand. I ran this in LibreOffice 24.2, not in Google Sheets.

How do you test it before you rely on it?

Test on a copy with made-up subs.

1. Enter two applications for one sub. Check that the second shows the right "previous net earned" and "this application."
2. Change one Status to Paid. Check that the flag clears and the Sub Summary "Net paid" rises by that application's net.
3. Clear a Due date. Check that the flag says No due date.
4. Type a date as text. Check that the flag does not behave, then fix the format.
5. Add an application with a new sub that is not on the Sub Summary. Check that you notice it is missing. The summary only lists subs you type in.
6. Compare every figure to the application itself.

What are the limits of "net paid"?

"Net paid" adds up the net of each application marked Paid. It does not know about a partial payment, a deduction, a back charge or a correction to an earlier application. If an application is reissued, I would mark the old row Disputed, leave its numbers alone and add a new row with the next application number. Be careful, because the formulas will count both. Decide with your accountant how to handle corrections before you rely on the totals.

What should this sheet not do?

• It does not decide what you owe. Your subcontract does.
• It does not give legal advice about retainage, timing or lien rights. Ask an attorney.
• It does not replace your books. Your accountant's records are the record. Use this to see what is moving.
• It does not verify the sub's numbers. A person checks percent complete against the work.
• It does not verify waivers or insurance. The Waiver received column only says whether you marked it. Check with an attorney, insurer or safety professional for anything that carries legal, coverage or safety weight.

Where does an LLM fit in this?

Not in the arithmetic. The columns are sums and dates, and formulas do that better than a model. An LLM can help at the front: someone pastes the text of a pay application email and wants the numbers pulled out into a row. A person checks those numbers against the application before they go in. My stance: AI writes the code, plain automation runs it, and an LLM shows up only where the input is messy and a person checks the result. If you are unsure which part of your office to automate first, read what to automate first in a small business.

Where this has limits

• Not run in Google Sheets. I tested the formulas in LibreOffice 24.2 with five made-up applications. Google Sheets should behave the same, but I have not checked. Test it.
• I read the FAR clauses through a fetch tool that returned excerpts. They are a federal example and may not apply to you.
• I did not research state rules. Prompt payment and retainage rules vary.
• One project per sheet. Several projects need several sheets or an added Job column.
• Whole-column formulas. They are simple, and slow on a very large sheet.
• I am not an accountant or a lawyer. If a number carries legal or money weight, ask one.

If subcontractor pay applications are the job nobody has time for, bring the workflow to us on /letstalk. You can also read how we approach this work on our AI-powered operations page. Do not send contracts, pay applications or passwords.

Frequently asked questions

How do I track subcontractor pay applications in Google Sheets?

Keep one row per application with the sub, the application number, work completed to date, the retainage percentage, the status and the due date. Formulas can work out retainage held and the net for each application.

How do I calculate retainage in a spreadsheet?

Multiply work completed to date by the retainage percentage in your subcontract. Net earned is work completed minus retainage held. Your subcontract may release retainage in stages, so check it.

Does this tell me when payment is legally due?

No. Type the due date from your subcontract into the Due column. The sheet flags it. It does not know what the law or your contract says.

Can this replace my accounting software?

No. It tracks the paperwork. Your accounting records are the record.

What about lien waivers?

The sheet has a column to mark whether you received one. What a waiver must say and when you need one are questions for an attorney.

What if an application is corrected?

Mark the old row Disputed, add a new row with the next application number, and decide with your accountant how the totals should count it.

Read next: The change order log tracks the approvals that change what a pay app should include.