Build the Position Size Formula in Four Cells

[Fairman]

Build the Position Size Formula in Four Cells

Goal: a spreadsheet that returns the lot size from four inputs, so sizing never depends on mental arithmetic.

Layout. Put your equity in cell B1, the risk percentage in B2, the stop distance in pips in B3, and the pip value per standard lot in your account currency in B4. Label each one in column A.

The formula for the lot size, placed in B5, is:

=ROUNDDOWN(B1*B2/(B3*B4),2)

The ROUNDDOWN function with two decimals applies the 0.01 lot step and always rounds in the safe direction. If your broker allows 0.001 steps, change the 2 to 3.

Test with numbers. B1 is 10000, B2 is 0.01 (formatted as 1 percent), B3 is 25, B4 is 10. The formula computes 100 divided by 250, which is 0.4, and displays 0.40.

Add a check row. In B6, compute the actual risk after rounding:

=B5*B3*B4

For the test, that is 100 dollars. Next to it, in B7, show the percentage of equity:

=B6/B1

Try a case where rounding matters: equity 3800, stop 25, pip value 10. The raw lot size is 0.152, the formula returns 0.15, and the actual risk is 37.50 dollars, 0.99 percent.

Add a warning cell that turns red if the actual risk exceeds your limit. For example, in B8: =IF(B7>B2,"OVER","OK").

Keep the pip value cell linked to a conversion table, as in a later drill, so it updates when exchange rates change.

Exercise: build it, and verify against three trades you sized by hand.

[Fairman]

For the pip value cell, a second small formula can handle the common cases. For a USD account trading a pair quoted in USD, like EUR/USD, the pip value per standard lot is 10 dollars. For a pair with USD as the base, like USD/JPY, it's 100,000 times the pip size divided by the price. At a USD/JPY price of 150, that's 100,000 times 0.01 divided by 150, about 6.67 dollars. Crosses need the rate of the quote currency against USD. Putting these in a small lookup table, with prices updated manually or by a data link, keeps cell B4 accurate without mental arithmetic.

[Amanhasnoname]

I built almost the same sheet and the thing I ended up changing was the pip value cell. In the first version I typed 10 and forgot about it, and then I started trading a cross where the pip value in my account currency was closer to 7.4, so every position was about a third larger than I thought. The sheet looked tidy and was wrong for weeks.

Now I keep a small table of pairs with their pip values and refresh it from the broker's contract specification once a month. The lot size formula looks the value up by the pair name. It's a few more minutes of work, but it means I can't forget to update it.

The warning cell is the best idea in your layout. I've added a second check that compares the stop distance with the current spread, and it flags the trade if the spread is more than about ten percent of the stop. On a small stop, the spread can quietly be the largest part of the risk.