How to calculate a trailing drawdown in Google Sheets for a prop firm challenge

By Richard C · AIL Studio

Not financial advice. This is a spreadsheet tutorial for record-keeping. Prop firm rules change, and firms define trailing drawdown differently. Verify with your firm's current rules page before you buy or trade. Nothing here is a trading recommendation.

Prop Firm Rules Cheat Sheet (Free, Oct 2026): a free .xlsx that puts drawdown type, daily loss, profit target, minimum days, payout split and price for 10 prop firms side by side (checked Oct 2026 against each firm's own site).

A trailing drawdown is the rule that ends many prop firm accounts. Unlike a static drawdown, the floor can move up after you make money. Two accounts with the same starting balance and the same dollar limit can fail on different days, depending on whether the firm trails at end of day or intraday, and whether the floor locks at the starting balance.

This article builds only that check in Google Sheets: end-of-day trailing, intraday trailing, the lock, and the mistakes that hide a breach. For a full challenge tracker (daily loss, profit target, consistency and the rest), see How to track prop firm challenge rules in Google Sheets.

What "trailing" means (in plain words)

Static drawdown: the floor is fixed. On a $50,000 account with a $2,000 max drawdown, you fail at $48,000 no matter how high you climbed first.

Trailing drawdown: the floor follows your highest balance upward. After you grow the account, you can fail above the original static floor.

End-of-day (EOD) trailing: the peak (and usually the floor) updates from the balance at the close of the trading day. Intraday open profit that disappears before the close often does not raise the floor.

Intraday trailing: the peak updates from equity while trades are open. A winner that reverses can raise the floor, then leave you too close to it.

Lock at starting balance: many firms stop trailing once the floor reaches the starting balance. After that, the floor does not rise further. Some firms do not lock. Check your plan.

Examples from the Oct 2026 cheat sheet (still verify on the firm's site): end-of-day trailing appears on plans from Topstep, Take Profit Trader and My Funded Futures; intraday trailing appears on Apex's intraday-trail evaluations; static max loss appears on plans from FTMO and FundedNext. Those are illustrations of types, not a promise that any one plan still works that way today.

1. Settings

Cell	Label (column A)	Value (column B)
B1	Starting balance	50000
B2	Max drawdown ($)	2000

These are example data for a $50K-style account. Replace them with your firm's numbers. Whether the floor locks at the starting balance is handled in the floor formula in section 2, not in a separate settings cell.

Static floor for comparison:

B4: =B1-B2      → 48000

2. End-of-day trailing log

Headers in row 9: Date, Daily P&L, Running balance, Peak EOD balance, Trailing floor, Trailing check, Static check (A–G). Put Example data (not real trades) in A8.

Example data (made-up numbers for illustration, not real trades):

Row	A: Date	B: Daily P&L
10	2026-10-01	2500
11	2026-10-02	-1500
12	2026-10-03	800
13	2026-10-06	-2100

You only type columns A and B.

Running balance (column C)

C10: =$B$1+B10
C11: =C10+B11      (fill down to C13)

Peak EOD balance (column D)

D10: =MAX($B$1,C10)
D11: =MAX(D10,C11)      (fill down to D13)

Trailing floor with lock (column E)

E10: =MIN(D10-$B$2,$B$1)      (fill down to E13)

MIN(..., $B$1) stops the floor rising above the starting balance. If your firm does not lock, use:

E10: =D10-$B$2      (fill down)

Checks (columns F and G)

F10: =IF(C10<=E10,"BREACH","OK")      (fill down)
G10: =IF(C10<=$B$4,"BREACH","OK")     (fill down)

Results with the example data

Row	Balance	Peak EOD	Trailing floor (locked)	Trailing	Static
10	52500	52500	50000	OK	OK
11	51000	52500	50000	OK	OK
12	51800	52500	50000	OK	OK
13	49700	52500	50000	BREACH	OK

I checked these formulas against the example table in Python (same arithmetic Google Sheets uses for MAX, MIN and running sums). Row 13 is the point: the account is still above the static floor of 48000, but the trailing floor has already locked at 50000 after the Day 1 gain, so −2100 fails the trailing rule.

Once a row shows BREACH, the firm has usually failed the account. Later rows that look OK do not undo that.

Without the lock, the same peak of 52500 would set a floor of 50500, and row 13 would still breach (49700 ≤ 50500). With a smaller early gain, lock vs no-lock can disagree, so match your firm's wording.

3. Intraday trailing (one day of equity snapshots)

EOD formulas cannot see open profit. To approximate an intraday trail, log equity during the day, including open P&L.

Headers in row 20: Time, Equity (incl. open P&L), Peak equity, Trailing floor, Check (A–E). Label A19 Example data (not real trades).

Example data for a single day starting at 50000 with a $2,000 trail and a lock at the starting balance:

Row	A: Time	B: Equity
21	09:35	50000
22	10:10	52200
23	11:40	49950
24	15:50	50800

C21: =MAX($B$1,B21)
C22: =MAX(C21,B22)           (fill down)
D21: =MIN(C21-$B$2,$B$1)     (fill down; drop $B$1 if no lock)
E21: =IF(B21<=D21,"BREACH","OK")  (fill down)

Time	Equity	Peak	Floor	Check
09:35	50000	50000	48000	OK
10:10	52200	52200	50000	OK
11:40	49950	52200	50000	BREACH
15:50	50800	52200	50000	OK

Same arithmetic check as above. At 11:40 the account is already failed under an intraday trail. The 15:50 close of 50800 would look fine under an EOD-only rule for that day (peak 50800, locked floor 48800, close OK). That gap is why mixing up EOD and intraday is expensive.

A sheet like this is a record. The firm's platform is the official clock and the official equity.

4. Common mistakes

1. Using only the close for an intraday firm. Your EOD log can show OK on a day the live platform already failed you.
2. Forgetting the lock. If the firm locks at the starting balance and you keep trailing above it, your sheet's floor is too high (stricter than the firm) or, if you never trail at all, too low (looser than the firm).
3. Comparing to starting balance instead of the floor. After a winning streak, "I'm still above 50K" is not the trailing test.
4. Logging deposits, resets or payouts in the same balance column. Trailing peaks are about trading equity under the plan rules. Keep admin adjustments in a separate column so they do not inflate the peak by accident.
5. Treating the sheet as live risk control. Daily and intraday limits on many platforms include open trades and update faster than a manual sheet. Use the sheet to understand the rule and to review after the close.

5. Conditional formatting

Select the check columns, then Format → Conditional formatting → Text is exactly BREACH, with a red fill. That makes the first failing day hard to miss when you scroll.

When to use a ready-made sheet

Building the formulas once is useful for learning. If you also want a trade log with automatic P&L, win rate, R-multiple, review tabs and a Prop Firm Tracker in one file, Trader's Edge Journal ($14) is a Google Sheets / Excel template from AIL Studio. You get an .xlsx with 15 sample trades and a short PDF quick-start. It is a record-keeping tool: it does not tell you what to trade and does not guarantee results.

Related reading:

• How to track prop firm challenge rules in Google Sheets (daily loss, max drawdown, consistency)
• Prop firm rules compared (Oct 2026)

Not financial advice. Rules change, verify with the firm before buying.

By Richard C · AIL Studio

Comments

[Arnold Holm]

Your 11:40 breach followed by a 15:50 OK is a useful place to add a separate account-status column. In F21, =IF(COUNTIF($E$21:E21,"BREACH")>0,"FAILED","ACTIVE") can be filled down. That keeps FAILED visible after the first breach, while column E still shows the current snapshot. It makes your point that a recovery does not undo the earlier failure explicit in the sheet. The equality rule still needs to match the exact plan.