Subcontractor Payment Tracking Spreadsheet: The 7-Day Clock and the 150% Cap

A framing sub sends pay application #4 claiming $104,880. Your superintendent walks the roof and says it is 25 percent framed, not the 35 percent the sub billed. So you do what most general contractors do: you park the whole check until the two of you agree. Your subcontractor payment tracking spreadsheet shows the invoice as "on hold", and on-hold feels safe.

In California it is not safe, and the spreadsheet cannot tell you why. The dispute is worth $11,210 after retainage. By holding $104,880 over it for 38 days, you have run up a $3,522.60 statutory penalty, handed the sub a claim for attorney's fees, and given the state a cause for discipline against your license. The sub was wrong about the roof. You still lost.

Most payment logs record what was paid and when. That is a checkbook. A real subcontractor payment log has to answer two questions before the check goes out: what is the most you can lawfully hold, and what is the latest day you can pay it. This article builds the sheet that answers both, using a $412,000 framing subcontract and five subs paid out of one owner payment.

Two Ways to Lose Money on a Sub Payment

A GC can get a sub payment wrong in two directions, and they pull against each other.

• Pay too fast or too much. You pay for work that is not in place. If the sub then walks off the job, every dollar you paid ahead of the work is gone, and you still need someone else to finish at a replacement price.
• Pay too slow or hold too much. You sit on money the law says belongs to the sub. On a California job that costs 2 percent a month plus the other side's legal bill. On a federal job it costs interest under the Prompt Payment Act.

Most offices guard against only one of these, depending on who got burned last. The PM who lost a sub mid-job holds everything. The owner who got a nasty letter from a sub's lawyer pays everything. A tracking sheet that knows both limits turns that from temperament into arithmetic.

What the law actually says

California Business and Professions Code section 7108.5 is the clearest version, so it runs the example. Read the whole section once. It has five subsections.

1. A prime contractor or subcontractor must pay a sub no later than seven days after receipt of each progress payment, unless otherwise agreed to in writing.
2. In a good faith dispute, you may withhold no more than 150 percent of the disputed amount.
3. A violation carries a penalty, payable to the sub, of 2 percent of the amount due per month for every month payment is not made.
4. In a collection action, the prevailing party gets attorney's fees and costs.
5. It applies to private and public works, except where Public Contract Code section 10262 applies.

Retention has its own clock. Under California Civil Code section 8814, once you receive all or part of a retention payment you have 10 days to pay each sub its share, and you can withhold no more than 150 percent of the estimated value of a disputed amount. Section 8818 sets the same 2 percent monthly penalty for a late retention payment.

On federal work, 31 U.S.C. 3905(b) requires every subcontract to carry a clause obligating the prime to pay within 7 days out of amounts the agency pays it, plus an interest penalty clause at the rate set under section 3902(a).

Other states set their own number of days and their own rate. The sheet below takes both as input cells, so the structure holds wherever you build. Note the phrase "unless otherwise agreed to in writing" in the California statute: your subcontract's payment clause is the first place to look, and the statute is what applies when it says nothing.

The Pay Application Ledger: Claimed, Approved, Billed

The mistake starts with a ledger that stores one percent-complete number per line. You need three, because three different people have an opinion about the same line.

Rows 5 to 10 hold the lines. Three helper columns do the work:

• =B5*D5 in column G is what the sub says it has earned to date.
• =B5*E5 in column H is what you agree it has earned. This is the number you can defend with photos and a daily log.
• =MAX(0,B5*(D5-E5)) in column I is the gross disputed amount on that line. Only the roof line is nonzero here, at $11,800.

Column F is the one nobody keeps, and it is the one that hurts. Your own pay application to the owner went in before your superintendent walked the roof, and it passed the sub's 35 percent straight through. The owner paid it. So on the day you tell the sub the roof is only 25 percent done, you are holding the owner's payment for the very 10 percent of roof you refuse to pay for. Flag it on every line:

=IF(F5>E5,"HOLDING SUB MONEY","OK")

That flag does not decide the dispute, but it tells you how the dispute will look. California's statute measures what you owe the sub as the amounts "allowed the contractor on account of the work performed by the subcontractors". Your own signed pay application saying 35 percent is a bad exhibit to bring to an argument about 25. Fix the owner billing on the next cycle, or accept that you are financing your side of the dispute with money that was released for the sub's work. If you bill the owner off a schedule of values, the progress billing template article covers how to keep the owner-facing percentages tied to what you have actually verified in the field.

The Most You Can Lawfully Hold

With the ledger in place, the payment block sits under it. Retainage is 5 percent in this subcontract (cell B14) and the sub has been paid $142,215 to date (cell B15), which is the prior $149,700 earned less retainage.

B18 is the cell that changes behavior. It says you may hold up to $16,815 against an $11,210 dispute. That is more than the dispute, and it is all you get. The 150 percent gives you a cushion for being partly wrong about the value of the disagreement. It does not give you the check.

B19 is the floor. Whatever else happens, $88,065 leaves your account by the deadline. Between B19 and B20 is your choice: pay $88,065 and keep the full 150 percent cushion, or pay $93,670 and hold exactly the disputed amount. Both are lawful. Holding the entire $104,880 is not.

Four policies on the same pay application

Here is what each policy costs. The owner's payment arrives on August 6, 2026, so the 7-day deadline is August 13. The roof dispute settles on September 20, 38 days after the deadline. "Cushion" is the approved value of work in place ($248,300) minus what you have paid the sub to date: the money standing between you and a loss if the sub leaves the job tomorrow.

