Excel formula for money management, help needed!

[D.Gilberto]

Its probably incredibly straight forward for even the general user of Excel, but im hoping someone could shed some light on how to:

Account: 5,000.00
Risk Profile: 1% (per trade)
Consecutive losses to ruin: ?

I want this formula to basically tell me how many trades in a row would I need to lose based on my risk profile of 1% (compound down, of course...)

Reason why is because I am calculating a monthly target of a 3-4% growth and compounding this over a year whilst maintaining my risk profile in matter of fact means my risk of ruin consecutive trades would be increasing
(mistake me if im wrong!) when in fact I want this number to stay the same and therefore increase my position size relative to the overall risk in line with good or bad performance....

I hope this is clear?

Would really appreciate someone's help!

Please ask any questions if you`re prepared to help and it isnt clear?

 

[Lee Shepherd]

Hi domgilbert,

If I were to post back I just know it would bring up many different questions. I use excel constantly so will be able to help give you formulas for this. Obviously as you know - technically risking 1% of account on formula would go on for ever and ever (in principle) as the risk would become so low it wouldn't be worth trading, it would be pennies. You would have a cut off price in mind and this would ultimately give you your drawdown amount. Skype me with a mic and I'll talk you through it if I can help at all.

Regards,

Lee

 

[scose-no-doubt]

There you go, son.

Just change the consecutive losses manually until you can get the solver add-in for excel and familiarise yourself with it then you can use that instead.

 

[D.Gilberto]

Hi domgilbert,

If I were to post back I just know it would bring up many different questions. I use excel constantly so will be able to help give you formulas for this. Obviously as you know - technically risking 1% of account on formula would go on for ever and ever (in principle) as the risk would become so low it wouldn't be worth trading, it would be pennies. You would have a cut off price in mind and this would ultimately give you your drawdown amount. Skype me with a mic and I'll talk you through it if I can help at all.

Regards,

Lee

Hey Lee,

Yeah I appreciate this, I think you`re right regarding the cut off price, personally I wouldnt be interested if my account were to reach a drawdown of a maximum of 90%?

So to keep things simple, if we took £100.00 (without considering compound) and the balance was to negate as far as £10.00 that is what I would be wanting to find out how many trades (with compound) it would take me to lose consecutively at 1% risk per trade....?

Although with this said I am interested to see £5k.

I appreciate it`ll be in the hundreds but im sure you can see from my original post I am wanting to compound in line with not only my risk profile of 1% but risk of ruin, therefore giving me a theoretical advantage by increasing my risk per trade relevant to the account balance and its performance (whether that be positive or negative)....


I dont have a microphone but funnily enough Im looking at picking one up this weekend. Would you think what i`m asking for a difficult process in creating the formula itself?

Thanks!

 

[D.Gilberto]

There you go, son.

Just change the consecutive losses manually until you can get the solver add-in for excel and familiarise yourself with it then you can use that instead.

Aw blinder, thank you very much! Very kind of you, appreciate it

 

[scose-no-doubt]

No probs.

 

[D.Gilberto]

Ok so i`ve actually decided to spend a lot of time on sorting through some (what I find) complex formulas (I know in reality they`re easy to people familiar with excel!)

If anyone would like to see where im stuck at please find attached the excel spreadsheet.

I guess what it is im trying to do is make the spreadsheet reflect "on the fly" my daily performance when I upload a new spreadsheet direct from my broker onto this excel sheet.

In turn, im wanting to see based upon my performance, what amount of risk I should be trading at per trade if im down on my account and likewise if Im up.

If you look at the top hopefully that makes it clearer if you play around with the top table!


Obviously any input would be greatly appreciated, not to mention any leg work done here that is shared; if you dont already have a sound statistical record analysis and can help, then any progress on this is yours to keep aswell!

(ive uploaded it with $50.00 to keep it simple and note that the bottom table is in no way connected with the top table: would like to see this somehow though!?)

 

[SuddenDeath]

in Victor Niederhoffer's book "the education of a speculator" he has a formula for risk of ruin,

 

[Oppi1904]

You are almost there! Just use the spreadsheet provided by scose-no-doubt. Then apply Goal Seek (What If Analysis). In the box that appears set the the value of the closing capital equal to 0 or any amount close it by changing the cell (number of consecutive losses). Goal Seek will give you the number of consecutive losses you have to suffer in order your closing capital to be equal to the amount that you have set.

This approach is called "Trial and Error". It is very well known to researchers.

Remember, you don't have to set the value of the closing capital equal to 0 rather than another amount of money which is more realistic. Say 100. If you set it to 0, that means your capital has to go from 1 to 0 with a 1% maximum loss per trade. For a capital of 1 units this means a risk per trade of 0.01 (cents, pennies, whatever). This is not realistic in financial markets. You cannot put on a trade and have a stop of 1 cent. This is not applicable. So setting the closing capital close to 100 you get a more realistic result. That is because if you started with a capital of 5,000 and now you are left with 100, you pretty much have blown up your account :cheesy:

