Maybe this Excel can be helpful for some people. I use it every day.
It can help you to find out what the ideal margin for you is.
You can change parameters to compare different settings, and see the impact of each change in the settings that you apply.
If you have questions just let me know. Best not in PM so that I don’t have to answer certain questions three times. And other people might want to see those answers too.
The model is reinvesting the entire account in each next trade (compounding). But you can use it also non compounding by just adding a parameter with the limited number of contracts.
In the screenshot you see the spreadsheet, but you don’t see the formulas. If I could upload the Excel you would have everything ready to use, including the formulas.
I cannot download an Excel on this forum, so I asked Brett if there is another solution.
Normally you can upload on discourse, but probably Ninjatrader blocked it.
My bet is that you’re using Excel’s Goal Seek.
I just recently discovered that function when trying to figure out at what price I would get a margin call for bitcoin futures.
I build the spreadsheet more than 25 years ago, long before Goal Seek.
I have another spreadsheet that I use before using the one I posted. In that spreadsheet, I analyze (now already) a few thousand trades to see for each trade the maximum open loss for each position.
I can then define with high accuracy where to put my stop, as I see for each different level of stop the impact on my results. I use the results from this spreadsheet to feed the spreadsheet that I posted.
Combining the two previous spreadsheets lead to another one that confirms the results of the two previous spreadsheets. And gives additional information.
In this spreadsheet, I see that: when I increase my stop, my profits go up. But also my risk goes up.
So I compare the higher risk with the higher returns. My optimal stop is at 6 points.
At 7 points, the risk raises faster than the profits. I get 23.5% more profits, but my risk is now 34.2% higher.
We also see that the profits no longer go up beyond a 7 point stop. Logical, as the system is trading with small stops for high efficiencies. The system gets mostly out before the 5 point stop is touched, and there is never a losing trade that goes beyond a 7 point stop. And I don’t miss any profits due to bad placement of a stop. Even at a 10 points stop, the profits don’t go up anymore. Only the risk raises.
After that spreadsheet, I still have the calculation of the expectancy. But that is a very complicated one. Not the formula, but how I use it. There are 4 parameters, so a huge amount of combinations. Each change in numbers from any of the 4 parameters has a direct impact on the expectancy.
Trading goes far beyond just creating a trading system. You get a lot of extra information by analyzing in Excel, which leads to higher profits and less risk.
