ARMFA lot size spreadsheet in Excel and a trading journal
A lot size spreadsheet in Excel is the most underrated risk management tool: it costs nothing, works without the internet and does not depend on someone else's service that may shut down. Below are the formulas you need to enter once to get the volume for a percentage of risk, the required margin, the drawdown by equity and the expectancy of a trade in units of risk. The formulas are given in two spellings: the Russian one for Excel and the English one for Google Sheets.
Your own spreadsheet is the only tool where the rules are written in your own words and every figure breaks down into its factors. It does not interfere with trading and does not work without you, but it repeats any formula a paid service has.
The calculation sheet: volume, loss and margin
The first sheet consists of eight input cells and six formulas. It is filled in before a trade in ten seconds, and the result matches any online calculator, because the formula is the same one.
| Cell | What is in it | Example |
|---|---|---|
| B1 | account equity, not the balance | 10,000 |
| B2 | risk per trade in percent | 1 |
| B3 | the distance to the stop in pips | 40 |
| B4 | the pip value for a full lot | 10 |
| B5 | the broker's volume step | 0.01 |
| B6 | the contract size | 100000 |
| B7 | leverage | 100 |
| B8 | the rate of the base currency against the account currency | 1.0850 |
| What we count | Formula for Excel | Formula for Google Sheets |
|---|---|---|
| Money risk | =B1*B2/100 | =B1*B2/100 |
| Size in lots | =FLOOR(B9/(B3*B4), B5) | =FLOOR(B9/(B3*B4),B5) |
| Loss at the stop | =B10*B3*B4 | =B10*B3*B4 |
| Required margin | =B10*B6/B7*B8 | =B10*B6/B7*B8 |
| Margin as a share of the account | =B12/B1 | =B12/B1 |
| Pip value for the size | =B10*B4 | =B10*B4 |
Rounding down in the volume formula is a matter of principle. Ordinary rounding sometimes gives a lot larger than the calculated one, and then the actual loss on the stop exceeds the set percentage — systematically and always in the same direction.
The trading journal: an Excel template made by hand
A trading journal in an Excel template is one flat table where a row equals a trade. Twelve columns are enough to count everything the paid services count. Extra fields do harm: they add friction and the journal stops being filled in.
| Column | What goes in | Filled in |
|---|---|---|
| A. Date | the date and time of the entry | automatically from the report |
| B. Pair | the instrument traded | from the report |
| C. Direction | buy or sell | from the report |
| D. Volume | lots | from the report |
| E. Entry | the entry price | from the report |
| F. Stop | the planned level that cancels the idea | manually |
| G. Risk in money | the planned price of being wrong | by formula |
| H. Result | profit or loss including costs | from the report |
| I. R | the result in units of risk | by formula |
| J. Setup | the type of entry by your own classification | manually |
| K. By the rules | yes or no | manually |
| L. Note | what went wrong | manually |
Three columns are filled in by hand, and it is those that turn a list of operations into a journal. Without the setup field there is no breakdown by types of entry, without the execution field there is no way to measure the cost of breaking your own rules.
Statistics formulas: drawdown, expectancy, streak
These formulas are entered once into service columns and count themselves after that. Their set repeats the reports of paid services — the only difference is that here you can see what every number is made of.
| Indicator | Formula for Excel | What it gives |
|---|---|---|
| Result in R | =H2/G2 | trades with different risk become comparable |
| Equity as a running total | =N1+H2 | the account curve by closed trades |
| Equity peak | =MAX($N$2:N2) | the maximum reached by this trade |
| Current drawdown | =(O2-N2)/O2 | the fall from the peak as a fraction |
| Maximum drawdown | =MAX(P:P) | the worst episode in the whole history |
| Win rate | =COUNTIF(I:I, ">0")/COUNT(I:I) | share of winning trades |
| Expectancy in R | =AVERAGE(I:I) | the average result of a trade |
| Profit factor | =SUMIF(I:I, ">0")/-SUMIF(I:I, "<0") | the return per unit of losses |
| Losing streak counter | =IF(I2<0, Q1+1, 0) | the length of the current chain of stops |
| Longest streak | =MAX(Q:Q) | the worst chain in the history |
| Expectancy by setup | =AVERAGEIF(J:J, "breakout", I:I) | which type of entry brings in money |
| Share of trades taken by the rules | =COUNTIF(K:K, "yes")/COUNTA(K:K) | the price of your own indiscipline |
break-even win rate = 1 ÷ (1 + the average R of a winner)
The last row of the table is the metric that exists neither in the terminal nor in account monitoring. Compare the expectancy in R across the trades marked «yes» and those marked «no»: the difference between them is the annual cost of breaking your own rules.
How to put the spreadsheet together in one evening
The order of assembly matters more than the styling. A spreadsheet put together in one evening and filled in every day is more useful than a perfect template that was downloaded and opened twice.
Eight input cells and six formulas from the first table. Check it against a familiar example: 1 % of 10,000 with a stop of 40 pips gives 0.25 lots.
20 minutesHeaders, the format of dates and numbers. Put the three manual columns — setup, execution and note — next to each other so they can be filled in with one movement.
20 minutesEquity, the peak, the drawdown and the streak counter. They are not there to be read but to feed the summary formulas, so they can be hidden.
20 minutesWin rate, expectancy, profit factor, maximum drawdown and the worst streak in the header of the sheet. Five numbers you see the moment you open it.
20 minutesExporting the report from the terminal once a week and pasting in the new rows. Typing every trade by hand does not survive even a month.
continuouslyExcel, Google Sheets or Notion
The tool is chosen by where the statistics will be counted, not by where it is nicer to write. A Notion trading diary is convenient for descriptions and screenshots, but a running maximum of equity cannot be counted there without tricks.
The full set of functions, work without the internet and fast pivot tables. The downside is that the file lives on one computer unless synchronisation is set up.
the calculationsThe same thing in a browser and with access from any device. English function names and a comma instead of a semicolon between arguments.
access from anywhereGood for describing setups, screenshots and conclusions. For statistics they are used alongside a spreadsheet, not instead of it.
the descriptionsIt saves an hour of assembly but comes with someone else's fields and someone else's logic. You will have to check the formulas anyway, and understanding another person's spreadsheet takes longer than building your own.
debatableA search for an Excel trading diary to download usually means a wish to skip the assembly. Practice shows the opposite: a spreadsheet built by hand does get filled in, because its author understands every column and knows what it is for.
A risk management spreadsheet: short answers
- Why the volume is rounded down
- Rounding up increases the loss beyond the set percentage. A function that rounds down to the volume step solves it with one formula.
- What to use as the base — the balance or equity
- Equity. With positions open, the balance does not account for floating loss and allows more risk than there actually is.
- How to count the drawdown in the spreadsheet
- Through a peak column: the maximum equity up to the current row, then the fall from it as a fraction. The maximum of that column is the worst episode.
- Do the spread and the swap need to be entered
- Not separately, if the result is taken from the terminal's report: the costs are already included in the outcome of the trade.
- How to transfer the history from MetaTrader
- Export the report from the history tab into a file and paste the rows into the journal. The terminal's columns match the first six columns of the spreadsheet.
- How many rows will the spreadsheet take
- Tens of thousands of trades without noticeable slowdown. The limit comes not from the volume but from the number of formulas with whole-column ranges.
Frequently asked questions
What formula counts the volume of a trade in Excel
The risk amount is divided by the product of the distance to the stop and the pip value, and the result is rounded down to the volume step by the FLOOR function. In Google Sheets the function has the same name, and the arguments are separated by a comma.
Where to get the pip value for the formula
In the symbol specification in the terminal: the tick value is multiplied by the number of ticks in a pip. For pairs with the dollar in the quote on a dollar account it is $10 per standard lot.
How to count the result of a trade in R in the spreadsheet
Divide the actual result in money by the planned risk of that trade. That is exactly why the planned risk column is filled in before the entry and not after the close.
Can trades be pulled from the terminal automatically
A spreadsheet has no direct connection, but exporting the report and pasting the new rows takes a minute a week. Full automation requires an intermediate service.
How is a spreadsheet better than a paid journal
In transparency and independence: you can see what every number is made of, and the data sits with you. It loses in the speed of breakdowns and in automatic import.
How to count the longest losing streak
With a service counter column: if the result is negative, one is added to the value of the previous row, otherwise zero is put in. The maximum of that column is the worst streak.
Do you need a separate tab for every month
No, that breaks the summary formulas. All trades live in one table, and periods are picked out with a filter or a pivot table by date.
How to account for a different risk per trade
The planned risk in money column is filled in separately for every trade. Then the result in R stays comparable even if the percentage changed.
Is Notion suitable for a trading journal
As a place for descriptions, screenshots and conclusions — yes. A running maximum of equity and the drawdown are awkward to count there, so the numbers usually stay in a spreadsheet.
Is it worth downloading a ready template
You can, but you will still have to check the formulas in it. Building your own spreadsheet takes an evening and gives you an understanding of every column — that is the main benefit.
How to count statistics by types of entry
With a conditional average function: the average result in R among the rows with the setup tag you need. Comparing such averages shows which part of the system brings in money.
What to do if the spreadsheet has stopped being filled in
Cut the number of manual columns down to three: setup, execution and a short note. A journal dies not from a shortage of fields but from a surplus of them.
Read next
