Home › Templates › Position size spreadsheet

Position Size Calculator Excel Template

A free position sizing spreadsheet for Excel and Google Sheets: type your account size, risk %, entry and stop, and it returns how many shares, forex lots, coins or futures contracts keep the loss at your stop within that risk, capped by a maximum position size, with the target price and reward-to-risk for each trade.

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no broker connection.

Symbol	Market	Side	Entry	Stop	Position size	Risk at stop	Risk %	Target	R:R	Check
TKR-A	Stock	Long	48.20	46.70	129 shares	$193.50	0.77%	52.00	2.53	Capped by max position
TKR-B	Stock	Short	22.10	23.35	200 shares	$250.00	1.00%	19.60	2.00	OK
EUR/USD	Forex	Long	1.0845	1.0815	0.83 lots	$249.00	1.00%	1.0905	2.00	OK
USD/JPY	Forex	Short	149.80	150.40	0.62 lots	$248.35	0.99%	148.60	2.00	OK
COIN-X	Crypto	Long	2,480.00	2,380.00	2.5 coins	$250.00	1.00%	2,750.00	2.70	OK
IDX-MICRO	Futures	Long	5,012.00	4,994.00	2 contracts	$180.00	0.72%	5,048.00	2.00	OK
IDX-MINI	Futures	Long	5,012.00	4,994.00	0 contracts	$0.00	0.00%	5,048.00	2.00	Size rounds to 0: risk too small
TKR-C	Stock	Long	15.40	14.95	277 shares	$124.65	0.50%	16.75	3.00	OK

Chart: the "Risk % of account" column of the same tab. No trade goes above the 1% line, because every size is rounded down.

What's inside the spreadsheet

Market defaults (editable)

The 25% cap and the size steps are starting values, not recommendations. Change them to your own rules and to what your broker or exchange accepts. Any row can override the risk %, multiplier and size step.

How to use it

1. On Trade plan, type your account size (B4), risk per trade (B5, for example 1%) and the default target as an R multiple (B6, for example 2).
2. Check the market defaults in D4:H7. Set the size step to the smallest order your broker accepts (0.001 for fractional shares, 0.01 for micro lots, and so on).
3. For each trade idea, fill the yellow columns: date, symbol, market, Long/Short, entry and stop. Add a target if you have one; if not, the sheet sets one at entry ± 2 × the stop distance.
4. Forex pair not quoted in your account currency? Type the FX rate in column J (see the forex section below). Futures? Type the dollar value of a one-point move in column I.
5. Read POSITION SIZE in column W and the Check column. Green "OK" is ready; amber means the position cap made the size smaller; red needs a fix.
6. Before placing several trades, look at the plan summary: total risk if every stop is hit, as a % of the account.

For a quick one-off number, the Calculator tab does the same maths for a single trade, one labelled step per row. The online position size calculator does it in the browser.

The formula for each market

Every market uses the same idea. Decide the loss you accept, measure the loss per unit at the stop, and divide:

Position size = (account × risk %) ÷ (|entry − stop| × multiplier × FX rate), rounded down to the size step.

Rounding down matters: it keeps the risk at or below your limit. The sheet uses ROUNDDOWN, which Microsoft describes as a function that "Rounds a number down, toward zero" (Microsoft Support).

Stocks: shares = risk $ ÷ (entry − stop)

Multiplier 1, step 1 share. Example TKR-B: $25,000 × 1% = $250.00 of risk. Short at 22.10, stop 23.35, so each share loses $1.25 at the stop. $250.00 ÷ $1.25 = 200 shares.

Forex: lots from pip value

