DEV Community

Cover image for How to track prop firm challenge rules in Google Sheets (daily loss, max drawdown, consistency)
Richard C
Richard C

Posted on

How to track prop firm challenge rules in Google Sheets (daily loss, max drawdown, consistency)

#googlesheets #trading #spreadsheets #tutorial

Not financial advice. This is a spreadsheet tutorial for record-keeping. Rules change, verify with the firm before buying.

Get the Prop Firm Rules Cheat Sheet (xlsx): a free sheet comparing drawdown, daily loss, profit target, minimum days, payout split and price for 10 prop firms (checked Oct 2026).

Prop firm challenges come with a handful of rules that can end an account: a daily loss limit, a max drawdown, a profit target, a minimum number of trading days and sometimes a consistency rule. Each one is simple arithmetic, so a small Google Sheet can check all of them every time you log a day.

Below we build that sheet from scratch. Every formula uses exact cell references, so you can type it in as you read.

1. Settings (A1:B6)

Put your challenge's numbers in column B. The values here are placeholders for a $50K-style account. Replace them with your firm's actual rules.

Cell Label (column A) Value (column B)
B1 Starting balance 50000
B2 Daily loss limit 1000
B3 Max drawdown 2000
B4 Profit target 3000
B5 Min trading days 5
B6 Consistency max (best day / target) 0.55

2. Daily P&L log (A9:H16)

Row 9 holds the headers: Date, Daily P&L, Running balance, Traded?, Daily loss check, Peak EOD balance, Trailing floor, Trailing DD check (columns A–H). Put the label "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 400
11 2026-10-02 -300
12 2026-10-05 0 (no trades)
13 2026-10-06 900
14 2026-10-07 -1100
15 2026-10-08 600
16 2026-10-09 700

You only type columns A and B. Everything else is a formula.

Running balance (column C) 

C10: =$B$1+B10
C11: =C10+B11      (fill down to C16)
Enter fullscreen mode Exit fullscreen mode

Days traded (column D) 

D10: =IF(B10<>0,"Yes","No")      (fill down to D16)
Enter fullscreen mode Exit fullscreen mode

A day with a P&L of exactly 0 counts as "No" here. If you can trade flat, type Yes/No by hand instead.

3. Daily loss limit check (column E)

Flag any day where the loss reached the limit:

E10: =IF(B10<=-$B$2,"BREACH","OK")      (fill down to E16)
Enter fullscreen mode Exit fullscreen mode

In the example, row 14 (-1100) shows BREACH. Many firms measure daily loss intraday, including open trades, so an end-of-day log is a record, not a live guard.

4. Max drawdown: two versions

Static drawdown

The floor never moves. It's the starting balance minus the max drawdown.

B21: =B1-B3                                   → 48000
B22: =IF(MIN(C10:C16)<=B21,"BREACH","OK")      → OK
Enter fullscreen mode Exit fullscreen mode

End-of-day trailing drawdown

The floor follows your highest end-of-day balance. Track the peak per row with MAX:

F10: =MAX($B$1,C10)
F11: =MAX(F10,C11)                (fill down to F16)
G10: =MIN(F10-$B$3,$B$1)          (fill down to G16)
H10: =IF(C10<=G10,"BREACH","OK")  (fill down to H16)
Enter fullscreen mode Exit fullscreen mode

Some firms stop trailing once the floor reaches the starting balance (it "locks" there). The MIN(...,$B$1) in column G handles that. If your firm doesn't lock, use =F10-$B$3 instead.

For a one-line summary of the latest values:

B23: =MAX(B1,MAX(C10:C16))          → 51200   (peak EOD balance)
B24: =MIN(B23-B3,B1)                → 49200   (trailing floor)
B25: =IF(C16<=B24,"BREACH","OK")    → OK
Enter fullscreen mode Exit fullscreen mode

5. Profit target progress 

B19: =C16              → 51200  (current balance)
B20: =SUM(B10:B16)     → 1200   (total P&L)
B26: =B20/B4           → 0.4    (format as %, so 40%)
Enter fullscreen mode Exit fullscreen mode

6. Minimum trading days 