Policy A's penalty is 2 percent a month on the $88,065 you had no right to hold, for two months, because 38 days late crosses into the second month. The sheet counts any started month as a full month, which is the reading that cannot surprise you: =B19*0.02*ROUNDUP(38/30,0). That is $3,522.60 before anyone's lawyer sends an invoice, and the fee-shifting sentence means the sub's lawyer is billing on your account from the first letter.

Policy B looks generous and is the dangerous one. After paying everything claimed, you have paid $247,095 against $248,300 of verified work. Your whole cushion is $1,205. If the framer leaves, you owe a replacement crew the remaining $163,700 of work at whatever premium a crew that shows up next week charges. At a 20 percent premium, which is an assumption to replace with your own last experience, that is $32,740 of extra cost against $1,205 of protection. The sub's retainage looks like protection until you notice you have already prepaid most of it on a roof that is not framed.

Policies C and D keep the cushion and cost nothing. C is the right default when you are not sure of your own number. D is right when the daily log and photos make you sure.

Tie the dispute column to your backcharge log as well. A backcharge the sub has not acknowledged is a disputed amount like any other, and the same 150 percent cap applies. The backcharge tracking spreadsheet article shows how to document one so it survives the conversation.

The Seven-Day Clock Starts When You Get Paid

The deadline is not measured from the sub's invoice date or from your approval. It runs from the day you receive the owner's payment. That means the payment log needs a date most logs never store: when the owner's money landed.

Build a second tab with one row per sub per owner payment. Put the statutory days in B2 (7 in California, or whatever your subcontract says in writing) and the monthly penalty rate in B3 (2 percent).

Column G uses TODAY() for unpaid rows on purpose: an unpaid line past its deadline shows a growing number every morning, which is the only kind of warning anyone reads. Add a status column with =IF(F5<>"","PAID",IF(TODAY()>E5,"LATE",IF(E5-TODAY()<=2,"DUE","OPEN"))) and sort by it.

If your subcontracts carry pay-when-paid language, the clock and the contract interact, and the gap between receipt and payment is where your cash goes. The pay-when-paid cash flow tracker article models that side.

Your Check Run Is the Violation

Here is the finding that matters more than any single dispute. Most small and mid-size GCs cut sub checks once a month, on a fixed day, because the bookkeeper works that way. Put that habit into the clock tab.

The owner's payment lands August 6. The office runs sub checks on the 25th. Every sub paid out of that owner payment is paid 12 days after the deadline, which the sheet counts as one month late.

What did the office gain by holding $224,235 for 19 days? Even at 4.00 percent on a sweep account, $466.90 of interest. The exposure it bought was $4,484.70. If every month runs the same way, that is $53,816.40 a year of penalty exposure to earn $5,602.80 of float. Most subs never send the letter. The one who does sends it after you have fired them, with twelve months of rows attached.

The fix costs nothing. Run sub checks weekly on a fixed weekday. Any owner payment then waits at most seven days for the next run, which fits inside a 7-day clock with no exceptions to manage. Keep the monthly run for suppliers whose terms are net 30.

What Has to Be True Before a Check Leaves

The floor and the deadline tell you when and how much. They do not tell you whether the payment is safe to release. Three documents gate every check, and each deserves a column that turns red:

1. Lien waiver for the prior payment. A conditional waiver for this payment and an unconditional waiver for the last one. The lien waiver tracking article covers the matching logic. Section 7108.5 says nothing about waivers, so a waiver hold stands on your subcontract. Check that it makes the waiver a condition of payment before you rely on it to stop the clock.
2. Current insurance certificate. An expired certificate on a sub who is still on site means you cannot prove that sub is insured on your job.
3. Sub-tier payment status. If the framer's lumber yard has sent you a preliminary notice, a joint check may be the safer way to pay. The joint check tracking article shows how to split one payment across the sub and its supplier without double paying either.

One formula gathers them: =IF(AND(J5="Y",K5>=TODAY(),L5="Y"),"RELEASE","HOLD: "&IF(J5<>"Y","waiver ","")&IF(K5<TODAY(),"COI ","")&IF(L5<>"Y","sub-tier","")). It names the missing document, so the PM who sees "HOLD" knows which phone call to make before the deadline in column E arrives.

The Recommendation

Stop tracking what you paid and start tracking what you owe, by when, and how much you can hold. Concretely:

• Store three percentages per line: claimed, approved, and billed to owner. Flag any line where you billed the owner more than you approved.
• Compute the floor every pay cycle: due as claimed, minus 150 percent of the disputed amount net of retainage. Pay at least that by the deadline, every time.
• Record the date the owner's money landed and run the deadline from it, not from the sub's invoice.
• Move sub checks to a weekly run. At $224,235 a month of sub payables, the monthly habit costs $4,484.70 of exposure per cycle to earn $466.90.
• Gate release on the waiver, the certificate and sub-tier status, and let the sheet name the missing one.

The Construction Budget Tracker already has the base this sits on. Its payment tracking tab logs every invoice from every sub and supplier and filters by vendor, status and date, so you can see who has been paid, who is pending and what is due this week. Its dashboard shows percentage complete against percentage spent, which is the project-level version of the cushion column in the policy table. Add the three-percentage ledger, the 150 percent floor and the owner-receipt date from this article, and the question "can I hold this check?" gets answered by a cell before anyone asks your lawyer.