I have done some replications :

Setting the closing capital equal to 100 you get the number 389.243. That means you need nearly 390 consecutive losses to reach that closing capital. If you set it equal to 10 you need 618.3479 (619) consecutive losses. Setting it to 1 you get 847.4368 (848) consecutive losses. Setting it to 0 you get 1557.132 (1558) consecutive losses. See the differences? That is because of the different magnitudes of the losses.

I hope that helps you. Have fun playing around with Goal seek!

 

[SuddenDeath]

You are almost there! Just use the spreadsheet provided by scose-no-doubt. Then apply Goal Seek (What If Analysis). In the box that appears set the the value of the closing capital equal to 0 or any amount close it by changing the cell (number of consecutive losses). Goal Seek will give you the number of consecutive losses you have to suffer in order your closing capital to be equal to the amount that you have set.

This approach is called "Trial and Error". It is very well known to researchers.

Remember, you don't have to set the value of the closing capital equal to 0 rather than another amount of money which is more realistic. Say 100. If you set it to 0, that means your capital has to go from 1 to 0 with a 1% maximum loss per trade. For a capital of 1 units this means a risk per trade of 0.01 (cents, pennies, whatever). This is not realistic in financial markets. You cannot put on a trade and have a stop of 1 cent. This is not applicable. So setting the closing capital close to 100 you get a more realistic result. That is because if you started with a capital of 5,000 and now you are left with 100, you pretty much have blown up your account :cheesy:

I have done some replications :

Setting the closing capital equal to 100 you get the number 389.243. That means you need nearly 390 consecutive losses to reach that closing capital. If you set it equal to 10 you need 618.3479 (619) consecutive losses. Setting it to 1 you get 847.4368 (848) consecutive losses. Setting it to 0 you get 1557.132 (1558) consecutive losses. See the differences? That is because of the different magnitudes of the losses.

I hope that helps you. Have fun playing around with Goal seek!

there is a formula for risk of ruin in victor niederhoffer's book "the education of a speculator" u want me to post formula here?

 

[D.Gilberto]

there is a formula for risk of ruin in victor niederhoffer's book "the education of a speculator" u want me to post formula here?

Thanks guys, really good stuff!


If you wouldn't mind, yes, the formula would be awesome to check out!

Please keep in mind I am a novice with excel, I understand the principles of basic formula` but im not too good with how to apply them, if that makes sense

 

[Liquid validity]

there is a formula for risk of ruin in victor niederhoffer's book "the education of a speculator" u want me to post formula here?

Didn't do Vic much good did it.
Published the same year his fund went down the chute:
Victor Niederhoffer - Wikipedia, the free encyclopedia
Blew up again in 2007.

There are much better people to read up on imo.
Although he is a good example of crap risk management, and what not to do.
So there is some worth from a learn from others mistakes perspective.

 

[SuddenDeath]

Didn't do Vic much good did it.
Published the same year his fund went down the chute:
Victor Niederhoffer - Wikipedia, the free encyclopedia
Blew up again in 2007.

There are much better people to read up on imo.
Although he is a good example of crap risk management, and what not to do.
So there is some worth from a learn from others mistakes perspective.

but he did make approx $300mill before going bust

 

[Liquid validity]

but he did make approx $300mill before going bust

Just to be clear, I'm not having a pop
Yes he made money and had the likes of Soros in his fund.
Point is plenty survived the times that caught him out.

1997 was marked by him returning funds to Soros,
then invested the remaining 100m in, and I quote:
"areas where Niederhoffer later admitted that he did not have much expertise."
Namely Thai banking sector, along with October 27 1997 DOW collapse.

To blow up twice when many of his high profile contemporaries survived
is the reason I struggle to think of Niederhoffer as anything other than an
example of poor risk management.

As you say their is still merit in some things he says, even if you blow 2 funds
making 300m is more than most will ever achieve for sure.
I'd have 100 times more respect if he had only made 75m but was still in the game...
For me Niederhoffer is a high stakes gambler, nothing more.
As I said earlier though, you can learn a lot about how not to manage risk...

 

[SuddenDeath]

Just to be clear, I'm not having a pop
Yes he made money and had the likes of Soros in his fund.
Point is plenty survived the times that caught him out.

1997 was marked by him returning funds to Soros,
then invested the remaining 100m in, and I quote:
"areas where Niederhoffer later admitted that he did not have much expertise."
Namely Thai banking sector, along with October 27 1997 DOW collapse.

To blow up twice when many of his high profile contemporaries survived
is the reason I struggle to think of Niederhoffer as anything other than an
example of poor risk management.

As you say their is still merit in some things he says, even if you blow 2 funds
making 300m is more than most will ever achieve for sure.
I'd have 100 times more respect if he had only made 75m but was still in the game...
For me Niederhoffer is a high stakes gambler, nothing more.
As I said earlier though, you can learn a lot about how not to manage risk...

the lesson maybe to retire once u have made $299million