One standard lot is usually 100,000 units of the base currency (some brokers also offer mini lots of 10,000 and micro lots of 1,000; check your broker's contract size and change the multiplier if needed). A pip is 0.0001 for most pairs and 0.01 for JPY pairs.

• Pip value per lot, in the quote currency = pip size × 100,000. For EUR/USD that is 0.0001 × 100,000 = 10 US dollars.
• If the quote currency is your account currency (EUR/USD or GBP/USD with a USD account), that is your pip value: $10.00 per standard lot. FX rate = 1.
• If it is not, convert. USD/JPY: 0.01 × 100,000 = ¥1,000 per pip. With a USD account, multiply by the value of ¥1 in dollars, which is 1 ÷ the USD/JPY price. At 149.80 that is 0.006676, so one pip is about $6.68 per lot. For a cross such as EUR/GBP, multiply the £10 pip value by the GBP/USD rate.
• The conversion rate moves with the market, so the pip value of non-USD-quoted pairs changes slightly every second. The sheet uses the rate you type; your broker uses its live rate, so expect small differences.

Example EUR/USD: long 1.0845, stop 1.0815 = 30 pips. One lot loses 30 × $10.00 = $300.00 at the stop. $250.00 ÷ $300.00 = 0.833, rounded down to 0.83 lots ($249.00 at risk). Example USD/JPY: 60 pips × $6.676 = $400.56 per lot → 0.62 lots.

Crypto: coins = risk $ ÷ price distance

Multiplier 1, step 0.0001 coin (set it to your exchange's minimum order step). Example COIN-X: long 2,480.00, stop 2,380.00, risk per coin $100.00 → 2.5 coins. The position cap allowed 2.5201, so risk was the tighter limit.

Futures: contracts = risk $ ÷ (points × $ per point)

Type the contract's dollar value per one-point move as the multiplier. CME Group describes the E-mini S&P 500 contract value as "$50 times the agreed upon E-mini futures price" and the Micro E-mini S&P 500 as "$5 times the agreed upon price", and notes that "a one tick move in the Micro E-mini S&P 500 equates to $1.25" (CME Group). Other contracts have other values, so check the exchange's contract specs.

Example with an 18-point stop and $250.00 of risk: a $5 contract loses $90.00 per contract → 2 contracts ($180.00 at risk). A $50 contract loses $900.00 → 0 contracts, and the Check column says "Size rounds to 0: risk too small". The choices are to skip the trade or use the smaller contract. Widening the stop only makes the loss per contract bigger.

Max position cap

A tight stop can make the risk formula ask for a very large position. The cap limits the position value to a % of the account: max size = account × max % ÷ (entry × multiplier × FX rate), rounded down. The sheet takes the smaller of the two sizes. Example TKR-A: risk alone allows 166 shares, but 25% of $25,000 at 48.20 allows only 129, so the size is 129 shares and the risk drops to $193.50 (0.77%). For forex and futures the default is no cap: their position value is leveraged, so a cap on it would block almost every trade. Keep the risk % as the control there, and check your margin separately.

Reward-to-risk and target price

Reward : risk = |target − entry| ÷ |entry − stop|. With no target typed, the sheet places one at entry + R multiple × stop distance (minus for a short). TKR-B: 22.10 − 2 × 1.25 = 19.60. TKR-A has a typed target of 52.00: (52.00 − 48.20) ÷ 1.50 = 2.53. To compare targets before entry, the risk reward calculator does the same sum.

The formulas, explained

Exact formulas from row 11 of the Trade plan and Example tabs. Inputs: C market, E entry, F stop, G target, J FX rate, K pip size. Settings: B4 account, B5 risk %, B6 default R. Helper columns M, N and O hold the risk %, multiplier and size step used (row value or market default).

The tiny +0.000000001 (typed as 1E-9) inside ROUNDDOWN stops a floating-point result such as 199.99999999 from being cut to 199 when the true answer is exactly 200. The Check column (AE) runs nested IF tests: account size set, market picked, stop on the correct side of entry, multiplier present, target on the correct side, size above 0, and whether the cap was used.

Worked example: the whole plan

The 8 example trades add up to $1,495.50 of risk if every stop is hit, 5.98% of the $25,000 account. No single trade risks more than 1%, but 7 open positions together could cost about 6% on a bad day. Correlated positions (two stocks in the same sector, or two USD pairs) can hit their stops together. The plan summary keeps that total in view.

Risk by account size

From the Risk table tab: the dollar amount at each risk level.

The same tab works out shares for one stop distance (default $2.00: a $10,000 account at 1% gets 50 shares) and what a run of full losses does. Each cell below shows the account left and, in brackets, the gain needed to get back to the start. It is arithmetic, not a forecast:

For the odds of such streaks given your win rate and payoff, see the risk of ruin calculator and understanding expectancy.

Common mistakes

• Sizing from the position value, not the stop. "1% of the account" is the loss at the stop, not the size of the trade. A 1% risk can be a position worth 25% of the account or more.
• Treating the stop as a guaranteed exit. Investor.gov states: "The stop price is not the guaranteed execution price for a stop order." Gaps and fast markets can fill you worse, so real losses can exceed the planned risk (Investor.gov bulletin).
• Using $10 a pip for every forex pair. That only holds when the quote currency is your account currency. Type the FX rate for the rest.
• Rounding up. 0.8333 lots rounded to 0.84 takes you over the limit. The sheet always rounds down.
• Typing 1 instead of 1%. In the risk cells, 1 means 100%. Type 1% or 0.01; the cell refuses values above 20%.
• Forgetting fees and spread. They are not in the risk figure. Leave a margin for them, especially on short-term trades.
• Adding up many 1% trades. Check the total open risk in the plan summary before placing the next one.

FAQ

How do you calculate position size in Excel?

Work out the money you are willing to lose (account × risk %), then divide it by the loss per unit if the stop is hit (|entry − stop| × multiplier). Round down to a size your broker accepts. In this template that is =ROUNDDOWN(P11/T11/O11+1E-9,0)*O11, where P is the risk amount, T the risk per unit and O the size step. A $25,000 account risking 1% with a $1.25 stop on a stock gives $250 / $1.25 = 200 shares.

What is the 1% rule in trading?

It is a common rule of thumb, not a regulation: risk no more than 1% of the account on one trade. On a $25,000 account that is $250 between entry and stop. The template uses whatever % you type, so 0.5% or 2% work the same way, and the Risk table shows the dollar amount for eight account sizes.

How do you calculate lot size in forex?

Lots = risk amount / (stop distance in price × 100,000 × FX rate), rounded down to 0.01 lot. For EUR/USD with a USD account and a 30-pip stop, one standard lot loses 0.0030 × 100,000 = $300 at the stop, so $250 of risk buys 0.83 lots. 100,000 units per standard lot is the usual broker convention; check your broker's contract size.

How much is one pip worth?

Pip value per lot = pip size × units per lot, in the quote currency. For pairs quoted in your account currency (EUR/USD, GBP/USD with a USD account) one pip on a standard lot is 0.0001 × 100,000 = $10.00. For other pairs, convert: USD/JPY gives 0.01 × 100,000 = ¥1,000 per pip, which is about $6.68 at a rate of 149.80. Because that rate moves, the pip value moves too.

How many futures contracts should I trade?

Contracts = risk amount / (stop distance in points × dollar value per point), rounded down to a whole contract. CME Group lists the Micro E-mini S&P 500 at $5 times the index price and the E-mini S&P 500 at $50. With $250 of risk and an 18-point stop, that is 2 Micro contracts ($90 each) but 0 E-mini contracts ($900 each): the larger contract is too big for that risk.

Can I use this position size spreadsheet in Google Sheets?

Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Drive and open it with Google Sheets. The formulas use functions that exist in Excel 2016+, Google Sheets and LibreOffice (ROUNDDOWN, INDEX, MATCH, IFERROR, COUNTIF), and the dropdowns are plain data validation lists.

Related tools and templates

• Position size calculatorSame maths in the browser
• Risk reward calculatorTarget vs stop before entry
• Risk of ruin calculatorHow risk % and losing streaks interact
• Trading plan templateWrite down your risk rules
• Trading journal templateLog the trades you size here
• Day trading journalDaily loss limit and R-multiple
• Trading checklistPre-trade checks
• All templatesFree spreadsheets

Educational template only, not financial or investment advice. Trading stocks, forex, crypto and futures can lose money, and leveraged products can lose more than you deposit. The risk figures exclude fees, spread, slippage and gaps. Example data is fictional: symbols TKR-A to TKR-C, COIN-X, IDX-MICRO and IDX-MINI, and all prices and amounts are made up. CME Group, Investor.gov and Microsoft material was retrieved on 29 October 2026.