Tuesday, April 12, 2005

Money Management - Can you make it

I have developped a Money Management Spreadsheet, which can tell you, if you statistically have a chance to make it

You need to know:

  • Your account value
  • Your current Win % and Loss % numbers.
  • Your current average Win$ and Loss$ numbers per trade
  • The number of trades you do on average/month

Win % and Loss%:
Win% + Breakeven% + Loss% = 100%

Win%  = Number of winning trades / number of all trades
Loss% = Number of losing trades/ number of all trades

I consider a breakeven trade any trade which is closed at the entry price or at the entry price +1 tick.

Win $ and Loss $:

Win$ is the amount of money you get on average on your winning trades minus commission
Win$ = (Amount won-commision) / number of winning trades
Loss$ = (Amount lost+commission) / number of losing trades

Let’s make an example:

  • Account value: 10k
  • Win% = 55%
  • Loss% = 25%
  • Win$ = 119$
  • Loss$ = 81$
  • Average Trades/month = 100

Download the MoneyManagement Spreadsheet, save it to your harddisk, start Excel and open MoneyManagement2.xls

You need to do the next just the first time you load the spreadsheet: Click Tools/Options and select Calculations. Enable Iterations, click ok


Enter the account balance in field D4, the Win$ in field B7, Loss$ in field B8, the Win% in field G7 and the Loss% in field G8

On the spreadsheet you see a few further entry options:

  • Account size<= / contracts. Here you can enter how many contracts you want to trade upto the given account size
  • Min Gain/month: The spreadsheet will calculate how much you can make on average / month. But a payout will occur only, if you make during a certain month more than this amount
  • cost BE trade: That’s just the amount you pay on a Breakeven trade. If you earn on average on your Breakeven trades, because you usually close them at BE+1 you could enter a +6$ here
  • Random Lucky/Disaster Trades: We all know, not every trade goes according to plan. Sometimes you get lucky, sometimes the s**t hits the fan. Here you can enter how many of your trades will be a Lucky or a Disaster trade. Below you enter how much more than the regular win or loss such a Lucky or Disaster trade makes. As you see for me the Luck doesn’t pay as much as the Disaster demands, but that’s how I experienced it until today.

Now hit a few times F9
This will recalculate the Spreadsheet and you will see the Ending balance change. You will allso see the Payout/year and per month change as well as the Profit factor.

What does this mean?
The spreadsheet calculates 1200 trades to simulate one year of trading, if you do 100 trades/month every time you hit the F9 key.

Now contrary to most Money management systems (and also earlier versions of this spreadsheet) this spreadsheet assumes, you just have the necessary margin to trade your system in your trading account and every surplus is taken out of the account and transferred into your private account to live from or just do what all good consumers should do: to spend it

That means the Ending balance is just the amount which is in your account at trade number 1200. As you take money out of the account at regular intervals, it’s not really of concern to you unless it’s below the necessary margin to continue trading your system.
What is of interest to you is the Payout/Year and the Payout/month (monthly). This tells you what you were able to get out of your trading account per year and per month.For everyone comparing systems the Profit Factor might be of interest as well.

So far the spreadsheet is nice, but it doesn’t really help you, as every time you hit F9 you are presented with a new result.

Let’s clear the yellow Box (field A15), hit F9 and keep it pressed until field D17 (left of it you see the label: Runs:) shows 1,500


Now the results look a lot more meaningful:

You have tried your system 1500 times and you get the Maximum Payout/year as well as the Minimum Payout/year. This tells you, if you don’t change your current system and your numbers remain constant you can expect with the example system posted above to make 98k$ minimum/year and 141k$ maximum/year. Not bad I think. But what’s even better, the system not once went broke as you see no number right of the label Gone bankrupt. The column Paid months shows you how many payouts you got per year. The max should be 12 unless you set the Min Gain/mth (B9) too high, the minimum in this case is 11, so in the worst case you can expect a payment 11 months out of 12. The profit factor ranges between 2.66 and 4.42. The average monthly payout of this system is 9.9k$. Really not bad. And all by trading just 2 contracts, unless the account balance drops below 5k$.

If you don’t do 100 trades / month?
You will need to adjust the numbers in Row I



In field I120 you will see the number 1. Delete it and enter the number 1 in field I100, if you do 80 trades per month. Repeat this for row I220, I320…,I1220 and enter the number 1 in field I180, I260, I340…I980. Column A shows the tradenumber  so it shouldn’t be so difficult.

If you want pyramid or test systems which go bankrupt even in late stages of the system, just clear I20 downto I1220, just leave the number 1 in field I1220, so you get meaningful Payout numbers. Then increase the contract numbers in row 6 or change the account values necessary to trade multiple contracts. The minimum account size is necessary to determine when you went bankrupt. The system stops a run, the moment your account balance goes below the minimum, as then IB will not allow you to trade any more futures.

Relaxed trading and I hope you won’t go bankrupt once statistically.

Btw: When I started my chances to go bankrupt where about 30% and I was happy, when I brought this down to 10% and it remaind there, even if I just had had again a losing streak. It really helped me to see, that statistically I still had a good chance to make it, that it just was a losing streak. And the statistics proved to be correct, even if it took a change of the trading system to become consistently profitable.


V.P.B. said...

Interesting spreadsheet. You may find the following link useful...


While it doesn't identify/project any sort of monthly income, it does an interesting graphical job of showing profit variations for a given set of performance parameters. I suggest entering 100 for the Line qty. parameter.

ashley said...

I really liked the information on currency trading, great job! I have my own currency trading secrets blog if you would like to come and see what I have on mine.

MidKnight said...

Hi croc,

Thanks a lot for this spreadsheet. I wonder if it would also be possible to record to record longest streaks for all the runs? Such as what is an extreme number of trades to have in a row without a loser and what is an extreme amount of trades to have in a row without a winner. Could be good information to have so the trader can get an idea of what is 'normal' streaks and extreme streaks for the probabilities they are trading.

Many thanks again - great tool.