This free Excel worksheet solves the most common position sizing mistake in forex: applying a uniform lot size across pairs that have fundamentally different pip values. Download the forex position sizing worksheet to calculate correct lot sizes for all major pairs, track correlated exposure across simultaneous trades, and monitor daily drawdown against prop firm limits.
What’s Included
- Account Settings Panel — Enter balance, base currency, max risk %, and margin warning level once in cells B2:B5; every calculation in the workbook references these values automatically.
- Lot Size Calculator — Seven-column table covering pair, entry price, stop loss in pips, risk %, pip value per lot, calculated lot size, and USD position value. Uses the formula
=RiskAmount/(StopPips*PipValue)with pair-specific pip values pulled from the reference table. - Pip Value Reference Table — Static reference for 10 major pairs (EUR/USD, GBP/USD, USD/JPY, AUD/USD, USD/CAD, NZD/USD, EUR/JPY, GBP/JPY, EUR/GBP, USD/CHF) at standard (100,000 units), mini (10,000 units), and micro (1,000 units) lot sizes. JPY cross pip values recalculate when you update the current rate column.
- Multi-Pair Exposure Tracker — Summary row showing total open risk % across all active positions, so you can see at a glance whether combined exposure is approaching 5–10% of account equity.
- Correlation Risk Flag — Highlights when the trade list contains EUR/USD and GBP/USD in the same direction, surfacing the hidden double-up in USD directional exposure these correlated pairs create.
- Daily Drawdown Meter — Session loss tracker that displays yellow at 3% (TopStep Forex threshold) and red at 5% (FTMO daily loss limit).
- Margin Requirement Column — Shows margin required per position at 30:1 leverage (ESMA cap for major pairs). At a 1.10 EUR/USD rate, one standard lot requires approximately $3,333 in margin.
How to Use
Step 1: Configure Your Account Settings
Open the worksheet and enter your account balance in cell B2, select USD as base currency in B3, set your per-trade risk percentage in B4 (1% is standard; prop firm traders often use 0.5%), and set the margin warning threshold in B5. These four values drive every downstream calculation — update B2 at the start of each trading week to reflect your current balance.
Step 2: Update JPY Pair Rates in the Reference Table
Navigate to sheet 2 (Pip Value Table) and update column C (Current Rate) for USD/JPY, EUR/JPY, and GBP/JPY. EUR/USD pip value is always $10 per standard lot regardless of price because USD is the quote currency. But USD/JPY pip value at 150.00 is $10/150 × 100 = $6.67 per standard lot — significantly less than EUR/USD. GBP/JPY at current rates typically exceeds $13 per standard lot. Getting these right before entering trades is the core function of this table.
Step 3: Enter Your Planned Trades
Return to sheet 1 and add a row for each trade. Columns A–E take pair name, entry price, stop loss in pips, and risk %. Column F calculates lot size automatically. Column G shows position value in USD. For a $5,000 account risking 1% ($50): a 25-pip stop on EUR/USD returns 0.20 lots; the same $50 risk with a 30-pip stop on GBP/USD returns 0.167 lots; a 20-pip stop on USD/JPY at 149.50 (pip value ≈ $6.69) returns 0.374 lots.
Step 4: Review the Exposure Tracker
With three trades entered above, the Exposure Tracker on sheet 3 shows total open risk at 3% of the $5,000 account ($150). It also flags the EUR/USD and GBP/USD rows as correlated USD-short exposure — the two positions together put $100 at risk from a single USD macro move, not the $50 per trade the individual risk percentages imply. This is the critical insight single-pair lot size calculators cannot surface.
Step 5: Monitor Drawdown During the Session
As trades close, enter realized P&L in the Daily P&L column (sheet 1, column H). The drawdown meter in cells I2:I4 updates automatically. Stop adding positions once the meter reaches your firm’s daily loss threshold. FTMO caps daily drawdown at 5% of the starting balance; TopStep Forex uses 3–4%.
Key Benefits
- Pair-accurate pip values — Pip value is calculated per pair using current rates, not a blanket $10/pip assumption that breaks immediately on any JPY cross.
- Correlation exposure visibility — Holding EUR/USD and GBP/USD longs simultaneously with a ~0.85 historical correlation effectively doubles USD directional risk beyond what per-trade percentages show.
- All lot size tiers supported — Reference table covers standard, mini, and micro lots, so a $1,000 account trading micros and a $100,000 account trading standards both use the same worksheet.
- Prop firm threshold markers — Daily drawdown meter is pre-configured for FTMO (5%) and TopStep Forex (3%), the two most common funded program limits.
- No broker dependency — Works offline, requires no API connection, and can be used for pre-trade planning before any position is opened.
Template vs JournalPlus App
| Feature | This Template | JournalPlus App |
|---|---|---|
| Lot Size Calculation | Formula-based, manual entry | Auto-calculated on trade import |
| Pip Value Accuracy | Manual rate updates required | Live data from broker feed |
| Correlation Tracking | Static flag (EUR/USD + GBP/USD) | Dynamic across all open positions |
| Trade Import | Manual entry per trade | Automatic from 50+ brokers including OANDA and MetaTrader |
| Performance Analytics | Not included | 30+ metrics, equity curve, drawdown |
| Prop Firm Tracking | Manual drawdown meter | Automated alerts at custom thresholds |
| Price | Free | $159 one-time, lifetime access |
This worksheet is a solid pre-trade planning tool — particularly useful when sizing across multiple pairs before market open. When you’re ready for automatic broker imports and analytics that persist across weeks and months, JournalPlus picks up where the spreadsheet leaves off.
Download
Download the free Forex Position Sizing & Lot Size Worksheet and start sizing positions correctly across all major pairs. No account required.
Frequently Asked Questions
What is a forex position sizing worksheet?
A forex position sizing worksheet is a spreadsheet that calculates the correct lot size for each trade based on account balance, risk percentage, stop loss in pips, and the pip value specific to each currency pair. It prevents traders from applying a fixed lot size across pairs with different pip values, which produces inconsistent dollar risk on every position.
How do I calculate lot size in Excel for forex?
The formula is: Lot Size = (Account Balance × Risk %) / (Stop Loss in Pips × Pip Value per Lot). On a $5,000 account risking 1% ($50) with a 25-pip stop on EUR/USD ($10/lot pip value), that is 0.20 lots. On USD/JPY at 149.50, the pip value drops to approximately $6.69/lot — the same $50 risk with a 20-pip stop requires 0.374 lots. The forex trading journal template complements this worksheet by storing the results after positions close.
What is the pip value for major forex pairs?
EUR/USD pip value is always $10 per standard lot because USD is the quote currency — this holds at any exchange rate. USD/JPY pip value changes with the rate: at 150.00 it is approximately $6.67 per standard lot. GBP/JPY pip values often exceed $13 per standard lot depending on the current cross rate, which is why the pip value reference table in this worksheet requires regular rate updates for JPY crosses.
How many lots should I trade on a $5,000 forex account?
With a 1% risk rule ($50 per trade), lot size depends entirely on your stop loss and the pair being traded. A 25-pip stop on EUR/USD gives 0.20 lots; a 30-pip stop on GBP/USD gives 0.167 lots. Sizing at 0.5% risk per trade is common for prop firm challenges, since FTMO caps daily drawdown at 5% and three losing trades at 1% each can approach that limit before slippage is accounted for.
Can I use this worksheet for FTMO or prop firm challenges?
Yes. The daily drawdown meter includes pre-set warnings at 3% (TopStep Forex) and 5% (FTMO). The multi-pair exposure tracker surfaces total open risk across all positions simultaneously, which matters because prop firms cap combined open exposure at 5–10% of account equity — a limit that applies regardless of whether each individual trade is sized at 1%. The prop firm trading journal template pairs well with this worksheet for recording completed trades during a challenge.