B27: =COUNTIF(D10:D16,"Yes")      → 6
B28: =IF(B27>=B5,"Yes","No")      → Yes
Enter fullscreen mode Exit fullscreen mode

7. Consistency rule

Some firms cap how much of your result can come from a single day. Topstep's wording is to keep your best day below 55% of your profit target. Firms define this differently (some use total profit, some use other percentages, some have no rule), so check your firm's exact definition and change B6 or the formula to match.

B29: =MAX(B10:B16)                    → 900   (best day)
B30: =B29/B4                          → 0.3   (30%)
B31: =IF(B30<=B6,"OK","Over limit")   → OK
Enter fullscreen mode Exit fullscreen mode

8. Optional: conditional formatting

Select E10:E16 and H10:H16, then go to Format → Conditional formatting → Text is exactly BREACH and pick a red fill. Do the same for B22, B25 and B31 (use Over limit for B31). For B26, a Color scale gives a quick visual of progress toward the target.

Results with the example data

Check Cell Result
Current balance B19 51200
Daily loss E14 BREACH (-1100 vs 1000 limit)
Static drawdown B22 OK (floor 48000)
Trailing drawdown B25 OK (floor 49200)
Profit target B26 40%
Trading days B27 / B28 6 / Yes
Consistency B30 / B31 30% / OK

I checked these formulas in a real spreadsheet with the example data above, and they gave these results.

A sheet like this keeps your records honest. It doesn't predict or improve results, and it doesn't replace the firm's own dashboard, which is the official record.

Bonus: trade stats formulas (win rate, R-multiple, profit factor)

The rule checks above work on daily totals. To review the trades behind them, add a second tab called Trades. Put "Example data (not real trades)" in A1 and the headers Trade #, P&L ($), Risk ($), R-multiple in A2:D2. Trades go in rows 3–10.

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

Row A: Trade # B: P&L ($) C: Risk ($)
3 1 300 150
4 2 -150 150
5 3 450 150
6 4 -200 100
7 5 -100 100
8 6 600 200
9 7 -150 150
10 8 250 125

"Risk" is what you planned to lose if your initial stop was hit.

R-multiple per trade (column D) 

D3: =IFERROR(B3/C3,"")      (fill down to D10)
Enter fullscreen mode Exit fullscreen mode

IFERROR leaves the cell blank instead of showing #DIV/0! when Risk is empty or 0. In the example, column D reads 2, -1, 3, -2, -1, 3, -1, 2.

Win rate, average R, profit factor 

B12: =COUNTIF(B3:B10,">0")/COUNT(B3:B10)      → 0.5    (50%, 4 of 8)
B13: =AVERAGE(D3:D10)                         → 0.625
B14: =IF(SUMIF(B3:B10,"<0")=0,"No losses",SUMIF(B3:B10,">0")/ABS(SUMIF(B3:B10,"<0")))   → 2.67
Enter fullscreen mode Exit fullscreen mode

Put the labels Win rate, Average R and Profit factor in A12:A14. Profit factor is gross wins divided by gross losses. Here that's 1600 / 600. The IF guard shows "No losses" instead of a divide-by-zero error.

AVERAGE skips the blank cells IFERROR leaves, so a trade with no recorded risk isn't counted as 0R. Treat these numbers as a description of past entries. A small sample like eight trades says very little about what comes next.

These stats sit next to the rule checks, not in place of them. Your daily loss check (column E on the first tab) and max drawdown checks (B22 static, columns F–H and B25 trailing) are still what tell you whether the account is within the firm's rules.

If you'd rather not build it yourself, the Trader's Edge Journal ($14) is a trading journal template for Google Sheets and Excel. It includes a trade log with automatic P&L, win rate and R-multiple, a performance dashboard, daily/weekly/monthly review tabs and a Prop Firm Tracker. It comes as an .xlsx with 15 sample trades and a short PDF quick-start guide. It's a record-keeping tool: it doesn't tell you what to trade or guarantee any results.

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

Related tool: to compare rules across firms before you pick a challenge, see the Prop Firm Rules Cheat Sheet.
Prop Firm Rules Cheat Sheet

By Richard C · AIL Studio

Related from AIL Studio

Top comments (0)