Max Drawdown - No VBA please

[PeterBrazel]

I am looking at some performance indicators on trading transactions and want to calculate max drawdown. Max Drawdown=a loss in equity from a previous high. E.G. If you were climbing a 29000ft mountain peak and you fell down 2000 feet then the 2000 feet would be the equivalent of a drwadown.

I have a database of transactions that contain column M=Nett proceeds of each transaction and column N=Account balance.

Not sure how to go about this. Remember this is not a mountain but a mountain range. Equity is constantly increasing and decreasing.

Bit tricky.

Thanks in advance.

 

[DBA]

Peter,

Can you add a column "O" which gives you on each line -
The difference between the total of the current line at columns "M" & "N" and the previous line.
Then use the MAX() over column "O" to give you the answer?

Regards,

DBA

 

[PeterBrazel]

Either I do not understand or it does not work.

Given that I have Column M =Transaction result i.e. profit or loss on the transaction. Column N=running portfolio balance. What formula are you suggesting.

Thanks again in advance.

Peter Brazel, Shepparton

 

[RichardS]

Could you possible post some sample data, and expected results?

Richard, Hamilton (Victoria is taking over!!)

 

[DBA]

Peter,

I was assuming that you were able to see (or at least be able to work out) the DrawDown somehow for each transaction based on the data you already have, then try to work out which was the highest value of those DrawDowns.

Is that a laymans view of it or have I missed what you are trying to acheive?

(I think the forum is being overrun by Victorians this afternoon) )

Regards,

DBA

 

[PeterBrazel]

OK here goes. This is the first group of transactions
Col M Col N Col O
Balance Fwd 41,354.00
-3,936.00 37,418.00
-1,236.00 36,182.00
14.00 36,196.00
4,964.00 41,160.00
5000.00 46160.00
-8000.00 38160.00

I just need to know what you are suggesting for column O at this stage.

We need to know what was the highest peak and then what was the biggest fall from that peak.

Thanks.

 

[DBA]

Peter,

Sorry I just re-read and realised that column M already has the data I was trying to work out in column O so you won't need it.

Will this give you the answer?

=MIN(M:M)

Regards,

DBA

 

[PeterBrazel]

No that will give me my biggest loss not my max drawdown.

 

[RichardS]

So you need to know the Maximum level the portfolio balance reached, and the minimum it reached AFTER that maximum?

=MAX(N:N)

will give the peak of your portfolio performance. Just trying to get my head around how we restrict the search for the MIN to those portfolio balances AFTER the maximum is found.

Richard

 

[PeterBrazel]

Richard,

Yes its all Vics today. Thank you for taking time with this. I think you now understand what I am trying to do. Sometimes I lose it myself. I am getting these KPI's from a trading book and trying to implement them for my own benefit.

Yes I guess MAX(N:N) will give us our highest peak. Now what is our greatest fall from that peak. Is it that peak less our biggest loss. If so that would be MIN(M:M) but I am not so sure that this is right.
I guess it is our greatest fall after that peak occurred is it.

Peter Brazel