Home / Blog

The Excel formulas a trading journal actually needs

Six formulas do most of the real work, and one of them — drawdown — is the one almost every free template gets wrong. Prefer the finished version? The finished workbook already has all six built and tested, Excel and Sheets both, no email. This post teaches the maths behind it.

Set the sheet up first

One row per closed trade, one column per fact: date, symbol, direction, lot size, entry, stop, exit, gross P&L, commission, swap. No merged cells anywhere in the data range — merged cells silently break both SUM ranges and PivotTables built from them. Enter dates as actual Excel dates, not text that merely looks like a date, or sorting by date and grouping by weekday both quietly fail. Give every column a plain, single-word header with no spaces — Entry, Stop, Exit rather than "Entry Price" — since a formula referencing a header by name in a structured table breaks the moment that header's exact text changes, and a space invites a stray typo that silently disconnects a formula from the column it was built for.

Formula 1 — initial risk in currency

=ABS(Entry-Stop)*LotSize*PipValue — the price distance between your entry and your stop, converted to an account-currency amount by your position size and your instrument's pip value. This is what every R-multiple on this trade is measured against, permanently, from the moment you entered — recalculating it if you later move your stop would quietly rewrite your own history to look better than the plan you actually traded.

Formula 2 — R-multiple

=NetPnL/InitialRisk — net profit or loss divided by the risk from Formula 1. A trade risking $100 that netted $250 is +2.5R; one that netted −$100 is exactly −1R. Both NetPnL and InitialRisk are already correctly signed and always positive respectively by the time you reach this formula, so no separate long/short adjustment is needed here — the direction is already baked into whether NetPnL came out positive or negative.

Formula 3 — net P&L after costs

=GrossPnL-Commission+Swap. Commission is entered as a positive cost and subtracted. Swap is different: MT4 and MT5 report it as a genuinely signed number, negative when it was charged, positive when it was credited to you on a carry trade, so it gets added, not subtracted — a formula that wraps swap in ABS() and subtracts it, which a few published templates do, quietly turns a positive swap credit into another cost. A gross-only journal flatters every trade by the exact size of what it paid in costs to place it.

Check your own broker's export before trusting this blindly: paste one row of raw swap values into a blank cell and look at the sign directly, rather than assuming every broker formats it the same way. Most retail forex and CFD brokers on MT4 and MT5 report swap as signed exactly as described above, but a broker or asset class charging swap as a flat positive fee regardless of direction is not unheard of, and the formula needs to match whichever convention your own statement actually uses.

Formula 4 — running equity and running peak

Equity: =SUM($R$2:R2)+StartingBalance, where R is your net P&L column, copied down so each row's range grows by one. Peak: =MAX($S$2:S2), where S is the Equity column you just built — the highest that running total has reached so far, inclusive of the current row.

Formula 5 — real drawdown

=S2-MAX($S$2:S2) for the per-row figure, and =MIN(T:T) for the maximum across the whole history, where T is that drawdown column. Your biggest losing trade is not your drawdown. A hypothetical 8-trade run in R-multiples — +1.0, −2.0, −1.0, −1.2, −1.2, +0.8, −0.5, +1.5 — has a worst single trade of exactly −2R, but the equity curve it produces bottoms out at −5.4R below the peak reached after trade one, because four losses in a row (−2.0, −1.0, −1.2, −1.2) landed back to back before the recovery began. The single worst trade and the real drawdown are two different numbers, and confusing them understates risk almost every time.

Formula 6 — profit factor and expectancy

Profit factor: =SUMIF(R:R,">0")/ABS(SUMIF(R:R,"<0")), where R is your R-multiple column — gross R gained over gross R lost. Expectancy in R: =AVERAGE(R:R), the plain mean of every R-multiple you have logged, which is exactly what expectancy is by definition, no separate win-rate-weighted formula required.

One PivotTable that earns its keep

Build a single PivotTable off the same data: R-multiple in the values area, averaged rather than summed, broken down by hour of day, by weekday, by symbol and by setup tag in turn. One caveat that catches almost everyone the first time: MT4 and MT5 timestamps are stamped in your broker's server time, not your own, so an hour-of-day breakdown built straight off the raw export describes your broker's clock, not the session you were actually trading in, until that offset is corrected.

Excel vs Google Sheets vs importing the statement

All six formulas above work identically in Excel and Google Sheets — nothing here needs a feature one has and the other doesn't. Excel handles a PivotTable over a few thousand rows more smoothly and has better native chart formatting; Sheets shares by link with no version-conflict dialog when two people have it open. Neither one imports an MT4 or MT5 statement directly — that gap is why importing the statement instead exists: it reads the HTML file your platform already exports, cleans out balance operations and broker symbol suffixes, and computes all six of these figures without you retyping a single row, entirely in your browser with nothing uploaded.

None of the three is strictly better for every trader. A spreadsheet you built yourself stays useful for as long as you are willing to keep typing, and it teaches the underlying maths in a way an automated tool never will, which is the entire reason this page walks through the formulas instead of just linking the finished file first. The trade-off shows up once volume climbs: past a hundred or so trades, retyping every row by hand starts costing more time than it saves, and that is the point at which importing the statement genuinely becomes the more honest use of an evening than continuing to copy numbers across by hand.

Questions

The finished workbook ·Maximum drawdown ·R-multiple ·Commission ·Swap ·Trading cost calculator ·Import the statement instead ·The Google Sheets version of this template

This is educational information about measuring your own trading, not financial advice. Trading carries risk of loss. Full risk disclaimer.